1

I got a database cluster with 1300 databases. I created a role and a user and granted access using the command GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser, so myuser is able to run queries in one particular database. When I connect to a different database with myuser and run select * from person, I get "permission denied", so I need to connect as admin to that database and run GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser again, then login as myuser and then I'm able to run select * from person.

I have 1300 databases in the cluster, as I said. Is there any command which grants read and write privileges in the whole cluster for myuser? Please note: myuser should not be a superuser.

I tried:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO service;`

\t

SELECT '\connect ' || datname || '; ' ||E'\n'|| 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to myuser;' FROM pg_database WHERE datistemplate = false;

but these commands only work in the database where I run them, and for all different databases I have to run them again like 1000 times.

0

1 Answer 1

2

Upgrade to at least PostgreSQL v14 and run

GRANT pg_read_all_data, pg_write_all_data TO myuser;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.