MIME-Version: 1.0
References: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
 <72acf8ae4e56886081b9f632569f290d3246c33b.camel@cybertec.at>
 <CAGA3vBuWjNdz=zfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw@mail.gmail.com>
 <8536f893e79693bd0a23d4cea7dbe0b6366378df.camel@cybertec.at>
 <CAGA3vBtp2H4+v-AnWLq_w8TVAuHS5++6dHW98YN1-q7N66ywYQ@mail.gmail.com>
 <CAKFQuwZducmkxoyh_=gvdHJ2Te5rHGctv70BQQsacmgMMjtZRw@mail.gmail.com>
 <647281.1765388029@sss.pgh.pa.us>
In-Reply-To: <647281.1765388029@sss.pgh.pa.us>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Wed, 10 Dec 2025 13:00:35 -0500
Message-ID: 
 <CAGA3vBs53Lwg2-2XiJO7+BW3mMOxCCbg2vw74MUDqFQ3-ajL2w@mail.gmail.com>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bf6b0106459cce35"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBs53Lwg2-2XiJO7%2BBW3mMOxCCbg2vw74MUDqFQ3-ajL2w%40mail.gmail.com>
Precedence: bulk

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

Tom,

You are *almost* there I think.  By my understanding, which admittedly
might be flawed, "pg_read_all_data" once given allows the role with that
privlidge to litterally "read all data" across all databases in that
cluster.  So while one can revoke public connect privs to a database and
keep a role with pg_read_all_data privs from connecting to it, you're
otherwise pretty much out of luck.  That option is unavailable in the
situation where users have differing privs on the same cluster.  For
example, if user0 needs to have "read_all" privs in database0, "read_all"
and "write_all" privs in database1, and various privs in database2 on the
same cluster, you can't use the CONNECT nor pg_hba.conf workarounds.  As
soon as a role who's a member of
"pg_read_all_data" can connect to a database in that cluster, it's game
over.  Doubly so for roles with the "pg_write_all_data" priv.

These built-in roles are a much welcomed addition in PostgreSQL.
Unfortunately in PostgreSQL, unlike other RDBMSs, roles are cluster wide
not database specific.  This leads to some interesting things in
multi-database tools such as DBeaver which includes a seperate Roles folder
in each PostgreSQL database connection containing copies of the exact same
roles.  Use the GUI to alter a role in the Roles folder for database0,
potentially be amazed that database1, and every other database in that
cluster, magically reflects the change.  I'm not saying that is is
nessicarrilly a bad thing, just different.  What it does mean though is
that cluster wide roles and privs can and do much more than one might
suspect.  This discussion of pg_read_all_data being a prime example.
Basically I think that because of the reliance on cluster wide roles in
PostgreSQL, it's potentially dangerous to introduce built-in roles with far
ranging privs without having a machinaism to limit them to specific
databases in that cluster.  The only realistic way to take advantage of the
extrodinarilly useful abilites they enable is to limit them to the
relatively rare instances where there is only a single database on a
cluster, or when the users can have the same access to all of the databases
on that cluster.

Hopefully I've made my self clear enough in this matter and have
demonstrated how being able to limit built-in cluster specific privs in a
per database way would be very useful.

Thanks for taking the time, everyone, to read my missives and contribute
your thoughts in this.
rik.

On Wed, Dec 10, 2025 at 12:33=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Fundamentally making group-role memberships per-database is a fundament=
al
> > change that seems quite unappealing to attempt without a solid use case
> > that it will enable.
>
> Yeah, I think this would be bad from both the intellectual-complexity
> and implementation-difficulty standpoints.
>
> However ... we've had multiple requests in the past to invent
> database-specific roles.  I wonder if it'd suffice for Richard's
> purposes to create such roles and grant them pg_read_all_data.
>
> You can sort of do that today, in that you can muck with pg_hba.conf
> or database CONNECT privileges to limit which DBs a role can log into.
> But either answer works only at initial login; they don't constrain
> SET ROLE, so they're not really adequate for permissions-limiting
> purposes.  I'm imagining a feature whereby a database-specific role
> is flat out not available in other databases; can't SET ROLE to it,
> can't GRANT privileges (at least on non-shared objects) to it.
> Probably role membership would still be nominally global, but it
> wouldn't matter if you couldn't use the role.
>
> This might still not pass the too-much-complexity test, but it
> has the advantage of being something that there's been multiple
> requests for.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr">Tom,=C2=A0<div><br></div><div>You are *almost* there I thi=
nk.=C2=A0 By my understanding, which admittedly might be flawed, &quot;pg_r=
ead_all_data&quot; once given allows the role with that privlidge to litter=
ally &quot;read all data&quot; across all databases in that cluster.=C2=A0 =
So while one can revoke public connect privs to a database and keep a role =
with pg_read_all_data privs from connecting to it, you&#39;re otherwise pre=
tty much out of luck.=C2=A0 That option is unavailable in the situation whe=
re users have differing privs on the same cluster.=C2=A0 For example, if us=
er0 needs to have &quot;read_all&quot; privs in database0, &quot;read_all&q=
uot; and &quot;write_all&quot; privs in database1, and various privs in dat=
abase2 on the same cluster, you can&#39;t use the CONNECT nor pg_hba.conf w=
orkarounds.=C2=A0 As soon as a role who&#39;s a member of=C2=A0<br>&quot;pg=
_read_all_data&quot; can connect to a database in that cluster, it&#39;s ga=
me over.=C2=A0 Doubly so for roles with the &quot;pg_write_all_data&quot; p=
riv.</div><div><br></div><div>These built-in roles are a much welcomed addi=
tion in PostgreSQL.=C2=A0 Unfortunately in PostgreSQL, unlike other RDBMSs,=
 roles are cluster wide not database specific.=C2=A0 This leads to some int=
eresting things in multi-database tools such as DBeaver which includes a se=
perate Roles folder in each PostgreSQL database connection containing copie=
s of the exact same roles.=C2=A0 Use the GUI to alter a role in the Roles f=
older for database0, potentially be amazed that database1, and every other =
database in that cluster, magically reflects the change.=C2=A0 I&#39;m not =
saying that is is nessicarrilly a bad thing, just different.=C2=A0 What it =
does mean though is that cluster wide roles and privs can and do much more =
than one might suspect.=C2=A0 This discussion of pg_read_all_data being a p=
rime example.=C2=A0 Basically I think that because of the reliance on clust=
er wide roles in PostgreSQL, it&#39;s potentially dangerous to introduce bu=
ilt-in roles with far ranging privs without having a machinaism to limit th=
em to specific databases in that cluster.=C2=A0 The only realistic way to t=
ake advantage of the extrodinarilly useful abilites they enable is to limit=
 them to the relatively rare instances where there is only a single databas=
e on a cluster, or when the users can have the same access to all of the da=
tabases on that cluster.</div><div><br></div><div>Hopefully I&#39;ve made m=
y self clear enough in this matter and have demonstrated how being able to =
limit built-in cluster specific privs in a per database way would be very u=
seful.</div><div><br></div><div>Thanks for taking the time, everyone, to re=
ad my missives and contribute your thoughts in this.</div><div>rik.</div></=
div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" c=
lass=3D"gmail_attr">On Wed, Dec 10, 2025 at 12:33=E2=80=AFPM Tom Lane &lt;<=
a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></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">&quot;David G. Johnsto=
n&quot; &lt;<a href=3D"mailto:david.g.johnston@gmail.com" target=3D"_blank"=
>david.g.johnston@gmail.com</a>&gt; writes:<br>
&gt; Fundamentally making group-role memberships per-database is a fundamen=
tal<br>
&gt; change that seems quite unappealing to attempt without a solid use cas=
e<br>
&gt; that it will enable.<br>
<br>
Yeah, I think this would be bad from both the intellectual-complexity<br>
and implementation-difficulty standpoints.<br>
<br>
However ... we&#39;ve had multiple requests in the past to invent<br>
database-specific roles.=C2=A0 I wonder if it&#39;d suffice for Richard&#39=
;s<br>
purposes to create such roles and grant them pg_read_all_data.<br>
<br>
You can sort of do that today, in that you can muck with pg_hba.conf<br>
or database CONNECT privileges to limit which DBs a role can log into.<br>
But either answer works only at initial login; they don&#39;t constrain<br>
SET ROLE, so they&#39;re not really adequate for permissions-limiting<br>
purposes.=C2=A0 I&#39;m imagining a feature whereby a database-specific rol=
e<br>
is flat out not available in other databases; can&#39;t SET ROLE to it,<br>
can&#39;t GRANT privileges (at least on non-shared objects) to it.<br>
Probably role membership would still be nominally global, but it<br>
wouldn&#39;t matter if you couldn&#39;t use the role.<br>
<br>
This might still not pass the too-much-complexity test, but it<br>
has the advantage of being something that there&#39;s been multiple<br>
requests for.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000bf6b0106459cce35--