Postgresql 基于角色的只读用户不允许从基于角色的完全访问用户读取

Question

我有一个 PG 数据库并创建了两个角色:

1. 对数据库和所有模式的完全访问权限
2. 对所有模式的只读权限

然后我创建用户并为其中一个用户分配具有完全访问权限的第一个角色，为另一个用户分配只读访问权限角色。

接下来，我以具有完全访问权限的用户身份创建一个表，现在该表归该用户所有。

然后我尝试以只读用户身份访问表(选择)，但权限被拒绝。

我已经尝试过以下代码片段:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE FULL_ROLE IN SCHEMA public GRANT SELECT ON TABLES TO READONLY_ROLE;

我认为主要问题是，表不属于角色，它属于分配了角色的用户。但我不想将只读用户连接到完整用户。

难道不能使用这种基于角色的方法吗？是否可以将表和功能表的默认所有者更改为完整角色而不是用户(在“CREATE TABLE”之前没有“SET ROLE”)？

---

更新 25.04.2019

下面是生成角色和用户的sql语句:

创建写入角色:

CREATE ROLE write_role;
GRANT ALL PRIVILEGES ON DATABASE test_db TO write_role;
GRANT ALL ON SCHEMA public TO write_role;

创建读取角色:

CREATE ROLE read_role;
GRANT CONNECT ON DATABASE test_db TO read_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO read_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_role;
ALTER DEFAULT PRIVILEGES FOR ROLE write_role IN SCHEMA public GRANT SELECT ON TABLES TO read_role;

创建写入用户:

CREATE USER write_user WITH ENCRYPTED PASSWORD '';
GRANT write_role TO write_user;

创建读取用户:

CREATE USER read_user WITH ENCRYPTED PASSWORD '';
GRANT read_role TO read_user;

以 write_user 身份创建表并插入数据:

CREATE TABLE link (
 ID serial PRIMARY KEY,
 url VARCHAR (255) NOT NULL,
 name VARCHAR (255) NOT NULL,
 description VARCHAR (255),
 rel VARCHAR (50)
);
INSERT INTO link (url, name)
VALUES
 ('http://www.postgresqltutorial.com','PostgreSQL Tutorial');

关于表的信息:

\dt
                 List of relations
 Schema | Name | Type  |           Owner
--------+------+-------+---------------------------
 public | link | table | write_user

选择为read_user:

$ select * from link;
ERROR:  permission denied for table link

---

更新 01.05.2019

感谢工作中的一位同事，我们找到了一个不是 A 计划而是 Z 计划的可行解决方案。这篇文章是灵感:Change Ownership via Trigger

这篇文章也很重要的原因是，其他具有 write_role 的用户也不能删除其他用户创建的表。

这是我稍微修改过的事件触发器:

CREATE OR REPLACE FUNCTION trg_create_set_owner()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag='CREATE TABLE' LOOP
    EXECUTE format('ALTER TABLE %s OWNER TO write_role', obj.object_identity);
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA %s to read_role', obj.schema_name);
  END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_create_set_owner
 ON ddl_command_end
 WHEN tag IN ('CREATE TABLE')
 EXECUTE PROCEDURE trg_create_set_owner();

Answer 1

需要检查的几件事:

• 您是否在公共(public)架构中创建了表？如果不是，READONLY_ROLE 是否对表所在的架构具有 USAGE 权限？
• ALTER DEFAULT PRIVILEGES 仅更改运行后创建的对象的权限。你在运行之前创建了你的表吗？
• 您是要从具有 readonly_role 的表中进行选择，还是要从分配了 readonly_role 的用户中进行选择？如果是后者，用户是否继承特权(默认)？

编辑:感谢您提供所有其他信息。 ALTER DEFAULT PRIVILEGES 仅适用于一个角色并且不会被继承(如果用户是具有不同默认权限的多个角色的成员，我认为弄清楚将什么权限应用于新对象并不是一件容易的事)。

在以写入用户创建表之前，只需运行:

SET ROLE write_role;