PostgreSQL : Accessing tables owned by another user

Question

作为 super 用户，我在 Postgres 中的同一模式上创建了两个角色:

1. read_only_with_create_view
2. 读_写

然后我从每个角色创建了两个用户:

1. read_only_with_create_view_user
2. 读写

现在 read_only_with_create_view_user 创建的任何新 View 都不能被 read_write_user 访问，因为 View 的所有者不同 (read_only_with_create_view_user)。

那么read_write_user访问所有新 View 的方法是什么？

我希望一个用户创建的所有内容都可供另一个用户访问。

我遵循的步骤:

CREATE ROLE read_only_role WITH
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

GRANT CONNECT ON DATABASE mydb to read_only_role;
GRANT USAGE,CREATE ON SCHEMA myschema TO read_only_role;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO read_only_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA myschema TO read_only_role;

CREATE USER read_only_with_create_view_user
WITH PASSWORD '*****'
in ROLE read_only_role;

-- Now created new views using this role. That means read_only_with_create_view_user is owner of those views.

-- Creating new read-write role. 

CREATE ROLE rw_role WITH
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity' IN ROLE read_only_role;

GRANT CONNECT ON DATABASE mydb to rw_role;

GRANT USAGE ON SCHEMA myschema TO crn_rw_role_qa;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO rw_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO rw_role;

CREATE USER read_write_user
WITH PASSWORD '*****'
in role rw_role;

使用 read_write_user 登录后，当我尝试访问由 read_only_with_create_view_user 创建的新 View 时，出现此错误:

ERROR:  permission denied for relation view_name
********** Error **********

ERROR: permission denied for relation view_name
SQL state: 42501

Answer 1

您可以将一个角色设置为另一个角色的成员:

GRANT read_only_with_create_view_user TO read_write_user; 

更多信息 here .

---

编辑

它永远不会像您期望的那样工作。查看您当前的用户模式:

角色 read_write_user 将无法访问 read_only_with_create_view_user 拥有的对象，因为两者没有任何关系。要使其按预期工作，您可以将对象所有权重新分配给“上层”角色，在本例中为:read_only_role(因为每个人都是该角色的成员)。但请注意，它将不再是只读角色。

您可以执行以下操作之一:

--Connected as read_only_with_create_view_user
CREATE VIEW my_view AS SELECT 1;

--Assign ownership to top-level role
ALTER VIEW my_view OWNER TO read_only_role;

或者您可能更喜欢这种方法:

--Connected as read_only_with_create_view_user
--Change current user to read_only_role
SET role = read_only_role;

--Create a view...
CREATE VIEW my_view AS SELECT 1;

--Turn back to read_only_with_create_view_user
RESET role;

如果您喜欢一次完成所有操作，您可以通过一条命令将 read_only_with_create_view_user 拥有的对象的所有权重新分配给您的顶级角色:

REASSIGN OWNED BY read_only_with_create_view_user TO read_only_role;

最后，如果您不想破坏只读规则，您当然也可以直接向对象授予权限。

-- As read_only_with_create_view_user, execute the following:
CREATE VIEW my_view_2 AS SELECT 1;
GRANT SELECT ON my_view_2 TO read_write_user