简单权限模型实践

简单权限模型

目标：开发者按功能自定义Role对象，自定义的Role包含了一些默认权限集合，后续可通过GRANT, REVOKE命令，管理自定义Role中的用户列表，简化权限管理的复杂操作。

注意：简单权限模型应在Database创建完成后立刻进行操作，如果Database中已存在了用户，或其它数据，应妥善处理已有对象的权限。

自定义Role列表

自定义Role	功能描述	其它
database_admin	管理database内的所有对象，增加删除用户
database_devops	管理database内的所有对象，包括新建、删除Schema、Table、Schema 等操作
database_writer	对当前database内的table, view 可写
database_reader	对当前database内的table, view 可读

创建Roles命令

CREATE ROLE database_admin with createrole;
CREATE ROLE database_devops;
CREATE ROLE database_writer;
CREATE ROLE database_reader;
 
GRANT pg_read_all_data TO database_reader;
GRANT pg_write_all_data TO database_writer;
 
GRANT database_reader TO database_devops;
GRANT database_writer TO database_devops;
GRANT pg_mONiTOr TO database_devops;
 
-- ONly admin has permissiON TO do role management
GRANT database_writer TO database_admin with admin optiON;
GRANT database_reader TO database_admin with admin optiON;
GRANT database_devops TO database_admin with admin optiON;
 
-- set default privilege of public schema
GRANT all ON schema public TO database_admin with GRANT optiON;
GRANT all ON schema public TO database_devops;
 
-- admin or devops should has permissiON TO create schemas
GRANT all ON database '<current_database>' TO database_admin with GRANT optiON;
GRANT all ON database '<current_database>' TO database_devops with GRANT optiON;

检查role继承关系

SELECT                                                                                                     
        CAST(a.rolname AS informatiON_schema.sql_identifier) AS GRANTee,
        CAST(b.rolname AS informatiON_schema.sql_identifier) AS role,
        m.GRANTor,
        CAST(CASE WHEN m.admin_optiON THEN 'YES' ELSE 'NO' END AS informatiON_schema.yes_or_no) AS GRANTable
FROM (SELECT roleid, member, GRANTor, admin_optiON FROM pg_auth_members) m
        JOIN pg_authid a ON (m.member = a.oid)
        JOIN pg_authid b ON (m.roleid = b.oid);

为User初始化权限

对每一个已有用户执行以下操作：(注意替换命令中的 user 为目标用户）

-- 建议使用Database Owner用户执行以下命令
 
-- database_admin获取所有schemas, tables, sequeuces 的权限，并且可以单独授权
ALTER DEFAULT privileges for role "<user>" GRANT all ON schemas TO database_admin;
ALTER DEFAULT privileges for role "<user>" GRANT all ON tables TO database_admin;
ALTER DEFAULT privileges for role "<user>" GRANT all ON sequences TO database_admin;
 
-- database_devops获取所有schemas, tables, sequeuces 的权限，并且可以单独授权
ALTER DEFAULT privileges for role "<user>" GRANT all ON schemas TO database_devops;
ALTER DEFAULT privileges for role "<user>" GRANT all ON tables TO database_devops;
ALTER DEFAULT privileges for role "<user>" GRANT all ON sequences TO database_devops;
 
 
-- database_admin获取所有schemas, tables, sequeuces 的权限，并且可以单独授权
ALTER DEFAULT privileges GRANT all ON schemas TO database_admin;
ALTER DEFAULT privileges GRANT all ON tables TO database_admin;
ALTER DEFAULT privileges GRANT all ON sequences TO database_admin;
 
-- database_devops获取所有schemas, tables, sequeuces 的权限，并且可以单独授权
ALTER DEFAULT privileges GRANT all ON schemas TO database_devops;
ALTER DEFAULT privileges GRANT all ON tables TO database_devops;
ALTER DEFAULT privileges GRANT all ON sequences TO database_devops;

添加、删除用户

-- 建议使用Database Owner用户执行以下命令
CREATE USER <user>;

新增用户后需要初始化用户权限

用户授权

当对用户role进行权限管理时，建议先set role database_admin;

database_admin 管理

-- 授权
GRANT database_admin TO <user>;
 
-- 取消权限
REVOKE database_admin FROM <user>;

database_devops 管理

-- 授权
GRANT database_devops TO <user>;
 
-- 取消权限
REVOKE database_devops FROM <user>;

database_writer 管理

-- 授权
GRANT database_writer TO <user>;
 
-- 取消权限
REVOKE database_writer FROM <user>;

database_reader 管理

-- 授权
GRANT database_reader TO <user>;
 
-- 取消权限
REVOKE database_reader FROM <user>;