PostgreSQL Roles Management

Summary: in this tutorial, we will introduce you to PostgreSQL roles concept and show you how to create user roles and group roles.

PostgreSQL uses the roles concept to manage database access permissions. A role can be a user or a group, depending on how you setup the role. A role that has login right is called user. A role may be a member of other roles, which are known as groups.

Creating PostgreSQL roles

From version 8.1, PostgreSQL uses the roles concept to incorporate the users and groups concepts. To create a new role, you use the CREATE ROLE statement as follows:

To get all available roles in the cluster, you query from the pg_roles system catalog as the following statement:

If you use the psql tool, you can use the \du command to list all existing roles.

Role attributes

The attributes of a database role define role’s privileges including login, superuser, database creation, role creation, password, etc.

The following statement creates a role that has login privilege, password, and valid date.

The following statement creates a role that has superuser status, which means this role can bypass all authorization checks:

Notice that you must be a superuser in order to create another superuser.

If you want a role to have database creation privilege, you use the following statement:

Use the following statement to create a role that has creation privilege:

Role membership

It is easier to manage roles as a group so that you can grant or revoke privileges from a group as a whole. In PostgreSQL, you create a role that represents a group, and then grant membership in the group role to individual user roles.

By convention, a group role does not have LOGIN privilege.

To create a group role, you use the CREATE ROLE statement as follows:

For example, the following statement creates sales group role:

Now, you can add a user role to a group role by using the GRANT statement:

For example, to add the doe user role to the sales group role, you use the following statement:

To remove a user role from a group role, you use REVOKE statement:

For example, to remove doe user role from the sales group role, you use the following statement:

Notice that PostgreSQL does not allow you to have circular membership loops, in which a role is the member of another role and vice versa.

Group and user role inheritance

A user role can use privileges of the group role in the following ways:

• First, a user role can use the SET ROLE statement to temporarily become the group role, which means the user role use privileges of the group role rather than the original privileges. In addition, any database objects created in the session are owned by the group role, instead of the user role.
• Second, a user role that has the INHERIT attribute will automatically have the privileges of the group roles of which it is a member, including all privileges inherited by the group roles.

See the following example:

If you connect to PostgreSQL as doe, you will have privileges of doe plus privileges granted to sales, because doe user role has the INHERIT attribute. However, you do not have privileges of marketing because the NOINHERIT attribute is defined for the sales user role.

After executing the following statement:

You will have only privileges granted to sales, not the ones that granted to doe.

And after executing the following statement:

You only have privileges granted to marketing, not the ones that granted to admin and doe.

To restore the original privilege, you can use the following statement:

Notice that only privileges on the database object are inheritable. The LOGIN, SUPERUSER, CREATEROLE, and CREATEDB are the special role that cannot be inherited as an ordinary privilege.

Removing roles

You can use the DROP ROLE statement to remove a group role or user role.

Before removing a role, you must reassign or remove all objects it owns and revoke its privileges.

If you remove a group role, PostgreSQL revokes all memberships in a group automatically. The user roles of the group are not affected.