PostgreSQL Row-Level Security Explained with Real Error Fixes

Row-Level Security (RLS) allows PostgreSQL to enforce data access rules directly at the database layer. You're implementing Row-Level Security (RLS) in PostgreSQL. Your UPDATE policy looks permissive—you've set both USING (true) and WITH CHECK (true). Everything should work, right?

Then you try to archive a record by flipping an archived flag, and PostgreSQL hits you with:

ERROR: new row violates row-level security policy

Wait, what? Your UPDATE policy literally says "allow everything." How can this fail?

If you've encountered this, you're not alone. This behavior catches even experienced DBAs off guard because it seems contradictory. But it's actually PostgreSQL doing exactly what it should—protecting your data according to a security principle you might not have considered.

A Real-World Scenario

Let's say you're building an order management system. Instead of deleting orders, you want to archive them—a common "soft delete" pattern:

• archived = false - active order (users can see it)
• archived = true - archived order (hidden from users)

Here's your table:

CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    customer   TEXT,
    archived   BOOLEAN DEFAULT false
);

You set up a role and enable RLS:

CREATE ROLE app_user LOGIN PASSWORD 'app_user';
ALTER ROLE app_user NOBYPASSRLS;
ALTER TABLE orders OWNER TO app_user;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Now you create your policies:

-- Hide archived orders
CREATE POLICY orders_read
ON orders
FOR SELECT
USING (archived = false);
-- Allow all updates (or so you think)
CREATE POLICY orders_update
ON orders
FOR UPDATE
USING (true)
WITH CHECK (true);
GRANT SELECT, UPDATE ON orders TO app_user;

You insert a test order:

INSERT INTO orders (order_id, customer)
VALUES (1001, 'Acme Corp');

Now you try to archive it as user ‘app_user’:

UPDATE orders
SET archived = true
WHERE order_id = 1001;

Although the UPDATE policy is permissive, PostgreSQL rejects the operation and throws the error given below:

ERROR:  new row violates row-level security policy for table "orders"

Why This Happens

Here's the critical principle behind PostgreSQL's RLS:

If a user cannot see a row after an operation completes, they shouldn't be able to perform that operation in the first place.

When you execute an UPDATE with a WHERE clause, PostgreSQL performs these steps:

1. Row Selection: Apply SELECT policies to find matching rows
2. Update Execution: Apply UPDATE USING to verify you can modify the row
3. Row Revalidation: Apply UPDATE WITH CHECK and check SELECT policies again

That third step is where things fail. After your update:

• The row now has archived = true
• Your SELECT policy says USING (archived = false)
• PostgreSQL asks: "Can the user still see this row?"
• Answer: No
• Result: Operation rejected

Even though your UPDATE policy says WITH CHECK (true), the SELECT policy still applies to the final row state. This isn't a bug—it's security by design.

Why This Makes Sense

Imagine if PostgreSQL allowed this update. You'd have a situation where:

• User updates a row to make it invisible
• User can no longer see the row
• User can no longer undo their mistake or modify the row further

This violates the fundamental security principle: users shouldn't be able to modify data they can't access.

Solution: Use DELETE, Not UPDATE

The solution is to align with PostgreSQL's security model rather than fight against it. Here's the approach:

Step 1: Create a DELETE Policy

CREATE POLICY orders_delete
ON orders
FOR DELETE
USING (archived = false);

This allows deletion only on visible (non-archived) rows. From RLS's perspective, this makes sense—you can only delete what you can see.

Step 2: Create a Privileged Trigger Function

CREATE FUNCTION archive_order()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog AS
$$
BEGIN
   -- This runs with elevated privileges, bypassing RLS
   UPDATE public.orders
      SET archived = true
      WHERE order_id = OLD.order_id;
   
   -- Return NULL to prevent the actual DELETE
   RETURN NULL;
END;
$$;

The SECURITY DEFINER keyword is crucial here—it makes the function run with the privileges of its creator (typically a superuser or the table owner), bypassing RLS restrictions.

Step 3: Attach the Trigger

CREATE TRIGGER archive_order
BEFORE DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION archive_order();

Now when application code executes:

DELETE FROM orders WHERE order_id = 1001;

Here's what happens:

1. RLS checks: "Can user see this row?" - Yes (archived = false)
2. RLS checks: "Can user delete visible rows?" - Yes (DELETE policy allows it)
3. Trigger fires with elevated privileges
4. Trigger updates archived flag (bypassing RLS)
5. Trigger returns NULL (preventing actual deletion)

Result: The order is archived, not deleted, and RLS is satisfied. The row still exists if you check as a superuser.

The "new row violates row-level security policy" error isn't a PostgreSQL bug or limitation—it's the database correctly enforcing a fundamental security principle: users shouldn't be able to modify data they cannot see. When implementing soft deletes with Row-Level Security, the traditional UPDATE approach breaks this principle by changing a row's visibility as part of the operation itself.

The solution requires a mindset shift: instead of fighting against RLS, work with its security model. Use DELETE operations combined with SECURITY DEFINER triggers to implement soft deletes, and create explicit management functions for operations like archiving and unarchiving. This approach may feel less "direct" than a simple UPDATE statement, but it provides stronger security guarantees, better audit trails, and clearer intent in your application code.