PostgreSQL - Read Write Role

 This guide explains how to create a PostgreSQL role that allows users to:

• Read data and schema
• View query execution plans
• Modify data (INSERT / UPDATE / DELETE) without schema changes
• Create and manage objects (CRUD) within their own schema only

Step 1: Create a Base Role (No Login)

CREATE ROLE read_write NOLOGIN;

Step 2: Grant Read Access on Shared Schema

GRANT USAGE ON SCHEMA public TO read_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_write;

Step 3: Allow Data Modification (Without Schema Changes)

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

Step 4: Ensure Future Tables Inherit Permissions

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO read_write;

Step 5: Enable Query Performance Insights

ALTER ROLE read_write SET track_io_timing TO on;

Step 6: Create User with Personal Schema

CREATE ROLE rw_user LOGIN PASSWORD 'securepassword';
GRANT read_write TO rw_user;

CREATE SCHEMA AUTHORIZATION rw_user;

Conclusion

The user now has:
• Read and write access to shared schema tables
• No permission to alter shared schema structure
• Full CREATE / ALTER / DROP privileges within their own schema

This approach follows PostgreSQL security best practices by separating:
• Permission roles (NOLOGIN)
• User login roles
• Shared schema access
• Personal schema ownership