ALTER DEFAULT PRIVILEGES

Function

ALTER DEFAULT PRIVILEGES allows you to set the permissions that will be used for objects to be created. It does not affect permissions assigned to existing objects.

A user can modify only the default permissions of objects created by the user or the role to which the user belongs. These permissions can be set globally (all objects created in the database) or for objects in a specified schema.

To view information about the default permissions of database users, query the system catalog PG_DEFAULT_ACL.

Precautions

• Only the permissions for tables (including views), sequences, functions, and types (including domains) can be altered.
• The VACUUM, DROP, and ALTER permissions on foreign tables cannot be granted to users.

Syntax

1
2
3
4

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke;

1
2
3
4
5

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE | VACUUM | ALTER | DROP } 
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES 
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]

1
2
3
4

GRANT { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON FUNCTIONS 
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]

1
2
3
4

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES 
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ]

1
2
3
4
5

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

1
2
3
4
5
6

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE | VACUUM | ALTER | DROP } 
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES 
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

1
2
3
4
5

REVOKE [ GRANT OPTION FOR ]
    { { EXECUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON FUNCTIONS 
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

1
2
3
4
5

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES 
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

1
2
3
4
5
6

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

Parameter Description

1

SELECT a.rolname, n.nspname FROM pg_authid as a, pg_namespace as n WHERE has_schema_privilege(a.oid, n.oid, 'CREATE');

Examples

• Grant the SELECT permission on all the tables (and views) in tpcds to every user.

  1	ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT SELECT ON TABLES TO PUBLIC;

• Grant the INSERT permission on all the tables in tpcds to user jack.

  1	ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack;

• Revoke the preceding permissions.

  1 2	ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE SELECT ON TABLES FROM PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE INSERT ON TABLES FROM jack;

• Assume that there are two users test1 and test2. If you require that user test2 can query tables created by user test1, execute the following statements:
  1. Grant the schema permission of user test1 to test2.

     1	GRANT usage, create ON SCHEMA test1 TO test2;

  2. Grant user test2 the permission to query tables of user test1.

     1	ALTER DEFAULT PRIVILEGES FOR USER test1 IN SCHEMA test1 GRANT SELECT ON tables TO test2;

  3. Create a table as user test1.

     1 2	SET ROLE test1 password '{password}'; CREATE TABLE test3( a int, b int);

  4. Enable the test2 user to perform the query.

     1 2 3 4 5

     SET ROLE test2 password '{password}'; SELECT * FROM test1.test3; a | b ---+--- (0 rows)

Helpful Links

GRANT, REVOKE