ALTER ROW LEVEL SECURITY POLICY

Function

ALTER ROW LEVEL SECURITY POLICY modifies the definition of an existing row-level access control policy, including the name of the policy, the user to which the policy applies, and the policy expression.

Precautions

Only the table owner or administrator can execute ALTER ROW LEVEL SECURITY POLICY.

Syntax

1
2
3
4
5

ALTER [ ROW LEVEL SECURITY ] POLICY [ IF EXISTS ] policy_name ON table_name RENAME TO new_policy_name

ALTER [ ROW LEVEL SECURITY ] POLICY policy_name ON table_name
    [ TO { role_name | PUBLIC } [, ...] ]
    [ USING ( using_expression ) ]

Parameter Description

Examples

Create the sample table all_data and users alice and bob.

1
2
3
4
5
6

DROP TABLE IF EXISTS all_data;
CREATE TABLE all_data (a int, b int);
DROP USER IF EXIST alice CASCADE;
DROP USER IF EXIST bob CASCADE;
CREATE USER alice PASSWORD '{password}';
CREATE USER bob PASSWORD '{password}';

Enable the row-level security policy for the all_data table.

1

ALTER TABLE all_data ENABLE ROW LEVEL SECURITY;

Create a row-level security policy named all_data_rls on the all_data table.

1

CREATE ROW LEVEL SECURITY POLICY all_data_rls ON all_data USING(b = CURRENT_USER);

Change the name of the row-level security policy from all_data_rls to all_data_new_rls.

1

ALTER ROW LEVEL SECURITY POLICY all_data_rls ON all_data RENAME TO all_data_new_rls;

Change the users to which the row-level access control policy all_data_new_rls is applied to alice and bob.

1

ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data TO alice, bob;

Modify the filter criteria of the row-level access control policy all_data_new_rls. (alice and bob can access only the data rows whose value in column a is greater than 100 and the value in column b is the same as the current login username.)

1

ALTER ROW LEVEL SECURITY POLICY all_data_new_rls ON all_data USING (a > 100 AND b = current_user);

Helpful Links

CREATE ROW LEVEL SECURITY POLICY and DROP ROW LEVEL SECURITY POLICY