Category: CRM and lead management

CRM Admin Role: How to Detect and Fix Authorization Bypasses

Read this guide in Polish

This document describes possible paths to circumvent the role of administrator in CRM systems using Postgres + RLS (row-level security) and external authentication mechanism (e.g. Supabase/Auth). Do not treat the title as proof of cause — the case needs a close review. I focus on technical mechanisms: incorrect RLS policies, incorrectly written SQL functions with SECURITY DEFINITION, acceptance of role with JWT token without validation and excessive public key permissions. The document presents a specific differential diagnosis with a breakdown by reason, detailed corrective steps, post-amendment tests and warnings that were most frequently made. Each step is described to give a specific command to execute on the server or in the RLS policy repository and functions.

How to Recognize Cause

RLS policies exist, but refer to the role from the field jwt.claims.role without additional validation or use only current setting('jwt.claims.role', true) which allows manipulation if JWT is not credible Signed or proxies replace the headers. Check if the policy uses auth.uid() (reliable identification) or checks the row owner id.

SQL functions or procedures performed with SECURITY DEFINER permissions can perform operations as user owner of the function (often superuser or admin). If endpoint calls this function without additional input controls, this may allow the rating to be escalated. Review the function definitions and EXECUTE permissions.

Public or service keys (anon/public) used without restriction allow the client to call endpoints that bypass RLS by calling on the server side or expose RPC. Check which keys have SELECT/INSERT/UPDATE/EXECUTE and whether there are network restrictions and a row-level interception policy.

Step by step solution

1. Collect a list of all RLS policies on affected tables: SELECT polycine, permissive, roles, cmd, qual, with check FROM pg polities WHERE tablename = 'name tablename'; compare qual and with check fields with expected reference conditions to auth.uid() or owner id.

2 . Check JWT: Verify the signature of the tokens used by the application (alg, kid) and compare claims.role with the actual user id. Use the opensl or jwt.io tool (locally) to decode and check the signature and expiration; reject trusts based on proxy headers.

3 . Review all functions/procedures from SECURITY DEFINITION: SELECT n.nspname, p.proname, p.provolate, p.prosecdef FROM pg proc p JOIN pg namespace n ON p.pronamespace=n.oid WHERE p.prosecdef=true; For each function limit EXECUTE permissions and add validation caller IS ROLE('Expected') or additional input checks.

4 . Restore RLS policies where they have been temporarily disabled, and replace terms based on auth.uid() and with check checking ownership id = auth.uid(). Remove policies that allow public/anon to write/execute without conditions.

5 . Close public key access: remove unnecessary public API-keys, limit their permissions and introduce domain/client IP restrictions. Generate new app/service keys after policy and rotation has been specified.

6 . Add audit login: enter the trigger AFTER INSERT/UPDATE/DELETE, which saves actor id (auth.uid()), source (api/function) and pre/after value to play escalations. Go to a separate, protected table.

7 . Run a code review of server and client endpoints: make sure that no endpoint sets the role in the token on the client side and that backend validates roles based on JWT signature and checks permissions before calling SQL function.

How to see if the problem is gone

Try to trigger a susceptible endpoint with a token in which you will convert claim roles to 'admin' locally (but without a valid signature) — the request must be rejected by verification of the JWT signature. Verify response 401/403 instead of 200.

Create an integration test that performs operations requiring admin privileges using a lower clearance account; the operation should return the RLS/permission denied error. Repeat the test after turning off the public/anon keys and after turning the keys.

Try calling the SECURITY DEFINITION function with controlled parameters in the test environment; make sure that the function rejects requests when caller is not the owner/expected service and does not reveal admin data.

What to do

Do not disable Row-Level Security on production as a ‘fast’ fix. Disabling RLS removes the control layer and hides the problem only temporarily; instead, the policy improvements would force auth.uid() and with check.

Do not trust the role sent from the client without checking the JWT signature and key privileges. Accepting the role from the header or body field allows for simple escalation of permissions by any client.

Do not leave SQL function with SECURITY DEFINITION without EXECUTE input control and limitations: this is a typical escalation mechanism. Instead, give minimum permissions and validate caller id and arguments.