Message-ID: <8536f893e79693bd0a23d4cea7dbe0b6366378df.camel@cybertec.at>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Date: Wed, 10 Dec 2025 14:10:03 +0100
In-Reply-To: 
 <CAGA3vBuWjNdz=zfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw@mail.gmail.com>
References: 
	<CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
	 <72acf8ae4e56886081b9f632569f290d3246c33b.camel@cybertec.at>
	 <CAGA3vBuWjNdz=zfMNvpqYRJRdQCapbexWnD4kgOss2PMbw5ZZw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/8536f893e79693bd0a23d4cea7dbe0b6366378df.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-12-10 at 08:06 -0500, richard coleman wrote:
> Multiple clusters would be nice, but we don't have the available servers =
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=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 granting super user privs.
> Unless there is something else that I am missing which could be used when=
 creating your
> suggested "readonly_dbname" role.=C2=A0

Yes, and that is ALTER DEFAULT PRIVILEGES.

> It's a shame that PostgreSQL has created some extremely useful built in r=
oles, but then
> limits them such that they can only be utilized for vanishingly few actua=
l use cases.
>=20
> Hopefully the PostgreSQL devs revisit these built in roles with a thought=
 toward making
> database specific ones assignable=C2=A0 with a mechanism like:
>=20
> grant=C2=A0pg_read_all_data=C2=A0on database=C2=A0foo=C2=A0to=C2=A0user_r=
ole;

Frankly, I think that "pg_read_all_data" is ugly and should never have been=
 added.

Yours,
Laurenz Albe