MIME-Version: 1.0
References: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
In-Reply-To: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 9 Dec 2025 17:45:58 -0500
Message-ID: 
 <CANzqJaA8JTM1V_+9ACXGWjbCYYu_hio5EA-=2ne_7jmmhw31FQ@mail.gmail.com>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000088069706458cad2d"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaA8JTM1V_%2B9ACXGWjbCYYu_hio5EA-%3D2ne_7jmmhw31FQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, Dec 9, 2025 at 4:13=E2=80=AFPM richard coleman <rcoleman.ascentgl@g=
mail.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 server=
s
> with multiple unrelated databases.
>

How about
ALTER DEFAULT PRIVILEGES IN SCHEMA foo1, foo2, foo3, ... GRANT SELECT ON
ALL TABLE TO bar;

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 9, 2025 at 4:13=E2=80=AFPM ri=
chard coleman &lt;<a href=3D"mailto:rcoleman.ascentgl@gmail.com">rcoleman.a=
scentgl@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote=
_container"><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">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.<div><br></div><div>Unfortunately they appear to be server-wide rol=
es.</div><div><br></div><div>Woud it be possible to have roles like these t=
hat are database specific?</div><div><br></div><div>If there are 100 databa=
ses on a server, it would be extremely helpful to be able to do something l=
ike:</div><div><br></div><div><b>grant </b>pg_read_all_data<b> on database =
</b>foo<b> to </b>user_role<b>;</b></div><div><br></div><div>Otherwise thes=
e roles are unusable from a practical stand point on servers with multiple =
unrelated databases.</div></div></blockquote><div><br></div><div></div></di=
v><div>How about=C2=A0</div><div>ALTER=C2=A0DEFAULT PRIVILEGES IN SCHEMA fo=
o1, foo2, foo3, ... GRANT SELECT ON ALL TABLE TO bar;</div><div><br></div><=
span class=3D"gmail_signature_prefix">-- </span><br><div dir=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;Redact=
ed&gt; lobster!</div></div></div></div></div></div>

--00000000000088069706458cad2d--