↑↓ 选择↵ 打开⌫ 切换范围完整搜索

PG.CENTER 连接 PostgreSQL 文档、百科与生态知识。由 Pigsty 维护。

Wiki / SQL 命令

ALTER DEFAULT PRIVILEGES

定义默认访问权限

英文手册覆盖始于 PostgreSQL 6.4. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

9.09.19.29.39.49.59.61011121314151617181920
anchor
SQL-ALTERDEFAULTPRIVILEGES
file
sql-alterdefaultprivileges.html
lang
en
name
ALTER DEFAULT PRIVILEGES
purpose
define default access privileges
purpose zh
未知
sections same as
未知
slug
18

语法

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 | MAINTAIN }
[, ...] | 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 ]

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

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | 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 ]

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECTS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
语法图

从左向右阅读；分支表示选择，旁路表示可选，循环表示重复。点击带下划线的参数可打开对应规则。

ALTER DEFAULT PRIVILEGES FOR ROLE USER target_role , IN SCHEMA schema_name , abbreviated_grant_or_revoke
abbreviated_grant_or_revoke
GRANT SELECT INSERT UPDATE DELETE TRUNCATE REFERENCES TRIGGER MAINTAIN , 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 GRANT SELECT UPDATE , ALL PRIVILEGES ON LARGE OBJECTS TO GROUP role_name PUBLIC , WITH GRANT OPTION REVOKE GRANT OPTION FOR SELECT INSERT UPDATE DELETE TRUNCATE REFERENCES TRIGGER MAINTAIN , 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 REVOKE GRANT OPTION FOR SELECT UPDATE , ALL PRIVILEGES ON LARGE OBJECTS FROM GROUP role_name PUBLIC , CASCADE RESTRICT

版本定义 PG 18

ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
其中 abbreviated_grant_or_revoke 是下列之一：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | 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 ]

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

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | 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 ]

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

描述

ALTER DEFAULT PRIVILEGES允许你设置将应用于未来创建对象的权限。（它不会影响已经存在的对象上已分配的权限。）权限既可以全局设置（即，对当前数据库中未来创建的所有对象生效），也可以只针对指定模式中未来创建的对象设置。

虽然你可以修改自己的默认权限，也可以修改你所属角色的默认权限，但在创建对象时，新对象的权限只受当前角色的默认权限影响，不会从当前角色所属的任何角色继承。

如第 5.8 节中所述，任何对象类型的默认权限通常都会将所有可授予的权限授予对象拥有者，并且也可能向PUBLIC授予某些权限。不过，这种行为可以通过 ALTER DEFAULT PRIVILEGES修改全局默认权限来改变。

当前，只有模式、表（包括视图和外部表）、序列、函数、类型（包括域）以及大对象的权限可以修改。对于此命令，函数包括聚合函数和过程。此命令中的FUNCTIONS和 ROUTINES等价。（今后更推荐使用ROUTINES，因为这是同时指代函数和过程的标准术语。在较早的PostgreSQL版本中，只允许使用FUNCTIONS。无法分别为函数和过程设置默认权限。）

按模式指定的默认权限会叠加到该对象类型的全局默认权限之上。这意味着，如果某项权限是在全局范围内授予的（无论是默认授予，还是根据此前未指定模式的ALTER DEFAULT PRIVILEGES命令授予的），就不能按模式将其撤销。按模式执行的REVOKE 只适用于抵消此前按模式执行的GRANT所产生的效果。

参数

target_role

修改由target_role创建的对象的默认权限；如果省略，则为当前角色。

schema_name

一个现有模式的名称。如果指定，将修改以后在该模式中创建的对象的默认权限。如果省略IN SCHEMA，则修改全局默认权限。在为模式和大对象设置权限时，不允许使用IN SCHEMA，因为模式不能嵌套，而大对象不属于任何模式。

role_name

要为其授予或撤销权限的现有角色的名称。此参数以及 abbreviated_grant_or_revoke 中的所有其他参数，均按GRANT或REVOKE中的描述工作，只不过这里设置的是整类对象的权限，而不是特定的具名对象。

注解

使用psql的\ddp命令，可以获取现有默认权限分配的信息。权限显示的含义与第 5.8 节中针对\dp的说明相同。

如果你希望删除一个其默认权限已被修改的角色，则必须先撤销对其默认权限所做的更改，或者使用DROP OWNED BY 去除该角色的默认权限条目。

示例

为你后续在模式myschema中创建的所有表（以及视图）向所有人授予 SELECT 权限，并且也允许角色webuser对它们执行 INSERT：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;

撤销上述操作，使后续创建的表不再拥有任何超出正常情况的权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;

移除通常会在函数上授予的公共 EXECUTE 权限，使后续由角色admin创建的所有函数不再自动具有该权限：

ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

但请注意，你不能用仅限单个模式的命令实现这一效果。下面这条命令不会产生作用，除非它是在撤销与之对应的GRANT：

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

这是因为按模式设置的默认权限只能向全局设置增加权限，不能移除由全局设置授予的权限。

兼容性

在 SQL 标准中没有ALTER DEFAULT PRIVILEGES语句。

另见

GRANT, REVOKE

比较版本

完整来源事实

synopsis html

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } <em class="replaceable"><code>target_role</code></em> [, ...] ]
    [ IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] ]
    <em class="replaceable"><code>abbreviated_grant_or_revoke</code></em>

<span class="phrase">where <em class="replaceable"><code>abbreviated_grant_or_revoke</code></em> is one of:</span>

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | CREATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMAS
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECTS
    TO { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SEQUENCES
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTIONS | ROUTINES }
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON TYPES
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { USAGE | CREATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMAS
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECTS
    FROM { [ GROUP ] <em class="replaceable"><code>role_name</code></em> | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

synopsis text

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 | MAINTAIN }
[, ...] | 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 ]

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

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | 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 ]

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

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · a709ab85

正文语言: zh-Hans · 1b5ca64c