MIME-Version: 1.0
References: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
 <CANzqJaA8JTM1V_+9ACXGWjbCYYu_hio5EA-=2ne_7jmmhw31FQ@mail.gmail.com>
In-Reply-To: 
 <CANzqJaA8JTM1V_+9ACXGWjbCYYu_hio5EA-=2ne_7jmmhw31FQ@mail.gmail.com>
From: richard coleman <rcoleman.ascentgl@gmail.com>
Date: Tue, 9 Dec 2025 18:21:05 -0500
Message-ID: 
 <CAGA3vBs=_E7eOXRngW9fkKSvE+r9_s+z=wOZNBvjfZ_yRROc5Q@mail.gmail.com>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001b467606458d2b84"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGA3vBs%3D_E7eOXRngW9fkKSvE%2Br9_s%2Bz%3DwOZNBvjfZ_yRROc5Q%40mail.gmail.com>
Precedence: bulk

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

Ron,

That wouldn't come even close to what pg_read_all_data grants.
A role assigned to pg_read_all_data automatically has the ability to read
everything, in every schema that exists now or in the future.

The old way, your suggestion, means that you have to keep rerunning that
command everytime someone creates a schema, creates a table, creates a
view, recreates a table, recreates a view, etc. for all eternity.  Not only
that, you have to tailor the command to each new schema, etc.

This makes shared privs much more streamlined and removes the chance that a
user will forget to assign privs to objects that they create.

I hope that helps make it clearer.
rik.


On Tue, Dec 9, 2025 at 5:46=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com=
> wrote:

> On Tue, Dec 9, 2025 at 4:13=E2=80=AFPM richard coleman <
> rcoleman.ascentgl@gmail.com> 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 to
>> 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.
>>
>
> How about
> ALTER DEFAULT PRIVILEGES IN SCHEMA foo1, foo2, foo3, ... GRANT SELECT ON
> ALL TABLE TO bar;
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

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

<div dir=3D"ltr">Ron,<div><br></div><div>That wouldn&#39;t come even close =
to what pg_read_all_data grants.</div><div>A role assigned to pg_read_all_d=
ata automatically has the ability to read everything, in every schema that =
exists now or in the future.</div><div><br></div><div>The old way, your sug=
gestion, means that you have to keep rerunning that command everytime someo=
ne creates a schema, creates a table, creates a view, recreates a table, re=
creates a view, etc. for all eternity.=C2=A0 Not only that, you have to tai=
lor the command to each new schema, etc.</div><div><br></div><div>This make=
s shared privs much more streamlined and removes the chance that a user wil=
l forget to assign privs to objects that they create.</div><div><br></div><=
div>I hope that helps make it clearer.</div><div>rik.</div><div><br></div><=
div><br></div><div><br></div></div><br><div class=3D"gmail_quote gmail_quot=
e_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Dec 9, 2025 at 5=
:46=E2=80=AFPM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com">r=
onljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 9, 2025=
 at 4:13=E2=80=AFPM richard coleman &lt;<a href=3D"mailto:rcoleman.ascentgl=
@gmail.com" target=3D"_blank">rcoleman.ascentgl@gmail.com</a>&gt; wrote:</d=
iv><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><div dir=3D"ltr">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 permissi=
on setting in PostgreSQL.<div><br></div><div>Unfortunately they appear to b=
e server-wide roles.</div><div><br></div><div>Woud it be possible to have r=
oles like these that are database specific?</div><div><br></div><div>If the=
re are 100 databases on a server, it would be extremely helpful to be able =
to do something like:</div><div><br></div><div><b>grant </b>pg_read_all_dat=
a<b> on database </b>foo<b> to </b>user_role<b>;</b></div><div><br></div><d=
iv>Otherwise these roles are unusable from a practical stand point on serve=
rs with multiple unrelated databases.</div></div></blockquote><div><br></di=
v><div></div></div><div>How about=C2=A0</div><div>ALTER=C2=A0DEFAULT PRIVIL=
EGES IN SCHEMA foo1, foo2, foo3, ... GRANT SELECT ON ALL TABLE TO bar;</div=
><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div d=
ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted=
&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div=
><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>
</blockquote></div>

--0000000000001b467606458d2b84--