Menu

Creating a read-only user in PostgreSQL

First you need to create a ‘role’ (i.e. a user). You possibly also want the password to never expire:

CREATE ROLE averagejoe LOGIN ENCRYPTED PASSWORD '***' NOINHERIT VALID UNTIL 'infinity';

Now grant this role access to the selected database:

GRANT CONNECT ON DATABASE cooldb TO averagejoe;

Now connect to said database:

\c cooldb

Allow SELECT on all existing tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO averagejoe;

Make sure that newly created tables will also become accessible:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO averagejoe;

Job done!

Tags: , , ,