Re: database specific pg_read_all_data / pg_write_all_data

Tom,

You are *almost* there I think. By my understanding, which admittedly
might be flawed, "pg_read_all_data" once given allows the role with that
privlidge to litterally "read all data" across all databases in that
cluster. So while one can revoke public connect privs to a database and
keep a role with pg_read_all_data privs from connecting to it, you're
otherwise pretty much out of luck. That option is unavailable in the
situation where users have differing privs on the same cluster. For
example, if user0 needs to have "read_all" privs in database0, "read_all"
and "write_all" privs in database1, and various privs in database2 on the
same cluster, you can't use the CONNECT nor pg_hba.conf workarounds. As
soon as a role who's a member of
"pg_read_all_data" can connect to a database in that cluster, it's game
over. Doubly so for roles with the "pg_write_all_data" priv.

These built-in roles are a much welcomed addition in PostgreSQL.
Unfortunately in PostgreSQL, unlike other RDBMSs, roles are cluster wide
not database specific. This leads to some interesting things in
multi-database tools such as DBeaver which includes a seperate Roles folder
in each PostgreSQL database connection containing copies of the exact same
roles. Use the GUI to alter a role in the Roles folder for database0,
potentially be amazed that database1, and every other database in that
cluster, magically reflects the change. I'm not saying that is is
nessicarrilly a bad thing, just different. What it does mean though is
that cluster wide roles and privs can and do much more than one might
suspect. This discussion of pg_read_all_data being a prime example.
Basically I think that because of the reliance on cluster wide roles in
PostgreSQL, it's potentially dangerous to introduce built-in roles with far
ranging privs without having a machinaism to limit them to specific
databases in that cluster. The only realistic way to take advantage of the
extrodinarilly useful abilites they enable is to limit them to the
relatively rare instances where there is only a single database on a
cluster, or when the users can have the same access to all of the databases
on that cluster.

Hopefully I've made my self clear enough in this matter and have
demonstrated how being able to limit built-in cluster specific privs in a
per database way would be very useful.

Thanks for taking the time, everyone, to read my missives and contribute
your thoughts in this.
rik.

On Wed, Dec 10, 2025 at 12:33 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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
>