public inbox for [email protected]  

help / color / mirror / Atom feed

From: richard coleman <[email protected]>
To: Ron Johnson <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: database specific pg_read_all_data / pg_write_all_data
Date: Tue, 9 Dec 2025 18:21:05 -0500
Message-ID: <CAGA3vBs=_E7eOXRngW9fkKSvE+r9_s+z=wOZNBvjfZ_yRROc5Q@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaA8JTM1V_+9ACXGWjbCYYu_hio5EA-=2ne_7jmmhw31FQ@mail.gmail.com>
References: <CAGA3vBug6Sq_XYLxzmY470WFS6Z3OF28goYzX=QHrCc4hgQSDw@mail.gmail.com>
	<CANzqJaA8JTM1V_+9ACXGWjbCYYu_hio5EA-=2ne_7jmmhw31FQ@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: database specific pg_read_all_data / pg_write_all_data
  In-Reply-To: <CAGA3vBs=_E7eOXRngW9fkKSvE+r9_s+z=wOZNBvjfZ_yRROc5Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox