ALTER DEFAULT PRIVILEGES ​

Grant or revoke default access privileges on database objects that are created in the future. This command does not affect privileges defined on existing objects.

You can apply default privileges on the following objects:

• Tables and views (ON TABLES)
• Sequences (ON SEQUENCES)
• Stored procedures (ON PROCEDURES)

This command is very useful for databases and schemas in which new tables and other objects are created routinely as part of daily operations. By setting default privileges for users and roles, you avoid having to grant permissions on separate new objects as they are created. For example, you can make sure that users who belong to a specific role can automatically select from new temporary tables that are created in a specific schema.

You can alter privileges either for objects in the current database or for objects in the specified schema or schemas. If you specify default privileges on one or more schemas, these privileges are added to any global default privileges for the type of object in question.

ALTER DEFAULT PRIVILEGES
   [ FOR { ROLE | USER } creator_role [, ...] ]
   [ IN SCHEMA schema [, ...] ]
   { grant_command | revoke_command }

where grant_command is one of the following:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }
   [, ...] | ALL [ PRIVILEGES ] }
   ON TABLES
   TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
   [, ...] | ALL [ PRIVILEGES ] }
   ON SEQUENCES
   TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
   ON PROCEDURES
   TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]

and revoke_command is one of the following:

REVOKE [ GRANT OPTION FOR ]
   { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }
   [, ...] | ALL [ PRIVILEGES ] }
   ON TABLES
   FROM { role | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { { USAGE | SELECT | UPDATE }
   [, ...] | ALL [ PRIVILEGES ] }
   ON SEQUENCES
   FROM { role | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
   { EXECUTE | ALL [ PRIVILEGES ] }
   ON PROCEDURES
   FROM { role | PUBLIC } [, ...]
   [ CASCADE | RESTRICT ]

Note: The default privileges for any object type normally grant all permissions to the object owner, and may grant some privileges to PUBLIC as well. However, you can change this behavior by altering global default privileges with the ALTER DEFAULT PRIVILEGES command.

Before you can drop a role for which default privileges have been altered, you need to reverse the changes that were made. Alternatively, you can use the DROP OWNED BY command to remove the default privileges for the role (along with the objects owned by the role). For example:

premdb=# drop role role1;
ERROR:  role "role1" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role role1
premdb=# drop owned by role1;
DROP OWNED
premdb=# drop role role1;
DROP ROLE

Parameters ​

creator_role

Specify an existing role of which the current user is a member. If you do not specify FOR ROLE, the default target is the current role. This role specification defines an object creator. (It is not sufficient to be the object owner because ownership can be transferred.) Objects created by this role will be subject to the GRANT or REVOKE action that follows in the ALTER DEFAULT PRIVILEGES command. You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. (FOR USER is synonymous with FOR ROLE in this context.)

schema

Specify an existing schema so that default privileges are altered for objects that are subsequently created in that schema. If you do not specify IN SCHEMA, default privileges are altered for all schemas in the current database.

grant_command | revoke_command

In general, see the GRANT and REVOKE command descriptions for more information about the syntax that you can use, which is an abbreviated version of those two commands. Keep in mind that the ALTER DEFAULT PRIVILEGES command sets permissions for all objects of a given type, not for one or more named objects.

role_name

Specify the name of one or more existing roles that will be the receivers of the GRANT or REVOKE action. You can specify PUBLIC instead of a role name.

Examples ​

For example, grant all privileges to bobr on new tables and views that the current user creates:

premdb=# alter default privileges grant all on tables to bobr;
ALTER DEFAULT PRIVILEGES

Grant INSERT privileges to bobr on all new tables created by yellowbrick:

premdb=# alter default privileges for role yellowbrick grant insert on tables to bobr;
ALTER DEFAULT PRIVILEGES
premdb=# create table newtable(c1 int);
CREATE TABLE
premdb=# \c premdb bobr
Password for user bobr: 
You are now connected to database "premdb" as user "bobr".
premdb=> select * from newtable;
ERROR:  permission denied for relation newtable
premdb=> insert into newtable values(1);
INSERT 0 1

You can use the ybsql \ddp command to display default access privileges.

premdb=> \ddp
                  Default access privileges
   Owner    | Schema | Type  |        Access privileges        
-------------+--------+-------+---------------------------------
 yellowbrick |        | table | yellowbrick=arwdDxt/yellowbrick+
            |        |       | bobr=a/yellowbrick
(1 row)

In this case, bobr has INSERT privilege (bobr=a) on tables owned by yellowbrick.

Granting Schema-Level Privileges to Members of a Role ​

When you are setting up users and roles that will require access to specific schemas and tables, you may need to use a combination of GRANT commands and the ALTER DEFAULT PRIVILEGES command. This combination guarantees access to both existing objects and new objects when they are created.

In this example, dbuser1 is a superuser and owner of the database newdb. The users dbuser2 and dbuser3 belong to a role named dbrole.

premdb=# create user dbuser1 superuser login password 'dbuser1';
CREATE ROLE
premdb=# create user dbuser2 login password 'dbuser2';
CREATE ROLE
premdb=# create user dbuser3 login password 'dbuser3';
CREATE ROLE
premdb=# create role dbrole nologin role dbuser2, dbuser3;
CREATE ROLE
premdb=# create database newdb with owner=dbuser1;
CREATE DATABASE

Members of dbrole are granted permission to connect to newdb and usage on a new schema called dbo:

newdb=# grant connect on database newdb to dbrole;
GRANT
newdb=# create schema dbo;
CREATE SCHEMA
newdb=# grant usage on schema dbo to dbrole;
GRANT

Note: This GRANT USAGE statement must be issued explicitly by the schema owner for every applicable schema in the database. If this command is not run, only the schema owner has visibility to the underlying tables, procedures, and views. However, although this privilege allows the role to see objects within the schema, it does not grant SELECT on the tables in the schema.

Now dbuser1 creates a simple table in the dbo schema and inserts a value:

newdb=# create table dbo.table1(c1 int);
CREATE TABLE
newdb=# insert into dbo.table1 values(1);
INSERT 0 1
newdb=# select * from dbo.table1;
 c1 
----
  1
(1 row)

As expected, dbuser2 cannot select from the new table:

newdb=# \c newdb dbuser2
Password for user dbuser2: 
You are now connected to database "newdb" as user "dbuser2".
newdb=> select * from dbo.table1;
ERROR:  permission denied for relation table1

A quick solution to the problem is for dbuser1 to grant access to all tables in schema dbo for all members of dbrole:

newdb=> \c newdb dbuser1
Password for user dbuser1: 
You are now connected to database "newdb" as user "dbuser1".
newdb=# grant all on all tables in schema dbo to dbrole;
GRANT
newdb=# \c newdb dbuser2
Password for user dbuser2: 
You are now connected to database "newdb" as user "dbuser2".
newdb=> select * from dbo.table1;
 c1 
----
  1
(1 row)

This command is useful, but it only solves the problem for existing tables in that database and schema. When dbuser1 creates a new dbo table, dbuser2 and dbuser3 will not be able to access it. The solution is to run the following ALTER DEFAULT PRIVILEGES command, which allows all members of dbrole to access all future tables created by dbuser1:

newdb=# alter default privileges for user dbuser1 grant all privileges on tables to dbrole;
ALTER DEFAULT PRIVILEGES
newdb=# create table dbo.table2(c1 int);
CREATE TABLE
newdb=# insert into dbo.table2 values(1);
INSERT 0 1
newdb=# \c newdb dbuser3
Password for user dbuser3: 
You are now connected to database "newdb" as user "dbuser3".
newdb=> select * from dbo.table2;
 c1 
----
  1
(1 row)
newdb=# \c newdb dbuser2
Password for user dbuser2: 
You are now connected to database "newdb" as user "dbuser2".
newdb=> select * from dbo.table2;
 c1 
----
  1
(1 row)

Note that the ALTER DEFAULT PRIVILEGES command must be run by the object creator (user dbuser1 in this case).

Parent topic:SQL Commands