PostgreSQL 行安全策略-伙伴云

网友投稿 643 2022-05-30

行安全策略

除可以通过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;

-----------±-------±----±----±----------±-------------±-----------±------------±----------

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)

PostgreSQL 行安全策略

jydb=> select * from passwd;

-----------±-------±----±----±----------±-------------±-----------±------------±----------

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;

-----------±----------±-------------±-----------±------------±----------

bob | Bob | 123-456-7890 | | /home/bob | /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

PG高可用之Citus分布式集群搭建及使用

643 2022-05-30

PostgreSQL 行安全策略

PG高可用之Citus分布式集群搭建及使用

PostgreSQL插件之TimescaleDB

Capistrano:自动完成多台服务器上新版本的同步更新，包括数据库的改变

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

什么是在线文档？怎么发在线文档

用在线电子表格，居家办公更轻松

友情链接