Обсуждение: database specific pg_read_all_data / pg_write_all_data
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.
On Tue, Dec 9, 2025 at 4:13 PM richard coleman <rcoleman.ascentgl@gmail.com> 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.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.
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!
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 <ronljohnsonjr@gmail.com> wrote:
On Tue, Dec 9, 2025 at 4:13 PM richard coleman <rcoleman.ascentgl@gmail.com> 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.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.How aboutALTER 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!
On Tue, Dec 9, 2025 at 6:21 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote:
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,
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:
"
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.I hope that helps make it clearer.rik.On Tue, Dec 9, 2025 at 5:46 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Tue, Dec 9, 2025 at 4:13 PM richard coleman <rcoleman.ascentgl@gmail.com> 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.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.How aboutALTER 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!
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Tuesday, December 9, 2025, richard coleman <rcoleman.ascentgl@gmail.com> 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.Unfortunately they appear to be server-wide roles.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.
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 permissionsetting 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. 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
Laurenz,
grant pg_read_all_data on database foo to user_role;
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:
grant pg_read_all_data on database foo to user_role;
Thanks,
rik.
On Wed, Dec 10, 2025 at 5:01 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
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.
>
> 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.
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
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: > > grant pg_read_all_data on database foo to user_role; Frankly, I think that "pg_read_all_data" is ugly and should never have been added. Yours, Laurenz Albe
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 <laurenz.albe@cybertec.at> wrote:
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:
>
> grant pg_read_all_data on database foo to user_role;
Frankly, I think that "pg_read_all_data" is ugly and should never have been added.
Yours,
Laurenz Albe
On Wednesday, December 10, 2025, richard coleman <rcoleman.ascentgl@gmail.com> wrote:
I hope that the PostgreSQL devs revisit it in the future with an eye 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.
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
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.
On Wed, Dec 10, 2025 at 9:25 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, December 10, 2025, richard coleman <rcoleman.ascentgl@gmail.com> wrote:I hope that the PostgreSQL devs revisit it in the future with an eye 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.
"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
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.
"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@sss.pgh.pa.us> wrote:
"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
On 12/10/25 12:33, Tom Lane wrote: > "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. 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