DROP POLICY

On this page

The DROP POLICY statement removes an existing row-level security (RLS) policy from a table.

Syntax

DROP POLICY [ IF EXISTS ] policy_name ON table_name [ CASCADE | RESTRICT ];

Parameters

Examples

Replace a Policy

This example demonstrates dropping an existing policy before replacing it with a new one that has a different fundamental behavior (e.g., changing from PERMISSIVE to RESTRICTIVE), which cannot be done using ALTER POLICY.

Given an orders table that has row-level security enabled:

CREATE TABLE orders (
    user_id TEXT PRIMARY KEY,
    order_details TEXT,
    is_archived BOOLEAN DEFAULT FALSE NOT NULL
);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

And further given that an initial PERMISSIVE policy was created to grant access to non-archived orders for users in a customer_service role:

CREATE ROLE customer_service;

CREATE POLICY user_orders_policy ON orders
    AS PERMISSIVE -- This is the key aspect we want to change
    FOR ALL
    TO customer_service
    USING ( user_id = current_user AND is_archived = FALSE )
    WITH CHECK ( user_id = current_user AND is_archived = FALSE );

Next, we learn that changing security requirements will mandate a stricter approach going forward. We want to change this policy to act as a fundamental restriction that must be met; this cannot be accomplished with a PERMISSIVE policy. Since ALTER POLICY cannot change AS PERMISSIVE to AS RESTRICTIVE, we must drop the old policy and create a new one.

Next, drop the existing policy:

DROP POLICY IF EXISTS user_orders_policy ON orders;

After dropping the old policy, you can create the new, stricter policy:

CREATE POLICY user_orders_policy ON orders
    AS RESTRICTIVE -- Changed from PERMISSIVE
    FOR ALL
    TO customer_service
    USING ( user_id = current_user AND is_archived = FALSE )
    WITH CHECK ( user_id = current_user AND is_archived = FALSE );

See also

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

Was this helpful?