【云圖說】第132期 小云妹帶您快速玩轉RDS實例操作（2）——刪除與退訂
757
2025-04-02
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);
jydb=# create table users(id numeric,name varchar(20));
CREATE TABLE
jydb=# CREATE POLICY user_sel_policy ON users FOR SELECT USING (true);
CREATE POLICY
jydb=# CREATE POLICY user_mod_policy ON users USING (user_name = current_user);
ERROR: column “user_name” does not exist
jydb=#
jydb=# CREATE POLICY user_mod_policy ON users USING (name = current_user);
CREATE POLICY
在SELECT命令中，使用OR組合來使用這兩個策略，最終的效果是可以選擇所有行。在其他命令類型中，只應用第二個策略，因此效果與前面相同。
行安全策略也可以使用alter table命令來禁用。禁用行安全策略不會刪除在表上所定義的任何策略，它們只是被忽略。然后表中的所有行都可見并且能被修改，服從于標準的SQL特權系統。
下面是一個較大的例子，它展示了這種特性如何被用于生產環境。表 passwd模擬了一個 Unix 口令文件：
– 簡單的口令文件例子
CREATE TABLE passwd (
user_name text UNIQUE NOT NULL,
pwhash text,
uid int PRIMARY KEY,
gid int NOT NULL,
real_name text NOT NULL,
home_phone text,
extra_info text,
home_dir text NOT NULL,
shell text NOT NULL
);
執行結果如下:
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
創建用戶:
CREATE ROLE admin; – 管理員
CREATE ROLE bob; – 普通用戶
CREATE ROLE alice; – 普通用戶
執行結果如下:
jydb=# CREATE ROLE admin;
CREATE ROLE
jydb=# CREATE ROLE bob;
CREATE ROLE
jydb=# CREATE ROLE alice;
CREATE ROLE
CREATE ROLE mallor;
– 向表中插入數據
INSERT INTO passwd VALUES(‘admin’,‘xxx’,0,0,‘Admin’,‘111-222-3333’,null,’/root’,’/bin/dash’);
INSERT INTO passwd VALUES(‘bob’,‘xxx’,1,1,‘Bob’,‘123-456-7890’,null,’/home/bob’,’/bin/zsh’);
INSERT INTO passwd VALUES(‘alice’,‘xxx’,2,1,‘Alice’,‘098-765-4321’,null,’/home/alice’,’/bin/zsh’);
執行結果如下:
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
–確保在表上啟用行級安全性
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
執行結果如下:
jydb=# ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
ALTER TABLE
創建策略
– 管理員能看見所有行并且增加任意行
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
執行結果如下:
jydb=# CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
CREATE POLICY
–普通用戶可以看見所有行
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
執行結果如下:
jydb=# CREATE POLICY all_view ON passwd FOR SELECT USING (true);
CREATE POLICY
–普通用戶可以更新它們自己的記錄，但是限制普通用戶可用的 shell
CREATE POLICY user_mod ON passwd FOR UPDATE
USING (current_user = user_name)
WITH CHECK (
current_user = user_name AND
shell IN (’/bin/bash’,’/bin/sh’,’/bin/dash’,’/bin/zsh’,’/bin/tcsh’)
);
執行結果如下:
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有所有普通權限
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
執行結果如下:
jydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
GRANT
–普通用戶只在公共列上得到選擇訪問權限
GRANT SELECT
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
ON passwd TO public;
執行結果如下:
jydb=# GRANT SELECT
jydb-# (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
jydb-# ON passwd TO public;
GRANT
– 允許普通用戶更新特定行
GRANT UPDATE
(pwhash, real_name, home_phone, extra_info, shell)
ON passwd TO public;
執行結果如下:
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的函數來實現。不過要注意這類訪問可能會導致競爭條件，在不小心的情況下這可能會導致信息泄露。作為一個例子，考慮下面的表設計：
–定義權限組
CREATE TABLE groups (group_id int PRIMARY KEY,
group_name text NOT NULL);
INSERT INTO groups VALUES
(1, ‘low’),
(2, ‘medium’),
(5, ‘high’);
GRANT ALL ON groups TO alice; – alice 是管理員
GRANT SELECT ON groups TO public;
執行結果如下:
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)
–定義用戶的權限級別
CREATE TABLE users (user_name text PRIMARY KEY,
group_id int NOT NULL REFERENCES groups);
INSERT INTO users VALUES
(‘alice’, 5),
(‘bob’, 2),
(‘mallory’, 2);
GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;
CREATE ROLE mallory;
執行結果如下:
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)
–保存的信息的表將被保護
CREATE TABLE information (info text,
group_id int NOT NULL REFERENCES groups);
INSERT INTO information VALUES
(‘barely secret’, 1),
(‘slightly secret’, 2),
(‘very secret’, 5);
ALTER TABLE information ENABLE ROW LEVEL SECURITY;
執行結果如下:
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的，這行記錄應該是可見的或可被更新的
CREATE POLICY fp_s ON information FOR SELECT
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
執行結果如下:
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
–我們只依賴于行級安全性來保護信息表
GRANT ALL ON information TO public;
執行結果如下:
jydb=# GRANT ALL ON information TO public;
GRANT
現在假設alice希望更改information表中的"slightly secret"的信息，但是覺得用戶mallory不應該看到該行中的新內容，因此她這樣做:
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = ‘mallory’;
UPDATE information SET info = ‘secret from mallory’ WHERE group_id = 2;
COMMIT;
執行結果如下:
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可能被嵌入到一個安全性定義者函數中）。 還有，在被引用的表上過多并發地使用行共享鎖可能會導致性能問題， 特別是表更新比較頻繁時。另一種解決方案（如果被引用表上的更新 不頻繁就可行）是在更新被引用表時對它取一個排他鎖，這樣就沒有 并發事務能夠檢查舊的行值了。或者我們可以在提交對被引用表的更新 之后、在做依賴于新安全性情況的更改之前等待所有并發事務結束。
postgresql SQL
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章
