PostgreSQL 行安全策略

行安全策略除可以透過GRANT使用 SQL 標準的 特權系統之外，表還可以具有 行安全性策略，它針對每一個使用者限制哪些行可以 被普通的查詢返回或者可以被資料修改命令插入、更新或刪除。這種 特性也被稱為行級安全性。預設情況下，表不具有 任何策略，這樣使用者根據 SQL 特權系統具有對錶的訪問特權，對於 查詢或更新來說其中所有的行都是平等的。

當在一個表上啟用行安全性時（使用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY），所有對該表選擇行或者修改行的普通訪問都必須被一條 行安全性策略所允許（不過，表的擁有者通常不服從行安全性策略）。如果 表上不存在策略，將使用一條預設的否定策略，即所有的行都不可見或者不能 被修改。應用在整個表上的操作不服從行安全性，例如TRUNCATE和 REFERENCES。

行安全性策略可以針對特定的命令、角色或者兩者。一條策略可以被指定為 適用於ALL命令，或者SELECT、 INSERT、UPDATE或者DELETE。 可以為一條給定策略分配多個角色，並且通常的角色成員關係和繼承規則也適用。

要指定哪些行根據一條策略是可見的或者是可修改的，需要一個返回布林結果 的表示式。對於每一行，在計算任何來自使用者查詢的條件或函式之前，先會計 算這個表示式（這條規則的唯一例外是leakproof函式， 它們被保證不會洩露資訊，最佳化器可能會選擇在行安全性檢查之前應用這類 函式）。使該表示式不返回true的行將不會被處理。可以指定獨立的表示式來單獨控制哪些行可見以及哪些行被允許修改。策略表示式會作為查詢的一部分執行並且帶有執行該查詢的使用者的特權，但是安全性定義者函式可以被用來訪問對呼叫使用者不可用的資料。

具有BYPASSRLS屬性的超級使用者和角色在訪問一個表時總是 可以繞過行安全性系統。表擁有者通常也能繞過行安全性，不過表擁有者 可以選擇用ALTER TABLE ... FORCE ROW LEVEL SECURITY來服從行安全性。

用和禁用行安全性以及向表增加策略是隻有表擁有者具有的特權。

策略的建立可以使用CREATE POLICY命令，策略的修改 可以使用ALTER POLICY命令，而策略的刪除可以使用 DROP POLICY命令。要為一個給定表啟用或者禁用行 安全性，可以使用ALTER TABLE命令。

每一條策略都有名稱並且可以為一個表定義多條策略。由於策略是表相 關的，一個表的每一條策略都必須有一個唯一的名稱。不同的表可以擁有 相同名稱的策略。

當多條策略適用於一個給定查詢時，它們會被用OR 組合起來，這樣只要任一策略允許，行就是可訪問的。這類似於一個給定 角色具有它所屬的所有角色的特權的規則。

作為一個簡單的例子，這裡是如何在account關係上 建立一條策略以允許只有managers角色的成員能訪問行， 並且只能訪問它們賬戶的行：
CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers USING (manager = current_user);

上述政策隱式地提供了一個with check子句來標識它的using子句，因此這個約束應用於透過命令來選所擇的行(因此一個管理者不能select,update或delete現有屬於不同管理都的行)和透過命令來修改的行(因此屬於不同管理者的行不能透過insert或update來建立)。

如果沒有指定角色或者指定的使用者名稱為public，那麼這個燻將應用給系統中的所有使用者。 為了允許所有使用者只訪問在一個user表中的行記錄，可以使用如下一個簡單和策略：
CREATE POLICY user_policy ON users USING (user_name = current_user);

這與前面的示例類似

要對新增到表中的行與可見行使用不同的策略，可以組合多個策略。這對策略將允許所有的使用者來檢視users表中的所有行，但只能修改屬於他們自己的行記錄:
CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);

CREATE POLICY user_mod_policy ON users USING (user_name = current_user);

在SELECT命令中，使用OR組合來使用這兩個策略，最終的效果是可以選擇所有行。在其他命令型別中，只應用第二個策略，因此效果與前面相同。

行安全策略也可以使用alter table命令來禁用。禁用行安全策略不會刪除在表上所定義的任何策略，它們只是被忽略。然後表中的所有行都可見並且能被修改，服從於標準的SQL特權系統。

下面是一個較大的例子，它展示了這種特性如何被用於生產環境。表 passwd模擬了一個 Unix 口令檔案：
-- 簡單的口令檔案例子

jydb=# CREATE TABLE passwd (
jydb(# user_name text UNIQUE NOT NULL,
jydb(# pwhash text,
jydb(# uid int PRIMARY KEY,
jydb(# gid int NOT NULL,
jydb(# real_name text NOT NULL,
jydb(# home_phone text,
jydb(# extra_info text,
jydb(# home_dir text NOT NULL,
jydb(# shell text NOT NULL
jydb(# );
CREATE TABLE

--建立使用者:

jydb=# CREATE ROLE admin;
CREATE ROLE
jydb=# CREATE ROLE bob;
CREATE ROLE
jydb=# CREATE ROLE alice;
CREATE ROLE

-- 向表中插入資料

jydb=# INSERT INTO passwd VALUES('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT 0 1
jydb=# INSERT INTO passwd VALUES('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT 0 1
jydb=# INSERT INTO passwd VALUES('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
INSERT 0 1

--確保在表上啟用行級安全性

jydb=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
ALTER TABLE

建立策略
-- 管理員能看見所有行並且增加任意行

jydb=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
CREATE POLICY

--普通使用者可以看見所有行

jydb=# CREATE POLICY all_view ON passwd FOR SELECT USING (true);
CREATE POLICY

--普通使用者可以更新它們自己的記錄，但是限制普通使用者可用的 shell

jydb=# CREATE POLICY user_mod ON passwd FOR UPDATE
jydb-#   USING (current_user = user_name)
jydb-#   WITH CHECK (
jydb(#     current_user = user_name AND
jydb(#     shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
jydb(#   );
CREATE POLICY

--允許admin有所有普通許可權

jydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
GRANT

--普通使用者只在公共列上得到選擇訪問許可權

jydb=# GRANT SELECT
jydb-# (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
jydb-# ON passwd TO public;
GRANT

-- 允許普通使用者更新特定行

jydb=# GRANT UPDATE
jydb-# (pwhash, real_name, home_phone, extra_info, shell)
jydb-# ON passwd TO public;
GRANT

對於任意安全性設定來說，重要的是測試並確保系統的行為符合預期。 使用上述的例子，下面展示了許可權系統工作正確：

--admin 可以看到所有的行和欄位

jydb=# set role admin;
SET
jydb=> table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)
jydb=> select * from passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)

-- 測試 Alice 能做什麼

jydb=> set role alice;
SET
jydb=>  table passwd;
ERROR:  permission denied for relation passwd
jydb=> select * from passwd;
ERROR:  permission denied for relation passwd
jydb=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell   
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
(3 rows)
jydb=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd

--Alice 被允許更改她自己的 real_name，但不能改其他的

jydb=> update passwd set real_name = 'Alice Doe';
UPDATE 1
jydb=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
jydb=> update passwd set shell = '/bin/xx';
ERROR:  new row violates row-level security policy for table "passwd"
jydb=> delete from passwd;
ERROR:  permission denied for relation passwd
jydb=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd

-- Alice 可以更改她自己的口令；行級安全性會悄悄地阻止更新其他行

jydb=> update passwd set pwhash = 'abc';
UPDATE 1

引用完整性檢查（例如唯一或主鍵約束和外來鍵引用）總是會繞過行級安全策略以保證資料完整性得到維護。在開發模式和行級安全策略時必須小心避免 "隱蔽通道"透過這類引用完整性檢查洩露資訊。

在某些環境中確保不應用行級安全策略是很重要的。例如，當執行備份時，如果行級安全策略默默地造成備份操作忽略了一些行資料這將是災難性的。在這種情部鈣，你可以將row_security配置引數設定為off。這本身不會繞過行級安全策略，如果任何查詢結果因為行級安全策略而被過濾掉記錄時就是丟擲一個錯誤，然後就可以找到錯誤原因並修復它。

在上面的例子中，策略表示式只考慮了要被訪問或被更新行中的當前值。這是最簡單並且表現最好的情況。如果可能，最好設計行級安全策略應用來以這種方式工作。 如果需要參考其他行或者其他表來做出策略的決定，可以在策略表示式中透過使用子-SELECTs或者包含SELECT的函式來實現。不過要注意這類訪問可能會導致競爭條件，在不小心的情況下這可能會導致資訊洩露。作為一個例子，考慮下面的表設計：
--定義許可權組

jydb=> CREATE TABLE groups (group_id int PRIMARY KEY,group_name text NOT NULL);
CREATE TABLE
jydb=> INSERT INTO groups VALUES
jydb->   (1, 'low'),
jydb->   (2, 'medium'),
jydb->   (5, 'high');
INSERT 0 3
jydb=> GRANT ALL ON groups TO alice;
GRANT
jydb=> GRANT SELECT ON groups TO public;
GRANT
jydb=> select * from groups;
 group_id | group_name 
----------+------------
        1 | low
        2 | medium
        5 | high
(3 rows)

--定義使用者的許可權級別

jydb=# CREATE TABLE users (user_name text PRIMARY KEY,
jydb(#                     group_id int NOT NULL REFERENCES groups);
CREATE TABLE
jydb=# INSERT INTO users VALUES
jydb-#   ('alice', 5),
jydb-#   ('bob', 2),
jydb-#   ('mallory', 2);
INSERT 0 3
jydb=# GRANT ALL ON users TO alice;
GRANT
jydb=# GRANT SELECT ON users TO public;
GRANT
jydb=# CREATE ROLE mallory;
CREATE ROLE
jydb=# select * from users;
 user_name | group_id 
-----------+----------
 alice     |        5
 bob       |        2
 mallory   |        2
(3 rows)

--儲存的資訊的表將被保護

jydb=# CREATE TABLE information (info text,
jydb(#                           group_id int NOT NULL REFERENCES groups);
CREATE TABLE
jydb=# INSERT INTO information VALUES
jydb-#   ('barely secret', 1),
jydb-#   ('slightly secret', 2),
jydb-#   ('very secret', 5);
INSERT 0 3
jydb=# ALTER TABLE information ENABLE ROW LEVEL SECURITY;
ALTER TABLE

--對於使用者的安全策略group_id大於等於行的group_id的，這行記錄應該是可見的或可被更新的

jydb=# CREATE POLICY fp_s ON information FOR SELECT
jydb-#   USING (group_id < = (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY
jydb=# CREATE POLICY fp_u ON information FOR UPDATE
jydb-#   USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY

--我們只依賴於行級安全性來保護資訊表

jydb=# GRANT ALL ON information TO public;
GRANT

現在假設alice希望更改information表中的"slightly secret"的資訊，但是覺得使用者mallory不應該看到該行中的新內容，因此她這樣做:

jydb=# BEGIN;
BEGIN
jydb=# UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE 1
jydb=# UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
UPDATE 1
jydb=# COMMIT;
COMMIT
jydb=> select * from users;
 user_name | group_id 
-----------+----------
 alice     |        5
 bob       |        2
 mallory   |        1
(3 rows)
jydb=> select * from information;
        info         | group_id 
---------------------+----------
 barely secret       |        1
 very secret         |        5
 secret from mallory |        2
(3 rows)

--檢查使用者mallory是否可以檢視information表中的group_id=2的記錄

jydb=> set role mallory ;
SET
jydb=> SELECT * FROM information WHERE group_id = 2;
 info | group_id 
------+----------
(0 rows)
jydb=> SELECT * FROM information;
     info      | group_id 
---------------+----------
 barely secret |        1
(1 row)

可以看到現有使用者mallory因為users表中的group_id被修改為1了，所以不能檢視錶information中的group_id為2的記錄了。

這看起來是安全的，沒有視窗可供使用者mallory看到"secret from mallory"字串。不過，這裡有一種競爭條件。如果mallory正在並行地做：
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

並且她的事務處於READ COMMITTED模式，她就可能看到"secret from mallory"字串。如果她的事務在alice做完之後就到達information表的行記錄，這就會發生。它會阻塞等待alice的事務提交，然後拜FOR UPDATE子句所賜取得更新後的行內容。不過，對於來自users的隱式SELECT，它不會取得一個已更新的行， 因為子-SELECT沒有FOR UPDATE，相反會使用查詢開始時取得的快照讀取users行。因此策略表示式會測試mallory的許可權級別的舊值並且允許她看到被更新的行。

有多種方法能解決這個問題。一種簡單的答案是在行安全性策略中的 子-SELECT裡使用SELECT ... FOR SHARE。 不過，這要求在被引用表（這裡是users）上授予 UPDATE特權給受影響的使用者，這可能不是我們想要的（但是另一條行安全性策略可能被應用來阻止它們實際使用這個特權，或者子-SELECT可能被嵌入到一個安全性定義者函式中）。 還有，在被引用的表上過多併發地使用行共享鎖可能會導致效能問題， 特別是表更新比較頻繁時。另一種解決方案（如果被引用表上的更新 不頻繁就可行）是在更新被引用表時對它取一個排他鎖，這樣就沒有 併發事務能夠檢查舊的行值了。或者我們可以在提交對被引用表的更新 之後、在做依賴於新安全性情況的更改之前等待所有併發事務結束。