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 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.



  • Upgrade to at least PostgreSQL v14 and run

    GRANT read_all_data, write_all_data TO myuser;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2