PostgreSQL Role Membership

Summary: in this tutorial, you will learn about PostgreSQL group roles and how to use them to manage privileges more effectively.

---

Introduction to PostgreSQL group roles

It is easier to manage roles as a group so that you can grant or revoke privileges from a group as a whole instead of doing it on an individual role.

Typically, you create a role that represents a group and then grants membership in the group role to individual roles.

By convention, a group role does not have the LOGIN privilege. It means that you will not be able to use the group role to log in to PostgreSQL.

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

CREATE ROLE group_role_name;

For example, the following statement creates a group role sales:

CREATE ROLE sales;

When you use the \du command in the psql tool, you will see that the group roles are listed together with user roles:

List of roles
  Role name |                         Attributes                         | Member of
 -----------+------------------------------------------------------------+-----------
  alice     |                                                            | {}
  api       | 1000 connections                                           | {}
  bob       | Cannot login                                               | {}
  dba       | Create DB                                                  | {}
  john      | Superuser                                                  | {}
  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  sales     | Cannot login                                               | {}

To add a role to a group role, you use the following form of the GRANT statement:

GRANT group_role to user_role;

For example, the following statement adds the role alice to the group role sales:

GRANT sales TO alice;

If you run the \du command again, you will see that alice now is a member of sales:

\du

List of roles
  Role name |                         Attributes                         | Member of
 -----------+------------------------------------------------------------+-----------
  alice     |                                                            | {sales}
  api       | 1000 connections                                           | {}
  bob       | Cannot login                                               | {}
  dba       | Create DB                                                  | {}
  john      | Superuser                                                  | {}
  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  sales     | Cannot login                                               | {}

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

REVOKE group_role FROM user_role;

For example, the following statement uses the REVOKE statement to remove the role alice from the group role sales:

REVOKE sales FROM alice;

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.

PostgreSQL Role Membership example

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

• First, a role with the INHERIT attribute will automatically have privileges of the group roles of which it is the member, including any privileges inherited by that role.
• Second, a role can use the SET ROLE statement to temporarily become the group role. The role will have privileges of the group role rather than its original login role. Also, the objects are created by the role are owned by the group role, not the login role.

Step 1. Setting up a sample database and tables

1. Login to the PostgreSQL using the postgres database.

2. Create a new database called corp:

create database corp;

4. Create the contacts table:

create table contacts(
    id int generated always as identity primary key,
    name varchar(255) not null,
    phone varchar(255) not null
 );

5. Create the forecasts table:

create table forecasts(
     year int, 
     month int, 
     amount numeric
 );

Step 2. Setting roles and group roles

1. Create a role jane that can log in with a password and inherit all privileges of group roles of which it is a member:

create role jane inherit login password 'securePass1';

2. Grant the select on the forecasts table to jane:

grant select on forecasts to jane;

3. Use the \z command to check the grant table:

\z

4. Create the marketing group role:

create role marketing noinherit;

5. Create the planning group role:

create role planning noinherit;

6. Grant all privileges on contacts table to marketing:

grant all on contacts to marketing;

7. Grant all privileges on forecasts table to planning:

grant all on forecasts to planning;

8. Add jane as a member of marketing:

grant marketing to jane;

9. Add planning as a member of marketing:

grant marketing to planning;

Step 3. Using the roles

1. If you connect to PostgreSQL using the role jane , you will have privileges directly granted to jane plus any privileges granted to marketing because jane inherits marketing’s privileges:

psql -U jane -d corp

2. It’ll prompt you for the jane’s password.

3. The role jane can select data from the forecasts table:

dcorp=> select * from forecasts;

4. And insert a row into the contacts table:

corp=> insert into contacts(name, phone) values('Jone Doe','408-102-3459');

5. However, jane cannot insert a row into the forecasts table:

corp=> insert into forecasts(year, month, amount) values(2020,1,1000);
ERROR:  permission denied for table forecasts

6. After executing the following SET ROLE statement:

corp=> set role planning;

7. The role jane will have privileges granted to planning, not the ones that granted directly to jane or indirectly to marketing.

8. Now, jane can insert a row into the forecasts table:

corp=> insert into forecasts(year, month, amount) values(2020,1,1000);

9. If jane attempts to select data from the contacts table, it will fail because the role planning has no privilege on the contacts table:

corp=> select * from contacts;
ERROR:  permission denied for table contacts

To restore the original privileges of jane, you use the RESET ROLE statement:

RESET ROLE;

Summary

• Manage privileges on group roles instead of individual roles.
• Roles with INHERIT attribute takes all privileges of the group roles of which they are the members.

---