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:
Contact Us