Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database). To create a role use the CREATE ROLE SQL command:

CREATE ROLE name;

name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. (In practice, you will usually want to add additional options, such as LOGIN, to the command. More details appear below.) To remove an existing role, use the analogous DROP ROLE command:

DROP ROLE name;

For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:

createuser name
dropuser name

To determine the set of existing roles, examine the pg_roles system catalog, for example

SELECT rolname FROM pg_roles;

The psql program's \du meta-command is also useful for listing the existing roles.

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.

Every connection to the database server is made using the name of some particular role, and this role determines the initial access privileges for commands issued in that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -U command line option to indicate the role to connect as. Many applications assume the name of the current operating system user by default (including createuserand psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.

The set of database roles a given client connection can connect as is determined by the client authentication setup, as explained in Chapter 20. (Thus, a client is not limited to connect as the role matching its operating system user, just as a person's login name need not match his or her real name.) Since the role identity determines the set of privileges available to a connected client, it is important to carefully configure privileges when setting up a multiuser environment.

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

To set up a group role, first create the role:

CREATE ROLE name;

Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish.

Once the group role exists, you can add and remove members using the GRANT and REVOKE commands:

GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

You can grant membership to other group roles, too (since there isn't really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role to PUBLIC.

The members of a group role can use the privileges of the role in two ways. First, every member of a group can explicitly do SET ROLE to temporarily “become” the group role. In this state, the database session has access to the privileges of the group role rather than the original login role, and any database objects created are considered owned by the group role not the login role. Second, member roles that have the INHERIT attribute automatically have use of the privileges of roles of which they are members, including any privileges inherited by those roles. As an example, suppose we have done:

CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;

Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin, because joe “inherits” admin's privileges. However, privileges granted to wheel are not available, because even though joe is indirectly a member of wheel, the membership is via admin which has the NOINHERIT attribute. After:

SET ROLE admin;

the session would have use of only those privileges granted to admin, and not those granted to joe. After:

SET ROLE wheel;

the session would have use of only those privileges granted to wheel, and not those granted to either joe or admin. The original privilege state can be restored with any of:

SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

Note

The SET ROLE command always allows selecting any role that the original login role is directly or indirectly a member of. Thus, in the above example, it is not necessary to become adminbefore becoming wheel.

Note

In the SQL standard, there is a clear distinction between users and roles, and users do not automatically inherit privileges while roles do. This behavior can be obtained in PostgreSQLby giving roles being used as SQL roles the INHERIT attribute, while giving roles being used as SQL users the NOINHERIT attribute. However, PostgreSQL defaults to giving all roles theINHERIT attribute, for backward compatibility with pre-8.1 releases in which users always had use of permissions granted to groups they were members of.

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought of as special privileges, but they are never inherited as ordinary privileges on database objects are. You must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. Continuing the above example, we might choose to grant CREATEDB and CREATEROLE to the admin role. Then a session connecting as role joe would not have these privileges immediately, only after doing SET ROLE admin.

To destroy a group role, use DROP ROLE:

DROP ROLE name;

Any memberships in the group role are automatically revoked (but the member roles are not otherwise affected).

Functions, triggers and row-level security policies allow users to insert code into the backend server that other users might execute unintentionally. Hence, these mechanisms permit users to “Trojan horse” others with relative ease. The strongest protection is tight control over who can define objects. Where that is infeasible, write queries referring only to objects having trusted owners. Remove from search_path the public schema and any other schemas that permit untrusted users to create objects.

Functions run inside the backend server process with the operating system permissions of the database server daemon. If the programming language used for the function allows unchecked memory accesses, it is possible to change the server's internal data structures. Hence, among many other things, such functions can circumvent any system access controls. Function languages that allow such access are considered “untrusted”, and PostgreSQL allows only superusers to create functions written in those languages.

因為角色可以擁有資料庫物件，並且可以擁有存取其他物件的權限，所以移除角色通常不僅僅是快速 DROP USER 的問題。該角色擁有的任何物件必須先被移除或重新分配給其他使用者；而授予角色的任何權限也都必須被撤銷。

物件的所有權可以使用 ALTER 指令一次轉換，例如：

ALTER TABLE bobs_table OWNER TO alice;

或者，可以使用 REASSIGN OWNED 指令將要移除角色擁有的所有物件的所有權重新分配給另一個角色。由於 REASSIGN OWNED 無法存取其他資料庫中的物件，因此有必要在包含該角色擁有的物件的每個資料庫中執行它。（請注意，第一個這樣的 REASSIGN OWNED 將改變任何共享的資料庫間物件，即資料庫或資料表空間的所有權，這些資料庫或資料表空間由將被移除的角色所擁有）。

一旦任何有價值的物件已經轉移到新的所有者中，則可以使用 DROP OWNED 指令移弓除待移除角色擁有的任何剩餘物件。同樣，此指令無法存取其他資料庫中的物件，因此有必要在包含該角色擁有的物件的每個資料庫中執行它。此外，DROP OWNED 不會刪除整個資料庫或資料表空間，因此如果角色擁有尚未轉移到新所有者的任何資料庫或資料表空間，則必須手動執行此操作。

DROP OWNED 還負責為不屬於它的物件移除授予目標角色的所有權限。由於 REASSIGN OWNED 不會觸及這些物件，因此通常需要運行 REASSIGN OWNED 和 DROP OWNED（按此順序！）以完全移除要移除的角色的相依關係。

簡而言之，移除已用於擁有物件的角色的最一般的方式是：

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;

當並非所有擁有的物件都將被轉移到相同的繼任者使用者時，最好手動處理異常，然後執行上述步驟來清除。

如果在相依物件仍然存在的情況下嘗試 DROP ROLE，則會發出哪些物件需要重新分配或移除的訊息。

PostgreSQL 使用角色的概念來管理資料庫的存取權限。角色可以被視為資料庫使用者或一個資料庫使用者群組，具體取決於角色的設定方式。角色可以擁有資料庫物件（例如資料表和函數），並可以將這些物件的權限分配給其他角色，以控制誰可以存取哪些物件。此外，也可以將角色中的成員身份授予另一個角色，使得成員角色能夠使用分配給其他角色的權限。

角色的概念包含「使用者」和「群組」的概念。在 8.1 版之前的 PostgreSQL中，使用者和群組是不同種類的實體，但現在只有角色。任何角色都可以充當使用者、群組或兩者兼具。

本章介紹如何建立和管理角色。有關角色權限對各種資料庫物件的影響和更多訊息可以在 5.6 節中找到。

PostgreSQL provides a set of default roles which provide access to certain, commonly needed, privileged capabilities and information. Administrators can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information.

The default roles are described in Table 21.1. Note that the specific permissions for each of the default roles may change in the future as additional capabilities are added. Administrators should monitor the release notes for changes.

Table 21.1. Default Roles

The pg_monitor, pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables roles are intended to allow administrators to easily configure a role for the purpose of monitoring the database server. They grant a set of common privileges allowing the role to read various useful configuration settings, statistics and other system information normally restricted to superusers.

Care should be taken when granting these roles to ensure they are only used where needed to perform the desired monitoring.

Administrators can grant access to these roles to users using the GRANT command:

GRANT pg_signal_backend TO admin_user;

A database role can have a number of attributes that define its privileges and interact with the client authentication system.login privilege

Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a “database user”. To create a role with login privilege, use either:

CREATE ROLE name LOGIN;
CREATE USER name;

(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not.)superuser status

A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser. To create a new database superuser, use CREATE ROLEname SUPERUSER. You must do this as a role that is already a superuser.database creation

A role must be explicitly given permission to create databases (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEDB.role creation

A role must be explicitly given permission to create more roles (except for superusers, since those bypass all permission checks). To create such a role, use CREATE ROLE name CREATEROLE. A role with CREATEROLE privilege can alter and drop other roles, too, as well as grant or revoke membership in them. However, to create, alter, drop, or change membership of a superuser role, superuser status is required; CREATEROLE is insufficient for that.initiating replication

A role must explicitly be given permission to initiate streaming replication (except for superusers, since those bypass all permission checks). A role used for streaming replication must have LOGIN permission as well. To create such a role, use CREATE ROLE nameREPLICATION LOGIN.password

A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The password and md5 authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation with CREATE ROLE name PASSWORD 'string'.

A role's attributes can be modified after creation with ALTER ROLE. See the reference pages for the CREATE ROLE and ALTER ROLE commands for details.

Tip

It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it.

A role can also have role-specific defaults for many of the run-time configuration settings described in Chapter 19. For example, if for some reason you want to disable index scans (hint: not a good idea) anytime you connect, you can use:

ALTER ROLE myname SET enable_indexscan TO off;

This will save the setting (but not set it immediately). In subsequent connections by this role it will appear as though SET enable_indexscan TO off had been executed just before the session started. You can still alter this setting during the session; it will only be the default. To remove a role-specific default setting, use ALTER ROLE rolename RESET varname. Note that role-specific defaults attached to roles without LOGIN privilege are fairly useless, since they will never be invoked.