您现在的位置是：首页 >技术杂谈 >postgresql数仓权限划分规划网站首页技术杂谈

背景：

有两个库: data_warehouse_prod、data_warehouse_test

分别对应两个超级角色：prod_super_user、test_super_user

1. 创建角色并设置权限

创建两个超级用户角色 test_super_user 和 prod_super_user，并分别授予这两个角色对特定数据库的完全访问权限。同时确保这些角色只能连接和操作其对应的数据库。

创建 test_super_user 和 prod_super_user 角色

-- 创建 test_super_user 角色
CREATE ROLE test_super_user NOLOGIN;

-- 创建 prod_super_user 角色
CREATE ROLE prod_super_user NOLOGIN;

为角色授予对应数据库的访问权限

确保 test_super_user 仅对 data_warehouse_test 数据库有权限，prod_super_user 仅对 data_warehouse_prod 数据库有权限。

授予 test_super_user 角色对 data_warehouse_test 数据库的权限：

-- 切换到 test 数据库
c data_warehouse_test

-- 授予 test_super_user 所有权限
GRANT ALL PRIVILEGES ON DATABASE data_warehouse_test TO test_super_user;

-- 撤销 test_super_user 对 prod 数据库的访问权限
REVOKE CONNECT ON DATABASE data_warehouse_prod FROM test_super_user;

授予 prod_super_user 角色对 data_warehouse_prod 数据库的权限：

-- 切换到 prod 数据库
c data_warehouse_prod

-- 授予 prod_super_user 所有权限
GRANT ALL PRIVILEGES ON DATABASE data_warehouse_prod TO prod_super_user;

-- 撤销 prod_super_user 对 test 数据库的访问权限
REVOKE CONNECT ON DATABASE data_warehouse_test FROM prod_super_user;

2. 创建并设置子账户

现在创建子账户并将它们与各自的超级用户角色关联起来。子账户将根据不同的需求（例如 read_only、read_writer、super_user）获得不同权限。

在 test_super_user 下创建子账户 xmg_wangchao_test

-- 切换到 test 数据库
c data_warehouse_test

-- 创建 user_test 子账户
CREATE USER user_test WITH PASSWORD 'password';

-- 授予 user_test 角色 test_super_user 下的所有权限
GRANT user_test TO xmg_wangchao_test;

-- 授予 user_test 对 test 数据库的所有权限
GRANT ALL PRIVILEGES ON DATABASE data_warehouse_test TO user_test;

在 prod_super_user 下创建子账户

创建三个子账户 read_only、read_writer 和 super_user，分别授予不同的权限：

1. read_only 账户：只读权限

   -- 切换到 prod 数据库
   c data_warehouse_prod
   
   -- 创建 read_only 账户
   CREATE USER read_only WITH PASSWORD 'password';
   
   -- 授予 read_only 账户对 prod 数据库的只读权限
   GRANT CONNECT ON DATABASE data_warehouse_prod TO read_only;
   GRANT USAGE ON SCHEMA public TO read_only;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
   GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only;
   

2. read_writer 账户：读写权限

   -- 创建 read_writer 账户
   CREATE USER read_writer WITH PASSWORD 'password';
   
   -- 授予 read_writer 账户对 prod 数据库的读写权限
   GRANT CONNECT ON DATABASE data_warehouse_prod TO read_writer;
   GRANT USAGE ON SCHEMA public TO read_writer;
   GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_writer;
   GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_writer;
   

3. super_user 账户：完全权限

   -- 创建 super_user 账户
   CREATE USER super_user WITH PASSWORD 'password';
   
   -- 授予 super_user 账户对 prod 数据库的所有权限
   GRANT CONNECT ON DATABASE data_warehouse_prod TO super_user;
   GRANT USAGE ON SCHEMA public TO super_user;
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO super_user;
   GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO super_user;
   

3. 确保子账户可以看到所有视图、存储过程等

我们需要确保 read_writer 和 super_user 能看到所有的 schema、视图、存储过程等，包括以后创建的对象。

为此，需要设置默认的 search_path 以确保他们能访问到所有现有的 schema：

-- 设置所有用户（包括子账户）能够查看所有 schema
ALTER ROLE read_writer SET search_path TO public;
ALTER ROLE super_user SET search_path TO public;
-- 后续如果有多个schema需要重新赋权，将新增的schema再次赋权