DMARC-Filter: OpenDMARC Filter v1.3.2 mta-p8.oit.umn.edu 4XLS8P5P8cz9vCCL
MIME-Version: 1.0
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
 <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
In-Reply-To: <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
From: Matt Zagrabelny <mzagrabe@d.umn.edu>
Date: Sat, 5 Oct 2024 09:13:59 -0500
Message-ID: <CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r+rXSNjp8TQXcg@mail.gmail.com>
Subject: Re: grant connect to all databases
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e45f080623bb66a9"
Archived-At: <https://www.postgresql.org/message-id/CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r%2BrXSNjp8TQXcg%40mail.gmail.com>
Precedence: bulk

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

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


On Sat, Oct 5, 2024 at 9:02=E2=80=AFAM David G. Johnston <david.g.johnston@=
gmail.com>
wrote:

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

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

<div dir=3D"ltr"><div>Hi David (and others),<br></div><div><br></div><div>T=
hanks for the info about Public.</div><div><br></div><div>I should expound =
on my original email.</div><div><br></div><div>In our dev and test environm=
ents our admins (alice, bob, eve) are superusers. In production environment=
s we&#39;d like the admins to be read-only.</div><div><br></div><div>Is the=
 Public role something I can leverage to achieve this desire?</div><div><br=
></div><div>Thanks for the help!</div><div><br></div><div>-m<br></div><div>=
<br></div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"l=
tr" class=3D"gmail_attr">On Sat, Oct 5, 2024 at 9:02=E2=80=AFAM David G. Jo=
hnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.johnston@g=
mail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">On Saturday, October 5, 2024, Matt Zagrabelny &lt;<a href=3D"mailto=
:mzagrabe@d.umn.edu" target=3D"_blank">mzagrabe@d.umn.edu</a>&gt; wrote:<br=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>He=
llo,</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 pred=
efined 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 conne=
ct to my database(s).</div><div><br></div><div>I&#39;d like to not have to =
iterate over all the databases and &quot;GRANT 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></div><div><br></div><div>The pse=
udo-role Public exists for just this kind of thing.=C2=A0 In fact, in a def=
ault installation it already is given connect privileges on all databases c=
reated by the bootstrap superuser.</div><div><br></div><div>David J.</div><=
div><br></div>
</blockquote></div>

--000000000000e45f080623bb66a9--