Postgresql行安全策略(Row Security Policies)_postgres 行安全策略

PG行安全策略其实就是对不同用户，不同行数据的可见性，和可修改性。在我看来其实就是权限的维度不同，PG在9.5之前提供表级别，列级别的权限控制，9.5之后添加行级策略，这样用户就可以更加灵活的控制数据，对不同用户之间的数据隐私起到了保护作用。

先举一个列权限的例子,也可以通过视图的方式进行权限限制：

#一张表sex
\c postgres postgres 
postgres=> \d  sex
                    Table "public.sex"
  Column   |     Type     | Collation | Nullable | Default 
-----------+--------------+-----------+----------+---------
 sex       | character(1) |           |          | 
 otherinfo | text         |           |          | 
Indexes:
    "idx_sex_1" btree (sex)
#赋予otherinfo列给hank查看
grant select (otherinfo) on sex to hank;

#hank登陆后，查询所有列，报错
\c postgres hank
select * from sex;
ERROR:  permission denied for table sex

#查询otherinfo列，可正常查询
select otherinfo from sex;
      otherinfo      
---------------------
 4157362this is test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

表默认是没有任何行安全策略的， 行安全策略可以针对命令和角色指定。如命令SELECT、INSERT、UPDATE或DELETE。

 CREATE POLICY    #创建
  ALTER POLICY    #修改
   DROP POLICY    #删除
1
2
3

命令帮助如下：

Command:     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_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
1
2
3
4
5
6
7
8
9

这里需要注意USING和WITH CHECK选项:

1. USING语句用于检查现有表中行的策略表达式
2. WITH CHECK语句用于检查新行,也就是insert和update

#有accounts这样一张表
postgres=> \d accounts
                          Table "public.accounts"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 number | text    |           |          | 
 client | text    |           |          | 
 amount | numeric |           |          | 
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "accounts_number_key" UNIQUE CONSTRAINT, btree (number)
#数据如下
postgres=# select * from accounts;
 id | number | client |  amount  
----+--------+--------+----------
  1 | 1001   | alice  |  1000.00
  2 | 2001   | bob    | 910.0000
  3 | 2002   | bob    |     0.00
  4 | a      | a      |        3
(4 rows)
#创建一个policy,这里是给hank用户查询
CREATE POLICY account_ids ON accounts TO hank  USING (id = 1 or id =2);

#启用策略
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY

#使用hank可以查看到id为1和2的行
postgres=# \c  postgres hank
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
 id | number | client |  amount  
----+--------+--------+----------
  1 | 1001   | alice  |  1000.00
  2 | 2001   | bob    | 910.0000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

如果启用行策略，但是实际没有行策略，那么查询不到数据

postgres=> \c postgres postgres 
postgres=# \d  accounts
                          Table "public.accounts"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 number | text    |           |          | 
 client | text    |           |          | 
 amount | numeric |           |          | 
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "accounts_number_key" UNIQUE CONSTRAINT, btree (number)
Policies (forced row security enabled): (none)

postgres=# \c postgres hank
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
(0 rows)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

如果用户想要访问所有数据，不受行策略影响,也可以修改role属性为bypassrls，绕过行安全策略，示例如下：

postgres=> \c postgres postgres 
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "postgres".
postgres=# alter role hank bypassrls ;
ALTER ROLE
postgres=# \c postgres hank
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
 id | number | client |  amount  
----+--------+--------+----------
  1 | 1001   | alice  |  1000.00
  2 | 2001   | bob    | 910.0000
  3 | 2002   | bob    |     0.00
  4 | a      | a      |        3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

可查询策略相关视图：

postgres=> select * from pg_policies ;
 schemaname | tablename | policyname  | permissive | roles  | cmd |          qual          | with_check 
------------+-----------+-------------+------------+--------+-----+------------------------+------------
 public     | accounts  | account_ids | PERMISSIVE | {hank} | ALL | ((id = 1) OR (id = 2)) | 
(1 row)

ostgres=> select * from pg_policy;
-[ RECORD 1 ]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid           | 106391
polname       | account_ids
polrelid      | 48472
polcmd        | *
polpermissive | t
polroles      | {16449}
polqual       | {BOOLEXPR :boolop or :args ({OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 54} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 59 :constvalue 4 [ 1 0 0 0 0 0 0 0 ]}) :location 57} {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 64} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 69 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location 67}) :location 61}
polwithcheck  | 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

另外一下两个解释一下：
PERMISSIVE：默认选项，适用于一个给定查询的所有宽容性策略将被使用布尔“OR”操作符组合在一起。通过创该策略，可以在能被访问的记录集合中进行增加。如：

CREATE POLICY account_ids on accounts  TO hank  USING (id = 1 or  id =2);
#增加id=3,hank用户查询的时候，可以显示id=3的记录
CREATE POLICY account_ids on accounts  TO hank  USING (id = 1 or  id =2  or id =3);
1
2
3

RESTRICTIVE：限制性策略。适用于一个给定查询的所有限制性策略将被使用布
尔“AND”操作符组合在一起。通过创建限制性策略，管理员可以减少能被访问的记录集合，因为每一条记录都必须通过所有的限制性策略。在限制性策略作用之前，需要至少一条PERMISSIVE策略授予行的访问权限。如果只有限制性策略存在，则所有记录都不能被访问。当PERMISSIVE和RESTRICTIVE混合存在时，只有当一个记录能通过至少一条PERMISSIVE策略以及所有的限制性策略时，该记录才是可访问的。

以上说明是官方文档解释，一下难以理解，下面举个例子说明：
只有RESTRICTIVE的policy,记录都不显示

postgres=# CREATE POLICY account_ids_restric on accounts as RESTRICTIVE  TO hank  USING (id = 1 and client='alice');
CREATE POLICY
postgres=# \c  postgres hank
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=> select * from accounts;
 id | number | client | amount 
----+--------+--------+--------
(0 rows)
1
2
3
4
5
6
7
8
9

添加PERMISSIVE属性的policy

postgres=> \c postgres postgres 
psql (13.4, server 12.6)
You are now connected to database "postgres" as user "hank".
postgres=# CREATE POLICY account_ids on accounts  TO hank  USING (id = 1 or  id =2);
CREATE POLICY

#可见id = 1 and client='alice'的行了
postgres=# \c  postgres hank
psql (13.4, server 12.6)
postgres=> select * from accounts;
 id | number | client | amount  
----+--------+--------+---------
  1 | 1001   | alice  | 1000.00
  
postgres=> \d accounts
                          Table "public.accounts"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 number | text    |           |          | 
 client | text    |           |          | 
 amount | numeric |           |          | 
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (id)
    "accounts_number_key" UNIQUE CONSTRAINT, btree (number)
Policies (forced row security enabled):
    POLICY "account_ids"
      TO hank
      USING (((id = 1) OR (id = 2)))
    POLICY "account_ids_restric" AS RESTRICTIVE
      TO hank
      USING (((id = 1) AND (client = 'alice'::text)))
      
postgres=> select * from pg_policies ;
 schemaname | tablename |     policyname      | permissive  |    roles    |  cmd   |                           qual                            | with_check 
------------+-----------+---------------------+-------------+-------------+--------+-----------------------------------------------------------+------------
 public     | accounts  | account_ids_restric | RESTRICTIVE | {hank}      | ALL    | ((id = 1) AND (client = 'alice'::text))                   | 
 public     | accounts  | account_ids         | PERMISSIVE  | {hank}      | ALL    | ((id = 1) OR (id = 2))                                    | 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

参考：
https://www.postgresql.org/docs/13/sql-createpolicy.html
https://www.postgresql.org/docs/13/ddl-rowsecurity.html