Missing Column GRANT, Not RLS, Blocked an Admin Balance Top-Up in Postgres

The post describes two gates. A table or column GRANT decides whether a role may touch a table and its columns at all. The RLS policy runs after that and decides which rows ^[2]. "RLS allowing you is not the same as you being allowed to write," the author wrote ^[11].

In this schema, an admin's request arrives as the authenticated role. The top-up RPC was SECURITY INVOKER, so it wrote users.balance as authenticated too ^[3]. That role had never been granted balance, wallet, member_level, the tier_* fields or annual_spent. The gap was deliberate. Those columns were kept for service_role and a few DEFINER functions so that no logged-in customer could write their own balance or tier ^[4]. The shop runs on stored value, so the balance column holds customers' money ^[18]. is_admin() did clear the admin at the row level, but the write failed at the column check first ^[3].

The author spent a while editing the policy, and nothing helped ^[16]. Postgres returned "permission denied for table users" and did not say which gate had refused ^[5]. "That is the worst part of it," the author wrote ^[5]. The debugging rule I'd take from this is to list the grants on every column the statement writes before touching a policy that looks correct.

The fix leaves the grant layer alone. The author rejected granting the columns to authenticated because it would open balance writes to every logged-in user ^[7]. Instead, fn_admin_topup_balance is SECURITY DEFINER and owned by postgres. Its first statement raises 'not authorized' unless is_admin() is true. Then it calls the original INVOKER function. EXECUTE is revoked from anon and granted to authenticated ^[6]. Deduction got the same wrapper ^[6]. The frontend's admin call site now points at the wrapper, and the customer self-service and service_role paths did not change ^[13].

"The ordering is the whole design," the author wrote ^[12]. A DEFINER function owned by postgres holds the owner's rights from the first line of its body. Any statement placed ahead of the guard would run with them ^[8]. This is careful work. The elevation lives inside one function and only takes effect after the check passes.

The part I would copy is the scanner. The deduction path failed with the same error that afternoon, and the author treated the two failures as one family ^[14]. "Memory won't enumerate that, and neither will grep," the author wrote ^[17]. The scanner queries pg_proc for three conditions: prosecdef = false, a function source that writes one of the privileged users columns, and EXECUTE granted to authenticated ^[9]. It returned four hits. Two were the functions just wrapped, allowlisted with the recorded reason "guarded by fn_admin_*". The other two were cron maintenance functions, fn_expire_bonuses and fn_reconcile_balances ^[10]. So half the hits were functions outside the two known failures ^[19].

The column list belongs to one coffee shop. The query shape carries over to any schema where a low-privilege role can execute an INVOKER function that writes columns the role was never granted. The published excerpt says the two cron functions did not get the DEFINER plus is_admin() treatment, and it ends before saying what they got instead ^[15].