How to Handle Multi-Tenant Authorization at the DB Level with Supabase RLS + SECURITY DEFINER

When building a SaaS with Supabase, you eventually hit a wall where RLS policies alone aren't enough. As you try to express conditions like "check whether this user is a member of that organization while also considering per-resource roles," your policy expressions get increasingly tangled, query performance noticeably degrades, and you end up compromising by moving some authorization logic up to the application layer. I did exactly that at first — and honestly thought it was the right approach. It wasn't until I migrated a query with an org-membership-role chain condition to a SECURITY DEFINER function that I saw the number of query executions drop from per-row iteration to exactly once across thousands of rows, and my thinking changed.

Combining PostgreSQL's SECURITY DEFINER functions with RLS policies lets you encapsulate complex authorization logic at the DB level, achieving both security and performance at the same time. This article covers everything from the core concepts to practical multi-tenant SaaS patterns and the security pitfalls that are easy to fall into. If you're already using Supabase in production and have configured RLS at least once, what follows should feel immediately familiar.

Core Concepts

RLS: Where Row-Level Access Control Hits Its Limits

RLS is PostgreSQL's row-level access control mechanism. Once you define policies on a table, PostgreSQL automatically applies those conditions like a WHERE clause on every SELECT, INSERT, UPDATE, and DELETE request. Supabase adopts this as its default security layer, so you can reference the current authenticated user directly in SQL using helpers like auth.uid() and auth.jwt().

For simple conditions, this is perfectly sufficient.

-- Simple RLS policy: only see your own posts
CREATE POLICY "own_posts_only"
ON public.posts
FOR SELECT
USING (user_id = auth.uid());

The problem arises when conditions get complex — for example, "only active members of the organization this project belongs to can view it." You need to join the org_members table, but if that table also has RLS applied, you'll either hit a permission conflict or suffer a significant performance hit, because the structure ends up executing a subquery repeatedly for every row.

SECURITY DEFINER Functions: A Black Box for Encapsulating Authorization Logic

SECURITY DEFINER is one of PostgreSQL's function attributes. The default (SECURITY INVOKER) runs with the permissions of the calling user, but a SECURITY DEFINER function runs with the permissions of the user who defined the function.

Key point: A SECURITY DEFINER function bypasses RLS only when the function owner is the table owner or holds the bypassrls privilege. If the owner is a regular role, RLS still applies. In Supabase's default setup, the function owner is postgres (the table owner), which is why RLS is bypassed for internal queries.

Combining this with RLS policies yields a powerful pattern. The policy itself applies to all users, but the internal queries used for authorization run under the function owner's privileges, bypassing RLS. This allows you to safely query internal tables like org_members.

-- Create a role-check helper function in the private schema
CREATE OR REPLACE FUNCTION private.has_role(role_name text)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid()
      AND role = role_name
  );
$$;
 
-- Call the function from an RLS policy
CREATE POLICY "admin_only"
ON public.sensitive_data
FOR ALL
USING ((SELECT private.has_role('admin')));

The key detail is wrapping the call in (SELECT private.has_role('admin')). If you call the function directly, the PostgreSQL optimizer may execute it once per row; wrapping it in SELECT causes the optimizer to treat it as an initPlan (a pre-evaluated subquery in the execution plan), so it's evaluated exactly once per query. On a table with thousands of rows, the performance difference is substantial.

One caveat: marking a function STABLE lets PostgreSQL cache and reuse its result within the same transaction when called with the same arguments. This is great for performance, but if membership changes mid-transaction, a stale cached result may be used. If membership changes frequently in real time, consider using VOLATILE instead.

Recent Changes: What's Different in Supabase in 2025

Before diving into applying this pattern in earnest, there are a few changes Supabase made this year that are directly relevant.

First, Postgres Event Triggers have been introduced, so RLS is now automatically enabled on any table you create — whether through the dashboard, CLI, or migrations. Previously there was a trap where "tables created via the CLI had RLS off by default," but that gap is now closed.

Second, the Custom Access Token Auth Hook has been enhanced. You can now inject information like org membership, roles, and tenant IDs as custom claims into the JWT before it's issued, and reference them directly in RLS via auth.jwt()->'app_metadata'->>'role'. Honestly, this feature covers more cases than I expected — I've moved all simple global role checks over to it. There are quite a few cases where JWT claims alone are sufficient, without needing SECURITY DEFINER functions.

Third, following reported vulnerability cases where SECURITY DEFINER functions in managed PostgreSQL environments failed to isolate execution context — enabling privilege escalation — the industry has broadly increased security scrutiny of this usage pattern. This doesn't have a direct impact on self-hosted Supabase instances or standard PostgreSQL environments, but regular security audits and use of pg_audit are recommended.

Practical Application

Checking Org Membership: Querying org_members Without RLS Conflicts

This is the most common pattern in SaaS. The condition — "only active members of the organization can view this project" — causes a permission conflict if you join org_members directly, since that table also has RLS applied.

-- 000_init_private_schema.sql
 
-- Create private schema (excluded from api.exposed_schemas, blocking direct external calls)
CREATE SCHEMA IF NOT EXISTS private;
 
-- Step 1: Org membership check function
CREATE OR REPLACE FUNCTION private.is_org_member(org_id uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.org_members
    WHERE organization_id = org_id
      AND user_id = auth.uid()
      AND status = 'active'
  );
$$;
 
-- Explicitly grant execute permission to the authenticated role
GRANT EXECUTE ON FUNCTION private.is_org_member(uuid) TO authenticated;
 
-- Step 2: Apply RLS to the projects table
CREATE POLICY "org_member_access"
ON public.projects
FOR SELECT
USING ((SELECT private.is_org_member(organization_id)));

The private schema is not exposed to the PostgREST API unless it's included in api.exposed_schemas in Supabase's supabase/config.toml. Since only public is exposed by default, placing functions in the private schema is enough to block direct external RPC calls.

Per-Resource Role Separation: Handling admin > editor > viewer Hierarchies

This applies when roles have a hierarchical structure and different operations require different roles.

-- Returns the role level (parameter named p_resource_id to distinguish from column names)
CREATE OR REPLACE FUNCTION private.get_user_role(p_resource_id uuid)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = ''
AS $$
  SELECT role FROM public.resource_permissions
  WHERE resource_id = p_resource_id
    AND user_id = auth.uid()
  LIMIT 1;
$$;
 
-- Any member can read; only admin/editor can write
CREATE POLICY "read_any_member"
ON public.documents
FOR SELECT
USING ((SELECT private.get_user_role(id)) IS NOT NULL);
 
CREATE POLICY "write_requires_editor"
ON public.documents
FOR UPDATE
USING ((SELECT private.get_user_role(id)) IN ('admin', 'editor'));

Prefixing parameter names like p_resource_id may seem minor, but it prevents confusion about whether $1 in WHERE resource_id = $1 refers to a column or an argument — and it makes the code much easier to read for someone seeing it for the first time.

JWT Custom Claims + RLS: Simple Role Checks Made Easy

If you've already injected role claims into the JWT via the Custom Access Token Hook, you can handle things more simply without SECURITY DEFINER functions.

-- When the JWT app_metadata contains a role claim
CREATE POLICY "moderator_access"
ON public.reported_content
FOR ALL
USING (
  (auth.jwt()->'app_metadata'->>'role') = 'moderator'
);

If roles are simple and can be expressed as JWT claims, this approach is much lighter. On the other hand, if roles differ per resource or involve complex conditions like a membership chain, SECURITY DEFINER functions are the better choice. In practice, a hybrid RBAC+ABAC architecture is common — handling global roles via JWT claims and fine-grained per-resource permissions via functions.

Protecting Audit Log Integrity: Allow Inserts Only, Block Updates and Deletes Entirely

Audit logs must never be modified or deleted once written. Restricting writes exclusively through a SECURITY DEFINER function enforces this immutability at the DB level.

-- Revoke direct INSERT permission from the authenticated role (without this line, the pattern is incomplete)
REVOKE INSERT ON public.audit_logs FROM authenticated;
 
-- Inserts are only possible through the SECURITY DEFINER function
CREATE OR REPLACE FUNCTION private.log_action(action text, resource_id uuid)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  INSERT INTO public.audit_logs (user_id, action, resource_id, created_at)
  VALUES (auth.uid(), action, resource_id, now());
$$;
 
GRANT EXECUTE ON FUNCTION private.log_action(text, uuid) TO authenticated;

REVOKE INSERT ... FROM authenticated; is the critical piece. Without it, the premise that "INSERTs are only possible through the function" is not enforced at the DB level. RLS policies alone cannot fully block external paths outside the SECURITY DEFINER function, so the direct permission revocation must be paired with it to complete the pattern.

Pros and Cons

Honestly, when I first introduced this pattern, I fell squarely into item 2 in the drawbacks table below. I didn't really understand what SET search_path = '' meant, so I just left it out — it wasn't until a code review flagged it that I properly understood why it's essential. This is a powerful pattern, but misused, it can actually become a security hole.

Advantages

Drawbacks and Caveats

What is FORCE ROW LEVEL SECURITY? By default, table owners bypass RLS. Applying ALTER TABLE t FORCE ROW LEVEL SECURITY; forces the owner to follow policies without exception. This prevents migration scripts from unintentionally accessing all data.

The Most Common Mistakes in Practice

1. Creating SECURITY DEFINER functions in the public schema. When people start out unfamiliar with the private schema, it's common to just put everything in public. Because Supabase exposes functions in the public schema via PostgREST RPC, any authenticated user can make a direct call like supabase.rpc('is_org_member', { org_id: '...' }) to bypass the role check. The first checkpoint is separating with CREATE SCHEMA private; and confirming that private is not included in api.exposed_schemas in supabase/config.toml.

2. Omitting SET search_path = ''. I didn't understand what this option meant at first and left it out, but without it, an attacker can place malicious functions or tables with the same name in an untrusted schema and trick the SECURITY DEFINER function into executing unintended objects. Running supabase test db will catch this omission as a lint warning.

3. Not knowing that RLS is bypassed in views. In PostgreSQL, views behave as SECURITY DEFINER by default, so access through a view does not go through RLS. Using views as API endpoints without knowing this can inadvertently expose all data. If you're on PostgreSQL 15 or later, it's recommended to explicitly use CREATE VIEW my_view WITH (security_invoker = true) AS .... The supabase test db command's lint 0010 automatically detects this issue.

Closing Thoughts

Combining SECURITY DEFINER functions with RLS lets you implement complex business authorization logic safely and efficiently at the DB level, rather than in the application layer. That said, given how powerful this is, schema isolation and search_path configuration are not optional — they're mandatory.

Four steps you can take right now:

1. Start by understanding your current state. The starting point is a full audit of tables that don't have RLS enabled. Then run the supabase test db command to automatically surface security warnings (lint 0004, 0010, etc.).

2. Audit SECURITY DEFINER views and functions exposed in the public schema. Views flagged by lint 0010 should be converted to security_invoker = true, and authorization-related functions in the public schema should be migrated to the private schema.

3. Create the private schema and move authorization helper functions into it. Create the schema with CREATE SCHEMA private;, then extract complex RLS policy expressions into functions of the form private.check_something(). Always include both SECURITY DEFINER and SET search_path = ''.

4. Evaluate whether JWT Custom Claims can be leveraged. If your roles are relatively simple, injecting role claims into the JWT via the Custom Access Token Auth Hook and referencing them directly in RLS via auth.jwt()->'app_metadata'->>'role' is much lighter. Consider a hybrid architecture: complex per-resource permissions handled by SECURITY DEFINER functions, and global role checks handled by JWT claims.

References

Official Documentation

• Row Level Security | Supabase Docs
• RLS Performance and Best Practices | Supabase Troubleshooting
• Do I need to expose "security definer" Functions in RLS Policies? | Supabase
• Custom Claims & Role-based Access Control (RBAC) | Supabase Docs
• Performance and Security Advisors | Supabase Docs
• PostgreSQL Documentation: Row Security Policies

Further Reading

• Supabase Security Retro: 2025 | Supabase Blog
• Supabase RLS Best Practices: Production Patterns for Secure Multi-Tenant Apps | makerkit.dev
• Enforcing RLS in Supabase: A Deep Dive into Multi-Tenant Architecture | DEV Community
• Optimizing Postgres Row Level Security (RLS) for Performance | Scott Pierce
• Supabase RLS using Functions - Security Definers | Entroblog

Security References

• Preventing Privilege Escalation in PostgreSQL RLS | hoop.dev
• Postgres Row-Level Security Footguns | Bytebase