Open In App

PostgreSQL – Role Membership

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 17 Aug, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads