public inbox for [email protected]  

help / color / mirror / Atom feed

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

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!

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]
  Subject: Re: database specific pg_read_all_data / pg_write_all_data
  In-Reply-To: <CANzqJaBSBNBVbJkPX5YRj6o5Tf0wPTohrakzf9bUShCZFBpyAw@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