0

what's the recommended way to provide readonly access to all databases in postgresql version 12.

I am using this found at How do you create a read-only user in PostgreSQL?

CREATE ROLE readaccess;
CREATE USER my_user_test WITH PASSWORD 'my_user_test';

GRANT readaccess TO my_user_test;
\c database_name;

-- need to connect to the database first on which we need to execute the below commands
GRANT CONNECT ON DATABASE database_name TO readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Assign permissions to read all newly tables created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

But using this approach I need to individually connect to each database and provide the role read only access.

Is there a better approach to provide a read only access all existing databases at once?

Thanks.

opensource-developer
  • 2,826
  • 4
  • 38
  • 88
  • You should read the docs before depending on a 13 year old answer. Or at least look at the newer answers, for instance the one from Laurenz Albe which points you at the docs [Postgres pre-defined roles](https://www.postgresql.org/docs/current/predefined-roles.html) – Adrian Klaver May 16 '22 at 14:44
  • Thanks Adrian, this is helpful. sorry i missed informing in the question that i am trying to accomplish this on `v12` – opensource-developer May 16 '22 at 14:50
  • Ahh, that would make a difference. Then the method you are using is about as good as it gets. – Adrian Klaver May 16 '22 at 14:58

0 Answers0