Create a read-only role and user in PostgreSQL

17 August, 2019 - 2 min read

This article assumes you can login to your PostgreSQL instance, and can run SQL queries on it.

Create a new database

First, login with the system user (usually postgres), to the postgres database.

psql -p <port> -U <user> <database>

Which gives, if the instance is listening on the default post (5432):

psql -p 5432 -U postgres postgres

Create the database exampledb:

CREATE DATABASE exampledb;

Now, quit the psql console, by typing \q, quit or exit. The last two commands work since PostgreSQL 11.

Connect to the newly created database

From this point onwards, the commands will be executed against the exampledb database. We connect to it with the system user:

psql -p 5432 -U postgres exampledb

Revoke default privileges

We need to revoke the existing privileges on the newly created database and on the public schema:

REVOKE ALL ON DATABASE exampledb FROM public; -- this will revoke default database privileges (CREATE, CONNECT ...) from roles in 'PUBLIC' (all roles). 
REVOKE ALL ON SCHEMA public FROM public; -- this will revoke default schemas privileges from roles in 'PUBLIC' (all roles).

Create the read-only role

-- reader role
CREATE ROLE exampledb_read_role NOINHERIT;
GRANT CONNECT ON DATABASE exampledb TO exampledb_read_role;
GRANT USAGE ON SCHEMA public TO exampledb_read_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO exampledb_read_role;

Note that in the reader role, we are granting selection privileges on tables of exampledb in an ALTER DEFAULT PRIVILEGES statement. Altering the default privileges is important, since, at this point, the newly created database does not have any table. This will make sure that the selection privileges are present for tables created in the future in that database. If you are applying the above recipe to a database with already existing tables, you also need the grant privileges on the existing tables (since we revoked existing privileges), in addition to the previous statements:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO exampledb_read_role;

Create a read-only user

-- user
CREATE USER exampledb_read_user INHERIT IN ROLE exampledb_read_role;

That's it. Now, creating additional read-only users is as simple as creating a user inheriting the role exampledb_read_role.