postgresql行级安全策略

行级安全策略可以配置表中的那些数据可以被那些用户进行某种操作，比如rsl表中的name=aa的数据可以让a1看到

using 针对已经存在的记录的校验. 可实施在select, update, delete, ALL上

whth check 针对将要新增的记录的校验, 可实施在insert, update, ALL上

删除行级安全策略命令：drop policy a1_select_rsl(策略名) ON rsl（表名）;

测试如下：
创建测试表

mydb=> create table rsl(id int,name varchar(20));
CREATE TABLE
mydb=> insert into rsl values(1,'aa');
insert into rsl values(5,'aa');
insert into rsl values(6,'aa');
insert into rsl values(7,'aa');
insert into rsl values(8,'aa');
insert into rsl values(1,'bb');
insert into rsl values(2,'bb');
insert into rsl values(3,'bb');
insert into rsl values(4,'bb');
insert into rsl values(5,'bb');
insert into rsl values(6,'bb');
insert into rsl values(7,'bb');
insert into rsl values(8,'bb');
insert into rsl values(1,'cc');
insert into rsl values(2,'cc');
insert into rsl values(3,'cc');
insert into rsl values(4,'cc');
insert into rsl values(5,'cc');
insert into rsl values(6,'cc');
insert into rsl values(7,'cc');
insert into rsl values(8,'cc');INSERT 0 1
mydb=> insert into rsl values(2,'aa');
INSERT 0 1
mydb=> insert into rsl values(3,'aa');
INSERT 0 1
mydb=> insert into rsl values(4,'aa');
INSERT 0 1
mydb=> insert into rsl values(5,'aa');
INSERT 0 1
mydb=> insert into rsl values(6,'aa');
INSERT 0 1
mydb=> insert into rsl values(7,'aa');
INSERT 0 1
mydb=> insert into rsl values(8,'aa');
INSERT 0 1
mydb=> insert into rsl values(1,'bb');
INSERT 0 1
mydb=> insert into rsl values(2,'bb');
INSERT 0 1
mydb=> insert into rsl values(3,'bb');
INSERT 0 1
mydb=> insert into rsl values(4,'bb');
INSERT 0 1
mydb=> insert into rsl values(5,'bb');
INSERT 0 1
mydb=> insert into rsl values(6,'bb');
INSERT 0 1
mydb=> insert into rsl values(7,'bb');
INSERT 0 1
mydb=> insert into rsl values(8,'bb');
INSERT 0 1
mydb=> insert into rsl values(1,'cc');
INSERT 0 1
mydb=> insert into rsl values(2,'cc');
INSERT 0 1
mydb=> insert into rsl values(3,'cc');
INSERT 0 1
mydb=> insert into rsl values(4,'cc');
INSERT 0 1
mydb=> insert into rsl values(5,'cc');
INSERT 0 1
mydb=> insert into rsl values(6,'cc');
INSERT 0 1
mydb=> insert into rsl values(7,'cc');
INSERT 0 1
mydb=> insert into rsl values(8,'cc');
INSERT 0 1
mydb=>
mydb=>
mydb=>
mydb=>
mydb=>

查看表结构检查下数据

mydb=> \d rsl ;
                       Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(20) |           |          |

\d: extra argument ";" ignored
mydb=>
mydb=>
mydb=>
mydb=> \d rsl
                       Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 name   | character varying(20) |           |          |

mydb=> select * from rsl
mydb-> ;
 id | name
----+------
  1 | aa
  2 | aa
  3 | aa
  4 | aa
  5 | aa
  6 | aa
  7 | aa
  8 | aa
  1 | bb
  2 | bb
  3 | bb
  4 | bb
  5 | bb
  6 | bb
  7 | bb
  8 | bb
  1 | cc
  2 | cc
  3 | cc
  4 | cc
  5 | cc
  6 | cc
  7 | cc
  8 | cc
(24 rows)

mydb=>
mydb=>
mydb=>
mydb=>
mydb=>

创建行级安全策略
该策略使表中name=aa的数据对a1来说是可以select，即可以查询到的

mydb=> create policy a1_select_rsl ON rsl for select to a1 using(name=a1);
ERROR:  column "a1" does not exist
mydb=> create policy a1_select_rsl ON rsl for select to a1 using(name=aa);
ERROR:  column "aa" does not exist
mydb=> create policy a1_select_rsl ON rsl for select to a1 using(name='aa');
CREATE POLICY

需要添加单引号，不添加单引号会报错

rsl表中的行级安全策略没有启用，将策略启用下

mydb=>
mydb=>
mydb=> \d+ rsl
                                           Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              |
 name   | character varying(20) |           |          |         | extended |              |
Policies (row security disabled):
    POLICY "a1_select_rsl" FOR SELECT
      TO a1
      USING (((name)::text = 'aa'::text))

mydb=> alter table rsl enable row level security
mydb-> ;
ALTER TABLE
mydb=>
mydb=> \d+ rsl
                                           Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              |
 name   | character varying(20) |           |          |         | extended |              |
Policies:
    POLICY "a1_select_rsl" FOR SELECT
      TO a1
      USING (((name)::text = 'aa'::text))

切换用户至a1进行查询，查询之前给a1赋予查询权限

mydb=> select current_user;
 current_user
--------------
 user01
(1 row)

mydb=> \c - mydb
FATAL:  role "mydb" does not exist
Previous connection kept
mydb=> \c - a1
You are now connected to database "mydb" as user "a1".
mydb=> select current_user;
 current_user
--------------
 a1
(1 row)

mydb=> select * from rsl;
ERROR:  permission denied for relation rsl

mydb=# grant select ON rsl to a1;//使用创建表的用户进行赋权，或者使用超级用户进行赋权
GRANT

mydb=> select * from rsl;
 id | name
----+------
  1 | aa
  2 | aa
  3 | aa
  4 | aa
  5 | aa
  6 | aa
  7 | aa
  8 | aa
(8 rows)

使用b1查询rsl表发现查不出数据

mydb=> select current_user;
 current_user
--------------
 b1
(1 row)

mydb=> select * from rsl;
ERROR:  permission denied for relation rsl
mydb=# grant select ON rsl to b1;//使用创建表的用户进行赋权，或者使用超级用户进行赋权
GRANT

mydb=> select * from rsl;
 id | name
----+------
(0 rows)

创建插入策略，使得b1可以对name=当前用户的数据可以插入该表

mydb=> create policy a1_insert_t1 on rsl for insert to b1 with check( name = current_user);
ERROR:  must be owner of relation rsl

切换到user01用户给b1赋予插入权限之后，在切换到b1进行测试

mydb=> create policy a1_insert_t1 on rsl for insert to b1 with check( name = current_user);
CREATE POLICY
mydb=>
mydb=>
mydb=> \d+ rsl
                                           Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              |
 name   | character varying(20) |           |          |         | extended |              |
Policies:
    POLICY "a1_insert_t1" FOR INSERT
      TO b1
      WITH CHECK (((name)::name = CURRENT_USER))
    POLICY "a1_select_rsl" FOR SELECT
      TO a1
      USING (((name)::text = 'aa'::text))

mydb=> \c - user01
You are now connected to database "mydb" as user "user01".
mydb=> grant insert on rsl to b1;//给B1插入权限
GRANT
mydb=> \c - b1
You are now connected to database "mydb" as user "b1".
mydb=> insert into rsl values(9,'bb');//由于当前用户current_user是b1所以插入(9,'bb')不成功
ERROR:  new row violates row-level security policy for table "rsl"
mydb=>
mydb=> insert into rsl values(9,'b2');//由于当前用户current_user是b1所以插入(9,'b2')不成功
ERROR:  new row violates row-level security policy for table "rsl"
mydb=> insert into rsl values(9,'b1');//由于当前用户current_user是b1所以插入(9,'b1')成功
INSERT 0 1
mydb=> select current_user;
 current_user
--------------
 b1
(1 row)

mydb=> select * from rsl;//b1无查询策略所以无法查看数据
 id | name
----+------
(0 rows)

mydb=> \c - user01
You are now connected to database "mydb" as user "user01".
mydb=> select * from rsl;//使用表的owner查询，可以查到已插入的数据
 id | name
----+------
  1 | aa
  2 | aa
  3 | aa
  4 | aa
  5 | aa
  6 | aa
  7 | aa
  8 | aa
  1 | bb
  2 | bb
  3 | bb
  4 | bb
  5 | bb
  6 | bb
  7 | bb
  8 | bb
  1 | cc
  2 | cc
  3 | cc
  4 | cc
  5 | cc
  6 | cc
  7 | cc
  8 | cc
  9 | b1
(25 rows)

mydb=>

with check（true）

drop policy a1_insert_t1 ON rsl;

create policy a1_insert_t1 on rsl for insert to b1 with check(true);//该策略即为使用b1用户
mydb=> \d+ rsl
                                           Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              |
 name   | character varying(20) |           |          |         | extended |              |
Policies:
    POLICY "a1_insert_t1" FOR INSERT
      TO b1
      WITH CHECK (true)
    POLICY "a1_select_rsl" FOR SELECT
      TO a1
      USING (((name)::text = 'aa'::text))

mydb=>  insert into rsl values(10,'bb');
INSERT 0 1
mydb=> insert into rsl values(10,'bbbb');
INSERT 0 1
mydb=> select current_user;
 current_user
--------------
 b1
(1 row)

mydb=> select current_user;
 current_user
--------------
 b1
(1 row)

a1无法插入
mydb=> \c - a1
You are now connected to database "mydb" as user "a1".
mydb=> insert into rsl values(10,'aaaa');
ERROR:  new row violates row-level security policy for table "rsl"

using(true)//即为对所有的行数据的操作权限

mydb=> \d+ rsl
                                           Table "public.rsl"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              |
 name   | character varying(20) |           |          |         | extended |              |
Policies:
    POLICY "a1_insert_t1" FOR INSERT
      TO b1
      WITH CHECK (true)
    POLICY "a1_select_rsl" FOR SELECT
      TO a1
      USING (true)

mydb=> select * from rsl;
 id | name
----+------
  1 | aa
  2 | aa
  3 | aa
  4 | aa
  5 | aa
  6 | aa
  7 | aa
  8 | aa
  1 | bb
  2 | bb
  3 | bb
  4 | bb
  5 | bb
  6 | bb
  7 | bb
  8 | bb
  1 | cc
  2 | cc
  3 | cc
  4 | cc
  5 | cc
  6 | cc
  7 | cc
  8 | cc
  9 | b1
 10 | bb
 10 | bbbb
(27 rows)

如果创建的行级安全策略没有to username，则说明是对所有用户使用该策略

mydb=> create policy all_select_rsl on rsl  for select using(true);
CREATE POLICY
mydb=> \c - b1
You are now connected to database "mydb" as user "b1".
mydb=> select current_user;
 current_user
--------------
 b1
(1 row)

mydb=> select * from rsl;
 id | name
----+------
  1 | aa
  2 | aa
  3 | aa
  4 | aa
  5 | aa
  6 | aa
  7 | aa
  8 | aa
  1 | bb
  2 | bb
  3 | bb
  4 | bb
  5 | bb
  6 | bb
  7 | bb
  8 | bb
  1 | cc
  2 | cc
  3 | cc
  4 | cc
  5 | cc
  6 | cc
  7 | cc
  8 | cc
  9 | b1
 10 | bb
 10 | bbbb
(27 rows)

本文参考文档： https://blog.csdn.net/weixin_39540651/article/details/117331707
https://blog.csdn.net/dazuiba008/article/details/121020256