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.
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.
💡
Note that policies are permissive by default, and Postgres combines multiple permissive policies using the OR operator. For example, if one policy is written as USING (true) and another as USING ((SELECT auth.uid()) = user_id), the result would be true, making all rows visible to the role.
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:
SELECT * FROM orders
WHERE user_id = auth.uid() AND EXTRACT(MONTH FROM order_date) = 1;
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.
💡
Note that there is also a user_metadata object, which is not intended to store security or authentication-related data. It can be updated by the Supabase client using an anonymous token, meaning malicious users could impersonate others if it's used for authentication. The app_metadata object, on the other hand, cannot be updated without the service_role key, which must be securely stored and protected from client access.
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 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
💡
The "Access Token Hook" is called by Supabase on each login and when a session is refreshed. The returned claims override the
request.jwt configuration from PostgREST, allowing for customizations. However, this feature can be
misleading. The claims are overridden in the issued JWT and can be used in RLS policies, functions, procedures etc.. However, the token returned by the
supabase.getUser and
supabase.getSession methods won’t contain the modifications made via the hook function. This means you won’t see
tenant_id in supabase user/session objects, even though it’s present in the JWT payload. While this may not be a major issue, misunderstanding how it works can lead to subtle bugs. Supabase does not consider this inconvenience a mistake or bug, as shown in
the related issue.
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.
❗
UPDATE policies have a special case that might be confusing. If the policy isn’t working correctly, it’s likely because it’s missing a USING clause. This clause allows Postgres to determine when to apply the WITH CHECK rule. Without it, the query may return a 200 success status without actually updating anything, as the default behavior is to treat the condition as false. Adding USING (true) will fix the issue.
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.
