Great question—this is such a common frustration with PostgreSQL's Row Level Security, since there's no native CREATE POLICY IF NOT EXISTS syntax (as of PostgreSQL 16, anyway). Let's walk through the two most reliable, low-risk solutions to avoid those "policy already exists" errors without leaving your tables unprotected.

1. Wrap Drop + Create in a Transaction (Atomic, No Intermediate Risk) #

Your concern about dropping policies first is totally valid—unless you wrap the drop and create in a transaction. PostgreSQL transactions are fully atomic: either every step completes successfully, or none of them take effect. If your script gets interrupted mid-execution, the entire transaction rolls back, and your original policy stays intact.

Here's how to implement this:

BEGIN;
-- Drop the policy only if it exists
DROP POLICY IF EXISTS "Users can read their own profile" ON user_profiles;
-- Create the updated policy
CREATE POLICY "Users can read their own profile" ON user_profiles
  FOR INSERT WITH CHECK (auth.uid() = user_id);
COMMIT;

This approach is ideal if you need to ensure the policy always matches your desired state (e.g., you're updating the policy logic and want to overwrite any existing version). The key here is that the DROP doesn't take effect until the COMMIT runs—so no window where your table is unprotected.

2. Check for Policy Existence Before Creating (No Drop Required) #

If you don't need to modify existing policies and just want to ensure the policy exists, you can use a PL/pgSQL anonymous block to check the system catalogs first. This skips the creation step entirely if the policy is already present.

Example code:

DO $$
BEGIN
  -- Check if the policy already exists in the pg_policy system table
  IF NOT EXISTS (
    SELECT 1 
    FROM pg_policy
    WHERE polname = 'Users can read their own profile'
      AND relname = 'user_profiles'
      AND schemaname = 'public' -- Replace with your table's schema if needed
  ) THEN
    CREATE POLICY "Users can read their own profile" ON user_profiles
      FOR INSERT WITH CHECK (auth.uid() = user_id);
  END IF;
END $$;

This method avoids any drop operation entirely, so there's zero risk of accidentally removing a working policy. It's perfect for setup scripts where you just want to ensure the policy is created once, not updated.

Why These Count as "Idempotent" #

You mentioned some discussions claim idempotent policy creation exists, and you weren't sure if that's accurate. These two methods are idempotent—you can run them as many times as you want without errors or unintended side effects. The only catch is that there's no single-line CREATE POLICY IF NOT EXISTS syntax like there is for tables, so you have to use these workarounds.

内容的提问来源于stack exchange，提问作者AJP