ALTER ROW LEVEL SECURITY POLICY

    注意事项

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

    参数说明

    • policy_name

      行访问控制策略名称。

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

    • new_policy_name

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

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

    • using_expression

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

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

    相关链接