Postgresql 中文操作指南
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES — 定义默认访问权限
ALTER DEFAULT PRIVILEGES — define default access privileges
Synopsis
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | CREATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | CREATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SCHEMAS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]Description
ALTER DEFAULT PRIVILEGES 允许你设置将来创建的对象适用的权限。（它不影响已分配给现有的对象的权限。）可以全局设置权限（即对当前数据库中创建的所有对象）或仅对在指定模式中创建的对象进行权限设置。
ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas.
虽说你可以更改你自己的默认权限和你属于的角色的默认权限，但在对象创建时，新对象权限仅受当前角色的默认权限影响，并且不会从当前角色所属的任何角色继承。
While you can change your own default privileges and the defaults of roles that you are a member of, at object creation time, new object permissions are only affected by the default privileges of the current role, and are not inherited from any roles in which the current role is a member.
如 Section 5.7 中所述，任何对象类型的默认权限通常向对象所有者授予所有可授予的权限，并且可能也向 PUBLIC 授予一些权限。但是，可以通过使用 ALTER DEFAULT PRIVILEGES 更改全局默认权限，从而更改此行为。
As explained in Section 5.7, the default privileges for any object type normally grant all grantable permissions to the object owner, and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.
当前，仅可以更改模式、表（包括视图和外部表）、序列、函数和类型（包括域）的权限。对于此命令，函数包括聚合和过程。单词 FUNCTIONS 和 ROUTINES 在此命令中是等效的。( ROUTINES 是更优的选择，作为函数和过程的标准术语。在早期版本的 PostgreSQL 中，仅允许使用单词 FUNCTIONS 。不能为函数和过程单独设置默认权限。)
Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered. For this command, functions include aggregates and procedures. The words FUNCTIONS and ROUTINES are equivalent in this command. (ROUTINES is preferred going forward as the standard term for functions and procedures taken together. In earlier PostgreSQL releases, only the word FUNCTIONS was allowed. It is not possible to set default privileges for functions and procedures separately.)
按模式指定的默认权限会添加到特定对象类型的全局默认权限中。这意味着，如果按全局方式授予权限（无论默认还是根据未指定模式的先前的 ALTER DEFAULT PRIVILEGES 命令），则你无法按模式撤回权限。按模式 REVOKE 仅用于逆转按模式先前 GRANT 的影响。
Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type. This means you cannot revoke privileges per-schema if they are granted globally (either by default, or according to a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful to reverse the effects of a previous per-schema GRANT.
Parameters
-
target_role
-
Change default privileges for objects created by the target_role, or the current role if unspecified.
-
-
schema_name
-
The name of an existing schema. If specified, the default privileges are altered for objects later created in that schema. If IN SCHEMA is omitted, the global default privileges are altered. IN SCHEMA is not allowed when setting privileges for schemas, since schemas can’t be nested.
-
-
role_name
Notes
使用 psql 的 \ddp 命令获取关于现有默认权限分配的信息。权限显示的含义与 Section 5.7 中对 \dp 的解释相同。
Use psql's \ddp command to obtain information about existing assignments of default privileges. The meaning of the privilege display is the same as explained for \dp in Section 5.7.
如果你希望删除已经更改了默认权限的角色，则需要逆转其默认权限中的更改，或使用 DROP OWNED BY 来清除该角色的默认权限条目。
If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.
Examples
向每个人授予你随后在模式 myschema 中创建的所有表（和视图）的 SELECT 权限，并允许角色 webuser 在其中进行 INSERT 操作：
Grant SELECT privilege to everyone for all tables (and views) you subsequently create in schema myschema, and allow role webuser to INSERT into them too:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;撤销以上操作，以便随后创建的表不会有任何比普通权限的权限更的高的权限：
Undo the above, so that subsequently-created tables won’t have any more permissions than normal:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;删除通常在由角色 admin 随后创建的所有函数上授予的公共 EXECUTE 权限：
Remove the public EXECUTE permission that is normally granted on functions, for all functions subsequently created by role admin:
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;但是请注意，你可以使用仅限于一个模式的命令来 cannot 实现该效果。此命令没有任何效果，除非它正在撤回匹配 GRANT ：
Note however that you cannot accomplish that effect with a command limited to a single schema. This command has no effect, unless it is undoing a matching GRANT:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;这是因为按模式默认权限只能向全局设置添加权限，而不是删除其授予的权限。
That’s because per-schema default privileges can only add privileges to the global setting, not remove privileges granted by it.