PostGreSQL数据库-行级安全策略

A00000豆豆米米💕 2024-10-14

186

行级安全策略说明：

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: https://www.postgresql.org/docs/14/sql-createpolicy.html

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: https://www.postgresql.org/docs/14/sql-droppolicy.html

–修改策略

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: https://www.postgresql.org/docs/14/sql-alterpolicy.html

示例：

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的行级安全性。

答：

以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”

----------------±--------±----------±---------±--------±---------±------------±-------------±------------

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’);

---------±---------±-----------±--------------±------------±------------±---------------±-------------±------------±--------------±-------------±----------±------

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;

| 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”

----------------±--------±----------±---------±--------±---------±------------±-------------±------------

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;

-----±--------±---------±-------±--------------±---------±--------±-------------

(0 rows)

或者

appdb=# \d+ commission

Table “apps.commission”

----------------±--------±----------±---------±--------±---------±------------±-------------±------------

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”

----------------±--------±----------±---------±--------±---------±------------±-------------±------------

Access method: heap

参考：

1.https://www.cnblogs.com/zhangfx01/p/14367567.html

2.https://www.postgresql.org/docs/14/sql-createpolicy.html

3.https://www.postgresql.org/docs/14/sql-droppolicy.html

4.https://www.postgresql.org/docs/14/sql-droppolicy.html

postgresql运维

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

PostGreSQL数据库-行级安全策略

行级安全策略说明：

行级安全策略语法

–创建策略

–删除策略

–修改策略

相关字典表查看安全策略

示例：

评论