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.oid
GROUP 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 test1
CREATE TEMPORARY TABLE temp_sales_data (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    price NUMERIC(10, 2)
);     -- 创建临时表将被拒绝，需要 TEMPORARY 权限
\c - halo
grant temporary on database test to test1 ;  -- 赋予  TEMPORARY 权限
\c - test1
CREATE 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 r
JOIN pg_namespace n ON true
WHERE 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 test1
create schema test1;        -- 创建成功，test1拥有数据库级别 create 
\c test test2
create 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 r
JOIN pg_namespace n ON true
WHERE (r.rolname = 'test1' OR r.rolname = 'test2') AND n.nspname = 'public';
-- 验证 schema 级 CREATE 权限
\c test test1
CREATE TABLE public.test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);        -- test1无 public 的 CREATE 权限，执行失败
\c test test2
CREATE 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 test2
CREATE 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 r
JOIN pg_namespace n ON true
WHERE (r.rolname = 'test1' OR r.rolname = 'test2') AND n.nspname = 'public';
-- 查询public.test_table
select * from public.test_table;
-- 赋予test1 USAGE 权限
\c test halo
grant USAGE on schema public TO public;
-- test1 虽有 USAGE 权限 ，但无此表 select 权限(需配合对象级权限)
\c test test1
select has_table_privilege('test1','public.test_table','select');
select * from public.test_table;
-- 赋予test1 select 表权限
\c test halo
grant select ON table public.test_table TO test1;
\c test test1
select * 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_type
FROM information_schema.table_privileges
WHERE 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 test1
SELECT 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;

-- 创建数据库 hr
CREATE 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');


-- 创建组角色 managers
CREATE 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_managers
ON departments 
FOR SELECT
USING (manager = current_user);


-- 使用 alice 角色连接到 hr 数据库
\c hr alice
-- 查询 departments 表
SELECT * FROM departments;


-- 使用 bob 角色连接到 hr 数据库
\c hr bob
-- 查询 departments 表
SELECT * FROM departments;


-- 使用超级用户连接到 hr 数据库
\c hr halo
SELECT * FROM departments;