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/)

PostgreSQL DBA(102) - pgAdmin(Row Level Security)

相關文章