Re: database specific pg_read_all_data / pg_write_all_data
| От | richard coleman |
|---|---|
| Тема | Re: database specific pg_read_all_data / pg_write_all_data |
| Дата | |
| Msg-id | CAGA3vBsf9i9q7Z1JLJYXi=aQG8SsVhSyqGAqy84eUkRmsuJqLg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: database specific pg_read_all_data / pg_write_all_data ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-admin |
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.
В списке pgsql-admin по дате отправления: