PostgreSQL – Role Membership
Group roles are a powerful feature that can significantly simplify the process of granting or revoking privileges, thereby reducing complexity and saving time. However, it is important to note that, by convention, a group role does not have the ‘LOGIN’ privilege.
In this article, we will explore PostgreSQL group roles and their practical applications in managing database privileges.
Creating and Managing Group Roles
1. Creating a Group Role
To create a group role, you can use the CREATE ROLE statement as follows:
Syntax: CREATE ROLE group_role_name;
2. Adding a Role to a Group Role
The ‘GRANT’ statement can be used to add a role to a group role, as shown below:
Syntax: GRANT group_role TO user_role;
3. Removing a Role from a Group 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;
Using Group Role Privileges
A role can use the privileges of the group role in the following ways:
- INHERIT Attribute: If we use the INHERIT attribute, the members of group role gets all the privileges of the group automatically.
- SET ROLE Statement: To create a temporary role use the SET ROLE statement.
PostgreSQL Role Membership Example
Let us take a look at an example of Role Membership in PostgreSQL to better understand the concept.
Step 1: Create a New Database
Create a new database called corp:
CREATE DATABASE corp;
Switch to the ‘corp‘ database:
\c corp
Step 2: Create Tables
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
);
Step 3: Create Roles and Assign Privileges
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:
Step 4: Create and Manage Group Roles
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;
Demonstrating Role Privileges
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:
Conclusion
Group roles in PostgreSQL provide an efficient way to manage privileges. By creating group roles and assigning user roles to these groups, one can not only simplify privilege management but also enhance security by ensuring that roles have only the necessary privileges.
