PostgreSQL提供了Row Level Security（RLS）来控制哪些行可以访问或修改。 **一、简介** 其标准的语法是： ``` [pg12@localhost ~]$ psql -d testdb Timing is on. Expanded display is used automatically. psql (12.0) Type "help" for help. [local]:5432 pg12@testdb=# \help create policy 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 ) ] URL: https://www.postgresql.org/docs/12/sql-createpolicy.html [local]:5432 pg12@testdb=# ``` 其中，USING表示满足using_expression表达式为T的行才能看到或操作，否则这些行将被隐藏（hidden）； WITH CHECK表示在执行INSERT/UPDATE操作时，如新数据不能通过check_expression表达式的校验则被视为非法数据。 **二、注意事项** 1.RLS必须通过ALTER TABLE … ENABLE ROW LEVEL SECURITY显式启用； 2.如启用RLS，那么至少存在一个policy 创建数据表，插入数据，启用RLS ``` [local]:5432 pg12@testdb=# drop table if exists t_rls1; NOTICE: table "t_rls1" does not exist, skipping DROP TABLE Time: 37.363 ms [local]:5432 pg12@testdb=# create table t_rls1(id int,c1 int); CREATE TABLE Time: 137.931 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# insert into t_rls1 values(1,1); INSERT 0 1 Time: 1.820 ms [local]:5432 pg12@testdb=# insert into t_rls1 values(2,2); INSERT 0 1 Time: 0.582 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# ALTER TABLE t_rls1 ENABLE ROW LEVEL SECURITY; ALTER TABLE Time: 1.714 ms [local]:5432 pg12@testdb=# select * from t_rls1; id | c1 ----+---- 1 | 1 2 | 2 (2 rows) ``` 创建新的user，查询数据表，因为不存在policy，因此没有数据返回（但为什么pg12这个用户可以？后续有解释） ``` Time: 23.349 ms [local]:5432 pg12@testdb=# create user testuser with passwod 'test'; ERROR: unrecognized role option "passwod" LINE 1: create user testuser with passwod 'test'; ^ Time: 1.165 ms [local]:5432 pg12@testdb=# create user testuser with password 'test'; CREATE ROLE Time: 15.721 ms [local]:5432 pg12@testdb=# grant all on t_rls1 to testuser; GRANT Time: 8.125 ms [local]:5432 pg12@testdb=# ``` 3.同一个命令，同一种类型，多个policys之间是OR的关系，即某行满足其中一个policy就可以返回（如select命令）；同一个命令不同的命令类型，则多个policys之间的关系是AND，必须所有policys都满足才能返回（如update中存在where子句时）。 4.只有表的owner可以创建policys 5.超级用户或者具有BYPASSRLS权限的用户会跳过RLS检查。这解释先前为什么policy没有，但pg12查询有数据返回但testuser用户没有。 ``` [local]:5432 pg12@testdb=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- pg12 | Superuser, Create role, Create DB, Replication, Bypass RLS | {} testuser | ``` 6.数据表的owner用户默认会跳过RLS检查 ``` [local]:5432 testuser@testdb=> create table t_rls2(id int,c1 int); CREATE TABLE Time: 10.256 ms [local]:5432 testuser@testdb=> [local]:5432 testuser@testdb=> insert into t_rls2 values(1,1); INSERT 0 1 Time: 3.422 ms [local]:5432 testuser@testdb=> insert into t_rls2 values(2,2); INSERT 0 1 Time: 2.580 ms [local]:5432 testuser@testdb=> [local]:5432 testuser@testdb=> ALTER TABLE t_rls2 ENABLE ROW LEVEL SECURITY; ALTER TABLE Time: 2.124 ms [local]:5432 testuser@testdb=> select * from t_rls2; id | c1 ----+---- 1 | 1 2 | 2 (2 rows) Time: 1.127 ms [local]:5432 testuser@testdb=> \d t_rls2 Table "public.t_rls2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | c1 | integer | | | Policies (row security enabled): (none) ``` testuser1&testuser2均为普通用户，但testuser1查询数据有返回，但testuser2没有 ``` [pg12@localhost ~]$ psql -d testdb -U testuser2 Timing is on. Expanded display is used automatically. psql (12.0) Type "help" for help. [local]:5432 testuser2@testdb=> select * from t_rls2; id | c1 ----+---- (0 rows) Time: 3.808 ms [local]:5432 testuser2@testdb=> ``` 可通过ALTER TABLE ... FORCE ROW LEVEL SECURITY命令强制启用 ``` [local]:5432 testuser@testdb=> ALTER TABLE t_rls2 FORCE ROW LEVEL SECURITY; ALTER TABLE Time: 1.967 ms [local]:5432 testuser@testdb=> select * from t_rls2; id | c1 ----+---- (0 rows) Time: 2.369 ms ``` 具体的应用案例可详见参考资料中的链接。 **三、参考资料** PG Conf of US 2019 - Row Level Security [Using “Row Level Security” to make large companies more secure](https://www.cybertec-postgresql.com/en/using-row-level-security-to-make-large-companies-more-secure/)