Supabase Part 2: Access Control with RLS

This is the second article in a series on Supabase, focusing on solutions to common problems, tips, and recommendations.

In this post, I will share tips and tricks about role based access control, using row level security.

Performance

Using multiple policies for the same query/role combination is not recommended, as each policy adds a small overhead to the query engine. It’s better to write all rules in a single policy per table and operation, if possible.

The key factor for performance in policy queries is avoiding row-level function calls. This isn’t unique to Supabase or even Postgres; all SQL query engines function similarly. SQL engines first parse WHERE predicates and compute constants if possible, then execute the query. Supabase also documented this some time ago to prevent confusion among developers. A more detailed example will clarify this further:

-- get current user's orders from january
-- auth.uid => get current user id from jwt token

-- example #1 (bad)
SELECT * FROM orders
WHERE user_id = auth.uid() AND EXTRACT(MONTH FROM order_date) = 1;

-- example #2 (good)
SELECT * FROM orders
WHERE user_id = (SELECT auth.uid()) AND EXTRACT(MONTH FROM order_date) = 1;

In the first example, the query engine would evaluate the predicate for N rows, checking if the order was placed in January and if it belongs to the current user. The date filter depends on each row’s order_date value, and it needs to check the user_id for ownership. However, there’s a subtle issue with this query: Postgres runs the auth.uid() function for each row it processes, for no good reason. This happens because Postgres can’t infer our intention or what the function does. The purpose needs to be explicit, as shown in example #2. By wrapping it in a SELECT subquery and not referencing any columns from the table, Postgres understands that you want to compute a function that returns a constant value independent from found rows.

When you write user_id = (SELECT auth.uid()) in your RLS policies, the function is called once, and the final query is optimized to user_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx' behind the scenes. For a very simple function, the performance difference can be negligible, especially with small tables. However, in other cases, it could become a disaster.

Custom Claims

When you want to add custom claims to extend your policies from basic user id validations to more complex rules, you can use app_metadata claim in supabase token’s payload along with an access token hook. A typical use case would be storing extra user/tenant attributes in app_metadata to use in policies, as shown below.

-- example access token hook
CREATE OR REPLACE FUNCTION "private"."access_token_hook"("event" jsonb)
  RETURNS "pg_catalog"."jsonb" AS $BODY$
DECLARE
    user_id uuid;
    tenant_id uuid;
    claims jsonb;
BEGIN
    claims := event->'claims';
    user_id := (claims->>'sub')::uuid;
    RAISE LOG 'TOKEN HOOK START: %', user_id;

    -- retrieve active tenant-id (e.g. select from a custom users table)
    tenant_id := select ...;
    claims := jsonb_set(claims, '{app_metadata,tenant_id}', to_jsonb(tenant_id));
    event := jsonb_set(event, '{claims}', claims);    
    RAISE LOG 'TOKEN HOOK END: %', claims;

    return event;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100
  SET "search_path"=''

After enabling this hook from Supabase Studio, request.jwt.claims will include the app_metadata.tenant_id parameter for new sessions. Since accessing it can be tedious, it's a better idea to write helper functions to use in policies.

-- create a helper function to use in RLS policies
CREATE OR REPLACE FUNCTION "auth"."tenant_id"()
  RETURNS "pg_catalog"."uuid" AS $BODY$
DECLARE
    "tenant_id" uuid;
BEGIN
  SELECT coalesce((nullif(current_setting('request.jwt.claims', true), '')::jsonb->'app_metadata'->>'tenant_id'), null)::uuid INTO "tenant_id";
  RETURN "tenant_id";
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100

Debugging

An RLS policy has two parameters for rule definitions: USING for select, delete and update queries and WITH CHECK for insert, and update* queries. While USING simply filters out rows that don’t meet the rules, WITH CHECK returns permission error. Although, one policy can validate all queries, sometimes it is better to write seperate policies by sql command.

For more complex policies, you may want to customize error messages to provide better insights to the client or for debugging purposes. This is particularly useful when the same rules apply to many tables. One solution is to create an error function, as shown below, and call it upon failure.

CREATE OR REPLACE FUNCTION "public"."throw_rls_policy_error"("message" text)
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE
  error_message text;
BEGIN
    error_message := 'Access denied by policy';
    IF message IS NOT NULL THEN 
      error_message := error_message || ': ' || message;
    END IF;
    RAISE EXCEPTION '%', error_message USING ERRCODE = '42501';
    RETURN false;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100
  SET "search_path"=''

CREATE POLICY "RESTRICT_INSERTS_BY_USER" ON "public"."table_name"
FOR INSERT
TO authenticated
WITH CHECK (
    CASE
    WHEN ( (SELECT auth.uid()) = user_id ) THEN true
    ELSE public.throw_rls_policy_error(CONCAT_WS('MY_POLICY_NAME failed because user_id ', user_id::text, ' != ', (SELECT auth.uid()::text)))
    END
);

Throwing errors in USING clauses usually doesn’t make sense because it can break the entire query. However, there is an exception. If you're restricting tables or schemas by tenant, it can be a good idea to throw an error for tenants who are not allowed to access the table. The following policy will restrict all access to the table for the relevant tenant.

Why throw an error instead of just restricting all rows? The answer is simple: it prevents Postgres from executing the query at all, failing immediately and avoiding unnecessary performance overhead.

CREATE POLICY "RESTRICT_TABLE_BY_TENANT" ON "tenant_schema"."table_name"
FOR ALL
TO authenticated
USING ( 
CASE
    WHEN (( SELECT auth.tenant_id() AS tenant_id) = '***') THEN true
    ELSE public.throw_rls_policy_error('RESTRICTED_TO_TENANT'::text)
END )
WITH CHECK ( 
CASE
    WHEN (( SELECT auth.tenant_id() AS tenant_id) = '***') THEN true
    ELSE public.throw_rls_policy_error('RESTRICTED_TO_TENANT'::text)
END );

Maintenance

Managing policies per table can become cumbersome if there are hundreds of tables or the number of tables continuously increases over time. I recommend grouping policies atomically by name and purpose, and writing procedures that create and update each policy. This approach helps automate policies, maintain consistency, avoide hidden bugs and security leaks. For example, there shouldn’t be two policies named “NO_DELETE” on different tables, where one uses USING(false) and another uses USING(status = 1). These should be split into multiple policies based on their specific rules.

Conclusion

Overall, RLS is a very useful feature for controlling access to the database, but it should be used sparingly. Designing a full RBAC system with RLS is not efficient if not impossible, as the overall maintenance and developer experience can become cumbersome when dealing with many tables. Policies should be designed to cover major security restrictions (such as tenant or user ID-based rules), while the rest should be handled in the application using traditional methods (ACLs, RBAC, ABAC etc.).

For complex access control requirements on specific tables or schemas, it’s better to isolate them in a private schema that isn’t exposed to PostgREST. This ensures Supabase clients can’t access them directly. Instead, they should be accessed only through secure channels, such as a custom backend or edge functions, with the access controls in place.

In the next article, I will cover multi-tenancy with schema-per-tenant model and multi-account-per-user support.