How to create a user with readonly privileges for all databases in Postgresql?

user_663039 0
Viewed 44120

I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:

GRANT select ON DATABASE dbname to user1;

But I got the following error:

ERROR:  invalid privilege type SELECT for database

When I googled people advised to do the grant select operation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds?

database-permissions postgresql permissions security
7 Answers

I do the next steps for create read-only user:

create your_user:

1. createuser --interactive --pwprompt

go to postgresql in your_databases:

2. psql your_database

define access privileges:

3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;

define default access privileges:

4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;
user_94859290

Try:

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;
GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
user_4692940

I'm aware that this is a very old question, but it still comes up when searching for this problem so I'm just posting this for an up-to-date answer.

As of Postgres 14 or newer, there are now predefined roles for that purpose:

CREATE USER your_readonly_user WITH PASSWORD 'changeme';
GRANT pg_read_all_data TO your_readonly_user;

According to the docs, this gives all the necessary privileges to:

"Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to."

See here for more info on the predefined roles.

user_54928260
Related