怎么理解PostgreSQL行安全策略

這篇文章主要講解了“怎么理解PostgreSQL行安全策略”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“怎么理解PostgreSQL行安全策略”吧！

行安全策略除可以通過(guò)GRANT使用 SQL 標(biāo)準(zhǔn)的 特權(quán)系統(tǒng)之外，表還可以具有 行安全性策略，它針對(duì)每一個(gè)用戶限制哪些行可以 被普通的查詢返回或者可以被數(shù)據(jù)修改命令插入、更新或刪除。這種 特性也被稱為行級(jí)安全性。默認(rèn)情況下，表不具有 任何策略，這樣用戶根據(jù) SQL 特權(quán)系統(tǒng)具有對(duì)表的訪問(wèn)特權(quán)，對(duì)于 查詢或更新來(lái)說(shuō)其中所有的行都是平等的。

當(dāng)在一個(gè)表上啟用行安全性時(shí)（使用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY），所有對(duì)該表選擇行或者修改行的普通訪問(wèn)都必須被一條 行安全性策略所允許（不過(guò)，表的擁有者通常不服從行安全性策略）。如果 表上不存在策略，將使用一條默認(rèn)的否定策略，即所有的行都不可見(jiàn)或者不能 被修改。應(yīng)用在整個(gè)表上的操作不服從行安全性，例如TRUNCATE和 REFERENCES。

行安全性策略可以針對(duì)特定的命令、角色或者兩者。一條策略可以被指定為 適用于ALL命令，或者SELECT、 INSERT、UPDATE或者DELETE。 可以為一條給定策略分配多個(gè)角色，并且通常的角色成員關(guān)系和繼承規(guī)則也適用。

要指定哪些行根據(jù)一條策略是可見(jiàn)的或者是可修改的，需要一個(gè)返回布爾結(jié)果 的表達(dá)式。對(duì)于每一行，在計(jì)算任何來(lái)自用戶查詢的條件或函數(shù)之前，先會(huì)計(jì) 算這個(gè)表達(dá)式（這條規(guī)則的唯一例外是leakproof函數(shù)， 它們被保證不會(huì)泄露信息，優(yōu)化器可能會(huì)選擇在行安全性檢查之前應(yīng)用這類 函數(shù)）。使該表達(dá)式不返回true的行將不會(huì)被處理。可以指定獨(dú)立的表達(dá)式來(lái)單獨(dú)控制哪些行可見(jiàn)以及哪些行被允許修改。策略表達(dá)式會(huì)作為查詢的一部分運(yùn)行并且?guī)в羞\(yùn)行該查詢的用戶的特權(quán)，但是安全性定義者函數(shù)可以被用來(lái)訪問(wèn)對(duì)調(diào)用用戶不可用的數(shù)據(jù)。

具有BYPASSRLS屬性的超級(jí)用戶和角色在訪問(wèn)一個(gè)表時(shí)總是 可以繞過(guò)行安全性系統(tǒng)。表?yè)碛姓咄ǔＲ材芾@過(guò)行安全性，不過(guò)表?yè)碛姓?可以選擇用ALTER TABLE ... FORCE ROW LEVEL SECURITY來(lái)服從行安全性。

用和禁用行安全性以及向表增加策略是只有表?yè)碛姓呔哂械奶貦?quán)。

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

每一條策略都有名稱并且可以為一個(gè)表定義多條策略。由于策略是表相 關(guān)的，一個(gè)表的每一條策略都必須有一個(gè)唯一的名稱。不同的表可以擁有 相同名稱的策略。

當(dāng)多條策略適用于一個(gè)給定查詢時(shí)，它們會(huì)被用OR 組合起來(lái)，這樣只要任一策略允許，行就是可訪問(wèn)的。這類似于一個(gè)給定 角色具有它所屬的所有角色的特權(quán)的規(guī)則。

作為一個(gè)簡(jiǎn)單的例子，這里是如何在account關(guān)系上 創(chuàng)建一條策略以允許只有managers角色的成員能訪問(wèn)行， 并且只能訪問(wèn)它們賬戶的行：
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);

上述政策隱式地提供了一個(gè)with check子句來(lái)標(biāo)識(shí)它的using子句，因此這個(gè)約束應(yīng)用于通過(guò)命令來(lái)選所擇的行(因此一個(gè)管理者不能select,update或delete現(xiàn)有屬于不同管理都的行)和通過(guò)命令來(lái)修改的行(因此屬于不同管理者的行不能通過(guò)insert或update來(lái)創(chuàng)建)。

如果沒(méi)有指定角色或者指定的用戶名為public，那么這個(gè)熏將應(yīng)用給系統(tǒng)中的所有用戶。 為了允許所有用戶只訪問(wèn)在一個(gè)user表中的行記錄，可以使用如下一個(gè)簡(jiǎn)單和策略：
CREATE POLICY user_policy ON users USING (user_name = current_user);

這與前面的示例類似

要對(duì)添加到表中的行與可見(jiàn)行使用不同的策略，可以組合多個(gè)策略。這對(duì)策略將允許所有的用戶來(lái)查看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組合來(lái)使用這兩個(gè)策略，最終的效果是可以選擇所有行。在其他命令類型中，只應(yīng)用第二個(gè)策略，因此效果與前面相同。

行安全策略也可以使用alter table命令來(lái)禁用。禁用行安全策略不會(huì)刪除在表上所定義的任何策略，它們只是被忽略。然后表中的所有行都可見(jiàn)并且能被修改，服從于標(biāo)準(zhǔn)的SQL特權(quán)系統(tǒng)。

下面是一個(gè)較大的例子，它展示了這種特性如何被用于生產(chǎn)環(huán)境。表 passwd模擬了一個(gè) Unix 口令文件：
-- 簡(jiǎn)單的口令文件例子

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

--創(chuàng)建用戶:

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

-- 向表中插入數(shù)據(jù)

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

--確保在表上啟用行級(jí)安全性

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

創(chuàng)建策略
-- 管理員能看見(jiàn)所有行并且增加任意行

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

--普通用戶可以看見(jiàn)所有行

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有所有普通權(quán)限

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

--普通用戶只在公共列上得到選擇訪問(wèn)權(quán)限

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

對(duì)于任意安全性設(shè)置來(lái)說(shuō)，重要的是測(cè)試并確保系統(tǒng)的行為符合預(yù)期。 使用上述的例子，下面展示了權(quán)限系統(tǒng)工作正確：

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

-- 測(cè)試 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 可以更改她自己的口令；行級(jí)安全性會(huì)悄悄地阻止更新其他行

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

引用完整性檢查（例如唯一或主鍵約束和外鍵引用）總是會(huì)繞過(guò)行級(jí)安全策略以保證數(shù)據(jù)完整性得到維護(hù)。在開(kāi)發(fā)模式和行級(jí)安全策略時(shí)必須小心避免 "隱蔽通道"通過(guò)這類引用完整性檢查泄露信息。

在某些環(huán)境中確保不應(yīng)用行級(jí)安全策略是很重要的。例如，當(dāng)執(zhí)行備份時(shí)，如果行級(jí)安全策略默默地造成備份操作忽略了一些行數(shù)據(jù)這將是災(zāi)難性的。在這種情部鈣，你可以將row_security配置參數(shù)設(shè)置為off。這本身不會(huì)繞過(guò)行級(jí)安全策略，如果任何查詢結(jié)果因?yàn)樾屑?jí)安全策略而被過(guò)濾掉記錄時(shí)就是拋出一個(gè)錯(cuò)誤，然后就可以找到錯(cuò)誤原因并修復(fù)它。

在上面的例子中，策略表達(dá)式只考慮了要被訪問(wèn)或被更新行中的當(dāng)前值。這是最簡(jiǎn)單并且表現(xiàn)最好的情況。如果可能，最好設(shè)計(jì)行級(jí)安全策略應(yīng)用來(lái)以這種方式工作。 如果需要參考其他行或者其他表來(lái)做出策略的決定，可以在策略表達(dá)式中通過(guò)使用子-SELECTs或者包含SELECT的函數(shù)來(lái)實(shí)現(xiàn)。不過(guò)要注意這類訪問(wèn)可能會(huì)導(dǎo)致競(jìng)爭(zhēng)條件，在不小心的情況下這可能會(huì)導(dǎo)致信息泄露。作為一個(gè)例子，考慮下面的表設(shè)計(jì)：
--定義權(quán)限組

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)

--定義用戶的權(quán)限級(jí)別

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)

--保存的信息的表將被保護(hù)

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

--對(duì)于用戶的安全策略group_id大于等于行的group_id的，這行記錄應(yīng)該是可見(jiàn)的或可被更新的

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

--我們只依賴于行級(jí)安全性來(lái)保護(hù)信息表

jydb=# GRANT ALL ON information TO public;
GRANT

現(xiàn)在假設(shè)alice希望更改information表中的"slightly secret"的信息，但是覺(jué)得用戶mallory不應(yīng)該看到該行中的新內(nèi)容，因此她這樣做:

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)

可以看到現(xiàn)有用戶mallory因?yàn)閡sers表中的group_id被修改為1了，所以不能查看表information中的group_id為2的記錄了。

這看起來(lái)是安全的，沒(méi)有窗口可供用戶mallory看到"secret from mallory"字符串。不過(guò)，這里有一種競(jìng)爭(zhēng)條件。如果mallory正在并行地做：
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

并且她的事務(wù)處于READ COMMITTED模式，她就可能看到"secret from mallory"字符串。如果她的事務(wù)在alice做完之后就到達(dá)information表的行記錄，這就會(huì)發(fā)生。它會(huì)阻塞等待alice的事務(wù)提交，然后拜FOR UPDATE子句所賜取得更新后的行內(nèi)容。不過(guò)，對(duì)于來(lái)自u(píng)sers的隱式SELECT，它不會(huì)取得一個(gè)已更新的行， 因?yàn)樽?SELECT沒(méi)有FOR UPDATE，相反會(huì)使用查詢開(kāi)始時(shí)取得的快照讀取users行。因此策略表達(dá)式會(huì)測(cè)試mallory的權(quán)限級(jí)別的舊值并且允許她看到被更新的行。

有多種方法能解決這個(gè)問(wèn)題。一種簡(jiǎn)單的答案是在行安全性策略中的 子-SELECT里使用SELECT ... FOR SHARE。 不過(guò)，這要求在被引用表（這里是users）上授予 UPDATE特權(quán)給受影響的用戶，這可能不是我們想要的（但是另一條行安全性策略可能被應(yīng)用來(lái)阻止它們實(shí)際使用這個(gè)特權(quán)，或者子-SELECT可能被嵌入到一個(gè)安全性定義者函數(shù)中）。 還有，在被引用的表上過(guò)多并發(fā)地使用行共享鎖可能會(huì)導(dǎo)致性能問(wèn)題， 特別是表更新比較頻繁時(shí)。另一種解決方案（如果被引用表上的更新 不頻繁就可行）是在更新被引用表時(shí)對(duì)它取一個(gè)排他鎖，這樣就沒(méi)有 并發(fā)事務(wù)能夠檢查舊的行值了?；蛘呶覀兛梢栽谔峤粚?duì)被引用表的更新 之后、在做依賴于新安全性情況的更改之前等待所有并發(fā)事務(wù)結(jié)束。

感謝各位的閱讀，以上就是“怎么理解PostgreSQL行安全策略”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)怎么理解PostgreSQL行安全策略這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！