在PostgreSQL中更改组角色的默认权限 [英] Alter default privileges for a group role in PostgreSQL

问题描述

我在Postgres 9.2中创建了两个小组角色：一个叫做 admins ，另一个叫做 readers 。

I have created two group roles in Postgres 9.2: one is called admins and the other is called readers.

想法很简单：管理员创建表和读取器拥有对这些表的读取权限。

The idea is very simple: admins create tables and readers have read access to these tables.

在授予两个组角色特权之后，一切都按exisintg对象的预期工作。但是现在关于新对象呢？

After granting privileges to both group roles everything worked as expected for exisintg objects. But now what about new objects?

所以在阅读了这篇帖子我更改了默认特权，为 admins的任何新表授予 readers 的 SELECT 特权创建：

So after reading this post I altered the default privileges to grant SELECT privileges to readers for any new table that admins create:

ALTER DEFAULT PRIVILEGES FOR ROLE admins IN SCHEMA public GRANT SELECT ON TABLES TO readers;
ALTER DEFAULT PRIVILEGES FOR ROLE admins IN SCHEMA public GRANT SELECT ON SEQUENCES TO readers;

但显然，更改默认特权仅会影响角色本身，而不是角色成员。让我告诉你。

But apparently, ALTER DEFAULT PRIVILEGES only affects the role itself but not the members of the role. Let me show you.

如果我以 userX （ admins的成员）并创建一个新表，未授予默认权限（因此，阅读器无法访问该表）：

If I login as userX (a member of admins) and create a new table, no default privileges are granted (and therefore, readers cannot access this table):

test=# CREATE TABLE table1 (name VARCHAR(10)); -- Creating table as userX
test=# \dp table1
                           Access privileges
 Schema |  Name  | Type  | Access privileges | Column access privileges 
--------+--------+-------+-------------------+--------------------------
 public | table1 | table |                   | 

但是，如果我将表创建为管理员（读者可以访问此表）：

However, the default privileges are granted if I create the table as admins (readers can access this table):

test=# SET ROLE admins;
test=# CREATE TABLE table2 (name VARCHAR(10)); -- Creating table as admins
test=# \dp table2
                             Access privileges
 Schema |  Name  | Type  |   Access privileges   | Column access privileges 
--------+--------+-------+-----------------------+--------------------------
 public | table2 | table | readers=r/admins     +| 
        |        |       | admins=arwdDxt/admins | 

是否可以更改组角色所有成员的默认特权？还是我应该更改每个用户的默认特权？

Is there a way to alter the default privileges for ALL members of a group role? Or should I just alter default privileges for each user?

更新：在此PostgreSQL中论坛中有人问了一个非常类似的问题，答案是：

很遗憾，我看不到一种实现您所期望的方法的方法

Unfortunately I can't see a way to achieve what you want without granting default privileges to everybody involved.

但是2年前问了这个问题。现在有解决方案吗？

However this question was asked 2 years ago. Is there a solution now?

推荐答案

如果用户创建了一个表，那么该用户将成为该表的所有者。因此，在您的情况下，适用于userX的任何默认特权，而不适用于管理员的默认特权。解决方案是在创建表之前 SET角色管理员：

If a user creates a table then this user becomes the owner of the table. So in your case any default privileges for userX apply, not those of admins. the solution is to SET ROLE admins before creating your table:

SET ROLE admins;
CREATE TABLE ... -- This now applies default privileges of admins
;
RESET ROLE;

一般来说，您总是希望这样做：通过组角色创建所有表和视图或其他日常操作中未使用的角色，并将访问关系授予另一个组角色，该组角色的特权由常规登录角色（用户）继承。这大大简化了安全性管理。

More in general, you would want to do this always: Create all tables and views through a group role or some other role not used in daily operations and grant access to the relations to another group role whose privileges are inherited by regular login roles (users). This greatly facilitates security management.

干杯，
Patrick

Cheers, Patrick

这篇关于在PostgreSQL中更改组角色的默认权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！