How to create a read-only user in Postgresql

This is a note of how to create a read-only user in Postgresql database.

First, connect to the database by super user.

Run this command to create an user and assign read-only to that user

CREATE USER <user> WITH PASSWORD '<password>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <user>;

Example

CREATE USER readonly1 WITH PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly1;

Example of revoke and drop users

Revoke and drop users

DROP USER readonly1;
REVOKE ALL PRIVILEGES ON DATABASE <database> FROM readonly1;