Message-ID: <72acf8ae4e56886081b9f632569f290d3246c33b.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>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Wed, 10 Dec 2025 11:01:18 +0100
In-Reply-To: 
 <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
References: 
	<CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@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/72acf8ae4e56886081b9f632569f290d3246c33b.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-12-09 at 16:13 -0500, richard coleman wrote:
> 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 Postgr=
eSQL.
>=20
> Unfortunately they appear to be server-wide roles.
>=20
> Woud it be possible to have roles like these that are database specific?
>=20
> If there are 100 databases on a server, it would be extremely helpful to =
be able to do something like:
>=20
> grant pg_read_all_data on database foo to user_role;
>=20
> Otherwise these roles are unusable from a practical stand point on server=
s with multiple unrelated databases.

I think they were mostly added for compatibility with Microsoft SQL Server,
if I remember correctly.

I suggest creating roles named "readonly_dbname" for each database with
the appropriate privileges and assigning those.

A different approach would be to use different database clusters for differ=
ent
databases.

Yours,
Laurenz Albe