CREATE ROW LEVEL SECURITY POLICY

功能描述

对表创建行访问控制策略。

当对表创建了行访问控制策略，只有打开该表的行访问控制开关(ALTER TABLE … ENABLE ROW LEVEL SECURITY)，策略才能生效。否则不生效。

当前行访问控制影响数据表的读取操作(SELECT、UPDATE、DELETE)，暂不影响数据表的写入操作(INSERT、MERGE INTO)。表所有者或系统管理员可以在USING子句中创建表达式，在客户端执行数据表读取操作时，数据库后台在查询重写阶段会将满足条件的表达式拼接并应用到执行计划中。针对数据表的每一条元组，当USING表达式返回TRUE时，元组对当前用户可见，当USING表达式返回FALSE或NULL时，元组对当前用户不可见。

行访问控制策略名称是针对表的，同一个数据表上不能有同名的行访问控制策略；对不同的数据表，可以有同名的行访问控制策略。

行访问控制策略可以应用到指定的操作(SELECT、UPDATE、DELETE、ALL)，ALL表示会影响SELECT、UPDATE、DELETE三种操作；定义行访问控制策略时，若未指定受影响的相关操作，默认为ALL。

行访问控制策略可以应用到指定的用户(角色)，也可应用到全部用户(PUBLIC)；定义行访问控制策略时，若未指定受影响的用户，默认为PUBLIC。

注意事项

  • 支持对行存表、行存分区表、列存表、列存分区表、复制表、unlogged表、hash表定义行访问控制策略。
  • 不支持外表、临时表定义行访问控制策略。
  • 不支持对视图定义行访问控制策略。
  • 同一张表上可以创建多个行访问控制策略，一张表最多创建100个行访问控制策略。
  • 系统管理员不受行访问控制影响，可以查看表的全量数据。
  • 通过SQL语句、视图、函数、存储过程查询包含行访问控制策略的表，都会受影响。

语法格式

  1. CREATE [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
  2.      [ AS { PERMISSIVE | RESTRICTIVE } ]
  3.      [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
  4.      [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
  5.      USING ( using_expression )

参数说明

  • policy_name

    行访问控制策略名称，同一个数据表上行访问控制策略名称不能相同。

  • table_name

    行访问控制策略的表名。

  • command

    当前行访问控制影响的SQL操作，可指定操作包括：ALL、SELECT、UPDATE、DELETE。当未指定时，ALL为默认值，涵盖SELECT、UPDATE、DELETE操作。

    当command为SELECT时，SELECT类操作受行访问控制的影响，只能查看到满足条件(using_expression返回值为TRUE)的元组数据，受影响的操作包括SELECT，UPDATE … RETURNING，DELETE … RETURNING。

    当command为UPDATE时，UPDATE类操作受行访问控制的影响，只能更新满足条件(using_expression返回值为TRUE)的元组数据，受影响的操作包括UPDATE, UPDATE … RETURNING, SELECT … FOR UPDATE/SHARE。

    当command为DELETE时，DELETE类操作受行访问控制的影响，只能删除满足条件(using_expression返回值为TRUE)的元组数据，受影响的操作包括DELETE, DELETE … RETURNING。

    行访问控制策略与适配的SQL语法关系参加下表：

    表 1 ROW LEVEL SECURITY策略与适配SQL语法关系

    Command

    SELECT/ALL policy

    UPDATE/ALL policy

    DELETE/ALL policy

    SELECT

    Existing row

    No

    No

    SELECT FOR UPDATE/SHARE

    Existing row

    Existing row

    No

    UPDATE

    No

    Existing row

    No

    UPDATE RETURNING

    Existing row

    Existing row

    No

    DELETE

    No

    No

    Existing row

    DELETE RETURNING

    Existing row

    No

    Existing row

  • role_name

    行访问控制影响的数据库用户。

    当未指定时，PUBLIC为默认值，PUBLIC表示影响所有数据库用户，可以指定多个受影响的数据库用户。

    CREATE ROW LEVEL SECURITY POLICY - 图1 须知：
    系统管理员不受行访问控制特性影响。

  • using_expression

    行访问控制的表达式（返回boolean值）。

    条件表达式中不能包含AGG函数和窗口（WINDOW）函数。在查询重写阶段，如果数据表的行访问控制开关打开，满足条件的表达式会添加到计划树中。针对数据表的每条元组，会进行表达式计算，只有表达式返回值为TRUE时，行数据对用户才可见（SELECT、UPDATE、DELETE）；当表达式返回FALSE时，该元组对当前用户不可见，用户无法通过SELECT语句查看此元组，无法通过UPDATE语句更新此元组，无法通过DELETE语句删除此元组。

示例

  1. --创建用户alice
  2. postgres=# CREATE ROLE alice PASSWORD 'Gauss@123';
  4. --创建用户bob
  5. postgres=# CREATE ROLE bob PASSWORD 'Gauss@123';
  7. --创建数据表all_data
  8. postgres=# CREATE TABLE all_data(id int, role varchar(100), data varchar(100));
  10. --向数据表插入数据
  11. postgres=# INSERT INTO all_data VALUES(1, 'alice', 'alice data');
  12. postgres=# INSERT INTO all_data VALUES(2, 'bob', 'bob data');
  13. postgres=# INSERT INTO all_data VALUES(3, 'peter', 'peter data');
  15. --将表all_data的读取权限赋予alice和bob用户
  16. postgres=# GRANT SELECT ON all_data TO alice, bob;
  18. --打开行访问控制策略开关
  19. postgres=# ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;
  21. --创建行访问控制策略，当前用户只能查看用户自身的数据
  22. postgres=# CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
  24. --查看表all_data相关信息
  25. postgres=# \d+ all_data
  26.                                Table "public.all_data"
  27.  Column |          Type          | Modifiers | Storage  | Stats target | Description
  28. --------+------------------------+-----------+----------+--------------+-------------
  29.  id     | integer                |           | plain    |              |
  30.  role   | character varying(100) |           | extended |              |
  31.  data   | character varying(100) |           | extended |              |
  32. Row Level Security Policies:
  33.     POLICY "all_data_rls"
  34.       USING (((role)::name = "current_user"()))
  35. Has OIDs: no
  36. Location Nodes: ALL DATANODES
  37. Options: orientation=row, compression=no, enable_rowsecurity=true
  39. --当前用户执行SELECT操作
  40. postgres=# SELECT * FROM all_data;
  41.  id | role  |    data
  42. ----+-------+------------
  43.   1 | alice | alice data
  44.   2 | bob   | bob data
  45.   3 | peter | peter data
  46. (3 rows)
  48. postgres=# EXPLAIN(COSTS OFF) SELECT * FROM all_data;
  49.          QUERY PLAN
  50. ----------------------------
  51.  Streaming (type: GATHER)
  52.    Node/s: All dbnodes
  53.    ->  Seq Scan on all_data
  54. (3 rows)
  56. --切换至alice用户执行SELECT操作
  57. postgres=# SELECT * FROM all_data;
  58.  id | role  |    data
  59. ----+-------+------------
  60.   1 | alice | alice data
  61. (1 row)
  63. postgres=# EXPLAIN(COSTS OFF) SELECT * FROM all_data;
  64.                            QUERY PLAN
  65. ----------------------------------------------------------------
  66.  Streaming (type: GATHER)
  67.    Node/s: All dbnodes
  68.    ->  Seq Scan on all_data
  69.          Filter: ((role)::name = 'alice'::name)
  70.  Notice: This query is influenced by row level security feature
  71. (5 rows)

相关链接

DROP ROW LEVEL SECURITY POLICY