ALTER DEFAULT PRIVILEGES

功能描述

设置应用于将来要创建的对象的权限（不会影响现有对象的权限）。

用户只可以修改由用户本身或者用户本身所属的角色所创建的对象的默认权限，这些权限可以对全局范围设置（即数据库中创建的所有对象），也可以为指定模式下的对象设置。

查看有关数据库用户的默认权限的信息，可以查询PG_DEFAULT_ACL系统表。

注意事项

• 目前只支持表（包括视图）、序列、函数和类型（包括域）的权限更改。
• 不支持将外表的VACUUM，DROP，ALTER的权限赋予用户。

语法格式

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 ]

参数说明

1

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

示例

• 将创建在模式tpcds里的所有表（和视图）的SELECT权限授予每一个用户。

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

• 将tpcds下的所有表的插入权限授予用户jack。

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

• 撤销上述权限。

  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;

• 假设有两个用户test1、test2，如果需要test2用户对test1用户未来创建的表都有查询权限，可执行如下操作：
  1. 将test1的schema的权限赋予test2用户。

     1	GRANT usage, create ON SCHEMA test1 TO test2;

  2. 将test1用户下的表的查询权限赋予test2用户。

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

  3. test1用户创建表。

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

  4. 使test2用户执行查询。

     1 2 3 4 5

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

相关链接

GRANT，REVOKE