原作者:tracy
一、测试环境
数据库版本：MogDB1.1.0/OpenGauss1.1.0
操作系统版本: Centos7.9
二、数据库对象权限
1. MogDB/OpenGauss数据库对象权限包括：
SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE、USAGE、ALTER、DROP、COMMENT、INDEX和VACUUM。
这些权限可以通过GRANT/REVOKE 来对用户进行授予或撤销。
2. 给用户赋予表的查询权限：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM } [, …] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, …] | ALL TABLES IN SCHEMA schema_name [, …] }
TO { [ GROUP ] role_name | PUBLIC } [, …]
[ WITH GRANT OPTION ];
例：GRANT SELECT ON all_data TO alice, bob, peter;
为了让用户成功的查询不属于它自己的sechma下的表，除了要执行上面语句赋予该用户对表的查询权限以外，还需要赋予该用户使用表所属shema的使用权限：
GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, …] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, …]
TO { [ GROUP ] role_name | PUBLIC } [, …]
[ WITH GRANT OPTION ];
例：GRANT USAGE ON SCHEMA enmo TO alice, bob, peter;
否则，用户在查询表数据时会报错：
enmo=> select * from enmo.all_data;
ERROR: permission denied for schema enmo
三、行级访问控制
1.行级访问控制简介
行级访问控制特性将数据库访问控制精确到数据表行级别，使数据库达到行级访问控制的能力。不同用户执行相同的SQL查询操作，读取到的结果是不同的。
用户可以在数据表创建行访问控制(Row Level Security)策略，该策略是指针对特定数据库用户、特定SQL操作生效的表达式。当数据库用户对数据表访问时，若SQL满足数据表特定的Row Level Security策略，在查询优化阶段将满足条件的表达式，按照属性(PERMISSIVE | RESTRICTIVE)类型，通过 AND或OR方式拼接，应用到执行计划上。
行级访问控制的目的是控制表中行级数据可见性，通过在数据表上预定义Filter，在查询优化阶段将满足条件的表达式应用到执行计划上，影响最终的执行结果。当前受影响的SQL语句包括SELECT，UPDATE，DELETE。
开启表的行级访问策略：
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} ENABLE ROW LEVEL SECURITY;
例：ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
关闭表的行级访问策略：
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} DISABLE ROW LEVEL SECURITY;
例：ALTER TABLE all_data DISABLE ROW LEVEL SECURITY;
创建行级访问控制策略：
CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC } [, …] ]
USING ( using_expression )
例：CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
删除行级访问控制策略：
DROP [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ]
例：DROP ROW LEVEL SECURITY POLICY all_data_rls ON all_data;
修改行级访问控制策略名称：
ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name;
例:ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;
修改行级访问控制策略影响用户：
ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
[ TO { role_name | PUBLIC } [, …] ]
[ USING ( using_expression ) ];
例：ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice, bob;
查询数据库中表的行级访问策略：
select *from PG_RLSPOLICIES;
2.测试：
a.创建行级访问控制策略，并验证策略生效
enmo=> select * from enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
(3 rows)
enmo=> \d+ all_data
Table "enmo.all_data"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
role | character varying(100) | | extended | |
data | character varying(100) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
enmo=> --打开行访问控制策略开关
enmo=> ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE
enmo=> --创建行访问控制策略，当前用户只能查看用户自身的数据
enmo=> CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
CREATE ROW LEVEL SECURITY POLICY
enmo=> \d+ all_data
Table "enmo.all_data"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
role | character varying(100) | | extended | |
data | character varying(100) | | extended | |
Row Level Security Policies:
POLICY "all_data_rls" FOR ALL
TO public
USING (((role)::name = "current_user"()))
Has OIDs: no
Options: orientation=row, compression=no, enable_rowsecurity=true
enmo=> select *from PG_RLSPOLICIES;
-[ RECORD 1 ]----+----------------------------------
schemaname | enmo
tablename | all_data
policyname | all_data_rls
policypermissive | PERMISSIVE
policyroles | {public}
policycmd | ALL
policyqual | ((role)::name = "current_user"())
enmo=>--行级访问策略对表的所有者无效
enmo=> select * from enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
(3 rows)
enmo=> \c - alice
Password for user alice:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "alice".
enmo=>--用户alice只能访问到role='alice'的数据行
enmo=> select *from enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
(1 row)
enmo=>--用户alice对表enmo.all_data的insert操作未受行级访问控制影响
enmo=> INSERT INTO enmo.all_data VALUES(4, 'ogdb', 'ogdb data');
INSERT 0 1
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
(1 row)
enmo=> \conninfo
You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauass/tmp" at port "15400".
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
4 | ogdb | ogdb data
(4 rows)
enmo=>--用户alice对表enmo.all_data的update操作只能修改role='alice'的数据行
enmo=> update enmo.all_data set id=id+10;
UPDATE 1
enmo=> EXPLAIN(COSTS OFF) update enmo.all_data set id=id+10;
QUERY PLAN
\----------------------------------------------------------------
Update on all_data
-> Seq Scan on all_data
Filter: ((role)::name = 'alice'::name)
Notice: This query is influenced by row level security feature
(4 rows)
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
11 | alice | alice data
(1 row)
enmo=> \conninfo
You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
2 | bob | bob data
3 | peter | peter data
4 | ogdb | ogdb data
11 | alice | alice data
(4 rows)
enmo=>--用户alice对表enmo.all_data的delete操作只能删除role='alice'的数据行
enmo=> delete from enmo.all_data;
DELETE 1
enmo=> EXPLAIN(COSTS OFF) delete from enmo.all_data;
QUERY PLAN
\----------------------------------------------------------------
Delete on all_data
-> Seq Scan on all_data
Filter: ((role)::name = 'alice'::name)
Notice: This query is influenced by row level security feature
(4 rows)
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+------+------
(0 rows)
enmo=> \conninfo
You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
2 | bob | bob data
3 | peter | peter data
4 | ogdb | ogdb data
(3 rows)
enmo=> --用户bob只能访问到role='bob'的数据行
enmo=> \c - bob
Password for user bob:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "bob".
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+------+----------
2 | bob | bob data
(1 row)
enmo=> EXPLAIN(COSTS OFF) SELECT * FROM enmo.all_data;
QUERY PLAN
\----------------------------------------------------------------
Seq Scan on all_data
Filter: ((role)::name = 'bob'::name)
Notice: This query is influenced by row level security feature
(3 rows)
enmo=>--行级访问策略对数据库初始化用户无效
enmo=# \c - ogdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "ogdb".
enmo=# select *from enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
(3 rows)
enmo=#
b.修改行级访问控制策略名称，并设置仅对alice用户生效
enmo=> ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;
ALTER ROW LEVEL SECURITY POLICY
enmo=> ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice;
ALTER ROW LEVEL SECURITY POLICY
enmo=> select *from PG_RLSPOLICIES;
-[ RECORD 1 ]----+----------------------------------
schemaname | enmo
tablename | all_data
policyname | all_data_new_rls
policypermissive | PERMISSIVE
policyroles | {alice}
policycmd | ALL
policyqual | ((role)::name = "current_user"())
enmo=> \c - alice
Password for user alice:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "alice".
enmo=>--用户alice只能访问到role='alice'的数据行
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
(1 row)
enmo=>
enmo=> \c - bob
Password for user bob:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "bob".
enmo=> --行级访问策略对用户bob无效
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
1 | alice | alice data
2 | bob | bob data
3 | peter | peter data
(3 rows)
enmo=>
c.对表禁用行级访问控制后，行级访问控制策略失效
enmo=> \conninfo
You are connected to database "enmo" as user "enmo" via socket in "/opt/OpenGauss/tmp" at port "15400".
enmo=> ALTER TABLE all_data DISABLE ROW LEVEL SECURITY;
ALTER TABLE
enmo=> \x
Expanded display is on.
enmo=> select *from PG_RLSPOLICIES;
-[ RECORD 1 ]----+----------------------------------
schemaname | enmo
tablename | all_data
policyname | all_data_new_rls
policypermissive | PERMISSIVE
policyroles | {alice}
policycmd | ALL
policyqual | ((role)::name = "current_user"())
enmo=> \dt+ all_data
List of relations
-[ RECORD 1 ]---------------------------------
Schema | enmo
Name | all_data
Type | table
Owner | enmo
Size | 8192 bytes
Storage | {orientation=row,compression=no}
Description |
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
2 | bob | bob data
3 | peter | peter data
4 | ogdb | ogdb data
1 | alice | alice data
(4 rows)
enmo=> \c - alice
Password for user alice:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmo" as user "alice".
enmo=>--行级访问控制策略未对alice用户生效
enmo=> SELECT * FROM enmo.all_data;
id | role | data
----+-------+------------
2 | bob | bob data
3 | peter | peter data
4 | ogdb | ogdb data
1 | alice | alice data
(4 rows)
enmo=> EXPLAIN(COSTS OFF) SELECT * FROM enmo.all_data;
QUERY PLAN
\----------------------
Seq Scan on all_data
(1 row)
enmo=>
