Halo数据库用户角色权限管理

CREATE USER dev_user;  -- 等价于  CREATE ROLE dev_user LOGIN;

CREATE {USER|ROLE} user_name [ WITH     [ SUPERUSER | NOSUPERUSER ]    [ CREATEDB | NOCREATEDB ]    [ CREATEROLE | NOCREATEROLE ]    [ INHERIT | NOINHERIT ]    [ LOGIN | NOLOGIN ]     [ REPLICATION | NOREPLICATION ]    [ BYPASSRLS | NOBYPASSRLS ]    [ CONNECTION LIMIT connlimit ]    [ VALID UNTIL 'timestamp' ]    [ IN ROLE role_name [, ...] ]    [ ROLE role_name [, ...] ]    [ ADMIN role_name ]    [ PASSWORD 'password' ]];

-- 先级联删除角色拥有的对象(慎用)，再删除角色DROP OWNED BY rolename CASCADE;DROP ROLE rolename;

ALTER {ROLE | USER} role_name [WITH] option;-- option 为角色属性，详细参考1.2 中角色属性参数

select * from pg_roles where rolname = 'role_name';

-- 创建角色组CREATE ROLE read_only;  grant usage on schema test TO read_only;GRANT SELECT ON ALL TABLES IN SCHEMA test TO read_only;  
-- 将用户加入角色组CREATE USER dev_user;GRANT read_only TO dev_user;  
-- 检查角色继承关系SELECT   rolname AS "子角色",  array_agg(roleid::regrole) AS "继承的父角色"FROM pg_auth_members JOIN pg_roles ON member = pg_roles.oidGROUP BY rolname;
-- 验证权限继承SET ROLE dev_user;  SELECT * FROM test.products;  -- 成功执行INSERT INTO test.products(item_id, item_name, price)VALUES (1001, 'Special Widget', 49.99);  -- 执行失败，无此表insert权限
-- 将用户移除角色组SET ROLE halo;REVOKE read_only FROM dev_user;SET ROLE dev_user;  SELECT * FROM test.products;  -- 移除角色组无权限，执行失败。

CREATE USER admin_user;GRANT read_only TO admin_user WITH ADMIN OPTION;  -- admin_user 可将 read_only 授予其他用户

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }ON DATABASE database_name [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }ON DATABASE database_name [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]
-- 其中role_specification可以是，[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

SELECT     d.datname AS "DbName",    r.rolname AS "RoleName",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN 'YES'        ELSE 'NO'    END AS "CONNECT",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'TEMPORARY') THEN 'YES'        ELSE 'NO'    END AS "TEMPORARY"FROM    pg_roles r, pg_database d WHERE r.rolname = 'username'ORDER BY d.datname;

-- 创建测试用户create user test1;-- 移除默认权限干扰revoke all ON database test FROM public;-- 仅赋予数据库连接权限grant connect on database test TO test1;-- 赋予schema所有权限(排除权限干扰)grant all on schema public TO test1;-- 查看用户权限SELECT     d.datname AS "DbName",    r.rolname AS "RoleName",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN 'YES'        ELSE 'NO'    END AS "CONNECT",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'TEMPORARY') THEN 'YES'        ELSE 'NO'    END AS "TEMPORARY"FROM    pg_roles r, pg_database d WHERE r.rolname = 'test1'ORDER BY d.datname;-- 创建全局临时表(验证)\c test test1CREATE TEMPORARY TABLE temp_sales_data (    id SERIAL PRIMARY KEY,    product_name VARCHAR(100),    quantity INT,    price NUMERIC(10, 2));     -- 创建临时表将被拒绝，需要 TEMPORARY 权限\c - halogrant temporary on database test to test1 ;  -- 赋予  TEMPORARY 权限\c - test1CREATE TEMPORARY TABLE temp_sales_data (    id SERIAL PRIMARY KEY,    product_name VARCHAR(100),    quantity INT,    price NUMERIC(10, 2));    -- 成功创建临时表

-- 创建超级用户create user test2 superuser;-- 查看超级用户数据库级权限SELECT     d.datname AS "DbName",    r.rolname AS "RoleName",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN 'YES'        ELSE 'NO'    END AS "CONNECT",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'TEMPORARY') THEN 'YES'        ELSE 'NO'    END AS "TEMPORARY"FROM    pg_roles r, pg_database d WHERE r.rolname = 'test2' AND d.datname = 'test';-- 尝试移除超级用户对test库所有权限,无效revoke all on database test FROM public;revoke all on database test FROM test2;-- 再次查看超级用户数据库级权限，权限未被移除SELECT     d.datname AS "DbName",    r.rolname AS "RoleName",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN 'YES'        ELSE 'NO'    END AS "CONNECT",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'TEMPORARY') THEN 'YES'        ELSE 'NO'    END AS "TEMPORARY"FROM    pg_roles r, pg_database d WHERE r.rolname = 'test2' AND d.datname = 'test';\c test test2

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }ON SCHEMA schema_name [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]-- 其中role_specification可以是，[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

SELECT     n.nspname AS schema_name,    r.rolname AS rolname,    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'USAGE') THEN 'YES'        ELSE 'NO'    END AS "USAGE",    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE"FROM    pg_roles rJOIN pg_namespace n ON trueWHERE r.rolname = 'username'ORDER BY n.nspname;

-- 创建测试用户create user test1;create user test2;-- 移除测试库权限干扰revoke create on database test FROM public;revoke create on schema public FROM public;-- 赋予 test1 数据库级别 create权限grant create on database test TO test1;-- 赋予 tes2 schema 级别 create权限grant create on schema public TO test2;-- 查看两个用户数据库级权限SELECT     d.datname AS "DbName",    r.rolname AS "RoleName",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CONNECT') THEN 'YES'        ELSE 'NO'    END AS "CONNECT",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE",    CASE         WHEN has_database_privilege(r.rolname, d.datname, 'TEMPORARY') THEN 'YES'        ELSE 'NO'    END AS "TEMPORARY"FROM    pg_roles r, pg_database d WHERE (r.rolname = 'test1' OR r.rolname = 'test2') AND d.datname = 'test';-- 验证数据库级 CREATE 权限\c test test1create schema test1;        -- 创建成功，test1拥有数据库级别 create \c test test2create schema test2;        -- 执行失败，无权-- 查看两个用户schema级权限SELECT     n.nspname AS schema_name,    r.rolname AS rolname,    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'USAGE') THEN 'YES'        ELSE 'NO'    END AS "USAGE",    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE"FROM    pg_roles rJOIN pg_namespace n ON trueWHERE (r.rolname = 'test1' OR r.rolname = 'test2') AND n.nspname = 'public';-- 验证 schema 级 CREATE 权限\c test test1CREATE TABLE public.test_table (    id SERIAL PRIMARY KEY,    name VARCHAR(50));        -- test1无 public 的 CREATE 权限，执行失败\c test test2CREATE TABLE public.test_table (    id SERIAL PRIMARY KEY,    name VARCHAR(50));        -- 执行成功

-- 创建测试用户create user test1;create user test2;-- 移除test1 USAGE权限revoke USAGE on schema public FROM test1;-- 赋予 tes2 schema 级别 create权限grant create on schema public TO test2;-- 创建测试表\c test test2CREATE TABLE public.test_table (    id SERIAL PRIMARY KEY,    name VARCHAR(50));        -- 执行成功\dt public.test_table-- 查看两个用户schema级权限SELECT     n.nspname AS schema_name,    r.rolname AS rolname,    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'USAGE') THEN 'YES'        ELSE 'NO'    END AS "USAGE",    CASE         WHEN has_schema_privilege(r.rolname, n.nspname, 'CREATE') THEN 'YES'        ELSE 'NO'    END AS "CREATE"FROM    pg_roles rJOIN pg_namespace n ON trueWHERE (r.rolname = 'test1' OR r.rolname = 'test2') AND n.nspname = 'public';-- 查询public.test_tableselect * from public.test_table;-- 赋予test1 USAGE 权限\c test halogrant USAGE on schema public TO public;-- test1 虽有 USAGE 权限 ，但无此表 select 权限(需配合对象级权限)\c test test1select has_table_privilege('test1','public.test_table','select');select * from public.test_table;-- 赋予test1 select 表权限\c test halogrant select ON table public.test_table TO test1;\c test test1select * from public.test_table;  -- 执行成功

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON { [ TABLE ] table_name [, ...]| ALL TABLES IN SCHEMA schema_name [, ...] }TO role_specification [, ...] [ WITH GRANT OPTION ]
-- 可对特定列赋权GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }ON [ TABLE ] table_name [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] | ALL [ PRIVILEGES ] }ON { [ TABLE ] table_name [, ...]| ALL TABLES IN SCHEMA schema_name [, ...] }FROM role_specification [, ...][ CASCADE | RESTRICT ]
-- 可对特定列进行权限回收REVOKE [ GRANT OPTION FOR ]{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }ON [ TABLE ] table_name [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]

-- 通过视图查询SELECT grantee, table_schema, table_name, privilege_typeFROM information_schema.table_privilegesWHERE grantee = 'username' AND table_name = 'table_name';
-- 通过快捷键查询\dp+ TableName
-- 例如：test=# \dp+ public.test_table                                Access privileges Schema |    Name    | Type  |  Access privileges  | Column privileges | Policies --------+------------+-------+---------------------+-------------------+---------- public | test_table | table | test2=arwdDxt/test2+|                   |         |            |       | test1=arwdDxt/test2 |                   | (1 row)
-- 权限标识符说明：a=INSERT, r=SELECT, w=UPDATE, d=DELETE, D=TRUNCATE, x=REFERENCES, t=TRIGGER *=WITH GRANT OPTION（表示可转授权限）

-- 创建测试表CREATE TABLE public.employees (    id SERIAL PRIMARY KEY,    name TEXT NOT NULL,    salary NUMERIC(10, 2),    department TEXT);INSERT INTO public.employees (name, salary, department)VALUES ('Alice', 5000.00, 'HR'),('Bob', 6000.00, 'Engineering'),('Charlie', 7000.00, 'Finance');-- 创建测试用户并赋权create user test1;grant all (name, department) on table public.employees to test1;-- 查看表权限\dp+ public.employees-- 列级安全测试\c test test1SELECT name, department from public.employees;   -- 查询授权列，成功SELECT name, salary, department from public.employees;   -- 查询包含未授权列，执行失败

GRANT { EXECUTE | ALL [ PRIVILEGES ] }ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ][ arg_name ] arg_type [, ...] ] ) ] [, ...]| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name[, ...] }TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON { SEQUENCE sequence_name [, ...]| ALL SEQUENCES IN SCHEMA schema_name [, ...] }TO role_specification [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]{ EXECUTE | ALL [ PRIVILEGES ] }ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ][ arg_name ] arg_type [, ...] ] ) ] [, ...]| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name[, ...] }FROM role_specification [, ...][ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]{ { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON { SEQUENCE sequence_name [, ...]| ALL SEQUENCES IN SCHEMA schema_name [, ...] }FROM role_specification [, ...][ CASCADE | RESTRICT ]
-- 若需要将整个shcema下的函数、存过和序列赋予某用户时，可使用下列语句grant all on all ROUTINES IN SCHEMA schema_name to rolename;grant all on all SEQUENCES IN SCHEMA schema_name to rolename;

GRANT { USAGE | ALL [ PRIVILEGES ] }ON { DOMAIN domain_name [, ...] | FOREIGN DATA WRAPPER fdw_name [, ...]| FOREIGN SERVER server_name [, ...] | TYPE type_name [, ...] }TO role_specification [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON { DOMAIN domain_name [, ...] | FOREIGN DATA WRAPPER fdw_name [, ...]| FOREIGN SERVER server_name [, ...] | TYPE type_name [, ...] }FROM role_specification [, ...][ CASCADE | RESTRICT ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }ON LARGE OBJECT loid [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }ON LARGE OBJECT loid [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]{ CREATE | ALL [ PRIVILEGES ] }ON TABLESPACE tablespace_name [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]

GRANT { USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]TO role_specification [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]{ USAGE | ALL [ PRIVILEGES ] }ON LANGUAGE lang_name [, ...]FROM role_specification [, ...][ CASCADE | RESTRICT ]

-- 启用行级安全ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- 创建数据库 hrCREATE DATABASE hr;
-- 切换到 hr 数据库\c hr
-- 创建 departments 表CREATE TABLE departments (    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOT NULL UNIQUE,    manager VARCHAR(255) NOT NULL);
-- 插入初始数据INSERT INTO departments (name, manager)VALUES ('Sales', 'alice'),('Marketing', 'bob'),('IT', 'jack');
-- 创建组角色 managersCREATE ROLE managers;
-- 授予 managers 角色对 public 模式中所有表的 SELECT 权限GRANT SELECT ON ALL TABLES IN SCHEMA public TO managers;
-- 创建部门经理角色并分配到 managers 组CREATE ROLE alice WITH LOGIN IN ROLE managers;CREATE ROLE bob WITH LOGIN IN ROLE managers;CREATE ROLE jack WITH LOGIN IN ROLE managers;
-- 在 departments 表上启用行级安全性ALTER TABLE departments ENABLE ROW LEVEL SECURITY;
-- 创建行级安全策略CREATE POLICY department_managersON departments FOR SELECTUSING (manager = current_user);
-- 使用 alice 角色连接到 hr 数据库\c hr alice-- 查询 departments 表SELECT * FROM departments;
-- 使用 bob 角色连接到 hr 数据库\c hr bob-- 查询 departments 表SELECT * FROM departments;
-- 使用超级用户连接到 hr 数据库\c hr haloSELECT * FROM departments;