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

Grant INSERT privileges to bobr on all new tables created in the public schema by the current user (ybdadmin@yellowbrickcloud.com). You can use the ybsql \ddp command to display default access privileges. 

yellowbrick=> alter default privileges in schema public grant insert on tables to bobr;
ALTER DEFAULT PRIVILEGES
yellowbrick=> \ddp
                                Default access privileges
             Owner             | Schema | Type  |           Access privileges            
-------------------------------+--------+-------+----------------------------------------
 ybdadmin@yellowbrickcloud.com | public | table | bobr=a/"ybdadmin@yellowbrickcloud.com"
(1 row)

In this case, bobr has INSERT privilege (bobr=a) on tables owned by ybdadmin. See also ybsql Slash (\) Commands.

Now log in as bobr and grant SELECT privileges to user ybsql10:
yellowbrick=> alter default privileges in schema public grant select on tables to ybsql10;
ALTER DEFAULT PRIVILEGES
yellowbrick=> \ddp
                                Default access privileges
             Owner             | Schema | Type  |           Access privileges            
-------------------------------+--------+-------+----------------------------------------
 bobr                          | public | table | ybsql10=r/bobr
 ybdadmin@yellowbrickcloud.com | public | table | bobr=a/"ybdadmin@yellowbrickcloud.com"
(2 rows)

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 the owner of the database newdb. The users dbuser2 and dbuser3 belong to a role named dbrole. 
premdb=# yellowbrick=> create user dbuser1 login password 'dbuser1' role "ybdadmin@yellowbrickcloud.com";
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).