PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.
The concept of roles subsumes the concepts of “users” and “groups”. Any role can act as a user, a group, or both.
This chapter describes how to create and manage roles. More information about the effects of role privileges on various database objects can be found in Section 5.7.
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 database-level objects (it belongs to a specific 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;
createuser name dropuser name
To determine the set of existing roles, examine the
pg_roles system view, 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 instance. 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
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.
A database role can have a number of attributes that define its privileges and interact with the client authentication system.
Only roles that have the
LOGINattribute can be used as the initial role name for a database connection. A role with the
LOGINattribute can be considered the same as a “database user”. To create a role with login privilege, use either:
CREATE USERis equivalent to
CREATE ROLEexcept that
LOGINby default, while
CREATE ROLEdoes not.)
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
SUPERUSER. You must do this as a role that is already a superuser.
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
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
CREATEROLE. A role with
CREATEROLEprivilege 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;
CREATEROLEis insufficient for that.
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
LOGINpermission as well. To create such a role, use
A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. The
md5authentication methods make use of passwords. Database passwords are separate from operating system passwords. Specify a password upon role creation with
It is good practice to create a role that has the
CREATEROLEprivileges, 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
varname. Note that role-specific defaults attached to roles without
LOGIN privilege are fairly useless, since they will never be invoked.
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.
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
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’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
SET ROLE wheel;
the session would have use of only those privileges granted to
wheel, and not those granted to either
admin. The original privilege state can be restored with any of:
SET ROLE joe; SET ROLE NONE; RESET ROLE;
SET ROLEcommand 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
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 PostgreSQL by giving roles being used as SQL roles the
INHERITattribute, while giving roles being used as SQL users the
NOINHERITattribute. However, PostgreSQL defaults to giving all roles the
INHERITattribute, 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
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
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).
Because roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.
Ownership of objects can be transferred one at a time using
ALTER commands, for example:
ALTER TABLE bobs_table OWNER TO alice;
Alternatively, the REASSIGN OWNED command can be used to reassign ownership of all objects owned by the role-to-be-dropped to a single other role.
Once any valuable objects have been transferred to new owners, any remaining objects owned by the role-to-be-dropped can be dropped with the DROP OWNED command.
DROP OWNED will not drop entire database or tablespaces, so it is necessary to do that manually if the role owns any database or tablespaces that have not been transferred to new owners.
DROP OWNED also takes care of removing any privileges granted to the target role for objects that do not belong to it. Because
REASSIGN OWNED does not touch such objects, it’s typically necessary to run both
REASSIGN OWNED and
DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped.
In short then, the most general recipe for removing a role that has been used to own objects is:
REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; DROP ROLE doomed_role;
When not all owned objects are to be transferred to the same successor owner, it’s best to handle the exceptions manually and then perform the above steps to mop up.
DROP ROLE is attempted while dependent objects still remain, it will issue messages identifying which objects need to be reassigned or dropped.