Subject: database specific pg_read_all_data / pg_write_all_data

From: richard coleman <[email-masked]>
Date: Tue, 9 Dec 2025 16:13:11 -0500

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 servers
with multiple unrelated databases.

Thanks for the help,
rik.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 9 Dec 2025 17:45:58 -0500

On Tue, Dec 9, 2025 at 4:13 PM richard coleman <[email-masked]>
wrote:

> If there are 100 databases on a server, it would be extremely helpful to
> be able to do something like:
>

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

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

---

From: richard coleman <[email-masked]>
Date: Tue, 9 Dec 2025 18:21:05 -0500

Ron,

That wouldn't come even close to what pg_read_all_data grants.
A role assigned to pg_read_all_data automatically has the ability to read
everything, in every schema that exists now or in the future.

The old way, your suggestion, means that you have to keep rerunning that
command everytime someone creates a schema, creates a table, creates a
view, recreates a table, recreates a view, etc. for all eternity.  Not only
that, you have to tailor the command to each new schema, etc.

This makes shared privs much more streamlined and removes the chance that a
user will forget to assign privs to objects that they create.

I hope that helps make it clearer.
rik.

On Tue, Dec 9, 2025 at 5:46 PM Ron Johnson <[email-masked]> wrote:

> On Tue, Dec 9, 2025 at 4:13 PM richard coleman <
> [email-masked]> wrote:
>
>> Otherwise these roles are unusable from a practical stand point on
>> servers with multiple unrelated databases.
>>
>
> How about

---

From: Ron Johnson <[email-masked]>
Date: Tue, 9 Dec 2025 19:23:18 -0500

> command everytime someone creates a schema,
>

Yes, for every new schema.  Do schemata get created that often?

> creates a table, creates a view, recreates a table, recreates a view, etc.
> for all eternity.
>

I don't think so:
https://www.postgresql.org/docs/17/sql-alterdefaultprivileges.html
"ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be
applied to objects created in the future."

You *would* have to do an initial "GRANT ALL ON ... TO bar" but this can be
scripted so you pass the user name as a parameter and loops through all
schemata.

Is it as convenient as per-database pg_read_all_data?  No.  But the
inconvenience *can* be mitigated.

Not only that, you have to tailor the command to each new schema, etc.
>
> This makes shared privs much more streamlined and removes the chance that
> a user will forget to assign privs to objects that they create.
>

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 9 Dec 2025 17:38:49 -0700

> Woud it be possible to have roles like these that are database specific?

 You can have roles that are database-specific; which then means those
roles can only apply the “all data” privileges within the database they are
permitted access to.

David J.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 10 Dec 2025 11:01:18 +0100

On Tue, 2025-12-09 at 16:13 -0500, richard coleman 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.
> 
> 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 servers 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 different
databases.

Yours,
Laurenz Albe

---

From: richard coleman <[email-masked]>
Date: Wed, 10 Dec 2025 08:06:17 -0500

Laurenz,

Multiple clusters would be nice, but we don't have the available servers to
accomodate that.
Without the pg_read_all_data role there is apparently no other way in
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.

It's a shame that PostgreSQL has created some extremely useful built in
roles, but then limits them such that they can only be utilized for
vanishingly few actual use cases.

Hopefully the PostgreSQL devs revisit these built in roles with a thought
toward making database specific ones assignable  with a mechanism like:

Thanks,
rik.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 10 Dec 2025 14:10:03 +0100

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  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. 

Yes, and that is ALTER DEFAULT PRIVILEGES.

> It's a shame that PostgreSQL has created some extremely useful built in roles, but then
> limits them such that they can only be utilized for vanishingly few actual use cases.
> 
> Hopefully the PostgreSQL devs revisit these built in roles with a thought toward making
> database specific ones assignable  with a mechanism like:
> 

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

Yours,
Laurenz Albe

---

From: richard coleman <[email-masked]>
Date: Wed, 10 Dec 2025 09:10:29 -0500

Laurenz,

Running many clusters on a single server, while possible, reduces the
amount of memory available to each cluster and each database process users
run respectively.

ALTER DEFAULT PRIVLIGES doesn't work on schema that doesn't exist at that
time that command was run.

I am sorry to hear that you think "pg_read_all_data" is ugly.  That
built-in role and others like it have proven very useful for a fairly
common use case; a small group of users that must share database objects
between them without having to constantly rejigger privileges on those
objects.

In the rare case where a group has their own database cluster it has saved
a lot of work.  Sadly, it is unable to be utilized on shared clusters
hosting dozens of databases for different groups in its current form.

I hope that the PostgreSQL devs revisit it in the future with an eye
towards making it applicable in more situations.

Thanks for you input,
rik.

On Wed, Dec 10, 2025 at 8:10 AM Laurenz Albe <[email-masked]>
wrote:

> PostgreSQL to
> roles, but then

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 10 Dec 2025 07:25:29 -0700

> towards making it applicable in more situations.

There are setups where roles can access multiple databases and in some of
those they have read/write all privileges and in others they do not?

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.  iMO you’ve claims here do not establish a solid use
case - they are lacking convincing details.  That said, the project is open
source - you can scratch your own itch.  But the model change is still a
complexity hill to overcome.

David J.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 10 Dec 2025 15:45:27 +0100

On Wed, 2025-12-10 at 09:10 -0500, richard coleman wrote:
> Running many clusters on a single server, while possible, reduces the amount of memory
> available to each cluster and each database process users run respectively.

Yes, but not a by much.

> ALTER DEFAULT PRIVLIGES doesn't work on schema that doesn't exist at that time that command was run.

That is not true.  The IN SCHEMA clause is optional.
You have to run one ALTER DEFAULT PRIVILEGES for each role that is to create tables, but
if you have many such roles, you are probably doing something wrong.

> I am sorry to hear that you think "pg_read_all_data" is ugly.

That is a purely personal judgement.  I am sure many people find the feature useful.

> That built-in role and others like it have proven very useful for a fairly common
> use case; a small group of users that must share database objects between them without
> having to constantly rejigger privileges on those objects.

I cannot claim to know what people do out there, but I must say that I haven't
encountered many such setups in the field.  Usually such "ad hoc" schemes run into
trouble by the time when people want to drop tables.

Yours,
Laurenz Albe

---

From: richard coleman <[email-masked]>
Date: Wed, 10 Dec 2025 11:22:03 -0500

David,

The most common situation is when there are disparate groups, each with
their own databases that are expected to have access to all of the
schema/tables/views in that database regardless of who creates them.

When that group has their own PostgreSQL cluster, the simpilest way to
achive this is to grant all of those users membershipo in the
pg_read_all_data and pg_write_all_data built-in roles.  Unfortunately, the
way that these roles work, this isn't an option when there are multiple
groups, each with their own database, sharing a PostgreSQL cluster.

Previously those users were sharing a single role so that they didn't run
into priviledge issues.  We've been discouraging this practice from
continuing for obvious reasons.

Without a database specific version of the pg_read_all_data and
pg_write_all_data built in roles we have to rely upon the users, some who
aren't particularly database savy, to remember to either reassign ownership
of their objects to a shared group role, or explicitely grant privs to
other members of their group.  As you can expect it isn't ideal and the DBA
has to occationally step in to grant these privs.

This is why I was inquiting after database specific versions of those
built-in roles.  Just as we can currently assign database specific privs;
connect, temporary, etc., being able to do the same with these built-in
roles would be a godsend.

I hope that helps clear things up.
rik.

---

From: Tom Lane <[email-masked]>
Date: Wed, 10 Dec 2025 12:33:49 -0500

"David G. Johnston" <[email-masked]> writes:
> 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

---

From: richard coleman <[email-masked]>
Date: Wed, 10 Dec 2025 13:00:35 -0500

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.

---

From: Joe Conway <[email-masked]>
Date: Wed, 10 Dec 2025 13:13:11 -0500

> purposes to create such roles and grant them pg_read_all_data.

There is a significant real world demand for doing multi-tenant 
PostgreSQL by having one tenant per database. It is not ideal by any 
means, but a substantial number of folks use that strategy whether we 
like it or not.

Anything we can do to make it less sketchy and painful would be useful 
indeed.

-- 
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com