PG行级安全策略RLS

由小麦苗 · 2022年3月3日

0 411 1

👉 本文共约11922个字，系统预计阅读时间或需45分钟。

简介

RLS(ROW Level Security) 是PostgreSQL 9.5版本中新增特性，提供了基于行的安全策略，限制数据库用户的查看表数据权限。要知道在9.5版本之前，数据库中对用户的权限管控在表级别，例如：限制某个用户只能查询某个表。采用RLS后，不同的用户访问一个表可以看到不同的数据。

我们在一般的数据库中可能对用户的查询权限只能到表级别，例如限制某个用户只能查询哪些表。PostgreSQL从9.5版本开始支持行级别的权限管控，允许不同的用户在表上查询到不同的数据。

默认的话，表没有任何安全策略限制。这样用户根据 SQL 特权系统具有对表的访问特权，对于查询或更新来说其中所有的行都是平等的。当在一个表上启用行安全性时，所有对该表选择行或者修改行的普通访问都必须被一条行安全性策略所允许（不过，表的拥有者通常不服从行安全性策略）。如果表上不存在安全策略，如果没有配置安全策略，所有的数据查询和更新都会禁止，但是对全表进行操作的命令，比如 TRUNCATE 和 REFERENCES 不受影响。

行安全性策略可以针对特定的命令、角色或者两者。一条策略可以被指定为适用于ALL命令，或者查询（SELECT）、插入（INSERT）、更新（UPDATE）或者删除（DELETE）。同一个策略可分配多个角色，并且通常的角色成员关系和继承规则也适用。但是表的所有者，超级用户 (postgres) 以及加上了 BYPASSRLS 属性的角色不受安全性的限制。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

只有所有者才具有启用禁用行级安全性，给表添加策略的权限。

常见命令：

CREATE POLICY ：创建策略
ALTER POLICY ：修改策略
DROP POLICY ：删除策略
ALTER TABLE ：用于行级安全性的启用禁用。

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR 的关系。

除可以通过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（对宽容性策略，默认的策略类型）或者AND（对限制性策略）组合在一起。这和给定角色拥有它作为成员的所有角色的特权的规则类似。宽容性策略和限制性策略在下文将会进一步讨论。

CREATE POLICY

CREATE POLICY — 为一个表定义一条新的行级安全性策略

大纲

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

CREATE POLICY name ON table_name

[ AS { PERMISSIVE | RESTRICTIVE } ]

[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

描述

CREATE POLICY为一个表定义一条行级安全性策略。注意为了应用已被创建的策略，在表上必须启用行级安全性（使用ALTER TABLE ... ENABLE ROW LEVEL SECURITY）。

一条策略授予权限以选择、插入、更新或者删除匹配相关策略表达式的行。现有的表行会按照USING中指定的表达式进行检查，而将要通过INSERT或UPDATE创建的新行会按照WITH CHECK中指定的表达式进行检查。当USING表达式对于一个给定行返回真时，该行对用户可见，而返回假或空时该行不可见。当一个WITH CHECK 表达式对一行返回真时，该行会被插入或更新，而如果返回假或空时会发生一个错误。

对于INSERT和 UPDATE语句，在BEFORE 触发器被引发后并且在任何数据修改真正发生之前， WITH CHECK表达式会被强制。因此，一个BEFORE ROW触发器可以修改要被插入的数据，从而影响安全性策略检查的结果。WITH CHECK表达式在任何其他约束之前被强制。

策略名称是针对每个表的。因此，一个策略名称可以被用于很多个不同的表并且对于不同的表呈现适合于该表的定义。

策略可以被应用于特定的命令或者特定的角色。除非特别指定，新创建的策略的默认行为是适用于所有命令和角色。多个策略可以应用于单个命令，更多细节请见下文。表 272总结了不同类型的策略如何应用于特定的命令。

对同时具有USING和WITH CHECK 表达式（ALL和UPDATE）的策略，如果没有定义WITH CHECK表达式，那么 USING表达式将被用于决定哪些行可见（普通 USING情况）以及允许哪些新行被增加（ WITH CHECK情况）。

如果为一个表启用了行级安全性但是没有适用的策略存在，将假定为一种 “默认否定”策略，这样任何行都不可见也不可更新。

参数

name
要创建的策略的名称。这必须和该表上已有的任何其他策略名称相区分。
table_name
该策略适用的表的名称（可以被模式限定）。
PERMISSIVE
指定策略被创建为宽容性策略。适用于一个给定查询的所有宽容性策略将被使用布尔“OR”操作符组合在一起。通过创建宽容性策略，管理员可以在能被访问的记录集合中进行增加。策略默认是宽容性的。
RESTRICTIVE
指定策略被创建为限制性策略。适用于一个给定查询的所有限制性策略将被使用布尔“AND”操作符组合在一起。通过创建限制性策略，管理员可以减少能被访问的记录集合，因为每一条记录都必须通过所有的限制性策略。注意在限制性策略真正能发挥作用减少访问之前，需要至少一条宽容性策略来授予对记录的访问。如果只有限制性策略存在，则没有记录能被访问。当宽容性和限制性策略混合存在时，只有当一个记录能通过至少一条宽容性策略以及所有的限制性策略时，该记录才是可访问的。
command
该策略适用的命令。合法的选项是 ALL、SELECT、 INSERT、UPDATE 以及DELETE。 ALL为默认。有关这些策略如何被应用的细节见下文。
role_name
该策略适用的角色。默认是PUBLIC，它将把策略应用到所有的角色。
using_expression
任意的SQL条件表达式（返回 boolean）。该条件表达式不能包含任何聚集或者窗口函数。如果行级安全性被启用，这个表达式将被增加到引用该表的查询。让这个表达式返回真的行将可见。让这个表达式返回假或者空的任何行将对用户不可见（在SELECT中）并且将对修改不可用（在UPDATE或DELETE中）。这类行会被悄悄地禁止而不会报告错误。
check_expression
任意的SQL条件表达式（返回 boolean）。该条件表达式不能包含任何聚集或者窗口函数。如果行级安全性被启用，这个表达式将被用在该表上的 INSERT以及 UPDATE查询中。只有让该表达式计算为真的行才被允许。如果任何被插入的记录或者跟新后的记录导致该表达式计算为假或者空，则会抛出一个错误。注意 check_expression 是根据行的新内容而不是原始内容计算的。

针对每种命令的策略

ALL
为一条策略使用ALL表示它将适用于所有命令，不管命令的类型如何。如果存在一条ALL策略以及更多特定的策略，则ALL策略和那些策略会被应用。此外， ALL策略将同时适用于一个查询的选择端和修改端，如果只定义了一个USING表达式则将该USING表达式用于两种情况。例如，如果发出一个UPDATE，那么 ALL策略将同时影响UPDATE 能更新哪些行（应用USING表达式）以及更新后的行是否被允许加入到表中（如果定义了WITH CHECK 表达式，则应用之；否则使用USING表达式）。如果一条INSERT 或者UPDATE命令尝试增加行到表中，但行没有通过ALL策略的 WITH CHECK表达式，则整个语句将会中断。
SELECT
对一条策略使用SELECT表示它将适用于 SELECT查询，并且无论何时都要求该约束所在的关系上的SELECT权限。其结果是在一次 SELECT查询期间，只有该关系中那些通过了 SELECT策略的记录才将被返回，并且查询要求 SELECT权限，例如 UPDATE也将只能看到那些 SELECT策略允许的行。一条 SELECT策略不能具有WITH CHECK表达式，因为它只适用于正在从关系中检索记录的情况。
INSERT
为一条策略使用INSERT表示它适用于 INSERT命令。没有通过这种策略的正在被插入的行会导致策略违背错误，并且整个INSERT命令将会中止。一条INSERT策略不能具有USING 表达式，因为它只适用于正在向关系增加记录的情况。注意在带有ON CONFLICT DO UPDATE的INSERT中，只有对通过 INSERT路径追加到关系的行才会检查 INSERT策略的WITH CHECK 表达式。
UPDATE
对策略使用UPDATE 意味着它将应用于UPDATE、SELECT FOR UPDATE和SELECT FOR SHARE 命令，还有INSERT 命令的辅助性的ON CONFLICT DO UPDATE 子句。由于UPDATE 需要提取现有的记录并且用新修改的记录代替，故UPDATE 策略接受USING 表达式和WITH CHECK 表达式。USING 表达式决定UPDATE 命令将能看到哪些要对其操作的记录，而WITH CHECK 表达式定义哪些被修改的行允许存回到关系中。任何更新后的值无法通过WITH CHECK表达式的行将会导致错误，并且整个命令将被中止。如果只指定了一个 USING子句，那么该子句将被用于 USING和WITH CHECK两种情况。典型地，UPDATE命令也需要从待更新关系中的列读数据（例如在WHERE子句、RETURNING子句或在SET子句右侧的表达式中）。这种情况下，正被更新的关系上也需要SELECT权限，并且除了UPDATE策略外，也要应用适当的SELECT或者ALL策略。这样，除由UPDATE或ALL策略授权更新行之外，通过SELECT或ALL策略用也必须能访问正被更新的行。当INSERT命令附加了ON CONFLICT DO UPDATE子句时，如果采用UPDATE路径，先以任何UPDATE策略的USING表达式检查待更新的行，然后以WITH CHECK表达式检查新修改的行。但要注意的是，不同于单独的UPDATE命令，如果现有的行不能通过USING表达式检查，则抛出错误（UPDATE路径永不\会静默地避免）。
DELETE
为一条策略使用DELETE表示它适用于 DELETE命令。只有通过这条策略的行才将能被 DELETE命令所看到。如果有的行不能通过该 DELETE策略的USING表达式，则它们可以通过SELECT看到但不能被删除。大多数情况下，DELETE命令也需要从其所删除的关系中的列读取数据（例如在WHERE子句或RETURNING子句中）。这种情况下，在该关系上也需要SELECT权限，并且除了DELETE策略，也要应用适当的SELECT或ALL策略。这样，除由DELETE或ALL策略授权删除行之外，通过SELECT或ALL策略，用户也必须能访问正被删除的行。DELETE策略不能具有WITH CHECK表达式，因为它只适用于正在从关系中删除记录的情况，所以没有新行需要检查。

表 272. 按命令类型应用的策略

多重策略的应用

当多种不同命令类型的策略应用于相同命令（例如SELECT和UPDATE策略应用于UPDATE命令）时，用户就必须同时具有这两种类型的权限（例如从关系中选取行和更新的权限）。这样一种策略类型的表达式就与另一种策略类型的表达式通过使用AND操作符组合在一起。

当相同命令类型的多种策略应用于同一命令时，则必须至少有一个PERMISSIVE策略授权对该关系的访问，所有的RESTRICTIVE策略必须通过。这样，所有的PERMISSIVE策略表达式都用OR来组合，所有的RESTRICTIVE策略表达式都用AND来组合，而结果用AND来组合。如果没有PERMISSIVE策略，则拒绝访问。

要注意的是，出于组合多种策略的目的，将ALL策略视为与所应用的任何其他类型的策略具有相同的类型。

例如，在UPDATE命令中，SELECT和UPDATE两种权限都需要，如果每种类型都有多个适用的策略，则将之以下面的方式组合：

expression from RESTRICTIVE SELECT/ALL policy 1
AND
expression from RESTRICTIVE SELECT/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE SELECT/ALL policy 1
  OR
  expression from PERMISSIVE SELECT/ALL policy 2
  OR
  ...
)
AND
expression from RESTRICTIVE UPDATE/ALL policy 1
AND
expression from RESTRICTIVE UPDATE/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE UPDATE/ALL policy 1
  OR
  expression from PERMISSIVE UPDATE/ALL policy 2
  OR
  ...
)

expression from RESTRICTIVE SELECT/ALL policy 1

AND

expression from RESTRICTIVE SELECT/ALL policy 2

AND

...

AND

(

expression from PERMISSIVE SELECT/ALL policy 1

expression from PERMISSIVE SELECT/ALL policy 2

...

)

AND

expression from RESTRICTIVE UPDATE/ALL policy 1

AND

expression from RESTRICTIVE UPDATE/ALL policy 2

AND

...

AND

(

expression from PERMISSIVE UPDATE/ALL policy 1

expression from PERMISSIVE UPDATE/ALL policy 2

...

)

注解

要为一个表创建或者修改策略，你必须是该表的拥有者。

虽然策略将被应用于针对数据库中表的显式查询上，但当系统正在执行内部引用完整性检查或者验证约束时不会应用它们。这意味着有间接的方法来决定一个给定的值是否存在。一个例子是向一个作为主键或者拥有唯一约束的列中尝试插入重复值。如果插入失败则用户可以推导出该值已经存在（这个例子假设用户被策略允许插入他们看不到的记录）。另一个例子是一个用户被允许向一个引用了其他表的表中插入，然而另一个表是隐藏表。通过用户向引用表中插入值可以判断存在性，成功表示该值存在于被引用表中。为了解决这些问题，应该仔细地制作策略以完全阻止用户插入、删除或者更新那些可能指示他们不能看到的值的记录，或者使用生成的值（例如代理键）来代替具有外部含义的键。

通常，系统将在应用用户查询中出现的条件之前先强制由安全性策略施加的过滤条件，这是为了防止无意中把受保护的数据暴露给可能不可信的用户定义函数。不过，被系统（或者系统管理员）标记为 LEAKPROOF的函数和操作符可以在策略表达式之前被计算，因为它们已经被假定为可信。

因为策略表达式会被直接加到用户查询上，它们将使用运行整个查询的用户的权限运行。因此，使用一条给定策略的用户必须能够访问表达式中引用的任何表或函数，否则在尝试查询启用了行级安全性的表时，他们将简单地收到一条没有权限的错误。不过，这不会改变视图的工作方式。就普通查询和视图来说，权限检查和视图所引用的表的策略将使用视图拥有者的权限以及任何适用于视图拥有者的策略。

在第 5.8 节中可以找到额外的讨论和实际的例子。

兼容性

CREATE POLICY是一种PostgreSQL扩展。

ALTER POLICY

ALTER POLICY — 更改一条行级安全性策略的定义

大纲

ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

描述

ALTER POLICY更改一条现有行级安全性策略的定义。请注意，ALTER POLICY只允许修改策略所应用的角色集合，和要修改的USING和WITH CHECK表达式。要更改策略的其他属性，例如其应用的命令，或者是允许还是限制，则必须删除并重新创建策略。

要使用ALTER POLICY，你必须拥有该策略所适用的表。

在ALTER POLICY的第二种形式中，如果指定了角色列表、 using_expression以及 check_expression，它们会被独立地替换。当这些子句之一被省略时，策略的对应部分不会被更改。

参数

name
要更改的现有策略的名称。
table_name
该策略所在的表的名称（可以被模式限定）。
new_name
该策略的新名称。
role_name
该策略适用的角色。可以一次指定多个角色。要把该策略应用于所有角色，可使用PUBLIC。
using_expression
该策略的USING表达式。详见 CREATE POLICY。
check_expression
该策略的WITH CHECK表达式。详见 CREATE POLICY。

兼容性

ALTER POLICY是一种PostgreSQL扩展。

DROP POLICY

DROP POLICY — 从一个表移除一条行级安全性策略

大纲

DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

1	DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE \| RESTRICT ]

描述

DROP POLICY从该表移除指定的策略。注意如果从一个移除了最后一条策略并且该表的行级安全性仍被 ALTER TABLE启用，则默认的否定策略将被使用。不管该表的策略存在与否，ALTER TABLE ... DISABLE ROW LEVEL SECURITY都可以被用来禁用一个表的行级安全性。

参数

IF EXISTS
如果该策略不存在也不抛出一个错误。这种情况下会发出一个提示。
name
要删除的策略的名称。
table_name
该策略所在的表的名称（可以被模式限定）。
CASCADE RESTRICT
这些关键词不会产生效果，因为它们不依赖于策略。

例子

要在名为my_table上删除策略p1：

DROP POLICY p1 ON my_table;

1	DROP POLICY p1 ON my_table;

兼容性

DROP POLICY是一种PostgreSQL扩展。

示例

示例一

作为一个简单的例子，这里是如何在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);

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

上面的策略隐含地提供了一个与其该约束适用于被一个命令选择的行（这样一个经理不能SELECT、UPDATE或者DELETE属于其他经理的已有行）以及被一个命令修改的行（这样属于其他经理的行不能通过INSERT或者UPDATE创建）。

如果没有指定角色或者使用了特殊的用户名PUBLIC，则该策略适用于系统上所有的用户。要允许所有用户访问users 表中属于他们自己的行，可以使用一条简单的策略：

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

1 2	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);

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 口令文件：

-− 简单的口令文件例子
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
);

CREATE ROLE admin;  -− 管理员
CREATE ROLE bob;    -− 普通用户
CREATE ROLE alice;  -− 普通用户

-− 填充表
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');

-− 确保在表上启用行级安全性
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-− 创建策略
-− 管理员能看见所有行并且增加任意行
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-− 普通用户可以看见所有行
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-− 普通用户可以更新它们自己的记录，但是限制普通用户可用的 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')
  );

-− 允许管理员有所有普通权限
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-− 用户只在公共列上得到选择访问
GRANT SELECT
  (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
  ON passwd TO public;
-− 允许用户更新特定行
GRANT UPDATE
  (pwhash, real_name, home_phone, extra_info, shell)
  ON passwd TO public;

-− 简单的口令文件例子

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

);

CREATE ROLE admin; -− 管理员

CREATE ROLE bob; -− 普通用户

CREATE ROLE alice; -− 普通用户

-− 填充表

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

-− 确保在表上启用行级安全性

ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

-− 创建策略

-− 管理员能看见所有行并且增加任意行

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

-− 普通用户可以看见所有行

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

-− 普通用户可以更新它们自己的记录，但是限制普通用户可用的 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')

);

-− 允许管理员有所有普通权限

GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;

-− 用户只在公共列上得到选择访问

GRANT SELECT

(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)

ON passwd TO public;

-− 允许用户更新特定行

GRANT UPDATE

(pwhash, real_name, home_phone, extra_info, shell)

ON passwd TO public;

对于任意安全性设置来说，重要的是测试并确保系统的行为符合预期。使用上述的例子，下面展示了权限系统工作正确：

-− admin 可以看到所有的行和域
postgres=> set role admin;
SET
postgres=> 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)

-− 测试 Alice 能做什么
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR:  permission denied for relation passwd
postgres=> 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)

postgres=> update passwd set user_name = 'joe';
ERROR:  permission denied for relation passwd
-− Alice 被允许更改她自己的 real_name，但不能改其他的
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR:  new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR:  permission denied for relation passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for relation passwd
-− Alice 可以更改她自己的口令；行级安全性会悄悄地阻止更新其他行
postgres=> update passwd set pwhash = 'abc';
UPDATE 1

-− admin 可以看到所有的行和域

postgres=> set role admin;

SET

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

-− 测试 Alice 能做什么

postgres=> set role alice;

SET

postgres=> table passwd;

ERROR: permission denied for relation passwd

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

postgres=> update passwd set user_name = 'joe';

ERROR: permission denied for relation passwd

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

postgres=> update passwd set real_name = 'Alice Doe';

UPDATE 1

postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';

UPDATE 0

postgres=> update passwd set shell = '/bin/xx';

ERROR: new row violates WITH CHECK OPTION for "passwd"

postgres=> delete from passwd;

ERROR: permission denied for relation passwd

postgres=> insert into passwd (user_name) values ('xxx');

ERROR: permission denied for relation passwd

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

postgres=> update passwd set pwhash = 'abc';

UPDATE 1

目前为止所有构建的策略都是宽容性策略，也就是当多条策略都适用时会被适用“OR”布尔操作符组合在一起。而宽容性策略可以被用来仅允许在预计情况中对行的访问，这比将宽容性策略与限制性策略（记录必须通过这类策略并且它们会被“AND”布尔操作符组合起来）组合在一起更简单。在上面的例子之上，我们增加一条限制性策略要求通过一个本地Unix套接字连接过来的管理员访问passwd表的记录：

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

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

然后，由于这条限制性规则的存在，我们可以看到从网络连接进来的管理员将无法看到任何记录：

=> SELECT current_user;
 current_user 
--------------
 admin
(1 row)

=> select inet_client_addr();
 inet_client_addr 
------------------
 127.0.0.1
(1 row)

=> SELECT current_user;
 current_user 
--------------
 admin
(1 row)

=> TABLE passwd;
 user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)

=> UPDATE passwd set pwhash = NULL;
UPDATE 0

=> SELECT current_user;

current_user

--------------

admin

(1 row)

=> select inet_client_addr();

inet_client_addr

------------------

127.0.0.1

(1 row)

=> SELECT current_user;

current_user

--------------

admin

(1 row)

=> TABLE passwd;

-----------+--------+-----+-----+-----------+------------+------------+----------+-------

(0 rows)

=> UPDATE passwd set pwhash = NULL;

UPDATE 0

参照完整性检查（例如唯一或逐渐约束和外键引用）总是会绕过行级安全性以保证数据完整性得到维护。在开发模式和行级安全性时必须小心避免 “隐通道”通过这类参照完整性检查泄露信息。

在某些环境中确保行安全性没有被应用很重要。例如，在做备份时，如果行安全性悄悄地导致某些行被从备份中忽略掉，这会是灾难性的。在这类情况下，你可以设置row_security配置参数为 off。这本身不会绕过行安全性，它所做的是如果任何结果会被一条策略过滤掉，就会抛出一个错误。然后错误的原因就可以被找到并且修复。

在上面的例子中，策略表达式只考虑了要被访问的行中的当前值。这是最简单并且表现最好的情况。如果可能，最好设计行安全性应用以这种方式工作。如果需要参考其他行或者其他表来做出策略的决定，可以在策略表达式中通过使用子-SELECT或者包含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;

-− 用户的特权级别的定义
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 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;

-− 对于安全性 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));

-− 我们只依赖于行级安全性来保护信息表
GRANT ALL ON information TO public;

-− 特权组的定义

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;

-− 用户的特权级别的定义

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

-− 对于安全性 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));

-− 我们只依赖于行级安全性来保护信息表

GRANT ALL ON information TO public;

现在假设alice希望更改“有一点点秘密” 的信息，但是觉得mallory不应该看到该行中的新内容，因此她这样做：

BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;

BEGIN;

UPDATE users SET group_id = 1 WHERE user_name = 'mallory';

UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;

COMMIT;

这看起来是安全的，没有窗口可供mallory看到 “对 mallory 保密”的字符串。不过，这里有一种竞争条件。如果mallory正在并行地做：

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

1	SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

并且她的事务处于READ COMMITTED模式，她就可能看到 “s对 mallory 保密”的东西。如果她的事务在alice 做完之后就到达信息行，这就会发生。它会阻塞等待 alice的事务提交，然后拜FOR UPDATE子句所赐取得更新后的行内容。不过，对于来自users的隐式SELECT，它不会\取得一个已更新的行，因为子-SELECT没有FOR UPDATE，相反会使用查询开始时取得的快照读取users行。因此，策略表达式会测试mallory的特权级别的旧值并且允许她看到被更新的行。

有多种方法能解决这个问题。一种简单的答案是在行安全性策略中的子-SELECT里使用SELECT ... FOR SHARE。不过，这要求在被引用表（这里是users）上授予 UPDATE特权给受影响的用户，这可能不是我们想要的（但是另一条行安全性策略可能被应用来阻止它们实际使用这个特权，或者子-SELECT可能被嵌入到一个安全性定义者函数中）。还有，在被引用的表上过多并发地使用行共享锁可能会导致性能问题，特别是表更新比较频繁时。另一种解决方案（如果被引用表上的更新不频繁就可行）是在更新被引用表时对它取一个排他锁，这样就没有并发事务能够检查旧的行值了。或者我们可以在提交对被引用表的更新之后、在做依赖于新安全性情况的更改之前等待所有并发事务结束。

示例三

首先创建测试表：

postgres=# create table t1 (id int,name text);
CREATE TABLE
postgres=# insert into t1 values(1,'user01');
INSERT 0 1
postgres=# insert into t1 values(2,'user02');
INSERT 0 1
postgres=# select * from t1;
 id |  name
----+--------
  1 | user01
  2 | user02
(2 rows)

postgres=# create table t1 (id int,name text);

CREATE TABLE

postgres=# insert into t1 values(1,'user01');

INSERT 0 1

postgres=# insert into t1 values(2,'user02');

INSERT 0 1

postgres=# select * from t1;

id | name

----+--------

1 | user01

2 | user02

(2 rows)

新建一个用户：

postgres=# create user user01 ;
CREATE ROLE
postgres=# grant select ON public.t1 to user01;
GRANT

postgres=# create user user01 ;

CREATE ROLE

postgres=# grant select ON public.t1 to user01;

GRANT

例1：限制user01只能查询t1表中name='user01'的数据
创建策略：

postgres=# create policy user01_select_t1 ON t1 for select to user01 using(current_user=name);
CREATE POLICY
postgres=# select * from pg_policies;
 schemaname | tablename |    policyname    | permissive |  roles   |  cmd   |         qual          | with_check
------------+-----------+------------------+------------+----------+--------+-----------------------+------------
 public     | t1        | user01_select_t1 | PERMISSIVE | {user01} | SELECT | (CURRENT_USER = name) |
(1 row)

postgres=# create policy user01_select_t1 ON t1 for select to user01 using(current_user=name);

CREATE POLICY

postgres=# select * from pg_policies;

------------+-----------+------------------+------------+----------+--------+-----------------------+------------

public | t1 | user01_select_t1 | PERMISSIVE | {user01} | SELECT | (CURRENT_USER = name) |

(1 row)

我们需要在表上开启行级安全性，否则该策略默认是disabled

postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |
 name   | text    |           |          |
Policies (row security disabled):
    POLICY "user01_select_t1" FOR SELECT
      TO user01
      USING ((CURRENT_USER = name))

postgres=# \d t1

Table "public.t1"

Column | Type | Collation | Nullable | Default

--------+---------+-----------+----------+---------

id | integer | | |

name | text | | |

Policies (row security disabled):

POLICY "user01_select_t1" FOR SELECT

TO user01

USING ((CURRENT_USER = name))

开启行级安全性：

postgres=# alter table t1 enable row level security ;
ALTER TABLE

1 2	postgres=# alter table t1 enable row level security ; ALTER TABLE

使用user01查看：

postgres=# \c - user01
You are now connected to database "postgres" as user "user01".
postgres=> select * from t1;
 id |  name
----+--------
  1 | user01
(1 row)
postgres=> select count(*) from t1;
 count
-------
     1
(1 row)

postgres=# \c - user01

You are now connected to database "postgres" as user "user01".

postgres=> select * from t1;

id | name

----+--------

1 | user01

(1 row)

postgres=> select count(*) from t1;

count

-------

(1 row)

可以看到，我们插入了2行数据，但是user01只能看到一条数据。

例2：创建新增数据的策略

postgres=#  create policy user01_insert_t1 on t1 for insert to user01 with check( name = current_user);
CREATE POLICY

1 2	postgres=# create policy user01_insert_t1 on t1 for insert to user01 with check( name = current_user); CREATE POLICY

我们创建了该策略，然后使用user01插入试试：

postgres=# \c postgres user01
psql (14.0, server 12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "user01".
postgres=> insert into t1 values(3,'user01');
ERROR:  permission denied for table t1

postgres=# \c postgres user01

psql (14.0, server 12.9)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "postgres" as user "user01".

postgres=> insert into t1 values(3,'user01');

ERROR: permission denied for table t1

没有权限，这是怎么回事？我们前面说了，对于insert需要使用with check进行限制。

重建策略：

postgres=# drop policy user01_insert_t1 ON t1;
DROP POLICY
postgres=# create policy user01_insert_t1 on t1 for insert to user01 with check(true);
CREATE POLICY

postgres=# drop policy user01_insert_t1 ON t1;

DROP POLICY

postgres=# create policy user01_insert_t1 on t1 for insert to user01 with check(true);

CREATE POLICY

再次切换user01用户插入：

postgres=# insert into t1 values(3,'user03');
INSERT 0 1

1 2	postgres=# insert into t1 values(3,'user03'); INSERT 0 1

我们再试试其它用户可不可以插入：

postgres=# \c - user02
psql (14.0, server 12.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "postgres" as user "user02".
postgres=>
postgres=> insert into t1 values(3,'user03');
ERROR:  permission denied for table t1
postgres=>

postgres=# \c - user02

psql (14.0, server 12.9)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

You are now connected to database "postgres" as user "user02".

postgres=>

postgres=> insert into t1 values(3,'user03');

ERROR: permission denied for table t1

postgres=>

可以看到user02没法插入数据了，说明我们的策略生效了。

总结

1、要做到行级安全性的管控，需要在表上开启行级安全性。

ALTER TABLE ... ENABLE ROW LEVEL SECURITY

1	ALTER TABLE ... ENABLE ROW LEVEL SECURITY

2、对于create policy中，需要注意：

using 针对已经存在的记录的校验. 可实施在select, update, delete, ALL上
with check 针对将要新增的记录的校验, 可实施在insert, update, ALL上

参考

http://postgres.cn/docs/13/ddl-rowsecurity.html

打赏赞(1)

标签： PG 行级安全策略RLS

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 5月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

AI (2)
ClickHouse (5)
DM达梦 (9)
Docker (34)
GBase (34)
Greenplum (85)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (3)
MySQL (177)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (9)
Oracle (590)
OS (329)
PostgreSQL (229)
Python (26)
Redis (18)
SQL Server (106)
SQLite (1)
StarRocks (9)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (9)
云 (10)
人大金仓KingBase (7)
其它 (78)
培训考证 (43)
大数据 (4)
存储 (4)
巡检监控 (43)
工具 (65)
数据迁移 (16)
架构篇 (9)
生活 (8)
电脑 (14)
网络 (78)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

PG行级安全策略RLS

简介

CREATE POLICY

大纲

描述

参数

针对每种命令的策略

多重策略的应用

注解

兼容性

ALTER POLICY

大纲

描述

参数

兼容性

DROP POLICY

大纲

描述

参数

例子

兼容性

示例

示例一

示例二

示例三

总结

参考

相关文章

您可能还喜欢...

【DB宝63】项目分享：使用ora2pg迁移Oracle19c到PG13并使用OGG配置实时同步

一个PG主键重复的案例

PG分布式之PGXL安装部署

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复