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…
The article explains a complex issue surrounding PostgreSQL security and row-level security (RLS). The core problem is that even when RLS grants permission, users may still receive a "permission denied" error when trying to modify data.
The author explains that RLS and GRANT permissions are two separate mechanisms. While RLS can allow access to rows, GRANT determines which columns a role can write to. The author had implemented an admin RLS policy that allowed admins to edit customer balances, but the underlying table "users" had column-level GRANT permissions that restricted writes to the columns "balance" and others.
The key revelation was that the admin role was being treated as an "authenticated" role when invoking a PostgreSQL RPC (remote procedure call). This meant the privileged columns were not actually granted to the admin role as they should have been. The solution was to use a SECURITY DEFINER function, which executes as the function owner (postgres) and bypasses the table-level GRANT restrictions. The function first checks the admin identity, then calls the original RPC.
The author fixed two functions (top-up and deduction) using this approach. After deployment, they discovered the same bug family existed in two other cron maintenance functions (expireBonuses and reconcileBalances). These functions were not intended to be called from the frontend and should have their privileges restricted. The author decided to revoke access for authenticated users and leave the service_role as the only entity with permission.
The main takeaways are:
1. When RLS allows access but a permission error occurs, examine column-level GRANT permissions in addition to the row-level policy.
2. For cases where admins need to write data that is otherwise restricted, wrap the function with SECURITY DEFINER and place the admin check as the first statement inside the function body.
3. Carefully audit and tailor privileges to each case - not all functions requiring admin actions need the same level of protection.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
