PostgreSQL Database Roles

References

• Database Roles
• • CREATE ROLE command
  • ALTER ROLE command

Notes

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, a group of database users, depending on how the role is set up. Roles can own database objects (for example, tables and functions) 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 membership role to use privileges assigned to another role.

The concept of roles subsumes the concepts of users and groups. [...] users and groups [used to be] distinct kinds of entities, but now there are only roles. Any role can act as a user, group, or both.

Database Roles

Database roles are conceptually completely separate from operating systems users. Database roles are global across a database cluster installation. Create a role:

CREATE ROLE name;

To remove an existing role:

DROP ROLE name;

The programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the Shell command line.

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

SELECT rolname FROM pg_roles;

To see those capable of logging in:

SELECT rolname FROM pg_roles WHERE rolcanlogin;

The psql program's \du meta-command is also useful for listing existing roles. In order to bootstrap the database system, a freshly initialized system always contains one predefined login-capable rile. This role is always a superuser, and it will have the same name as the operating system user that initialized the database cluster with initdb unless a different name is specified. This role is always a superuser, and it will have the same name as the operating system user that initialized the database cluster with initdb unless a different name is specified.

Every connection made 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.

Role Attributes

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

login privelege

• Only roles that have the LOGIN attribute can be used as the initial role name for a database connection.

CREATE ROLE name LOGIN;
CREATE USER name;

Note: CREATE USER is equivalent to CREATE ROLE except that CREATE USER includes LOGIN by default.

superuser status

• A database superuser bypasses all permission checks, except the right to login. This is a dangerous privilege, and should be used carefully.

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. To create such a role, use CREATE ROLE name CREATEROLE. A role with CREATEROLE privilege can alter and drop roles which have been granted to the CREATEROLE user with the ADMIN option.

initating replication

• A role must be explicitly 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 as role, use CREATE ROLE name REPLICATION LOGIN

password

• A password is only significant if the client authentication method requires the user to supply a password when connecting to the database. Specify a password upon role creation with CREATE ROLE name PASSWORD 'string'

inheritence of priveleges

• A role inherits the privileges of roles it is a member of, by default. However, to create a role which does not inherit privileges by default, use CREATE ROLE name NOINHERIT. Inheritance can be overwritten for individual grants by using WITH INHERIT TRUE or WITH INHERIT FALSE

bypassing row-level security

• A role must be explicitly given permission to bypass every row-level security (RLS) policy (except for superusers). To create such a role, use CREATE ROLE name BYPASSRLS as a superuser.

connection limit

• Connection limit can specify how many concurrent connections a role can make. -1 (the default) means no limit. Specify connection limit upon role creation with CREATE ROLE name CONNECTION LIMIT 'integer'.

A role's attribute can be modified with ALTER ROLE. When a non-superuser creates a role using the CREATEROLE privilege, the created role is automatically granted back to the creating user, just as if the bootstrap superuser has executed the command GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, INHERIT FALSE.

Role Membership

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 n the group role to individual user roles.

Create Group Role

CREATE ROLE name; -- create the role, typically role being used as group would not have LOGIN attribute
-- Once a 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, ...;

The members of a group role can use the privileges of the role in two ways:

1. Member roles that have been granted membership with the SET option can do SET ROLE to temporarily become the group role.
2. Member roles that have been granted membership with the INHERIT option automatically have use of the privileges of those directly or indirectly a member of, though the chain stops at memberships lacking the inherit option.

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. 

Dropping Roles

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:

ALTER TABLE bobs_table OWNER TO alice;

Alternatively, the REASSIGNED OWNED command can be used to reassign ownership of all objects owned by the role-to-be-dropped to a single other role.

Predefined Roles

PostgreSQL provides a set or predefined roles that 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.

Function Security

Functions, triggers, and row-level security policies allow users to insert code into the backend server that other users might execute unintentionally. 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.