PostgreSQL – Role Membership

In this article, we will look into PostgreSQL group roles and their uses to manage privileges in a PostgreSQL database more effectively. It is a standard practice to create group roles as it can significantly reduce the complexity and time required to grant or invoke privileges on the database. But there is a catch in this technique. By convention, a group role does not have the LOGIN privilege.

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

Syntax: CREATE ROLE group_role_name;

The GRANT statement can be used to add a role to a group role,as shown below:

Syntax: GRANT group_role to user_role;

To remove a user role from a group role, you can use the REVOKE statement as shown below:

Syntax: REVOKE group_role FROM user_role;

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

• If we use the INHERIT attribute, the members of group role gets all the privileges of the group automatically.
• To create a temporary role use the SET ROLE statement.

Example:

Create a new database called corp:

create database corp;

Switch to the corp database:

\c corp

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
);

Create the forecasts table:

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

Create a role ‘Anil’ that can log in with a password and inherit all privileges of group roles of which it is a member:

create role anil inherit login password 'securePass1';

Grant the select on the forecasts table to Anil:

grant select on forecasts to anil;

Use the below command to check the grant table:

\z

This will lead to the following:

Create the marketing group role:

create role marketing noinherit;

Create the planning group role:

create role planning noinherit;

Grant all privileges on contacts table to marketing:

grant all on contacts to marketing;

Grant all privileges on forecasts table to planning:

grant all on forecasts to planning;

Add ‘Anil’ as a member of marketing:

grant marketing to anil;

Add planning as a member of marketing:

grant marketing to planning;

Now, the role Anil can select data from the forecasts table:

select * from forecasts;

And insert a row into the contacts table:

insert into contacts(name, phone) values('Raju kumar', '408-102-3459');

As Anil can insert a row into the forecasts table, PostgreSQL will behave as expected and the insertion will take place as expected. So, check the inserted data use the following command”

SELECT * FROM contacts;

Output: