Subject: Re: grant connect to all databases

From: "David G. Johnston" <[email-masked]>
Date: Sat, 5 Oct 2024 07:02:43 -0700

On Saturday, October 5, 2024, Matt Zagrabelny <[email-masked]> wrote:

> Hello,
>
> I'd like to have a read-only user for all databases.
>
> I found the pg_read_all_data role predefined role, which I granted to my
> RO user:
>
> GRANT pg_read_all_data TO ro_user;
>
> ...but I cannot connect to my database(s).
>
> I'd like to not have to iterate over all the databases and "GRANT
> CONNECT...".
>
> Is there a way to do this with just one GRANT or equivalent command?
>

The pseudo-role Public exists for just this kind of thing.  In fact, in a
default installation it already is given connect privileges on all
databases created by the bootstrap superuser.

David J.

---

From: Matt Zagrabelny <[email-masked]>
Date: Sat, 5 Oct 2024 09:13:59 -0500

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.

Is the Public role something I can leverage to achieve this desire?

Thanks for the help!

-m

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Oct 2024 08:27:21 -0700

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

> Is the Public role something I can leverage to achieve this desire?

You should read:

https://www.postgresql.org/docs/current/ddl-priv.html

 From your original post:

"but I cannot connect to my database"

Was that due to a GRANT issue or a pg_hba.conf issue?

What was the actual complete error?

> 
> Thanks for the help!
> 
> -m
> 
> 
> 
> On Sat, Oct 5, 2024 at 9:02 AM David G. Johnston 
> <[email-masked] <mailto:[email-masked]>> wrote:
> 
>     On Saturday, October 5, 2024, Matt Zagrabelny <[email-masked]
>     <mailto:[email-masked]>> wrote:
> 
>         Hello,
> 
>         I found the pg_read_all_data role predefined role, which I
>         granted to my RO user:
> 
>         I'd like to not have to iterate over all the databases and
>         "GRANT CONNECT...".
> 
>     The pseudo-role Public exists for just this kind of thing.  In fact,
>     in a default installation it already is given connect privileges on
>     all databases created by the bootstrap superuser.
> 
>     David J.
> 

-- 
Adrian Klaver
[email-masked]