RLS says yes and Postgres still says permission denied: the 403 family I only understood on the second one

RLS says yes and Postgres still says permission denied: the 403 family I only understood on the second one

Read on: the layer of privileges RLS cannot reach at all · 繁體中文版

An admin tops up a customer's balance in the back office and the screen returns permission denied for table users. My RLS policy explicitly allows admins. The door is open and I can't get through — because the thing blocking me was not the door I was looking at.

The symptom: the admin is locked out of his own system

My coffee shop runs on stored value: customers top up, then draw down. Besides customers topping up themselves, an admin can top up or deduct on a customer's behalf from the POS.

Testing the admin path, I got:

permission denied for table users

First reaction: impossible. My RLS policy has an is_admin() clause. The admin's identity check passes. The door is open — why can't I walk through?

I spent a while fiddling with the policy. Nothing helped. I was looking at the wrong door.

The cause: RLS governs which rows, GRANT governs which columns

The sentence that unlocked it: RLS allowing you is not the same as you being allowed to write.

Postgres permissions are two independent gates:

1. Table / column level GRANT — may this role touch this table, these columns, at all?
2. RLS policy — given that it may, which rows may it touch?

I had been staring at gate 2. Gate 1 was the one stopping me.

My top-up RPC was SECURITY INVOKER, meaning it runs as the caller. In the frontend the admin is the authenticated role. And the privileged columns on users — balance, wallet, member_level, the tier_* fields, annual_spent — were deliberately never granted to authenticated. I had restricted them to service_role and a few DEFINER functions precisely so that no ordinary logged-in user could ever write their own balance or tier.

So the chain was: admin → authenticated → INVOKER RPC → the RPC writes users.balance still as authenticated → no column grant → permission denied.

is_admin() cleared the admin at the row level. Execution never got that far; it died one layer below, at the column level. The error message says permission denied for table users and tells you nothing about which of the two gates rejected you. That is the worst part of it.

The fix: a DEFINER wrapper with the guard on the first line

What I actually needed: admins can write these columns, ordinary users cannot.

Granting the privileged columns to authenticated is the wrong fix — that opens balance writes to every logged-in user. I wanted "a call that has passed an admin check temporarily gains the ability to write privileged columns."

Postgres's tool for that is SECURITY DEFINER: the function runs as its owner, not the caller. My DEFINER functions are owned by postgres, which can write anything.

So, wrap:

create function fn_admin_topup_balance(...)
returns ...
language plpgsql
security definer          -- runs as owner (postgres), can write privileged columns
as $$
begin
  if not is_admin() then                        -- guard FIRST
    raise exception 'not authorized';
  end if;
  perform fn_topup_balance(...);                -- call the original INVOKER function
end;
$$;

revoke all on function fn_admin_topup_balance from anon;
grant execute on function fn_admin_topup_balance to authenticated;

The ordering is the whole design. is_admin() must be the first statement. A DEFINER function is superuser-powered from the moment control enters its body, so the guard has to run before anything touches data. With that in place, authenticated may execute the function, while the body admits only admins. The frontend's admin call site points at the new function; the customer self-service path and the service_role path are untouched.

I fixed two this way: fn_admin_topup_balance and fn_admin_deduct_balance.

The turn: fixing the second one told me it was a family

I fixed top-up. The same afternoon I hit the identical 403 on deduction. Identical fix.

That is when I stopped and thought about something else: if two identical bugs surface on the same day, it isn't two bugs, it's a family. There must be other INVOKER functions writing privileged users columns as authenticated, sitting there untriggered because nobody has walked that path yet.

Memory won't enumerate that, and neither will grep. I wrote a scanner that asks the catalogue directly — pg_proc — with three conditions:

• prosecdef = false (INVOKER, not DEFINER)
• the function source writes one of the privileged users columns
• authenticated has EXECUTE on it

Four hits. Two were the ones I had just fixed (into the allowlist, with the reason recorded: "guarded by fn_admin_*"). The other two were cron maintenance functions: fn_expire_bonuses and fn_reconcile_balances.

Not every member of a family gets the same fix

Those two cron functions did not get the DEFINER + is_admin() treatment, because they should never be called from the frontend at all. They are scheduled tasks; the scheduler runs as superuser and needs no authenticated privileges. The one frontend call site I found for expireBonuses() turned out to be dead code with no callers.

So their fix is the opposite direction: REVOKE from authenticated, leave service_role. Narrow the grant instead of adding a gate. After deploying I queried the live ACLs to confirm only postgres and service_role can reach them.

That is the part worth underlining: a bug family does not imply a single remedy. What admins legitimately need gets a guarded privilege escalation. What nothing outside should ever touch gets its privileges taken away. The scanner's job is finding every member; deciding the remedy is still one judgement per member.

Three things to take away

1. On Supabase/Postgres, when RLS allows it and you still get permission denied, check the column-level GRANT before you touch the policy. They are two independent gates and the error message will not tell you which one rejected you.

2. When you need "admins can write this, users can't", wrap it in SECURITY DEFINER with is_admin() as the first statement. Inside a DEFINER body you are superuser — no unauthorised call may reach the line that touches data.

3. The second identical bug is the signal to write a scanner. Querying a system catalogue like pg_proc converts "are there others?" from an anxiety into a question with an answer. Allowlist the known-good with a written reason; whatever remains is the real work.

I still run that scanner as part of my health check. The next time somebody — including me — writes an INVOKER function that quietly writes a balance, it raises its hand before the deploy instead of after the 403.

---

Originally published on my blog: RLS says yes and Postgres still says permission denied: the 403 family I only understood on the second one

I keep a running index of every pothole I've hit building a real production system solo — symptom on the left, what to grep in your own repo on the right: coffeeshooters.com/potholes

And if your team is shipping AI-written code faster than anyone can read it, that's the thing I do for a living: coffeeshooters.com/code-audit

Comments

[OCTYN]

the second 403 being the signal to query pg_proc is the useful bit here. for the two cron functions, did the scanner catch the dead frontend caller too, or did you find that separately while checking each hit?