GRANT read only access to a PostgreSQL database for a user
I needed to create a PostgreSQL user that will have read only permissions on my database in order to do backups. I spent sometime trying to figure out how to do that. Most of this solution is inspired by this Stack Overflow answer. As I am fairly new to PostgreSQL I can’t assure the steps below represent the best approach to follow, I even believe some steps are unnecessary (which is something I will mention when in doubt). So let’s start from the beginning :
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
Then you 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>
We first of all allow the user to connect to the database
GRANT CONNECT ON DATABASE <dbName> TO <readonly_user>;
As I’m fairly new to permissions in PostgreSQL I granted database usage for the read only user, even though I believe this step is not necessary.
GRANT USAGE 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>;
As we want our <readonly_user> to have only read permissions we grant them to do SELECT queries on our database…
GRANT SELECT ON DATABASE <dbName> 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>;
The above manipulations grants read only access to the user for existing tables. If you wanna generalize to the tables that might be created later on you will have to change that by altering the default behavior
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <readonly_user>;
I hope this helps.