public inbox for [email protected]
help / color / mirror / Atom feeddatabase specific pg_read_all_data / pg_write_all_data
15+ messages / 6 participants
[nested] [flat]
* database specific pg_read_all_data / pg_write_all_data
@ 2025-12-09 21:13 richard coleman <[email protected]>
2025-12-09 22:45 ` Re: database specific pg_read_all_data / pg_write_all_data Ron Johnson <[email protected]>
2025-12-10 00:38 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
0 siblings, 3 replies; 15+ messages in thread
From: richard coleman @ 2025-12-09 21:13 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-09 22:45 ` Ron Johnson <[email protected]>
2025-12-09 23:21 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2 siblings, 1 reply; 15+ messages in thread
From: Ron Johnson @ 2025-12-09 22:45 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Tue, Dec 9, 2025 at 4:13 PM richard coleman <[email protected]>
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!
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-09 22:45 ` Re: database specific pg_read_all_data / pg_write_all_data Ron Johnson <[email protected]>
@ 2025-12-09 23:21 ` richard coleman <[email protected]>
2025-12-10 00:23 ` Re: database specific pg_read_all_data / pg_write_all_data Ron Johnson <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: richard coleman @ 2025-12-09 23:21 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 protected]> wrote:
> On Tue, Dec 9, 2025 at 4:13 PM richard coleman <
> [email protected]> 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!
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-09 22:45 ` Re: database specific pg_read_all_data / pg_write_all_data Ron Johnson <[email protected]>
2025-12-09 23:21 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 00:23 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Ron Johnson @ 2025-12-10 00:23 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Tue, Dec 9, 2025 at 6:21 PM richard coleman <[email protected]>
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:
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.
>
> I hope that helps make it clearer.
> rik.
>
>
>
>
> On Tue, Dec 9, 2025 at 5:46 PM Ron Johnson <[email protected]>
> wrote:
>
>> On Tue, Dec 9, 2025 at 4:13 PM richard coleman <
>> [email protected]> 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!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 00:38 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: David G. Johnston @ 2025-12-10 00:38 UTC (permalink / raw)
To: richard coleman <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Tuesday, December 9, 2025, richard coleman <[email protected]>
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 10:01 ` Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2 siblings, 1 reply; 15+ messages in thread
From: Laurenz Albe @ 2025-12-10 10:01 UTC (permalink / raw)
To: richard coleman <[email protected]>; Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
@ 2025-12-10 13:06 ` richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: richard coleman @ 2025-12-10 13:06 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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:
*grant *pg_read_all_data* on database *foo* to *user_role*;*
Thanks,
rik.
On Wed, Dec 10, 2025 at 5:01 AM Laurenz Albe <[email protected]>
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 13:10 ` Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Laurenz Albe @ 2025-12-10 13:10 UTC (permalink / raw)
To: richard coleman <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
@ 2025-12-10 14:10 ` richard coleman <[email protected]>
2025-12-10 14:25 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
2025-12-10 14:45 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
0 siblings, 2 replies; 15+ messages in thread
From: richard coleman @ 2025-12-10 14:10 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 protected]>
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 14:25 ` David G. Johnston <[email protected]>
2025-12-10 16:22 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 17:33 ` Re: database specific pg_read_all_data / pg_write_all_data Tom Lane <[email protected]>
1 sibling, 2 replies; 15+ messages in thread
From: David G. Johnston @ 2025-12-10 14:25 UTC (permalink / raw)
To: richard coleman <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
On Wednesday, December 10, 2025, richard coleman <
[email protected]> 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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 14:25 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
@ 2025-12-10 16:22 ` richard coleman <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: richard coleman @ 2025-12-10 16:22 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
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 <
[email protected]> wrote:
> On Wednesday, December 10, 2025, richard coleman <
> [email protected]> 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.
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 14:25 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
@ 2025-12-10 17:33 ` Tom Lane <[email protected]>
2025-12-10 18:00 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 18:13 ` Re: database specific pg_read_all_data / pg_write_all_data Joe Conway <[email protected]>
1 sibling, 2 replies; 15+ messages in thread
From: Tom Lane @ 2025-12-10 17:33 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: richard coleman <[email protected]>; Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
"David G. Johnston" <[email protected]> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 14:25 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
2025-12-10 17:33 ` Re: database specific pg_read_all_data / pg_write_all_data Tom Lane <[email protected]>
@ 2025-12-10 18:00 ` richard coleman <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: richard coleman @ 2025-12-10 18:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
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 <[email protected]> wrote:
> "David G. Johnston" <[email protected]> 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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 14:25 ` Re: database specific pg_read_all_data / pg_write_all_data David G. Johnston <[email protected]>
2025-12-10 17:33 ` Re: database specific pg_read_all_data / pg_write_all_data Tom Lane <[email protected]>
@ 2025-12-10 18:13 ` Joe Conway <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Joe Conway @ 2025-12-10 18:13 UTC (permalink / raw)
To: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; +Cc: richard coleman <[email protected]>; Laurenz Albe <[email protected]>; Pgsql-admin <[email protected]>
On 12/10/25 12:33, Tom Lane wrote:
> "David G. Johnston" <[email protected]> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: database specific pg_read_all_data / pg_write_all_data
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 10:01 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 13:06 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-10 13:10 ` Re: database specific pg_read_all_data / pg_write_all_data Laurenz Albe <[email protected]>
2025-12-10 14:10 ` Re: database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
@ 2025-12-10 14:45 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2025-12-10 14:45 UTC (permalink / raw)
To: richard coleman <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2025-12-10 18:13 UTC | newest]
Thread overview: 15+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-09 21:13 database specific pg_read_all_data / pg_write_all_data richard coleman <[email protected]>
2025-12-09 22:45 ` Ron Johnson <[email protected]>
2025-12-09 23:21 ` richard coleman <[email protected]>
2025-12-10 00:23 ` Ron Johnson <[email protected]>
2025-12-10 00:38 ` David G. Johnston <[email protected]>
2025-12-10 10:01 ` Laurenz Albe <[email protected]>
2025-12-10 13:06 ` richard coleman <[email protected]>
2025-12-10 13:10 ` Laurenz Albe <[email protected]>
2025-12-10 14:10 ` richard coleman <[email protected]>
2025-12-10 14:25 ` David G. Johnston <[email protected]>
2025-12-10 16:22 ` richard coleman <[email protected]>
2025-12-10 17:33 ` Tom Lane <[email protected]>
2025-12-10 18:00 ` richard coleman <[email protected]>
2025-12-10 18:13 ` Joe Conway <[email protected]>
2025-12-10 14:45 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox