How to grant read-write access to a user within the whole database cluster?
1
vote
1
answer
4605
views
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.
Asked by azamat
(11 rep)
Jun 2, 2022, 05:32 PM
Last activity: Jan 10, 2024, 12:00 PM
Last activity: Jan 10, 2024, 12:00 PM