Predefined roles introduced in PostgreSQL 14 reduced my responsibilities

 User management is a crucial role in the database, and the DBA should assign suitable permissions to users. For example, some users require read-only access to all tables, while others require read/write access (application user).

Now, if I want to allow the read or read-write access to the user, I need to perform specific instructions on the database.

Let's take example for read and read-write

Read

1. Grant select on all existing tables in schema to user

2. Alter default privileges for feature tables

3. Grant usage on schema

 

Read-Write

 

1. Grant select,insert,delete,update on all existing tables in schema to user

2. Alter default privileges for feature tables

3. Grant usage on schema

 

If there are comparable requirements for additional users, it is preferable to construct separate roles with different permissions, such as read and write roles, and then grant the roles to users depending on the requirements.

 

I used to follow the mentioned procedure, but not anymore after PostgreSQL 14. A new set of predefined roles has been introduced in PostgreSQL 14, which will simplify DBA work while providing permission to users, as seen below.

If I intend to perform the same activity to give read or read-write, I will only follow easy procedures.

 

Read

 

Grant pg_read_all_data to user ;

 

Read-Write

 

Grant pg_read_all_data,pg_write_all_data to user ;