5.8. 資料列安全原則

    當資料列安全原則在資料表裡被啓動後（使用 ALTER TABLE … ENABLE ROW LEVEL SECURITY），所有資料表的操作，就必須符合資料列安全原則的設定。（當然，資料表的擁有者並不受限於資料列安全原則。）如果資料表中未設定任何原則，那麼預設就是拒絕存取，意思就是任何資料列都不能被看見或修改。但如果是整個資料表的操作行為，像是 TRUNCATE 或 REFERENCES，就不會受到影響。

    資料列安全原則可以被設定在命令，使用者角色，或兩者兼具。安全原則也可以使用 ALL 的修飾字，或具體指出是 SELECT、INSERT、UPDATE、或 DELETE。多重角色可以共用一個安全原則，一般使用者或承繼的角色都會被同步影響到。

    要設定一個安全原則來指出哪些資料列可見或可修改，是以一個回傳值為布林值的表示式來決定的。這個表示式會計算每一個資料列的結果，在使用者進行任何操作之前。（這個規則唯一的例外是 leakproof 函數，用來確保沒有洩漏資訊；查詢最佳化元件會選押在確任資料列安全原則前就先執行它。）在這個表示式沒有回傳 true 的資料列，都是不能被存取的。獨立的表示式可用於提供資料列專屬的控制，判斷其是否可供讀取或修改。安全原則表示式是查詢的一部份，和使用者執行查詢時一起執行，不過，安全原則表示式是可以存取到該使用者看不到的資料。

    超級使用者因為擁有 BYPASSRLS 的屬性，所以永遠可以通過安全原則檢查而存取資料表。資料表的擁有者一般來說也是可以通過檢查，但可以使用 來強制適用安全原則。

    開啓或關閉資料列安全原則的權限，只屬於資料表擁有者。

    使用 CREATE POLICY 指令來建立安全原則；使用 指令來修改；使用 DROP POLICY 指令來移除原則。要開啓或關閉安全原則的功能，請使用 指令。

    每一個安全原則都有一個名稱，而一個資料表可以定義多個安全原則。安全原則是資料表專屬的，而每一個安全原則在所屬資料表內必須有一個唯一的名稱。不同的資料表下的安全原則可以取相同的名稱。

    先進行一個簡單的範例，我們建立一個安全原則在資料表 account 上，它只允許 managers 的使用者可以存取資料列，並且只能存取他自己帳號的資料列：

    如果沒有指定角色或使用者時，就會以 PUBLIC 替代，也就是所有使用者都適用。要允許所有使用者存取他們自己的資料列的話，就可以簡化指令為：

    2.     USING (user_name = current_user);

    想要定義一個安全原則是有別於可見性權限的話，請使用 WITH CHECK 字句。例如希望讓所有人都可以看到所有資料列，但只能修改自己的資料的話：

    1. CREATE POLICY user_policy ON users
    2.     USING (true)
    3.     WITH CHECK (user_name = current_user);

    資料列安全原則也可以透過 ALTER TABLE 指令關閉。不過關閉資料列安全原則，並不會移除任何已定義的原則，只是單純被忽略而已。然後資料表的所有資料列，就只依標準 SQL 的權限系統，決定查詢及修改的權力。

    下面是一個較複雜的例子，展示這個功能如何被應用於產品等級的環境裡。資料表 passwd 模擬 Unix 的密碼檔：

    對於任何的安全設定，很重要的是，你必須實際測試來確認系統的行為和你預期的相同。使用上面的例子，下面的測試表現出權限設如預期地運作。

    1. -- admin can view all rows and fields
    2. postgres=> set role admin;
    3. SET
    4. postgres=> table passwd;
    5.  user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info | home_dir    |   shell
    6. -----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
    7.  admin     | xxx    |   0 |   0 | Admin     | 111-222-3333 |            | /root       | /bin/dash
    8.  bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
    9.  alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
    10. (3 rows)
    12. -- Test what Alice is able to do
    13. postgres=> set role alice;
    14. SET
    15. postgres=> table passwd;
    16. ERROR:  permission denied for relation passwd
    17. postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
    18.  user_name | real_name |  home_phone  | extra_info | home_dir    |   shell
    19. -----------+-----------+--------------+------------+-------------+-----------
    20.  admin     | Admin     | 111-222-3333 |            | /root       | /bin/dash
    21.  bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
    22.  alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh
    23. (3 rows)
    25. postgres=> update passwd set user_name = 'joe';
    26. ERROR:  permission denied for relation passwd
    27. postgres=> update passwd set real_name = 'Alice Doe';
    28. UPDATE 1
    29. postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
    30. UPDATE 0
    31. postgres=> update passwd set shell = '/bin/xx';
    32. ERROR:  new row violates WITH CHECK OPTION for "passwd"
    33. postgres=> delete from passwd;
    34. ERROR:  permission denied for relation passwd
    35. postgres=> insert into passwd (user_name) values ('xxx');
    36. ERROR:  permission denied for relation passwd
    37. -- Alice can change her own password; RLS silently prevents updating other rows
    38. postgres=> update passwd set pwhash = 'abc';
    39. UPDATE 1

    所有的安全原則，目前來說都是開放安全原則，意思是當有多個安全原則被引用時，它們會以 OR 運算串連其結果。開放安全原則用於只允許在計畫內的環境使用的話，它會比和嚴格安全原則（把安全原則用 AND 串連起來判斷）一起使用來得簡單。基於上面的列子，我們建立一個嚴格安全原則，它限制管理者只能透過 unix socket 連線才能存取 passwd 資料表：

    1. CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
    2.     USING (pg_catalog.inet_client_addr() IS NULL);

    資料一致性的檢查，像是唯一性、主鍵、以及外部鍵參考，都會略過資料列安全原則，以維持資料的一致性。在發展資料庫結構時應該要特別小心，以資料列安全原則避免透過一致性檢查而產生隱藏通道洩露資訊。

    在某些情況，很重要的是要確認安全原則是否被觸發。舉例來說，當進行資料備份流程時，如果安全原則造成某些資料被備份程式忽略了，那可能就會很糟糕。在這種情況下，你可以把 row_security 這個參數設為 off。這並不是避開安全原則，而是在觸發安全原則時，會出現錯誤訊息，使得我們可以發現進而修正原則。

    在上面的例子裡，安全原則表示式只引用了目前資料列中的資料。這是最簡單也是最常見的形式，可以的話，最好以這樣的方式來設計安全原則。如果需要參考其他資料列或資料表來做決定的話，那麼可以使用子查詢或函數的方式達成，也就是包含一個 SELECT 的查詢語句在表示式中。要注意到的是，這種方法可能會造成資料庫內交易競爭（race condition）的狀態，不注意的話也可能產生資訊的洩漏。像這樣的例子，試試下面的資料表設計：

    1. -- definition of privilege groups
    2. CREATE TABLE groups (group_id int PRIMARY KEY,
    3.                      group_name text NOT NULL);
    5. INSERT INTO groups VALUES
    6.   (1, 'low'),
    7.   (2, 'medium'),
    8.   (5, 'high');
    10. GRANT ALL ON groups TO alice;  -- alice is the administrator
    11. GRANT SELECT ON groups TO public;
    13. -- definition of users' privilege levels
    14. CREATE TABLE users (user_name text PRIMARY KEY,
    15.                     group_id int NOT NULL REFERENCES groups);
    17.   ('alice', 5),
    18.   ('bob', 2),
    19.   ('mallory', 2);
    21. GRANT ALL ON users TO alice;
    22. GRANT SELECT ON users TO public;
    24. -- table holding the information to be protected
    25. CREATE TABLE information (info text,
    26.                           group_id int NOT NULL REFERENCES groups);
    28. INSERT INTO information VALUES
    29.   ('barely secret', 1),
    30.   ('slightly secret', 2),
    31.   ('very secret', 5);
    33. ALTER TABLE information ENABLE ROW LEVEL SECURITY;
    35. -- a row should be visible to/updatable by users whose security group_id is
    36. -- greater than or equal to the row's group_id
    37. CREATE POLICY fp_s ON information FOR SELECT
    38.   USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
    39. CREATE POLICY fp_u ON information FOR UPDATE
    40.   USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
    42. -- we rely only on RLS to protect the information table
    43. GRANT ALL ON information TO public;

    現在假設 alice 想要變更＂slightly secret＂的資訊，但決定不讓 mallory 看到新的內容，所以她這麼做：

    1. BEGIN;
    2. UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
    4. COMMIT;

    看起來很安全，因為沒有窗口讓 mallory 可以看到＂secret from mallory＂，然而，這裡就存在了交易競爭的情況。如果 mallory 也在同時做了：

    因為她的交易是屬於 READ COMMITTED 模式，所以她有可能會看到＂secret from mallory＂。這會剛好發生在，她在 alice 的交易完成前一刻。mallory 的指令會暫時擋下 alice 的提交完成，而因為 FOR UPDATE，她會取得更新後的資訊。所以她並沒有從隱含的使用者執行 SELECT 取得資訊，因為子查詢沒有 FOR UPDATE，使得其他使用者可以從快照裡取得資訊。因為安全原則是以舊的 mallory 權限允許她看見該筆資料。

    這個問題有好幾個面向的解決方式。一個簡單的方式就是使用 SELECT … FOR SHARE 在安全原則的子查詢裡。但這樣就必須要讓使用者擁有 UPDATE 的權限，可能不太合適。（但也可以用另一個安全原則來做更多的限制，又或是把子查詢封裝進另一個安全的函數裡）同時，大量的引用查詢也可能造成效能的問題，特別是更新資料的時候。另一個解決辦法，如果參考的資料表並不是很常更新的話，那麼可以在資料表更新時強制鎖定該資料表，確保沒有其他交易能在同時進行查詢，也就不會洩漏任何資訊。或是等待其他所有交易都完成後，才提交更新變更新的安全方案。