PostgreSQL – Create a Read Only User

You can create a read-only user in Postgres database by following the below sequence of commands:

Create user:

CREATE ROLE your_user WITH LOGIN PASSWORD 'your_password';

Allow user to connect to the database:

GRANT CONNECT ON DATABASE your_database TO your_user;

Grant schema to the user:

GRANT USAGE ON SCHEMA your_schema TO your_user;

Grant access to all tables:

GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO your_user;

Grant access to tables that will be created in future:

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema GRANT SELECT ON TABLES TO your_user;