DMARC-Filter: OpenDMARC Filter v1.3.2 mta-p8.oit.umn.edu 4XLVc53FDGz9vZq3
MIME-Version: 1.0
References: <CAOLfK3Vj-PFBJi28y1170ZP3dGeW2qpG_8_9CbaJWvEgXQ8-jQ@mail.gmail.com>
 <CAKFQuwYG8uQhN50MgcF1seg8+dwvgTMFez=wA3Rg2rosob78cg@mail.gmail.com>
 <CAOLfK3XOHnyWsLv_CdFAegWg1FgM3AK3WsO_r+rXSNjp8TQXcg@mail.gmail.com> <cebb02fa-5487-4c06-8527-9c8ba29c13a9@aklaver.com>
In-Reply-To: <cebb02fa-5487-4c06-8527-9c8ba29c13a9@aklaver.com>
From: Matt Zagrabelny <mzagrabe@d.umn.edu>
Date: Sat, 5 Oct 2024 11:04:39 -0500
Message-ID: <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
Subject: Re: grant connect to all databases
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a159610623bcf24e"
Archived-At: <https://www.postgresql.org/message-id/CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk%3DBbq7MODX30O_REPvQrg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Oct 5, 2024 at 10:27=E2=80=AFAM Adrian Klaver <adrian.klaver@aklave=
r.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

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Sat, Oct 5, 2024 at 10:27=E2=80=AF=
AM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.kl=
aver@aklaver.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);pa=
dding-left:1ex">On 10/5/24 07:13, Matt Zagrabelny wrote:<br>
&gt; Hi David (and others),<br>
&gt; <br>
&gt; Thanks for the info about Public.<br>
&gt; <br>
&gt; I should expound on my original email.<br>
&gt; <br>
&gt; In our dev and test environments our admins (alice, bob, eve) are <br>
&gt; superusers. In production environments we&#39;d like the admins to be =
read-only.<br>
<br>
What are the REVOKE and GRANT commands you use to achieve that?<br></blockq=
uote><div><br></div><div>GRANT alice TO pg_read_all_data;</div><div><br></d=
iv><div>...and then I could do something like this:<br></div><div>-- for $d=
atabase in $databases;</div><div>GRANT CONNECT ON database $database TO ali=
ce;</div><div><br></div><div>...but I&#39;d like to achieve it without the =
`for` loop.<br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">
<br>
&gt; <br>
&gt; Is the Public role something I can leverage to achieve this desire?<br=
>
<br>
You should read:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/ddl-priv.html" rel=3D"no=
referrer" target=3D"_blank">https://www.postgresql.org/docs/current/ddl-pri=
v.html</a></blockquote><div><br></div><div>Will do.<br></div><div>=C2=A0</d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
<br>
<br>
=C2=A0From your original post:<br>
<br>
&quot;but I cannot connect to my database&quot;<br>
<br>
Was that due to a GRANT issue or a pg_hba.conf issue?<br></blockquote><div>=
<br></div><div>It was due to the missing GRANT CONNECT from above. pg_hba l=
ooks OK.<br></div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex">
What was the actual complete error?<br></blockquote><div><br></div><div>ali=
ce$ psql foo<br></div><div>psql: error: connection to server at &quot;<a hr=
ef=3D"http://db.example.com">db.example.com</a>&quot; (fe80:100), port 5432=
 failed: FATAL: =C2=A0permission denied for database &quot;foo&quot;<br></d=
iv><div>=C2=A0</div><div>...after I GRANT CONNECT, I can connect. However, =
I don&#39;t want to have to iterate over all the databases to achieve the G=
RANT CONNECT.</div><div><br></div><div>I guess I was hoping that the pg_rea=
d_all_data would also allow connecting. Or if it didn&#39;t, there could/wo=
uld be a pg_connect_all_databases role.<br></div><div><br></div><div>Cheers=
,</div><div><br></div><div>-m<br></div></div></div>

--000000000000a159610623bcf24e--