Creating a group role for role-based segregation

It is a very challenging task for admins when there are several tens or hundreds of users in a database that need to be assigned privileges to access or modify database objects. It becomes a time-consuming task to individually manage each user and assign SELECT or INSERT privileges to hundreds of objects. For this reason, it is always recommended to provide access privileges to database users using GROUP ROLES. In this recipe, we shall see how GROUP ROLES can be used for role-based segregation.

Getting ready

A role in Postgres can INHERIT another role. This means one role can be granted the privileges of another role. This can be achieved using the GRANT or INHERIT keyword. So, there can be a read-only role and a read-write for each schema or for a set of objects belonging to a specific schema or an application's logic/module. So, if a user needs to access the objects of a specific application module, just the role belonging to that application module can be granted to the user. This helps in achieving better user management.

How to do it

The following steps need to be followed to complete the recipe:

1. Create the read-only and read-write roles for each schema respectively:

CREATE ROLE scott_readonly;
CREATE ROLE scott_readwrite;
CREATE ROLE tiger_readonly;
CREATE ROLE tiger_readwrite;

2. Grant the SELECT access in the schemas to their associated roles:

GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA scott TO scott_readonly;
GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA tiger TO tiger_readonly;

3. Grant usage and write access in the schemas to their appropriate read-write roles:

GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tiger TO tiger_readwrite;
GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA scott TO scott_readwrite;

4. Assign read-write roles to application users and read-only roles to individual users:

GRANT scott_readwrite to appuser1;
GRANT tiger_readwrite to appuser2;

GRANT scott_readonly to devuser1;
GRANT tiger_readonly to devuser2;

How it works

In order to understand how to implement the group roles in a better way, consider an example where there is a company that has one PostgreSQL database (salesdb) with two schemas – scott and tiger.

Now, the following are the requirements:

• Create 100 individual user accounts and 5 application users.
• Grant read access on the 2 schemas to 100 individual user accounts.
• Grant read and write access on 2 schemas to 5 application users.

There are multiple ways to achieve this. One way is to grant access to each of these objects to all the user accounts individually. But isn't that very time-consuming? The other way is to use group roles.

In order to use group roles, we need to create two roles for each schema, as seen in step 1 – one for read-only and the other for read-write for both scott and tiger schemas:

• scott_read_only: The READ-ONLY role to perform READS on the schema scott.
• scott_read_write: The READ-WRITE role to perform WRITES on the schema scott.

• tiger_read_only: The READ-ONLY role to perform READS on the schema tiger.
• tiger_read_write: The READ-WRITE role to perform WRITES on the schema tiger.

And then the reads and writes on the two schemas can be granted to their associated roles as seen in step 2 and step 3. And those roles can be granted to the individual users and the application users as seen in step 4.

By using the preceding approach, a role can be directly assigned to a new user instead of granting appropriate privileges on each object explicitly. This helps in achieving proper user management through role-based segregation.