Re: database specific pg_read_all_data / pg_write_all_data

On Tue, Dec 9, 2025 at 6:21 PM richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)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:
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 <ronljohnsonjr(at)gmail(dot)com>
> wrote:
>
>> On Tue, Dec 9, 2025 at 4:13 PM richard coleman <
>> rcoleman(dot)ascentgl(at)gmail(dot)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!
>>
>

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