Re: grant connect to all databases

On Sat, Oct 5, 2024 at 10:27 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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