Read-only user in PostgreSQL

by Leave a Comment

Create only read only with limited access to PostgreSQL Database. This user can only SELECT table data and also restricted by schema access. 

1.

Script to Create Read-Only user:

CREATE USER readuser WITH PASSWORD 'dV@pkLmn32'; 

ALTER USER readuser set default_transaction_read_only = on;

2.

Assign permission to this read only user:

GRANT CONNECT ON DATABASE "overhaul-backend_qa" TO readuser;

\c overhaul-backend_qa

GRANT USAGE ON SCHEMA public TO readuser;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readuser;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readuser;

 

3. In the above script, you can find that we have restricted user by Database, Schema and SELECT permission to tables. 

If you require to give any additional permission like EXECUTE permission for functions, use below script.

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readuser;