MIME-Version: 1.0
In-Reply-To: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sat, 5 Oct 2024 07:02:43 -0700
Message-ID: <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
Subject: Re: grant connect to all databases
To: Matt Zagrabelny <mzagrabe@d.umn.edu>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000de0a1d0623bb3d73"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYG8uQhN50MgcF1seg8%2BdwvgTMFez%3DwA3Rg2rosob78cg%40mail.gmail.com>
Precedence: bulk

--000000000000de0a1d0623bb3d73
Content-Type: text/plain; charset="UTF-8"

On Saturday, October 5, 2024, Matt Zagrabelny <mzagrabe@d.umn.edu> 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.

--000000000000de0a1d0623bb3d73
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Saturday, October 5, 2024, Matt Zagrabelny &lt;<a href=3D"mailto:mzagrab=
e@d.umn.edu">mzagrabe@d.umn.edu</a>&gt; wrote:<br><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex"><div dir=3D"ltr"><div>Hello,</div><div><br></div><div>I&#39;d like to=
 have a read-only user for all databases.</div><div><br></div><div>I found =
the pg_read_all_data role predefined role, which I granted to my RO user:</=
div><div><br></div><div>GRANT pg_read_all_data TO ro_user;</div><div><br></=
div><div>...but I cannot connect to my database(s).</div><div><br></div><di=
v>I&#39;d like to not have to iterate over all the databases and &quot;GRAN=
T CONNECT...&quot;.</div><div><br></div><div>Is there a way to do this with=
 just one GRANT or equivalent command?</div></div></blockquote><div><br></d=
iv><div><br></div><div>The pseudo-role Public exists for just this kind of =
thing.=C2=A0 In fact, in a default installation it already is given connect=
 privileges on all databases created by the bootstrap superuser.</div><div>=
<br></div><div>David J.</div><div><br></div>

--000000000000de0a1d0623bb3d73--