PostgreSQL行级安全策略

列级权限

grant select on column to

行级权限

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_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

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

• using 针对已经存在的记录的校验. 可实施在select, update, delete, ALL上
• whth check 针对将要新增的记录的校验, 可实施在insert, update, ALL上

要做到行级安全性的管控，需要在表上开启行级安全性。

ALTER TABLE ... ENABLE ROW LEVEL SECURITY

测试：

postgres=# select * from t1;
 tt | num 
----+-----
 a  |   2
 b  |   3
 c  |   1
(3 rows)


create user user01 ;
CREATE ROLE

grant select ON public.t1 to user01;
GRANT

例1：限制user01只能查询t1表中num=1的数据

postgres=# create policy user01_select_t1 ON t1 for select to user01 using(num=1);
CREATE POLICY
postgres=# \d t1
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 tt     | character varying(12) |           |          | 
 num    | numeric               |           |          | 
Policies (row security disabled):
    POLICY "user01_select_t1" FOR SELECT
      TO user01
      USING ((num = (1)::numeric))

• 我们需要在表上开启行级安全性，否则该策略默认是disabled

开启行级安全性：

postgres=# alter table t1 enable row level security ;
ALTER TABLE

使用user01查看：

postgres=# \c - user01 
You are now connected to database "postgres" as user "user01".
postgres=> select * from t1;
 tt | num 
----+-----
 c  |   1
(1 row)

例2：创建新增数据的策略——user02不能插数据

bill@bill=>alter table t2 enable row level security ;
ALTER TABLE

对于insert需要使用with check进行限制。

bill@bill=>create policy user01_insert_t1 on t2 for insert to user01 with check(true);
CREATE POLICY

再次切换user01用户插入：

user01@bill=>insert into t2 values(3,'user03');
INSERT 0 1

我们再试试其它用户可不可以插入：

user01@bill=>\c - user02
You are now connected to database "bill" as user "user02".
user02@bill=>insert into t2 values(3,'user03');
ERROR: permission denied for table t2

可以看到user02没法插入数据了，说明我们的策略生效了。

数据加密

除了RLS来限制用户访问数据的安全策略外，pg中还允许对行数据进行加密，这样对于用户来说某些关键的数据也是起到了限制访问的作用。

数据加密首先需要安装pgcrypto扩展：

bill@bill=>create extension pgcrypto ;
CREATE EXTENSION

这里简单演示下如何进行加密。

新建表：

bill@bill=>create table t1(id int,name text,password text);
CREATE TABLE

我们使用pgcrypto来对该表的password列进行加密，这里使用md5进行加密：

bill@bill=>insert into t1 values(1,'bill',crypt('123456',gen_salt('md5')));
INSERT 0 1
bill@bill=>select * from t1;
 id | name |              password
----+------+------------------------------------
  1 | bill | $1$4wUg1TlS$Fy7zodd7dRURSuI88GHx00
(1 row)

验证：返回t说明输入的密码正确。

bill@bill=>SELECT (password  = crypt('123456',password)) AS pwmd5 FROM t1 ;

 pwmd5
--------------
 t
(1 row)