MIME-Version: 1.0
References: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
 <72acf8ae4e56886081b9f632569f290d3246c33b.camel@cybertec.at>
In-Reply-To: <72acf8ae4e56886081b9f632569f290d3246c33b.camel@cybertec.at>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Wed, 10 Dec 2025 08:06:17 -0500
Message-ID: 
 <CAGA3vBuWjNdz=zfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw@mail.gmail.com>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000053b17f064598b2d1"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBuWjNdz%3DzfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw%40mail.gmail.com>
Precedence: bulk

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

Laurenz,

Multiple clusters would be nice, but we don't have the available servers to
accomodate that.
Without the pg_read_all_data role there is apparently no other way in
PostgreSQL to automatically assign these privs to each and every table/view
that exists or will be created without using the nuclear option and
granting super user privs.  Unless there is something else that I am
missing which could be used when creating your suggested "readonly_dbname"
role.

It's a shame that PostgreSQL has created some extremely useful built in
roles, but then limits them such that they can only be utilized for
vanishingly few actual use cases.

Hopefully the PostgreSQL devs revisit these built in roles with a thought
toward making database specific ones assignable  with a mechanism like:

*grant *pg_read_all_data* on database *foo* to *user_role*;*

Thanks,
rik.


On Wed, Dec 10, 2025 at 5:01=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at>
wrote:

> On Tue, 2025-12-09 at 16:13 -0500, richard coleman wrote:
> > In PostgreSQL 16+ the built in roles such as pg_read_all_data
> and pg_write_all_data are a welcome addition to permission setting in
> PostgreSQL.
> >
> > Unfortunately they appear to be server-wide roles.
> >
> > Woud it be possible to have roles like these that are database specific=
?
> >
> > If there are 100 databases on a server, it would be extremely helpful t=
o
> be able to do something like:
> >
> > grant pg_read_all_data on database foo to user_role;
> >
> > Otherwise these roles are unusable from a practical stand point on
> servers with multiple unrelated databases.
>
> I think they were mostly added for compatibility with Microsoft SQL Serve=
r,
> if I remember correctly.
>
> I suggest creating roles named "readonly_dbname" for each database with
> the appropriate privileges and assigning those.
>
> A different approach would be to use different database clusters for
> different
> databases.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">Laurenz,<div><br></div><div>Multiple clusters would be nic=
e, but we don&#39;t have the available servers to accomodate that.</div><di=
v>Without the pg_read_all_data role there is apparently no other way in=C2=
=A0 PostgreSQL to automatically assign these privs to each and every table/=
view that exists or will be created without using the nuclear option and gr=
anting super user privs.=C2=A0 Unless there is something else that I am mis=
sing which could be used when creating your suggested &quot;readonly_dbname=
&quot; role.=C2=A0</div><div><br></div><div>It&#39;s a shame that PostgreSQ=
L has created some extremely useful built in roles, but then limits them su=
ch that they can only be utilized for vanishingly few actual use cases.</di=
v><div><br></div><div>Hopefully the PostgreSQL devs revisit these built in =
roles with a thought toward making database specific ones assignable=C2=A0 =
with a mechanism like:</div><div><br><b>grant=C2=A0</b>pg_read_all_data<b>=
=C2=A0on database=C2=A0</b>foo<b>=C2=A0to=C2=A0</b>user_role<b>;</b></div><=
div><br></div><div>Thanks,</div><div>rik.</div><div><b><br></b></div><div><=
br></div></div><br><div class=3D"gmail_quote gmail_quote_container"><div di=
r=3D"ltr" class=3D"gmail_attr">On Wed, Dec 10, 2025 at 5:01=E2=80=AFAM Laur=
enz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cyber=
tec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">On Tue, 2025-12-09 at 16:13 -0500, richard coleman wrote:<br>
&gt; In PostgreSQL 16+ the built in roles such as=C2=A0pg_read_all_data and=
=C2=A0pg_write_all_data are a welcome addition to permission setting in Pos=
tgreSQL.<br>
&gt; <br>
&gt; Unfortunately they appear to be server-wide roles.<br>
&gt; <br>
&gt; Woud it be possible to have roles like these that are database specifi=
c?<br>
&gt; <br>
&gt; If there are 100 databases on a server, it would be extremely helpful =
to be able to do something like:<br>
&gt; <br>
&gt; grant pg_read_all_data on database foo to user_role;<br>
&gt; <br>
&gt; Otherwise these roles are unusable from a practical stand point on ser=
vers with multiple unrelated databases.<br>
<br>
I think they were mostly added for compatibility with Microsoft SQL Server,=
<br>
if I remember correctly.<br>
<br>
I suggest creating roles named &quot;readonly_dbname&quot; for each databas=
e with<br>
the appropriate privileges and assigning those.<br>
<br>
A different approach would be to use different database clusters for differ=
ent<br>
databases.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--00000000000053b17f064598b2d1--