From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: richard coleman <rcoleman.ascentgl@gmail.com>,
        Laurenz Albe <laurenz.albe@cybertec.at>,
        Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
In-reply-to: 
 <CAKFQuwZducmkxoyh_=gvdHJ2Te5rHGctv70BQQsacmgMMjtZRw@mail.gmail.com>
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>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 10 Dec 2025 07:25:29 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <647280.1765388029.1@sss.pgh.pa.us>
Date: Wed, 10 Dec 2025 12:33:49 -0500
Message-ID: <647281.1765388029@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/647281.1765388029%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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.

Yeah, I think this would be bad from both the intellectual-complexity
and implementation-difficulty standpoints.

However ... we've had multiple requests in the past to invent
database-specific roles.  I wonder if it'd suffice for Richard's
purposes to create such roles and grant them pg_read_all_data.

You can sort of do that today, in that you can muck with pg_hba.conf
or database CONNECT privileges to limit which DBs a role can log into.
But either answer works only at initial login; they don't constrain
SET ROLE, so they're not really adequate for permissions-limiting
purposes.  I'm imagining a feature whereby a database-specific role
is flat out not available in other databases; can't SET ROLE to it,
can't GRANT privileges (at least on non-shared objects) to it.
Probably role membership would still be nominally global, but it
wouldn't matter if you couldn't use the role.

This might still not pass the too-much-complexity test, but it
has the advantage of being something that there's been multiple
requests for.

			regards, tom lane