What ispg_read_all_data & pg_write_all_data role in PostgreSQL

                    pg_read_all_data & pg_write_all_data

pg_read_all_data is a built-in PostgreSQL role that allows a user to:

- Read all tables (SELECT)

- Read all schemas

- Read all sequences

Across all databases in the cluster

It is read-only (no INSERT/UPDATE/DELETE).

Step 1: Check if pg_read_all_data role exists

Expected result:

Role exists (PostgreSQL 14+)

If it does not exist → then PostgreSQL version is very old

Step 2: Create a read-only user

CREATE ROLE readonly_user LOGIN PASSWORD 'postgres@123';

Step 3: Grant pg_read_all_data to the user

    This instantly gives access to all existing & future tables.

Step 4: Grant CONNECT privilege on database

Step 5: Verify select(read-only) access to user readonly_user.

Select should work.

Step 6: Verify insert into a table from readonly_user.

It will not work.

Step 7: Verify pg_write_all_data role.

Step 8: create a user and grant pg_write_all_data role. Also grant connect to the database.

    

Step 9: Run insert command -

        It will work.

Step 10: Now run the select from pg_write_all_data on departments table -

    It will not work.

Step 11: If any user want select, insert, update and delete permission then we have to grant                             pg_read_all_data and pg_write_all_data both.