PostgreSQL - Read-Only Role

 

PostgreSQL Read-Only Role Setup 

Overview

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

• Read data from tables

• Access schema objects

• Use temporary tables

 The approach uses a NOLOGIN role for permissions and a LOGIN role for end users. All database, schema, and user names are generic so the steps can be reused across environments.

Step 1: Create the Read-Only Role (No Login)

CREATE ROLE read_only NOLOGIN;

Explanation

A NOLOGIN role acts as a permission group. Users inherit privileges by being granted membership in this role.

Step 2: Grant Schema Usage

GRANT USAGE ON SCHEMA schema_name TO read_only;

GRANT USAGE ON SCHEMA public TO read_only;

Explanation

USAGE allows the role to see objects inside the schema.

Step 3: Grant SELECT on Existing Tables

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Explanation

This grants read access to all current tables in the schemas.

Step 4: Grant SELECT on Future Tables

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name

GRANT SELECT ON TABLES TO read_only;

Explanation

This ensures new tables created later automatically grant SELECT permission to the read_only role.

Step 5: Allow Temporary Tables (Database Level)

GRANT TEMP ON DATABASE database_name TO read_only;

Explanation

This allows users to create temporary tables, which is often required for reporting or analytics queries.

Step 6: Create a User and Assign Read-Only Role

CREATE ROLE user_name LOGIN PASSWORD 'secure_password';

GRANT read_only TO user_name;

Explanation

This creates a login user and grants membership in the read_only role.

Example: Applying to Another Schema

CREATE ROLE read_only NOLOGIN;

 

GRANT USAGE ON SCHEMA another_schema TO read_only;

 

GRANT SELECT ON ALL TABLES IN SCHEMA another_schema TO read_only;

 

ALTER DEFAULT PRIVILEGES IN SCHEMA another_schema

GRANT SELECT ON TABLES TO read_only;

 

CREATE ROLE another_user LOGIN PASSWORD 'secure_password';

GRANT read_only TO another_user;

Best Practices

• Use group roles for permission management

• Avoid granting privileges directly to users

• Periodically review role memberships

• Restrict write access unless required

• Use strong passwords or IAM/SSO authentication

Conclusion

Using a READ_ONLY role simplifies access management and improves security by centralizing permissions. This approach is scalable and recommended for production environments.