GRANT read only access to a PostgreSQL database for a user

Uncategorized / February 27, 2025 / ashishpsk / 0

In your terminal, login as the postgres user (for security reasons, the postgres user has no password. That’s why we are logging in using -i option)

sudo -i -u postgres

OR

sudo -u postgres psql postgres

Connect to the PostgreSQL interactive terminal. Notice that you should mention the name of the concerned database, otherwise you will be doing operations on the postgres default database instead.

$ psql <dbName>

Allow the user to connect to the database

GRANT CONNECT ON DATABASE <dbName> TO <readonly_user>;

A database can have multiple schemas. A public schema is created and that’s where tables are created by default. So here we are granting usage to that specific schema.

GRANT USAGE ON SCHEMA public TO <readonly_user>;

…and all of it’s tables…

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <readonly_user>;

…and their related sequences

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <readonly_user>;