PostGreSQL数据库-行级安全策略
所有对数据的操作，暴扣数据查询和更新，都受策略的限制，如果没有配置安全策略，所有的查询和更新都会禁止，但是对全表进行操作的命令，truncate和refrences不受影响。但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系，所有策略中任意一个为TRUE都通过。USING：对表中的已有数据进行检查的语句，可实施在select，update，delete，all上。每个策略
行级安全策略说明：
9.5版本新增的特性，该特性是在数据库授权体系下提供的更细粒度的控制。通俗的说就是不同用户可以看到表中不同的数据，这种控制是行级别的
9.5以前的数据库安全技术是通过grant/revoke来实现的，这两个指令提供了对象级的安全限制，针对表还有列级别的安全限制。
所有对数据的操作，暴扣数据查询和更新，都受策略的限制，如果没有配置安全策略，所有的查询和更新都会禁止，但是对全表进行操作的命令，truncate和refrences不受影响
行级安全策略可以加在命令上，也可以加在角色上，也可以两者都加。命令可以是 ALL, SELECT, INSERT, UPDATE 和DELETE, 同一个策略也可以赋予多个角色。
但是表的所有者，超级用户 (postgres) 以及加上了 BYPASSRLS属性的角色不受安全性的限制。
如果应用想忽略行级安全性机制的限制，也可以将 row_security 设置为 off。
CREATE POLICY ， ALTER POLICY ， DROP POLICY 命令分别用于策略的创建、修改和删除， ALTER TABLE 可以用于行级安全性的启用 / 禁用。
每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，
但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系，所有策略中任意一个为TRUE都通过。
行级安全策略语法
--创建策略
CREATE POLICY
Description: define a new row-level security policy for a table
Syntax:
CREATE POLICY name ON table_name
[ AS { PERMISSIVE | RESTRICTIVE } ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]
URL: PostgreSQL: Documentation: 14: CREATE POLICY
name：同一个表上的policy不能重复，不同表的policy可以重复
table_name：为哪个表创建policy
AS，policy的生效模式，PERMISSIVE => or，RESTRICTIVE => and，默认PERMISSIVE
For，对哪个操作生效，默认ALL
TO，对哪个role生效，默认public
USING：对表中的已有数据进行检查的语句，可实施在select，update，delete，all上
WITH CHECK：对新数据进行检查的语句， 可实施在insert，update，all上
--删除策略
DROP POLICY
Description: remove a row-level security policy from a table
Syntax:
DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
URL: PostgreSQL: Documentation: 14: DROP POLICY
--修改策略
ALTER POLICY
Description: change the definition of a row-level security policy
Syntax:
ALTER POLICY name ON table_name RENAME TO new_name
ALTER POLICY name ON table_name
[ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ]
URL: PostgreSQL: Documentation: 14: ALTER POLICY
相关字典表查看安全策略
--查看表是否启用RLS规则
select relname,relrowsecurity,relforcerowsecurity from pg_class where relname='commission'
--查看某个角色是否可以跳过规则
select rolname,rolbypassrls from pg_roles where rolname='soojie'
--查看创建的安全策略
select * from pg_policy
--对表启用或者关闭安全策略
alter table commission enable row level policy;
alter table commission disable row level policy;
--使用户跳过、不跳过安全策略
alter user soojie bypassrls;
alter user soojie nobypassrls;
示例：
1)以postgres用户登录tdb数据库， 执行以下SQL，创建测试数据，并创建管用户john、soojie；
create table commission ( empno int, ename text, address text, salary int, account_number text );
insert into commission values (1, 'john', '2 down str',20000, 'HDFC-22001' );
insert into commission values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );
2)在创建的表commission 上启用行级安全性；
3)创建策略员工 john 只能查看包含 john 信息的行。同样，员工 clark 和 soojie 只能查看各自行中的信息，而超级用户或表所有者可以查看所有信息；
4)设置用户soojie 跳过行级安全策略；
5)删除行级安全策略并关闭表employee的行级安全性。
答：
1) 以postgres用户登录tdb数据库， 执行以下SQL，创建测试数据，并创建管用户john、soojie；
create table commission ( empno int, ename text, address text, salary int, account_number text );
insert into commission values (1, 'john', '2 down str',20000, 'HDFC-22001' );
insert into commission values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );
--1.1 创建schema
appdb=# \c
You are now connected to database "appdb" as user "postgres".
appdb=# create schema apps;
CREATE SCHEMA
--1.2 设置路径为apps,并将表创建到apps schema 中
appdb=# set search_path=apps;
SET
appdb=# show search_path;
search_path
-------------
apps
(1 row)
appdb=# create table commission ( empno int, ename text, address text, salary int, account_number text );
CREATE TABLE
appdb=# insert into commission values (1, 'john', '2 down str',20000, 'HDFC-22001' );
INSERT 0 1
appdb=# insert into commission values (3, 'soojie', 'Down st 17th', 60000, 'ICICI-19022' );
INSERT 0 1
--1.3 查看表信息
appdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
apps | commission | table | postgres
(1 row)
appdb=# \d+ commission
Table "apps.commission"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
empno | integer | | | | plain | | |
ename | text | | | | extended | | |
address | text | | | | extended | | |
salary | integer | | | | plain | | |
account_number | text | | | | extended | | |
Access method: heap
2)在创建的表 commission 上启用行级安全性；
--2.1 在表上启用行安全性
alter table commission enable row level security;
--2.2 验证表的行安全是否开启
appdb=# select relname,relrowsecurity from pg_class where relname='commission'
appdb-# ;
relname | relrowsecurity
------------+----------------
commission | t
(1 row)
3)创建策略员工 john、soojie 只能查看包含 john、soojie 各自信息的行，而超级用户或表所有者可以查看所有信息；
--3.1 分别创建john、soojie三个用户，并授予commission表的查询权限
create user john;
grant usage on schema apps to john;
grant select on apps.commission to john;
create user soojie;
grant usage on schema apps to soojie;
grant select on apps.commission to soojie;
appdb=# create user john;
CREATE ROLE
appdb=# grant usage on schema apps to john;
GRANT
appdb=# grant select on apps.commission to john;
GRANT
appdb=# create user soojie;
CREATE ROLE
appdb=# grant usage on schema apps to soojie;
GRANT
appdb=# grant select on apps.commission to soojie;
GRANT
--3.2 查看创建的用户信息，通过查询 pg_roles 字典表查看或者\du 查看
appdb=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
appuser | Create DB +| {}
| Password valid until 2024-11-01 00:00:00+08 |
john | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readonlyuser | | {}
soojie | | {}
或者
appdb=# select * from pg_roles where rolname in ('john','soojie');
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
john | f | t | f | f | t | f | -1 | ******** | | f | | 16434
soojie | f | t | f | f | t | f | -1 | ******** | | f | | 16435
(2 rows)
--3.3 创建行安全策略
appdb=# create policy emp_rls on commission for all using (ename=current_user);
CREATE POLICY
--3.4 查看创建的安全策略，通过查询 pg_policy 字典表查看或者\d+ 表名扩展信息查看
appdb=# select * from pg_policy;
oid | polname | polrelid | polcmd | polpermissive | polroles | polqual
| polwithcheck
-------+---------+----------+--------+---------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------+--------------
16436 | emp_rls | 16429 | * | t | {0} | {OPEXPR :opno 260 :opfuncid 247 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 950 :args ({VAR :varno 1 :varattno 2 :vartype 25 :vartypmod -1 :varcollid 100 :v
arlevelsup 0 :varnosyn 1 :varattnosyn 2 :location 51} {SQLVALUEFUNCTION :op 10 :type 19 :typmod -1 :location 57}) :location 56} |
(1 row)
或者
appdb=# \d+ commission
Table "apps.commission"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
empno | integer | | | | plain | | |
ename | text | | | | extended | | |
address | text | | | | extended | | |
salary | integer | | | | plain | | |
account_number | text | | | | extended | | |
Policies:
POLICY "emp_rls"
USING ((ename = CURRENT_USER))
Access method: heap
--3.5 使用postgres用户查看表commission
appdb=# \c - postgres
You are now connected to database "appdb" as user "postgres".
appdb=# select * from commission;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+----------------
1 | john | 2 down str | 20000 | HDFC-22001
2 | clark | 132 south avn | 80000 | HDFC-23029
3 | soojie | Down st 17th | 60000 | ICICI-19022
(3 rows)
--3.6 分别使用john和soojie用户查看commission的数据，切换之后要设置搜索路径才能找到表
\c - john
set search_path=apps;
select * from commission;
appdb=> \c - john
You are now connected to database "appdb" as user "john".
appdb=> set search_path=apps;
SET
appdb=> select * from commission;
empno | ename | address | salary | account_number
-------+-------+------------+--------+----------------
1 | john | 2 down str | 20000 | HDFC-22001
(1 row)
appdb=> \c - soojie
You are now connected to database "appdb" as user "soojie".
appdb=> set search_path=apps;
SET
appdb=> select * from commission;
empno | ename | address | salary | account_number
-------+--------+--------------+--------+----------------
3 | soojie | Down st 17th | 60000 | ICICI-19022
(1 row)
4)设置用户soojie 跳过行级安全策略
--4.1 切换到superuser才能更改权限；
\c - postgres
alter user soojie bypassrls;
appdb=> alter user soojie bypassrls;
ERROR: must be superuser to change bypassrls attribute
appdb=> \c - postgres
You are now connected to database "appdb" as user "postgres".
appdb=# alter user soojie bypassrls;
ALTER ROLE
--4.2 切换到soojie查看commission表的数据
appdb=# \c - soojie
You are now connected to database "appdb" as user "soojie".
appdb=> set search_path=apps
appdb-> ;
SET
appdb=> select *from commission;
empno | ename | address | salary | account_number
-------+--------+---------------+--------+----------------
1 | john | 2 down str | 20000 | HDFC-22001
2 | clark | 132 south avn | 80000 | HDFC-23029
3 | soojie | Down st 17th | 60000 | ICICI-19022
(3 rows)
5)删除行级安全策略并关闭表employee的行级安全性。
--5.1 删除行安全策略
appdb=# drop policy if exists emp_rls on commission;
DROP POLICY
--5.2 验证是否删除策略，分别查询 pg_policy 字典表和 \d+ 表的扩展信息查看
appdb=# select * from pg_policy;
oid | polname | polrelid | polcmd | polpermissive | polroles | polqual | polwithcheck
-----+---------+----------+--------+---------------+----------+---------+--------------
(0 rows)
或者
appdb=# \d+ commission
Table "apps.commission"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
empno | integer | | | | plain | | |
ename | text | | | | extended | | |
address | text | | | | extended | | |
salary | integer | | | | plain | | |
account_number | text | | | | extended | | |
Access method: heap
--5.3 关闭表commission的行安全策略，切换到表的拥有者才能执行
appdb=> alter table commission disable row level security;
ERROR: must be owner of table commission
appdb=> \c - postgres
You are now connected to database "appdb" as user "postgres".
appdb=# set search_path=apps;
SET
appdb=# alter table commission disable row level security;
ALTER TABLE
--5.4 验证是否关闭行安全策略
appdb=# select relname,relrowsecurity from pg_class where relname='commission';
relname | relrowsecurity
------------+----------------
commission | f
(1 row)
appdb=# \d+ commission
Table "apps.commission"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
empno | integer | | | | plain | | |
ename | text | | | | extended | | |
address | text | | | | extended | | |
salary | integer | | | | plain | | |
account_number | text | | | | extended | | |
Access method: heap
参考：
1.postgresql-行级安全-RLS - 月图灵 - 博客园
2.PostgreSQL: Documentation: 14: CREATE POLICY
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
16
0- 0
已为社区贡献2条内容
所有评论(0)