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

Examples

In this example, we 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, we must 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 has been updated to include an is_archived flag, and the initial policy needs 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
    -- 1. Change the applicable role(s)
    TO customer_service
    -- 2. Update the USING clause to filter out archived orders
    USING ( user_id = current_user AND is_archived = FALSE )
    -- 3. Update the WITH CHECK clause to prevent archiving/modifying archived orders via this policy
    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 matching their user_id and where is_archived is false.
• WITH CHECK ( user_id = current_user AND is_archived = FALSE ): Modifies the constraint for INSERT/UPDATE. Users attempting modifications must satisfy the user_id match, and the resulting row must have is_archived = FALSE. This prevents them from inserting archived orders or updating an order to set is_archived = TRUE via operations governed by this policy.

This ALTER POLICY statement reflects a typical evolution: refining role targeting and adapting 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, DISABLE} ROW LEVEL SECURITY
• ALTER ROLE ... WITH BYPASSRLS
• CREATE ROLE ... WITH BYPASSRLS

Was this helpful?