How to grant read-write access to a user within the whole database cluster?
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
myuseris able to run queries in one particular database. When I connect to a different database with
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 myuseragain, then login as
myuserand 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:
myusershould not be a superuser.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO service;`
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.
Upgrade to at least PostgreSQL v14 and run
GRANT read_all_data, write_all_data TO myuser;