Movatterモバイル変換

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.

There are different kinds of privileges:SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE,USAGE,SET,ALTER SYSTEM, andMAINTAIN. 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.

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; seeSection 20.3.)

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

ALTER TABLEtable_name OWNER TOnew_owner;

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 toSET ROLE to the new owning role.

To assign privileges, theGRANT command is used. For example, ifjoe is an existing role, andaccounts is an existing table, the privilege to update the table can be granted with:

GRANT UPDATE ON accounts TO joe;

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

The special“role” namePUBLIC 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 seeChapter 20.

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

REVOKE ALL ON accounts FROM PUBLIC;

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 theGRANT andREVOKE 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:

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

Postgres Pro grants privileges on some types of objects toPUBLIC by default when the objects are created. No privileges are granted toPUBLIC 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 toPUBLIC are as follows:CONNECT andTEMPORARY (create temporary tables) privileges for databases;EXECUTE privilege for functions and procedures; andUSAGE 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 theREVOKE 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 theALTER DEFAULT PRIVILEGES command.

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

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

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

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

Eachaclitem lists all the permissions of one grantee that have been granted by a particular grantor. Specific privileges are represented by one-letter abbreviations fromTable 5.1, with* appended if the privilege was granted with grant option. For example,calvin=r*w/hobbes specifies that the rolecalvin has the privilegeSELECT (r) with grant option (*) as well as the non-grantable privilegeUPDATE (w), both granted by the rolehobbes. Ifcalvin also has some privileges on the same object granted by a different grantor, those would appear as a separateaclitem entry. An empty grantee field in anaclitem stands forPUBLIC.

As an example, suppose that usermiriam creates tablemytable 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;

Thenpsql's\dp command would show:

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

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 forPUBLIC depending on the object type, as explained above. The firstGRANT orREVOKE 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 anALTER DEFAULT PRIVILEGES command will always be shown with an explicit privilege entry that includes the effects of theALTER.)

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.

The“Access privileges” column shows(none) when the object's privileges entry is non-null but empty. This means that no privileges are granted at all, even to the object's owner — a rare situation. (The owner still has implicit grant options in this case, and so could re-grant her own privileges; but she has none at the moment.)