ALTER ROW LEVEL SECURITY POLICY

    注意事项

    表的所有者或管理员用户才能进行此操作。

    参数说明

    • policy_name

      行访问控制策略名称。

    • 行访问控制策略的表名。

    • new_policy_name

      新的行访问控制策略名称。

    • 行访问控制策略应用的数据库用户，可以指定多个用户，PUBLIC表示应用到所有用户。

    • using_expression

      行访问控制的表达式，返回值为boolean类型。

    2. postgres=# CREATE TABLE all_data(id int, role varchar(100), data varchar(100));
    4. --创建行访问控制策略，当前用户只能查看用户自身的数据
    5. postgres=# CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(role = CURRENT_USER);
    6. postgres=# \d+ all_data
    7.                                Table "public.all_data"
    8.  Column |          Type          | Modifiers | Storage  | Stats target | Description
    9. --------+------------------------+-----------+----------+--------------+-------------
    10.  id     | integer                |           | plain    |              |
    11.  data   | character varying(100) |           | extended |              |
    12. Row Level Security Policies:
    13.     POLICY "all_data_rls"
    14.       USING (((role)::name = "current_user"()))
    15. Has OIDs: no
    16. Location Nodes: ALL DATANODES
    17. Options: orientation=row, compression=no
    19. --修改行访问控制all_data_rls的名称
    20. postgres=# ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;
    22. --修改行访问控制策略影响的用户
    23. postgres=# ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice, bob;
    24. postgres=# \d+ all_data
    25.                                Table "public.all_data"
    26.  Column |          Type          | Modifiers | Storage  | Stats target | Description
    27. --------+------------------------+-----------+----------+--------------+-------------
    28.  id     | integer                |           | plain    |              |
    29.  role   | character varying(100) |           | extended |              |
    30.  data   | character varying(100) |           | extended |              |
    31. Row Level Security Policies:
    32.       TO alice,bob
    33.       USING (((role)::name = "current_user"()))
    34. Has OIDs: no
    36. Options: orientation=row, compression=no, enable_rowsecurity=true
    38. --修改行访问控制策略表达式
    39. postgres=# ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data USING (id > 100 AND role = current_user);
    40. postgres=# \d+ all_data
    41.                                Table "public.all_data"
    42.  Column |          Type          | Modifiers | Storage  | Stats target | Description
    43. --------+------------------------+-----------+----------+--------------+-------------
    44.  id     | integer                |           | plain    |              |
    45.  role   | character varying(100) |           | extended |              |
    46.  data   | character varying(100) |           | extended |              |
    47. Row Level Security Policies:
    48.     POLICY "all_data_new_rls"
    49.       TO alice,bob
    50.       USING (((id > 100) AND ((role)::name = "current_user"())))
    51. Has OIDs: no
    52. Location Nodes: ALL DATANODES

    相关链接