Roles

What Are Roles in PostgreSQL?

A role in Postgres is one of the Postgres identifiers and a concept dedicated to group and manage database object privileges. Alternatively, it can be stated that role is used to denote:

• a user,

• a group of users, or

• a container for privileges to be used or inherited by other roles.

Roles and role names (like some other objects) are global across a database cluster.

Postgres diverges from the SQL standard when it comes to roles. In the SQL standard the concept of a user is distinct from the concept of a role.

Predefined Role

An initialized Postgres installation always comes with a one predefined role with a superuser attribute. Most probably the name of the role will be the same as the operating initializing system user. This role can be used to connect to a database cluster with a psql -U [role name] command and create additional roles.

Database Connection

A connection to a database is always associated with a role and is governed by the scope of privileges of that role.

Many Postgres command line commands, including psql, when no role is expressly indicated use the name of the current operating system user. This works similarly for the default database name. Therefore, when the current operating system user is johndoe simply entering psql on the command line will try to establish a connection to the database johndoe using the role johndoe. If no such role or database exist a fatal error is thrown.

$ psql

To expressly indicate the role to be used when establishing the connection use -U option.

$ psql -U otherjohndoe

To expressly indicate the database to which the connection should be established use -d option.

$ psql -d otherdatabase

-U and -d options can be combined.

$ psql -U otherjohndoe -d otherdatabase

The above command line command will try to establish a connection using the role otherjohndoe to the database otherdatabase.

Once connected it is possible to change the current's connection role using the SET ROLE command.

SET ROLE role_name;

Listing Roles

To list roles existing in the current Postgres database cluster use \du meta-command:

\du

The \du returns the list of all database cluster roles with indication of their names, attributes and memberships.

Creating & Dropping Roles

To create a role use CREATE ROLE command.

CREATE ROLE admin;

To drop a role use DROP ROLE command.

DROP ROLE admin;

A role cannot be dropped when there are database objects owned by it. To reassign or drop database objects globally within a given database use REASSIGN OWNED or DROP OWNED respectively.

Role Attributes

Each role can have different attributes - granted to it at its creation or later - defining the scope of privileges the role has and/or its login behavior. The attributes include among others:

• superuser - roles with SUPERUSER attribute ignore all permission checks,

• login - only roles with LOGIN attribute can establish initial connections,

• password - only roles with PASSWORD attribute require passwords for establishing connections,

• database creation - only roles with CREATEDB attribute can create/drop databases,

• role creation - only roles with CREATEROLE attribute can create/drop roles and grant/revoke role memberships.

To create a superuser role use the following command:

CREATE ROLE admin SUPERUSER;

To create a role with login and password attributes that can create databases and roles use the following command:

CREATE ROLE admin LOGIN PASSWORD 'foobar' CREATEDB CREATEROLE;

To alter an existing role use ALTER ROLE command. For example, to a take database creation privilege from a role use the command NOCREATEDB.

ALTER ROLE admin NOCREATEDB;

Ownership & Privileges

A role that creates an object becomes its owner.

CREATE DATABASE bike_shop;

An ownership can be transferred to another role.

ALTER DATABASE bike_shop OWNER TO jimmy;

Only a role owning an object can take effective actions on it unless it inherits respective privileges.

There are many kind of privileges. Among them are:

• INSERT,

• SELECT,

• UPDATE,

• DELETE, and

• CONNECT.

To grant privileges on a database object use GRANT ... ON ... TO command.

GRANT INSERT ON users TO jimmy;
GRANT ALL ON users TO jimmy;

To revoke privileges use REVOKE ... ON ... FROM command.

Memberships

Each role can be granted a membership in another role which allows it to use privileges of that another role.

Some roles can be created with the purpose of grouping privileges. Such roles can sometimes be referred to as group roles. Being granted a membership in a group role allows for sharing its privileges.

A role can use privileges of the role to which it is a member in two ways:

• it can set (using the SET ROLE command) the current's connection role to the role to which it is a member and use the privileges directly, or

• use the privileges itself provided it has the INHERIT attribute.

Member roles can be set to INHERIT or NOINHERIT. Default in Postgres is INHERIT.

SUPERUSER, LOGIN, CREATEDB and CREATEROLE attributes are not inherited.

To grant a membership in a given role use GRANT [group role] TO [role];.

GRANT admins TO joe;

To revoke use REVOKE [group role] FROM [role];.

REVOKE admins FROM joe;

To restore an initial connection role use SET ROLE or RESET ROLE.

SET ROLE NONE;
RESET ROLE;

All roles are members of the PUBLIC role. Therefore granting privileges to PUBLIC means granting them to all roles.