Postgresql 中文操作指南

5.7. Privileges #

创建对象时，将为其分配所有者。所有者通常是执行创建语句的角色。对于大多数类型的对象，初始状态是只有所有者（或超级用户）才能对对象执行任何操作。若要允许其他角色使用它，必须授予 privileges。

When an object is created, it is assigned an owner. The owner is normally the role that executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other roles to use it, privileges must be granted.

有不同类型的权限：SELECT，INSERT，UPDATE，DELETE，TRUNCATE，REFERENCES，TRIGGER，CREATE，CONNECT，TEMPORARY，EXECUTE，USAGE，SET 和 ALTER SYSTEM。适用于特定对象的权限因对象的类型（表格、函数等）而异。以下内容将更详细地显示这些权限的含义。以下部分和章节还将向您展示如何使用这些权限。

There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET and ALTER SYSTEM. The privileges applicable to a particular object vary depending on the object’s type (table, function, etc.). More detail about the meanings of these privileges appears below. The following sections and chapters will also show you how these privileges are used.

修改或销毁对象的对是对象所有者固有的，并且本身不可授予或撤销。（但是，与所有特权一样，该权利可以被所有权角色的成员继承；请参见 Section 22.3。）

The right to modify or destroy an object is inherent in being the object’s owner, and cannot be granted or revoked in itself. (However, like all privileges, that right can be inherited by members of the owning role; see Section 22.3.)

可以使用适合该对象的 ALTER 命令将对象分配给新的所有者，例如

An object can be assigned to a new owner with an ALTER command of the appropriate kind for the object, for example

ALTER TABLE table_name OWNER TO new_owner;

超级用户始终可以执行此操作；普通角色只有当他们同时是当前对象所有者（或继承所有者角色的权限）并能够 SET ROLE 到新的所有者角色时才能这样做。

Superusers can always do this; ordinary roles can only do it if they are both the current owner of the object (or inherit the privileges of the owning role) and able to SET ROLE to the new owning role.

使用 GRANT 命令分配权限。例如，如果 joe 是现有角色且 accounts 是现有表，则可以使用以下权限授予更新表的权限：

To assign privileges, the GRANT command is used. For example, if joe is an existing role, and accounts is an existing table, the privilege to update the table can be granted with:

GRANT UPDATE ON accounts TO joe;

编写 ALL 而不是特定权限将授予与对象类型相关的全部权限。

Writing ALL in place of a specific privilege grants all privileges that are relevant for the object type.

特殊“角色”名称 PUBLIC 可以用于向系统上的每个角色授予特权。同样，在数据库中有许多用户时，还可以设置“组”角色来帮助管理特权 - 有关详细信息，请参见 Chapter 22。

The special “role” name PUBLIC can be used to grant a privilege to every role on the system. Also, “group” roles can be set up to help manage privileges when there are many users of a database — for details see Chapter 22.

若要撤销先前授予的权限，请使用恰如其名的 REVOKE 命令：

To revoke a previously-granted privilege, use the fittingly named REVOKE command:

REVOKE ALL ON accounts FROM PUBLIC;

通常，只有对象的所有者（或超级用户）才能授予或撤销对对象上的权限。但是，可以授予“具有授予选项”的权限，这赋予接收者依次授予他人该权限的权利。如果随后撤销了“授予选项”，则所有直接或通过一系列授予从该接收者接收该权限的人员都将失去该权限。有关详细信息，请参阅 GRANT 和 REVOKE 参考页面。

Ordinarily, only the object’s owner (or a superuser) can grant or revoke privileges on an object. However, it is possible to grant a privilege “with grant option”, which gives the recipient the right to grant it in turn to others. If the grant option is subsequently revoked then all who received the privilege from that recipient (directly or through a chain of grants) will lose the privilege. For details see the GRANT and REVOKE reference pages.

对象的拥有者可以选择撤销其自身的一般权限，例如，使其表给自己及其他人均为只读。但拥有者始终享有所有授予选项，从而可以随时重新授予自己的权限。

An object’s owner can choose to revoke their own ordinary privileges, for example to make a table read-only for themselves as well as others. But owners are always treated as holding all grant options, so they can always re-grant their own privileges.

可用的权限为：

The available privileges are:

  • SELECT #

    • Allows SELECT from any column, or specific column(s), of a table, view, materialized view, or other table-like object. Also allows use of COPY TO. This privilege is also needed to reference existing column values in UPDATE, DELETE, or MERGE. For sequences, this privilege also allows use of the currval function. For large objects, this privilege allows the object to be read.

  • INSERT #

    • Allows INSERT of a new row into a table, view, etc. Can be granted on specific column(s), in which case only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). Also allows use of COPY FROM.

  • UPDATE #

    • Allows UPDATE of any column, or specific column(s), of a table, view, etc. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT …​ FOR UPDATE and SELECT …​ FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege. For sequences, this privilege allows use of the nextval and setval functions. For large objects, this privilege allows writing or truncating the object.

  • DELETE #

    • Allows DELETE of a row from a table, view, etc. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)

  • TRUNCATE #

    • Allows TRUNCATE on a table.

  • REFERENCES #

    • Allows creation of a foreign key constraint referencing a table, or specific column(s) of a table.

  • TRIGGER #

    • Allows creation of a trigger on a table, view, etc.

  • CREATE #

    • For databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.

    • For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

    • For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace.

    • Note that revoking this privilege will not alter the existence or location of existing objects.

  • CONNECT #

    • Allows the grantee to connect to the database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).

  • TEMPORARY #

    • Allows temporary tables to be created while using the database.

  • EXECUTE #

    • Allows calling a function or procedure, including use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions and procedures.

  • USAGE #

    • For procedural languages, allows use of the language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.

    • For schemas, allows access to objects contained in the schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to “look up” objects within the schema. Without this permission, it is still possible to see the object names, e.g., by querying system catalogs. Also, after revoking this permission, existing sessions might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

    • For sequences, allows use of the currval and nextval functions.

    • For types and domains, allows use of the type or domain in the creation of tables, functions, and other schema objects. (Note that this privilege does not control all “usage” of the type, such as values of the type appearing in queries. It only prevents objects from being created that depend on the type. The main purpose of this privilege is controlling which users can create dependencies on a type, which could prevent the owner from changing the type later.)

    • For foreign-data wrappers, allows creation of new servers using the foreign-data wrapper.

    • For foreign servers, allows creation of foreign tables using the server. Grantees may also create, alter, or drop their own user mappings associated with that server.

  • SET #

    • Allows a server configuration parameter to be set to a new value within the current session. (While this privilege can be granted on any parameter, it is meaningless except for parameters that would normally require superuser privilege to set.)

  • ALTER SYSTEM #

    • Allows a server configuration parameter to be configured to a new value using the ALTER SYSTEM command.

不同命令所需的权限列在各个命令的参考页中。

The privileges required by other commands are listed on the reference page of the respective command.

PostgreSQL 在创建某些类型的对象时默认向 PUBLIC 授予对这些对象的权限。默认情况下，不向 PUBLIC 授予对表、表列、序列、外部数据包装器、外部服务器、大型对象、模式、表空间或配置参数的任何权限。对于其他类型的对象，向 PUBLIC 授予的默认权限如下： CONNECT 和 TEMPORARY （创建临时表）数据库权限； EXECUTE 函数和过程权限；以及 USAGE 语言和数据类型（包括域）权限。当然，对象所有者可以 REVOKE 默认权限和明确授予的权限。（为了最大程度地确保安全，请在创建对象的同一事务中发布 REVOKE ；这样一来，没有另一个用户可以在此期间使用该对象。）此外，可以使用 ALTER DEFAULT PRIVILEGES 命令覆盖这些默认权限设置。

PostgreSQL grants privileges on some types of objects to PUBLIC by default when the objects are created. No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, tablespaces, or configuration parameters. For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains). The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these default privilege settings can be overridden using the ALTER DEFAULT PRIVILEGES command.

Table 5.1 显示了 ACL （访问控制列表）值中用于这些权限类型的单字母缩写。您会在下面列出的 psql 命令输出中或在查看系统目录的 ACL 列时看到这些字母。

Table 5.1 shows the one-letter abbreviations that are used for these privilege types in ACL (Access Control List) values. You will see these letters in the output of the psql commands listed below, or when looking at ACL columns of system catalogs.

Table 5.1. ACL Privilege Abbreviations

Privilege

Abbreviation

Applicable Object Types

SELECT

r (“read”)

LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column

INSERT

a (“append”)

TABLE, table column

UPDATE

w (“write”)

LARGE OBJECT, SEQUENCE, TABLE, table column

DELETE

d

TABLE

TRUNCATE

D

TABLE

REFERENCES

x

TABLE, table column

TRIGGER

t

TABLE

CREATE

C

DATABASE, SCHEMA, TABLESPACE

CONNECT

c

DATABASE

TEMPORARY

T

DATABASE

EXECUTE

X

FUNCTION, PROCEDURE

USAGE

U

DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE

SET

s

PARAMETER

ALTER SYSTEM

A

PARAMETER

Table 5.2 总结了每种类型的 SQL 对象可用的特权，使用上面显示的缩写。它还显示了用于检查每种对象类型的特权设置的 psql 命令。

Table 5.2 summarizes the privileges available for each type of SQL object, using the abbreviations shown above. It also shows the psql command that can be used to examine privilege settings for each object type.

Table 5.2. Summary of Access Privileges

Object Type

All Privileges

Default PUBLIC Privileges

psql Command

DATABASE

CTc

Tc

\l

DOMAIN

U

U

\dD+

FUNCTION or PROCEDURE

X

X

\df+

FOREIGN DATA WRAPPER

U

none

\dew+

FOREIGN SERVER

U

none

\des+

LANGUAGE

U

U

\dL+

LARGE OBJECT

rw

none

\dl+

PARAMETER

sA

none

\dconfig+

SCHEMA

UC

none

\dn+

SEQUENCE

rwU

none

\dp

TABLE (and table-like objects)

arwdDxt

none

\dp

Table column

arwx

none

\dp

TABLESPACE

C

none

\db+

TYPE

U

U

\dT+

授予特定对象的权限显示为 aclitem 条目的列表，每条目的格式如下：

The privileges that have been granted for a particular object are displayed as a list of aclitem entries, each having the format:

grantee=privilege-abbreviation[*].../grantor

每个 aclitem 都列出了一个受赠人已由特定授权人授予的所有权限。特定特权由 Table 5.1 中的单字母缩写表示，如果授予了授予选项，则追加 *。例如，calvin=r*w/hobbes 指定角色 calvin 具有特权 SELECT (r)，并具有授予选项 (*) 以及不可授予的特权 UPDATE (w)，两者均由角色 hobbes 授予。如果 calvin 也对由不同授予人授予的同一对象拥有一些特权，它们将显示为单独的 aclitem 条目。aclitem 中的空受赠人字段表示 PUBLIC。

Each aclitem lists all the permissions of one grantee that have been granted by a particular grantor. Specific privileges are represented by one-letter abbreviations from Table 5.1, with * appended if the privilege was granted with grant option. For example, calvin=r*w/hobbes specifies that the role calvin has the privilege SELECT (r) with grant option (*) as well as the non-grantable privilege UPDATE (w), both granted by the role hobbes. If calvin also has some privileges on the same object granted by a different grantor, those would appear as a separate aclitem entry. An empty grantee field in an aclitem stands for PUBLIC.

例如，假设用户 miriam 创建表 mytable 并执行以下操作：

As an example, suppose that user miriam creates table mytable and does:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

则 psql 的 \dp 命令将显示：

Then psql’s \dp command would show:

=> \dp mytable
                                  Access privileges
 Schema |  Name   | Type  |   Access privileges   |   Column privileges   | Policies
--------+---------+-------+-----------------------+-----------------------+----------
 public | mytable | table | miriam=arwdDxt/miriam+| col1:                +|
        |         |       | =r/miriam            +|   miriam_rw=rw/miriam |
        |         |       | admin=arw/miriam      |                       |
(1 row)

如果给定对象的“访问权限”列为空，则表示该对象具有默认权限（即，其在相关系统目录中的权限条目为 null）。默认权限始终包括所有者的所有权限，并且根据上述说明，可能包括 PUBLIC 的某些权限，具体取决于对象类型。对象上的第一个 GRANT 或 REVOKE 将实例化默认权限（例如生成 miriam=arwdDxt/miriam）然后根据指定的请求对其进行修改。类似地，“列权限”仅显示非默认权限的列的条目。（注意：在此目的下，“默认权限”始终表示对象类型的内置默认权限。其权限受 ALTER DEFAULT PRIVILEGES 命令影响的对象将始终显示包含 ALTER 影响的显式权限条目。）

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above. The first GRANT or REVOKE on an object will instantiate the default privileges (producing, for example, miriam=arwdDxt/miriam) and then modify them per the specified request. Similarly, entries are shown in “Column privileges” only for columns with nondefault privileges. (Note: for this purpose, “default privileges” always means the built-in default privileges for the object’s type. An object whose privileges have been affected by an ALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of the ALTER.)

请注意，所有者的隐式授予选项未在访问权限显示中标记。只有在明确向某人授予授予选项时，才会出现 *。

Notice that the owner’s implicit grant options are not marked in the access privileges display. A * will appear only when grant options have been explicitly granted to someone.