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>
In-Reply-To: 
 <CAKFQuwZducmkxoyh_=gvdHJ2Te5rHGctv70BQQsacmgMMjtZRw@mail.gmail.com>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Wed, 10 Dec 2025 11:22:03 -0500
Message-ID: 
 <CAGA3vBsf9i9q7Z1JLJYXi=aQG8SsVhSyqGAqy84eUkRmsuJqLg@mail.gmail.com>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
	Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005f1b9206459b6e3e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBsf9i9q7Z1JLJYXi%3DaQG8SsVhSyqGAqy84eUkRmsuJqLg%40mail.gmail.com>
Precedence: bulk

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

David,

The most common situation is when there are disparate groups, each with
their own databases that are expected to have access to all of the
schema/tables/views in that database regardless of who creates them.

When that group has their own PostgreSQL cluster, the simpilest way to
achive this is to grant all of those users membershipo in the
pg_read_all_data and pg_write_all_data built-in roles.  Unfortunately, the
way that these roles work, this isn't an option when there are multiple
groups, each with their own database, sharing a PostgreSQL cluster.

Previously those users were sharing a single role so that they didn't run
into priviledge issues.  We've been discouraging this practice from
continuing for obvious reasons.

Without a database specific version of the pg_read_all_data and
pg_write_all_data built in roles we have to rely upon the users, some who
aren't particularly database savy, to remember to either reassign ownership
of their objects to a shared group role, or explicitely grant privs to
other members of their group.  As you can expect it isn't ideal and the DBA
has to occationally step in to grant these privs.

This is why I was inquiting after database specific versions of those
built-in roles.  Just as we can currently assign database specific privs;
connect, temporary, etc., being able to do the same with these built-in
roles would be a godsend.

I hope that helps clear things up.
rik.

On Wed, Dec 10, 2025 at 9:25=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Wednesday, December 10, 2025, richard coleman <
> rcoleman.ascentgl@gmail.com> wrote:
>>
>> I hope that the PostgreSQL devs revisit it in the future with an eye
>> towards making it applicable in more situations.
>>
>
> There are setups where roles can access multiple databases and in some of
> those they have read/write all privileges and in others they do not?
>
> Fundamentally making group-role memberships per-database is a fundamental
> change that seems quite unappealing to attempt without a solid use case
> that it will enable.  iMO you=E2=80=99ve claims here do not establish a s=
olid use
> case - they are lacking convincing details.  That said, the project is op=
en
> source - you can scratch your own itch.  But the model change is still a
> complexity hill to overcome.
>
> David J.
>
>

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

<div dir=3D"ltr">David,<div><br></div><div>The most common situation is whe=
n there are disparate groups, each with their own databases that are expect=
ed to have access to all of the schema/tables/views in that database regard=
less of who creates them.</div><div><br></div><div>When that group has thei=
r own PostgreSQL cluster, the simpilest way to achive this is to grant all =
of those users membershipo in the pg_read_all_data and pg_write_all_data bu=
ilt-in roles.=C2=A0 Unfortunately, the way that these roles work, this isn&=
#39;t an option when there are multiple groups, each with their own databas=
e, sharing a PostgreSQL cluster.</div><div><br></div><div>Previously those =
users were sharing a single role so that they didn&#39;t run into priviledg=
e issues.=C2=A0 We&#39;ve been discouraging this practice from continuing f=
or obvious reasons.</div><div><br></div><div>Without a database specific ve=
rsion of the pg_read_all_data and pg_write_all_data built in roles we have =
to rely upon the users, some who aren&#39;t particularly database savy, to =
remember to either reassign ownership of their objects to a shared group ro=
le, or explicitely grant privs to other members of their group.=C2=A0 As yo=
u can expect it isn&#39;t ideal and the DBA has to occationally step in to =
grant these privs.</div><div><br></div><div>This is why I was inquiting aft=
er database specific versions of those built-in roles.=C2=A0 Just as we can=
 currently assign database specific privs; connect, temporary, etc., being =
able to do the same with these built-in roles would be a godsend.</div><div=
><br></div><div>I hope that helps clear things up.</div><div>rik.</div></di=
v><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Wed, Dec 10, 2025 at 9:25=E2=80=AFAM David G. Johnston=
 &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.c=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
">On Wednesday, December 10, 2025, richard coleman &lt;<a href=3D"mailto:rc=
oleman.ascentgl@gmail.com" target=3D"_blank">rcoleman.ascentgl@gmail.com</a=
>&gt; wrote:<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr"><div>I hope that the PostgreSQL devs revisit it in the future with an e=
ye towards making it applicable in more situations.</div></div></blockquote=
><div><br></div><div>There are setups where roles can access multiple datab=
ases and in some of those they have read/write all privileges and in others=
 they do not?</div><div><br></div><div>Fundamentally making group-role memb=
erships per-database is a fundamental change that seems quite unappealing t=
o attempt without a solid use case that it will enable. =C2=A0iMO you=E2=80=
=99ve claims here do not establish a solid use case - they are lacking conv=
incing details.=C2=A0 That said, the project is open source - you can scrat=
ch your own itch.=C2=A0 But the model change is still a complexity hill to =
overcome.</div><div><br></div><div>David J.</div><div><br></div>
</blockquote></div>

--0000000000005f1b9206459b6e3e--