MIME-Version: 1.0
References: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
 <72acf8ae4e56886081b9f632569f290d3246c33b.camel@cybertec.at>
 <CAGA3vBuWjNdz=zfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw@mail.gmail.com>
 <8536f893e79693bd0a23d4cea7dbe0b6366378df.camel@cybertec.at>
In-Reply-To: <8536f893e79693bd0a23d4cea7dbe0b6366378df.camel@cybertec.at>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Wed, 10 Dec 2025 09:10:29 -0500
Message-ID: 
 <CAGA3vBtp2H4+v-AnWLq_w8TVAuHS5++6dHW98YN1-q7N66ywYQ@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="000000000000d4385d0645999755"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBtp2H4%2Bv-AnWLq_w8TVAuHS5%2B%2B6dHW98YN1-q7N66ywYQ%40mail.gmail.com>
Precedence: bulk

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

Laurenz,

Running many clusters on a single server, while possible, reduces the
amount of memory available to each cluster and each database process users
run respectively.

ALTER DEFAULT PRIVLIGES doesn't work on schema that doesn't exist at that
time that command was run.

I am sorry to hear that you think "pg_read_all_data" is ugly.  That
built-in role and others like it have proven very useful for a fairly
common use case; a small group of users that must share database objects
between them without having to constantly rejigger privileges on those
objects.

In the rare case where a group has their own database cluster it has saved
a lot of work.  Sadly, it is unable to be utilized on shared clusters
hosting dozens of databases for different groups in its current form.

I hope that the PostgreSQL devs revisit it in the future with an eye
towards making it applicable in more situations.

Thanks for you input,
rik.


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

> On Wed, 2025-12-10 at 08:06 -0500, richard coleman wrote:
> > Multiple clusters would be nice, but we don't have the available server=
s
> to accomodate that.
>
> You can run many clusters on a single server...
>
> > 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.
>
> Yes, and that is ALTER DEFAULT PRIVILEGES.
>
> > 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;
>
> Frankly, I think that "pg_read_all_data" is ugly and should never have
> been added.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">Laurenz,<div><br></div><div>Running many clusters on a sin=
gle server, while possible, reduces the amount of memory available to each =
cluster and each database process users run respectively.</div><div><br></d=
iv><div>ALTER DEFAULT PRIVLIGES doesn&#39;t work on schema that doesn&#39;t=
 exist at that time that command was run.</div><div><br></div><div>I am sor=
ry to hear that you think &quot;pg_read_all_data&quot; is ugly.=C2=A0 That =
built-in role and others like it have proven very useful for a fairly commo=
n use case; a small group of users that must share database objects between=
 them without having to constantly rejigger privileges on those objects.=C2=
=A0</div><div><br></div><div>In the rare case where a group has their own d=
atabase cluster it has saved a lot of work.=C2=A0 Sadly, it is unable to be=
 utilized on shared clusters hosting dozens of databases for different grou=
ps in its current form.</div><div><br></div><div>I hope that the PostgreSQL=
 devs revisit it in the future with an eye towards making it applicable in =
more situations.</div><div><br></div><div>Thanks for you input,</div><div>r=
ik.</div><div><br></div><div><br></div></div><br><div class=3D"gmail_quote =
gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Dec 10=
, 2025 at 8:10=E2=80=AFAM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@c=
ybertec.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">On Wed, 2025-12-10 at 08:06 -0500, rich=
ard coleman wrote:<br>
&gt; Multiple clusters would be nice, but we don&#39;t have the available s=
ervers to accomodate that.<br>
<br>
You can run many clusters on a single server...<br>
<br>
&gt; Without the pg_read_all_data role there is apparently no other way in=
=C2=A0 PostgreSQL to<br>
&gt; automatically assign these privs to each and every table/view that exi=
sts or will be<br>
&gt; created without using the nuclear option and granting super user privs=
.<br>
&gt; Unless there is something else that I am missing which could be used w=
hen creating your<br>
&gt; suggested &quot;readonly_dbname&quot; role.=C2=A0<br>
<br>
Yes, and that is ALTER DEFAULT PRIVILEGES.<br>
<br>
&gt; It&#39;s a shame that PostgreSQL has created some extremely useful bui=
lt in roles, but then<br>
&gt; limits them such that they can only be utilized for vanishingly few ac=
tual use cases.<br>
&gt; <br>
&gt; Hopefully the PostgreSQL devs revisit these built in roles with a thou=
ght toward making<br>
&gt; database specific ones assignable=C2=A0 with a mechanism like:<br>
&gt; <br>
&gt; grant=C2=A0pg_read_all_data=C2=A0on database=C2=A0foo=C2=A0to=C2=A0use=
r_role;<br>
<br>
Frankly, I think that &quot;pg_read_all_data&quot; is ugly and should never=
 have been added.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000d4385d0645999755--