ALTER POLICY

On this page

The ALTER POLICY statement changes an existing row-level security (RLS) policy on a table.

Allowed changes to a policy using ALTER POLICY include:

• Rename the policy.
• Change the applicable roles.
• Modify the USING expression.
• Modify the WITH CHECK expression.

Syntax

ALTER POLICY policy_name ON table_name RENAME TO new_policy_name;

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

Parameters

Example

In this example, you will start by only allowing users to see or modify their own rows in an orders table. Then, as the schema is updated due to business requirements, you will refine the policy to take into account the new requirements.

CREATE TABLE orders (user_id TEXT PRIMARY KEY, order_details TEXT);

The original policy on the table was as follows:

CREATE POLICY user_orders_policy ON orders
    FOR ALL
    TO PUBLIC
    USING ( user_id = CURRENT_USER )
    WITH CHECK ( user_id = CURRENT_USER );

However, the orders table schema will be updated to include an is_archived flag, and the initial policy will need refinement.

-- Assume this change was made after the initial policy was created
ALTER TABLE orders ADD COLUMN is_archived BOOLEAN DEFAULT FALSE NOT NULL;
CREATE INDEX idx_orders_user_id_is_archived ON orders(user_id, is_archived); -- For performance

The policy requirements have changed as follows:

1. The policy should now only apply to users belonging to the customer_service role, not PUBLIC.
2. Users in customer_service should only be able to view and modify orders that are not archived (is_archived = FALSE). Archived orders should be invisible/immutable via this policy.

This assumes the customer_service role has been created:

CREATE ROLE customer_service;

This leads to the following ALTER POLICY statement:

ALTER POLICY user_orders_policy ON orders
    TO customer_service
    USING ( user_id = CURRENT_USER AND is_archived = FALSE )
    WITH CHECK ( user_id = CURRENT_USER AND is_archived = FALSE );

The changes to the ALTER POLICY statement can be explained as follows:

• TO customer_service: Restricts the policy's application from all users (PUBLIC) to only those who are members of the customer_service role. Other users will no longer be affected by this specific policy (they would need other applicable policies or RLS would deny access by default).
• USING ( user_id = CURRENT_USER AND is_archived = FALSE ): Modifies the visibility rule. Now, customer_service users can only see rows that match their user_id and are not archived.
• WITH CHECK ( user_id = CURRENT_USER AND is_archived = FALSE ): Modifies the constraint for INSERT/UPDATE. Users attempting modifications must match the user_id, and the resulting row must not be archived. This prevents the user from inserting archived orders or updating an order to set is_archived = TRUE via operations governed by this policy.

The preceding ALTER POLICY statement represents a typical use case: it refines role targeting and adapts the policy logic to accommodate schema changes and evolving access control requirements.

See also

• Row-level security (RLS) overview
• CREATE POLICY
• DROP POLICY
• SHOW POLICIES
• ALTER TABLE ... ENABLE ROW LEVEL SECURITY
• ALTER ROLE ... WITH BYPASSRLS
• CREATE ROLE ... WITH BYPASSRLS

Was this helpful?