作为超级用户,我在Postgres的同一模式下创建了两个角色:
1. read_only_with_create_view 2. read_write 然后我从每个角色创建了两个用户:
1. read_only_with_create_view_user 2. read_write 现在,任何由read_only_with_create_view_user创建的新视图都无法被read_write_user访问,因为视图的所有者不同(read_only_with_create_view_user)。
那么有什么方法可以让read_write_user访问所有新视图吗?
我希望一个用户创建的所有内容都可以被另一个用户访问。
我遵循的步骤:
1. read_only_with_create_view 2. read_write 然后我从每个角色创建了两个用户:
1. read_only_with_create_view_user 2. read_write 现在,任何由read_only_with_create_view_user创建的新视图都无法被read_write_user访问,因为视图的所有者不同(read_only_with_create_view_user)。
那么有什么方法可以让read_write_user访问所有新视图吗?
我希望一个用户创建的所有内容都可以被另一个用户访问。
我遵循的步骤:
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
创建的新视图时,出现以下错误:
ERROR: permission denied for relation view_name
********** Error **********
ERROR: permission denied for relation view_name
SQL state: 42501
INHERIT
属性。 - Michel Milezzi