Tag: PostgreSQL

How to add read only user to PostgreSQL (9+, Linux)

Login into postgres terminal first. It is important to specify database name you want to grant permissions to.

su postgres -c "/usr/pgsql-15/bin/psql your_database"

Than create new user:

CREATE ROLE readonly_user LOGIN ENCRYPTED PASSWORD 'readonly_password';

Then grant permissions to select from all tables in database that have already been created:

GRANT CONNECT ON DATABASE your_database TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

That’s all.

If you want read only user to automatically receive select permissions on newly created tables in future, than execute:

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

Sources:

  1. https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql