On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 10/5/24 07:13, Matt Zagrabelny wrote: > > Hi David (and others), > > > > Thanks for the info about Public. > > > > I should expound on my original email. > > > > In our dev and test environments our admins (alice, bob, eve) are > > superusers. In production environments we'd like the admins to be > read-only. > > What are the REVOKE and GRANT commands you use to achieve that? > GRANT alice TO pg_read_all_data; ...and then I could do something like this: -- for $database in $databases; GRANT CONNECT ON database $database TO alice; ...but I'd like to achieve it without the `for` loop. > > > > > Is the Public role something I can leverage to achieve this desire? > > You should read: > > https://www.postgresql.org/docs/current/ddl-priv.html Will do. > > > > From your original post: > > "but I cannot connect to my database" > > Was that due to a GRANT issue or a pg_hba.conf issue? > It was due to the missing GRANT CONNECT from above. pg_hba looks OK. > What was the actual complete error? > alice$ psql foo psql: error: connection to server at "db.example.com" (fe80:100), port 5432 failed: FATAL: permission denied for database "foo" ...after I GRANT CONNECT, I can connect. However, I don't want to have to iterate over all the databases to achieve the GRANT CONNECT. I guess I was hoping that the pg_read_all_data would also allow connecting. Or if it didn't, there could/would be a pg_connect_all_databases role. Cheers, -m
