Read-only User

PostgreSQL 14

PostgreSQL 14 introduced two predefined roles: pg_read_all_data and pg_write_all_data.

CREATE ROLE read_only WITH LOGIN PASSWORD 'sosecure';
GRANT pg_read_all_data TO read_only;

-- If you need write access in PostgreSQL 14:
GRANT pg_write_all_data TO role_needs_write;

Before PostgreSQL 14

-- readonly role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE db1 TO readonly_user;
GRANT CONNECT ON DATABASE db2 TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Need this for table created after this SQL
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

CREATE USER readonly_user WITH PASSWORD 'secret';
GRANT readonly TO readonly_user;