PostgreSQL的行级安全策略(RLS)

RLS(ROW Level Security) 是PostgreSQL 9.5版本中新增特性，提供了基于行的安全策略，限制数据库用户的查看表数据权限。要知道在9.5版本之前，数据库中对用户的权限管控在表级别，例如：限制某个用户只能查询某个表。采用RLS后，不同的用户访问一个表可以看到不同的数据。

1、概述

默认的话，表没有任何安全策略限制。这样用户根据 SQL 特权系统具有对表的访问特权，对于查询或更新来说其中所有的行都是平等的。当在一个表上启用行安全性时，所有对该表选择行或者修改行的普通访问都必须被一条 行安全性策略所允许（不过，表的拥有者通常不服从行安全性策略）。如果表上不存在安全策略，如果没有配置安全策略，所有的数据查询和更新都会禁止，但是对全表进行操作的命令，比如 TRUNCATE 和 REFERENCES 不受影响。

行安全性策略可以针对特定的命令、角色或者两者。一条策略可以被指定为适用于ALL命令，或者查询（SELECT）、 插入（INSERT）、更新（UPDATE）或者删除（DELETE）。同一个策略可分配多个角色，并且通常的角色成员关系和继承规则也适用。但是表的所有者，超级用户 (postgres) 以及加上了 BYPASSRLS 属性的角色不受安全性的限制。

只有所有者才具有启用 禁用行级安全性，给表添加策略的权限。

常见命令：

• CREATE POLICY ：创建策略
• ALTER POLICY ：修改策略
• DROP POLICY ：删除策略
• ALTER TABLE ：用于行级安全性的启用 禁用。

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系。

2、相关示例

新建一张表，并插入三条数据：

CREATE TABLE userlist (id text, name text);insert into userlist values(1,'user01');insert into userlist values(2,'user02');insert into userlist values(3,'user03');

执行查询操作，查看数据表中的结果

select * from userlist； id |  name  ----+-------- 2  | user02 1  | user01 3  | user03(3 rows)

创建一个用户，并授予其查询权限；

create user user01 with password '123456';create user user02 with password '123456';grant select ON public.userlist to user01,user02;grant insert ON public.userlist to user01,user02;grant update ON public.userlist to user01,user02;

此时user01、user02可以执行查询操作，获得整个表userlist的全部数据；

用例1：限制user01用户限制只能查询表userlist中name=‘user01’的数据

创建策略，

create policy user01_select ON userlist for select to user01 using(current_user=name);

此时查看表结构;

\d userlist

返回结果如下：

            Table "public.userlist" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- id     | text |           |          |  name   | text |           |          | Policies (row security disabled):    POLICY "user01_select" FOR SELECT      TO user01      USING (((CURRENT_USER)::text = name))

从这儿我们可以看到该策略是没有启用的，处于disabled状态；

要开启表的行级安全，需要使用ALTER TABLE命令：

alter table userlist  enable row level security ;

使用账号user01连接数据库

\c  -  user01You are now connected to database "memfire" as user "user01".

执行查询操作，得到结果如下：

select * from userlist id |  name  ----+-------- 1  | user01(1 row)

开启行级权限后，user01仅能够查询name=‘user01’的数据

使用账号user02连接数据库

\c - user02You are now connected to database "memfire" as user "user02".

执行查询操作，得到结果如下：

select * from userlist; id | name ----+------(0 rows)

开启行级权限后，user02无法查询数据，说明策略生效了

用例2：创建新增数据策略

切换到数据表owner账户, 创建一个插入策略；

create policy user01_insert on userlist for insert to user01 with check(true);

执行命令，查看已经创建的策略；

--pg_policies可以查看已经创建的策略select * from pg_policies;

返回结果如下：

 schemaname | tablename |  policyname   | permissive |  roles   |  cmd   |             qual              | with_check ------------+-----------+---------------+------------+----------+--------+-------------------------------+------------ public     | userlist  | user01_select | PERMISSIVE | {user01} | SELECT | ((CURRENT_USER)::text = name) |  public     | userlist  | user01_insert | PERMISSIVE | {user01} | INSERT |                               | true(2 rows)

使用账号user01连接数据库

\c  -  user01You are now connected to database "memfire" as user "user01".

插入一条新数据

 insert into userlist values(4,'user01');INSERT 0 1

执行查询操作

select * from userlist; id |  name  ----+-------- 4  | user01 1  | user01(2 rows)

使用账号user02连接数据库

\c - user02You are now connected to database "memfire" as user "user02".

插入一条新数据

insert into userlist values(5,'user02');ERROR:  new row violates row-level security policy for table "userlist"

采用user02无法插入数据，说明创建的策略生效。

用例3：创建删除数据策略

切换到数据表owner账户, 创建一个插入策略；

create policy user01_delete on userlist for delete to user01 using(current_user=name);

执行命令，查看已经创建的策略；

--pg_policies可以查看已经创建的策略select * from pg_policies;

返回结果如下：

 schemaname | tablename |  policyname   | permissive |  roles   |  cmd   |             qual              | with_check ------------+-----------+---------------+------------+----------+--------+-------------------------------+------------ public     | userlist  | user01_select | PERMISSIVE | {user01} | SELECT | ((CURRENT_USER)::text = name) |  public     | userlist  | user01_insert | PERMISSIVE | {user01} | INSERT |                               | true public     | userlist  | user01_delete | PERMISSIVE | {user01} | DELETE | ((CURRENT_USER)::text = name) | (3 rows)

执行查询操作

select * from userlist;id |  name  ----+-------- 2  | user02 4  | user01 1  | user01 3  | user03(4 rows)

使用账号user01连接数据库

\c - user01You are now connected to database "memfire" as user "user01".

删除数据

delete from userlist;DELETE 2

创建删除策略后，user01仅能够删除name=‘user01’的数据

使用账号user02连接数据库

\c - user02You are now connected to database "memfire" as user "user02".

执行删除操作

memfire=> delete from userlist;DELETE 0

采用user02无法删除数据，说明创建的策略生效。

样例4. 限制user01用户限制只能更新表userlist中name=‘user01’的数据

切换到数据表owner账户, 创建一个更新策略；

create policy user01_update on userlist for update to user01 using(current_user=name);

执行命令，查看已经创建的策略；

--pg_policies可以查看已经创建的策略select * from pg_policies;

返回结果如下：

 schemaname | tablename |  policyname   | permissive |  roles   |  cmd   |             qual              | with_check ------------+-----------+---------------+------------+----------+--------+-------------------------------+------------ public     | userlist  | user01_select | PERMISSIVE | {user01} | SELECT | ((CURRENT_USER)::text = name) |  public     | userlist  | user01_insert | PERMISSIVE | {user01} | INSERT |                               | true
 public     | userlist  | user01_delete | PERMISSIVE | {user01} | DELETE | ((CURRENT_USER)::text = name) | 
 public     | userlist  | user01_update | PERMISSIVE | {user01} | UPDATE | ((CURRENT_USER)::text = name) | 
(4 rows)

插入三条数据：

insert into userlist values(1,'user01');
insert into userlist values(4,'user01');
insert into userlist values(2,'user05');

执行查询操作：

 select * from userlist;
 id |  name  
----+--------
 2  | user02
 5  | user02
 4  | user01
 1  | user01
 3  | user03

更新所有name="user01“的数据的id更新为2

update userlist set id=2;
UPDATE 2

创建删除策略后，user01仅能够更新name=‘user01’的数据；

更新后，切换到表userlist表owner，执行查询操作

select * from userlist;
 id |  name  
----+--------
 2  | user02
 5  | user02
 2  | user01
 2  | user01
 3  | user03

今天就介绍到这儿，更多详细内容，可以参考行安全性策略。

参考资料

创建策略：http://postgres.cn/docs/9.5/sql-createpolicy.html

修改策略：http://postgres.cn/docs/9.5/sql-alterpolicy.html

删除策略：http://postgres.cn/docs/9.5/sql-droppolicy.html

用于行级安全性的启用 / 禁用：http://postgres.cn/docs/9.5/sql-altertable.html

行安全性策略：http://postgres.cn/docs/9.5/ddl-rowsecurity.html