DMARC-Filter: OpenDMARC Filter v1.3.2 mta-p5.oit.umn.edu 4XLfb14jVGz9vKZH
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> <CAOLfK3V10fBPAhXHLJ6hyZ41BVZQTk=Bbq7MODX30O_REPvQrg@mail.gmail.com>
 <0c089041-d4eb-4bf4-bfcb-6451224190c9@aklaver.com> <CAOLfK3W6K-r0+v7Sa9D6684hM3Jg0UxtZ4iOwZiJsteBj0rfoQ@mail.gmail.com>
 <8fec88b9-355f-487d-95ad-103af2229fde@aklaver.com>
In-Reply-To: <8fec88b9-355f-487d-95ad-103af2229fde@aklaver.com>
From: Matt Zagrabelny <mzagrabe@d.umn.edu>
Date: Sat, 5 Oct 2024 17:04:15 -0500
Message-ID: <CAOLfK3VmUpuPzqXTqr1So7WrqszVfUzkuGEOAk0gvStZ-aCXhA@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="000000000000aab9150623c1f8c4"
Archived-At: <https://www.postgresql.org/message-id/CAOLfK3VmUpuPzqXTqr1So7WrqszVfUzkuGEOAk0gvStZ-aCXhA%40mail.gmail.com>
Precedence: bulk

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

On Sat, Oct 5, 2024 at 3:12=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 10/5/24 11:24, Matt Zagrabelny wrote:
> >
>
> >
> > Nope. I create the role (via puppet) and then add the GRANT
> > pg_read_all_data TO (via puppet).
> >
>
> > What is \drgS? I don't believe I have that.
>
> That is available in Postgres 16+, you must running be in an instance of
> Postgres before that.
>

Ah. Yup!


>
>
> >   'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN
> > NOCREATEROLE NOCREATEDB NOSUPERUSER  CONNECTION LIMIT -1'
> > GRANT pg_read_all_data TO alice;
> >
>
> >   ...but I still cannot connect:
> >
> > $ psql -d test -U alice
> > psql: error: connection to server on socket
> > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  permission denied
> > for database "test"
> > DETAIL:  User does not have CONNECT privilege.
>
> Something is going on in the background.
>

Agreed.


>
> What version of Postgres?
>


psql (15.8 (Debian 15.8-0+deb12u1))


> Where did you install it from or where are you running it?
>

Installed from Debian repos via apt via puppet.


Still digging...

-m

--000000000000aab9150623c1f8c4
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 3:12=E2=80=AFP=
M Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.kla=
ver@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">On 10/5/24 11:24, Matt Zagrabelny wrote:<br>
&gt; <br>
<br>
&gt; <br>
&gt; Nope. I create the role (via puppet) and then add the GRANT <br>
&gt; pg_read_all_data TO (via puppet).<br>
&gt; <br>
<br>
&gt; What is \drgS? I don&#39;t believe I have that.<br>
<br>
That is available in Postgres 16+, you must running be in an instance of <b=
r>
Postgres before that.<br></blockquote><div><br></div><div>Ah. Yup!<br></div=
><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
<br>
&gt;=C2=A0 =C2=A0&#39;CREATE ROLE &quot;alice&quot; ENCRYPTED PASSWORD \=
9;$NEWPGPASSWD\&#39; LOGIN <br>
&gt; NOCREATEROLE NOCREATEDB NOSUPERUSER =C2=A0CONNECTION LIMIT -1&#39;<br>
&gt; GRANT pg_read_all_data TO alice;<br>
&gt; <br>
<br>
&gt;=C2=A0 =C2=A0...but I still cannot connect:<br>
&gt; <br>
&gt; $ psql -d test -U alice<br>
&gt; psql: error: connection to server on socket <br>
&gt; &quot;/var/run/postgresql/.s.PGSQL.5432&quot; failed: FATAL: =C2=A0per=
mission denied <br>
&gt; for database &quot;test&quot;<br>
&gt; DETAIL: =C2=A0User does not have CONNECT privilege.<br>
<br>
Something is going on in the background.<br></blockquote><div><br></div><di=
v>Agreed.<br></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">
<br>
What version of Postgres?<br></blockquote><div><br></div><div><br></div><di=
v>psql (15.8 (Debian 15.8-0+deb12u1)) </div><div><br></div><div>=C2=A0<br><=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">
Where did you install it from or where are you running it?<br></blockquote>=
<div><br></div><div>Installed from Debian repos via apt via puppet.</div><d=
iv><br></div><div>=C2=A0</div><div>Still digging...</div><div><br></div><di=
v>-m<br></div></div></div>

--000000000000aab9150623c1f8c4--