Postgresql 中文操作指南

GRANT

GRANT — 定义访问权限

GRANT — define access privileges

Synopsis

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
    ON PARAMETER configuration_parameter [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]
    [ GRANTED BY role_specification ]

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
    [ GRANTED BY role_specification ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

Description

GRANT 命令有两种基本变体：一种授予对数据库对象（表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程、过程语言、大对象、配置参数、架构、表空间或类型）的特权，另一种授予角色成员资格。这些变体在很多方面相似，但它们有足够的差异可以分开描述。

The GRANT command has two basic variants: one that grants privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, large object, configuration parameter, schema, tablespace, or type), and one that grants membership in a role. These variants are similar in many ways, but they are different enough to be described separately.

GRANT on Database Objects

GRANT 命令的此变体将对数据库对象的特权授予给一个或多个角色。这些权限将添加到已经授予的权限（如果存在）中。

This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those already granted, if any.

关键词 PUBLIC 表明将授予所有角色特权，包括以后可能创建的角色。 PUBLIC 可以被认为是一个隐式定义的组，它始终包含所有角色。任何特定角色都将获得直接授予给它的权限、授予当前其成员的任何角色的权限以及授予 PUBLIC 的权限。

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

如果指定了 WITH GRANT OPTION ，则特权接收者可以将其授予其他人。如果没有授予选项，则接收者无法这样做。无法向 PUBLIC 授予授予选项。

If WITH GRANT OPTION is specified, the recipient of the privilege can in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options cannot be granted to PUBLIC.

如果指定了 GRANTED BY ，则指定的授权者必须是当前用户。该子句目前仅以此形式存在，以兼容 SQL。

If GRANTED BY is specified, the specified grantor must be the current user. This clause is currently present in this form only for SQL compatibility.

不需要向对象的拥有者（通常是创建该对象的使用者）授予特权，因为拥有者默认拥有所有特权。（但是，为了安全，拥有者可以选择撤销他们自己的一些特权。）

There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default. (The owner could, however, choose to revoke some of their own privileges for safety.)

放弃对象或以任何方式更改其定义的权利不被视为可授予的特权；它是所有者固有的，不能授予或撤销。（但是，可以通过授予或撤销对拥有对象的成员角色的成员资格获得类似的效果；请参见下文。）拥有者也隐式拥有该对象的全部授予选项。

The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.

可能的权限包括：

The possible privileges are:

  • SELECT_INSERT_UPDATE_DELETE_TRUNCATE_REFERENCES_TRIGGER_CREATE_CONNECT_TEMPORARY_EXECUTE_USAGE_SET__ALTER SYSTEM

  • TEMP

    • Alternative spelling for TEMPORARY.

  • ALL PRIVILEGES

    • Grant all of the privileges available for the object’s type. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

FUNCTION 语法适用于普通函数、聚合函数和窗口函数，但不适用于过程；针对这些函数，请使用 PROCEDURE 。或者，可以使用 ROUTINE 来引用函数、聚合函数、窗口函数或过程，无论其确切类型如何。

The FUNCTION syntax works for plain functions, aggregate functions, and window functions, but not for procedures; use PROCEDURE for those. Alternatively, use ROUTINE to refer to a function, aggregate function, window function, or procedure regardless of its precise type.

还有一种选项可以授予一个或多个架构中所有相同类型对象的权限。此功能目前仅支持表、序列、函数和过程。 ALL TABLES 也会影响视图和外部表，就像特定的对象 GRANT 命令一样。 ALL FUNCTIONS 也影响聚合函数和窗口函数，但不影响过程，再次就像特定的对象 GRANT 命令一样。使用 ALL ROUTINES 包含过程。

There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, functions, and procedures. ALL TABLES also affects views and foreign tables, just like the specific-object GRANT command. ALL FUNCTIONS also affects aggregate and window functions, but not procedures, again just like the specific-object GRANT command. Use ALL ROUTINES to include procedures.

GRANT on Roles

GRANT 命令的此变体将一个角色的成员资格授予一个或多个其他角色，并且修改了成员资格选项 SET 、 INHERIT 和 ADMIN ；请参阅 Section 22.3 了解详情。角色中的成员资格很重要，因为它可能会允许角色的每个成员访问授予角色的特权，并且可能还允许更改角色本身。但是，实际授予的权限取决于与授权相关的选项。若要修改现有成员资格的选项，只需指定具有更新选项值的成员资格即可。

This variant of the GRANT command grants membership in a role to one or more other roles, and the modification of membership options SET, INHERIT, and ADMIN; see Section 22.3 for details. Membership in a role is significant because it potentially allows access to the privileges granted to a role to each of its members, and potentially also the ability to make changes to the role itself. However, the actual permissions conferred depend on the options associated with the grant. To modify that options of an existing membership, simply specify the membership with updated option values.

下面描述的每个选项均可以设置为 TRUE 或 FALSE 。关键词 OPTION 被接受为 TRUE 的同义词，所以 WITH ADMIN OPTION 是 WITH ADMIN TRUE 的同义词。当更改现有成员资格时，省略选项将导致保留当前值。

Each of the options described below can be set to either TRUE or FALSE. The keyword OPTION is accepted as a synonym for TRUE, so that WITH ADMIN OPTION is a synonym for WITH ADMIN TRUE. When altering an existing membership the omission of an option results in the current value being retained.

ADMIN 选项允许成员反过来向其他人授予角色成员资格，并撤销角色成员资格。如果没有管理员选项，普通用户不能这样做。不认为角色对自己拥有 WITH ADMIN OPTION 。数据库超级用户可以向任何人授予或撤销任何角色的成员资格。此选项默认为 FALSE 。

The ADMIN option allows the member to in turn grant membership in the role to others, and revoke membership in the role as well. Without the admin option, ordinary users cannot do that. A role is not considered to hold WITH ADMIN OPTION on itself. Database superusers can grant or revoke membership in any role to anyone. This option defaults to FALSE.

INHERIT 选项控制新成员资格的继承状态；有关继承的详细信息，请参见 Section 22.3 。如果将其设置为 TRUE ，它将导致新成员从被授予的角色中继承。如果设置为 FALSE ，则新成员不继承。如果在创建新的角色成员资格时未指定，则默认为新成员的继承属性。

The INHERIT option controls the inheritance status of the new membership; see Section 22.3 for details on inheritance. If it is set to TRUE, it causes the new member to inherit from the granted role. If set to FALSE, the new member does not inherit. If unspecified when creating a new role membership, this defaults to the inheritance attribute of the new member.

SET 选项（如果设置为 TRUE ）允许成员使用 SET ROLE 命令切换到被授权的角色。如果角色是另一个角色的间接成员，只有在每个授权都有 SET TRUE 的链的情况下，它才能使用 SET ROLE 更改为该角色。此选项默认为 TRUE 。

The SET option, if it is set to TRUE, allows the member to change to the granted role using the SET ROLE command. If a role is an indirect member of another role, it can use SET ROLE to change to that role only if there is a chain of grants each of which has SET TRUE. This option defaults to TRUE.

要创建属于另一个角色的对象或将现有对象的归属权授予另一个角色，您必须有向该角色 SET ROLE 的能力；否则，诸如 ALTER …​ OWNER TO 或 CREATE DATABASE …​ OWNER 的命令将失败。但是，继承了角色权限但没有向该角色 SET ROLE 的能力的用户可以通过操作该角色拥有的现有对象获得对该角色的完全访问权限（例如，他们可以重新定义现有函数以充当特洛伊木马）。因此，如果要继承角色的特权但不能通过 SET ROLE 访问它们，它不应该拥有任何 SQL 对象。

To create an object owned by another role or give ownership of an existing object to another role, you must have the ability to SET ROLE to that role; otherwise, commands such as ALTER …​ OWNER TO or CREATE DATABASE …​ OWNER will fail. However, a user who inherits the privileges of a role but does not have the ability to SET ROLE to that role may be able to obtain full access to the role by manipulating existing objects owned by that role (e.g. they could redefine an existing function to act as a Trojan horse). Therefore, if a role’s privileges are to be inherited but should not be accessible via SET ROLE, it should not own any SQL objects.

如果指定了 GRANTED BY ，则将记录此授予是由指定的角色完成的。用户只有在拥有该角色的权限时才能将授予归因于另一个角色。记录为授权方的角色必须在目标角色上拥有 ADMIN OPTION ，除非它是引导超级用户。当记录授权方是引导超级用户之外的其他角色时，它取决于授权方是否继续在角色上拥有 ADMIN OPTION ；因此，如果吊销了 ADMIN OPTION ，则必须也吊销依赖的授予。

If GRANTED BY is specified, the grant is recorded as having been done by the specified role. A user can only attribute a grant to another role if they possess the privileges of that role. The role recorded as the grantor must have ADMIN OPTION on the target role, unless it is the bootstrap superuser. When a grant is recorded as having a grantor other than the bootstrap superuser, it depends on the grantor continuing to possess ADMIN OPTION on the role; so, if ADMIN OPTION is revoked, dependent grants must be revoked as well.

与权限不同，不能向 PUBLIC 授予角色中的成员资格。另外请注意，此命令形式不允许在 role_specification 中出现噪音词 GROUP 。

Unlike the case with privileges, membership in a role cannot be granted to PUBLIC. Note also that this form of the command does not allow the noise word GROUP in role_specification.

Notes

REVOKE 命令用于吊销访问权限。

The REVOKE command is used to revoke access privileges.

从 PostgreSQL 8.1 开始，用户和组的概念已经被统一到称为角色的单一的实体类型中。因此，不再需要使用关键词 GROUP 来识别受让人是用户还是组。 GROUP 仍然允许在命令中使用，但它是一个噪音词。

Since PostgreSQL 8.1, the concepts of users and groups have been unified into a single kind of entity called a role. It is therefore no longer necessary to use the keyword GROUP to identify whether a grantee is a user or a group. GROUP is still allowed in the command, but it is a noise word.

如果用户对特定列或其整个表格持有 SELECT 、 INSERT 等权限，则他们可以对其进行操作。在表级别授予权限，然后为一列吊销此权限，它不会完成人们希望完成的操作：表级别授予不受列级别操作的影响。

A user may perform SELECT, INSERT, etc. on a column if they hold that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.

当对象的非所有者尝试对对象执行 GRANT 权限时，如果用户对对象没有任何权限，则将直接导致命令失败。只要提供了一些权限，命令将继续执行，但用户只能授予他们具有授予选项的那些权限。如果没有持有授予选项，则 GRANT ALL PRIVILEGES 形式将发出警告消息，而其他形式将在该命令中未持有任何特指权限的授予选项时发出警告。（原则上，这些陈述也适用于对象所有者，但由于所有者始终被视为持有所有授予选项，因此永远不会出现这种情况。）

When a non-owner of an object attempts to GRANT privileges on the object, the command will fail outright if the user has no privileges whatsoever on the object. As long as some privilege is available, the command will proceed, but it will grant only those privileges for which the user has grant options. The GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the privileges specifically named in the command are not held. (In principle these statements apply to the object owner as well, but since the owner is always treated as holding all grant options, the cases can never occur.)

应该注意的是，数据库超级用户可以访问所有对象，而不管对象权限设置如何。这可以与 Unix 系统中 root 的权限进行比较。与 root 一样，除非绝对必要，否则以超级用户身份操作是不明智的。

It should be noted that database superusers can access all objects regardless of object privilege settings. This is comparable to the rights of root in a Unix system. As with root, it’s unwise to operate as a superuser except when absolutely necessary.

如果超级用户选择发出 GRANT 或 REVOKE 命令，则此命令将被执行，就好像此命令是由受影响对象的所属者发出的。特别是，通过此命令授予的权限将显示为由对象所有者授予的权限。（对于角色成员资格，成员资格显示为由引导超级用户授予的。）

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner. (For role membership, the membership appears to have been granted by the bootstrap superuser.)

GRANT 和 REVOKE 也可以由不是受影响对象所有者但却属于拥有此对象的所属者的角色或属于在该对象上持有权限 WITH GRANT OPTION 的所属者的角色执行。在这种情况下，将记录权限是由实际拥有该对象或持有权限 WITH GRANT OPTION 的所属者授予的权限。例如，如果表 t1 由角色 g1 （角色 u1 是此角色的成员）所有，则 u1 可以向 u2 授予 t1 中的权限，但这些权限将显示为直接由 g1 授予的。角色 g1 中的任何其他成员以后都可以吊销这些权限。

GRANT and REVOKE can also be done by a role that is not the owner of the affected object, but is a member of the role that owns the object, or is a member of a role that holds privileges WITH GRANT OPTION on the object. In this case the privileges will be recorded as having been granted by the role that actually owns the object or holds the privileges WITH GRANT OPTION. For example, if table t1 is owned by role g1, of which role u1 is a member, then u1 can grant privileges on t1 to u2, but those privileges will appear to have been granted directly by g1. Any other member of role g1 could revoke them later.

如果执行 GRANT 的角色通过多个角色成员资格路径间接持有所需的权限，则无法指定将记录哪个包含角色授予了该权限。在这些情况下，最佳做法是使用 SET ROLE 成为您希望执行 GRANT 的特定角色。

If the role executing GRANT holds the required privileges indirectly via more than one role membership path, it is unspecified which containing role will be recorded as having done the grant. In such cases it is best practice to use SET ROLE to become the specific role you want to do the GRANT as.

对表授予权限不会自动将权限扩展到表使用的任何序列，包括与 SERIAL 列关联的序列。对序列的权限必须单独设置。

Granting permission on a table does not automatically extend permissions to any sequences used by the table, including sequences tied to SERIAL columns. Permissions on sequences must be set separately.

有关特定权限类型的详细信息以及如何检查对象的权限，请参阅 Section 5.7 。

See Section 5.7 for more information about specific privilege types, as well as how to inspect objects' privileges.

Examples

向表 films 中的所有用户授予插入权限：

Grant insert privilege to all users on table films:

GRANT INSERT ON films TO PUBLIC;

向视图 kinds 中的用户 manuel 授予所有可用权限：

Grant all available privileges to user manuel on view kinds:

GRANT ALL PRIVILEGES ON kinds TO manuel;

请注意，虽然上述操作如果由 kinds 的超级用户或所有者执行时确实会授予所有权限，但如果由其他人执行时，它只会授予该其他人拥有授予选项的那些权限。

Note that while the above will indeed grant all privileges if executed by a superuser or the owner of kinds, when executed by someone else it will only grant those permissions for which the someone else has grant options.

向用户 joe 授予角色 admins 中的成员资格：

Grant membership in role admins to user joe:

GRANT admins TO joe;

Compatibility

根据 SQL 标准， ALL PRIVILEGES 中的 PRIVILEGES 关键字是必需的。SQL 标准不支持对每个命令中的多个对象设置权限。

According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is required. The SQL standard does not support setting the privileges on more than one object per command.

PostgreSQL 允许对象所有者吊销自己的普通权限：例如，表所有者可以通过吊销自己的 INSERT 、 UPDATE 、 DELETE 和 TRUNCATE 权限来使表对自己只读。这在 SQL 标准中是不可能的。原因是 PostgreSQL 将所有者的权限视为所有者授予给自己的权限；因此他们也可以吊销这些权限。在 SQL 标准中，所有者的权限是由假定的实体“_SYSTEM”授予的。所有者不是“_SYSTEM”，因此不能吊销这些权利。

PostgreSQL allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read-only to themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE privileges. This is not possible according to the SQL standard. The reason is that PostgreSQL treats the owner’s privileges as having been granted by the owner to themselves; therefore they can revoke them too. In the SQL standard, the owner’s privileges are granted by an assumed entity “_SYSTEM”. Not being “_SYSTEM”, the owner cannot revoke these rights.

根据 SQL 标准，可以将授予选项授予 PUBLIC ；PostgreSQL 仅支持将授予选项授予角色。

According to the SQL standard, grant options can be granted to PUBLIC; PostgreSQL only supports granting grant options to roles.

SQL 标准允许 GRANTED BY 选项仅指定 CURRENT_USER 或 CURRENT_ROLE 。其他变体是 PostgreSQL 扩展。

The SQL standard allows the GRANTED BY option to specify only CURRENT_USER or CURRENT_ROLE. The other variants are PostgreSQL extensions.

SQL 标准规定了对其他类型对象（字符集、校对、转换）采用 USAGE 权限。

The SQL standard provides for a USAGE privilege on other kinds of objects: character sets, collations, translations.

在 SQL 标准中，序列仅具有 USAGE 权限，该权限控制 NEXT VALUE FOR 表达式的使用，这相当于 PostgreSQL 中的 nextval 函数。序列权限 SELECT 和 UPDATE 是 PostgreSQL 的扩展。序列 USAGE 权限对 currval 函数的应用也是 PostgreSQL 的扩展（函数本身也是）。

In the SQL standard, sequences only have a USAGE privilege, which controls the use of the NEXT VALUE FOR expression, which is equivalent to the function nextval in PostgreSQL. The sequence privileges SELECT and UPDATE are PostgreSQL extensions. The application of the sequence USAGE privilege to the currval function is also a PostgreSQL extension (as is the function itself).

对数据库、表空间、模式、语言和配置参数的权限是 PostgreSQL 的扩展。

Privileges on databases, tablespaces, schemas, languages, and configuration parameters are PostgreSQL extensions.

See Also

REVOKE 、 ALTER DEFAULT PRIVILEGES

REVOKE, ALTER DEFAULT PRIVILEGES