Why SECURITY DEFINER Is Postgres's Most Dangerous Setting

Your RLS policies are correct. You tested them. You even wrote pgTAP tests for them. And your database is still leaking tenant data to strangers.

The reason is almost never RLS itself. It's a function sitting a few tables away, tagged SECURITY DEFINER, quietly running with superuser privileges every time anyone calls it. No error. No log entry. It just returns rows it was never supposed to return.

This is what a security definer leak actually looks like in production Postgres and Supabase apps, and it's the single most dangerous configuration option in the entire database engine. Get it wrong and it doesn't just weaken your security, it deletes the concept of tenant isolation entirely for anyone who finds the function.

This article picks up where our guide to silent RLS mistakes left off. If you haven't read that one, SECURITY DEFINER made the list. Here's the full breakdown of why it's there, how attackers actually exploit it, and the audit queries you need to run today.

Table of Contents

1. SECURITY INVOKER vs SECURITY DEFINER
2. Does SECURITY DEFINER Bypass RLS?
3. A Real Supabase Cross-Tenant Leak, Step by Step
4. How search_path Hijacking Turns This Into Remote Code Execution
5. The Heroku rds_superuser Exploit
6. Why AI Coding Assistants Keep Writing This Bug
7. Auditing Your Database for SECURITY DEFINER Leaks
8. Fixing and Locking Down SECURITY DEFINER Functions
9. Quick Answers About SECURITY DEFINER
10. Frequently Asked Questions

SECURITY INVOKER vs SECURITY DEFINER

Simply put, SECURITY INVOKER is the default and it runs a function as the calling user. SECURITY DEFINER flips that: the function runs as whoever owns it, regardless of who calls it.

Per the official PostgreSQL CREATE FUNCTION documentation, this is the exact behavior split. SECURITY INVOKER means the function's SQL runs with the permissions of the user who issued the call. It's the safe default because a user still can't do anything through the function that they couldn't already do directly.

SECURITY DEFINER changes the execution context entirely. The function is executed with the privileges of the role that created it. In a managed platform like Supabase, that owner is often postgres or a platform admin role, which typically holds superuser or BYPASSRLS privileges.

That distinction is the entire vulnerability in one sentence: a normal user calling a SECURITY DEFINER function temporarily borrows the owner's privileges for the duration of that call. If the owner can see every tenant's data, so, briefly, can the function, and so can whoever's calling it.

Does SECURITY DEFINER Bypass RLS?

Yes, and this is worth stating without hedging because it's the question every engineer eventually asks after finding a leak: if a SECURITY DEFINER function is owned by a superuser or a role with the BYPASSRLS attribute, it bypasses Row Level Security completely.

The official Postgres documentation on Row Security Policies confirms that superusers and roles with BYPASSRLS always skip the row security system when querying a table. RLS policies aren't disabled, they're just never consulted for that role's queries.

This is different from the classic "views bypass RLS" problem covered in our anti-patterns article and in most existing coverage of this topic. Views got a real fix in Postgres 15 with WITH (security_invoker = true), which forces a view to respect the querying user's RLS policies instead of the view creator's. SECURITY DEFINER functions have no equivalent switch. There's no security_invoker = true option for a function. The only defense is careful, deliberate design inside the function body, which is exactly where most teams get it wrong.

The takeaway: RLS protects direct table access. It says nothing about what happens inside a function that was explicitly told to ignore it.

A Real Supabase Cross-Tenant Leak, Step by Step

Here's the scenario that keeps showing up in Supabase bug reports, and it's a lot more mundane than a sophisticated attack. A team builds a billing dashboard and needs to calculate aggregate metrics across several tables. RLS makes that awkward because the calculation needs to touch data the calling user technically shouldn't see row by row, so a developer reaches for SECURITY DEFINER to skip the friction.

-- The vulnerable function
CREATE OR REPLACE FUNCTION get_billing_metrics(target_tenant_id uuid)
RETURNS TABLE (total_revenue numeric, invoice_count bigint)
SECURITY DEFINER
LANGUAGE sql
AS $$
  SELECT sum(amount), count(*)
  FROM invoices
  WHERE tenant_id = target_tenant_id;
$$;

Nothing here looks obviously wrong at first glance. The function takes a tenant_id parameter, so it looks scoped. But look at what's actually missing: there's no check that the calling user belongs to target_tenant_id. Because the function is SECURITY DEFINER and owned by a privileged role, it doesn't need RLS to permit the query, it bypasses RLS entirely and will happily return any tenant's invoices to anyone who calls it.

This is not a hypothetical. A real GitHub issue against a production Supabase application described exactly this pattern: eight SECURITY DEFINER functions accepted an org_id parameter but never verified that the calling user's auth.uid() actually belonged to that organization. Any authenticated user could call get_billing_metrics() with a competitor's tenant ID, straight through the public API, and walk away with their revenue numbers.

The fix is one line, and it's the difference between a safe RPC and a cross-tenant data breach:

-- The corrected function
CREATE OR REPLACE FUNCTION get_billing_metrics(target_tenant_id uuid)
RETURNS TABLE (total_revenue numeric, invoice_count bigint)
SECURITY DEFINER
SET search_path = pg_catalog, public
LANGUAGE sql
AS $$
  SELECT sum(amount), count(*)
  FROM invoices
  WHERE tenant_id = target_tenant_id
    -- This is the check that was missing.
    -- Confirm the caller actually belongs to this tenant
    -- before the DEFINER privileges are allowed to touch the data.
    AND EXISTS (
      SELECT 1 FROM tenant_members
      WHERE tenant_id = target_tenant_id
        AND user_id = auth.uid()
    );
$$;

REVOKE EXECUTE ON FUNCTION get_billing_metrics(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION get_billing_metrics(uuid) TO authenticated;

Summary: a SECURITY DEFINER RPC is only as safe as the manual tenant check written inside it. RLS won't save you here because RLS was never consulted.

How search_path Hijacking Turns This Into Remote Code Execution

The tenant-check bypass above is bad enough on its own, but there's a second, more severe failure mode buried in how Postgres resolves object names. Simply put, if a SECURITY DEFINER function calls another function or object without fully qualifying its schema, an attacker can trick it into running attacker-controlled code with the owner's elevated privileges.

Here's why it survives so many code reviews. Postgres resolves unqualified names like some_function() by searching through the schemas listed in the active search_path, in order, until it finds a match. A SECURITY DEFINER function inherits the caller's session-level search_path unless the function explicitly pins its own. That's the gap.

The Exact Attack, Step by Step

1. An attacker inspects a SECURITY DEFINER function and finds it calls an unqualified system function, for example event_trigger() instead of pg_catalog.event_trigger().
2. The attacker creates their own function with the identical name and signature inside a schema they control, typically public, since most roles can create objects there by default.
3. The attacker alters their own session's search_path to put their controlled schema ahead of pg_catalog.
4. The attacker calls the original SECURITY DEFINER function.
5. When that function tries to resolve the unqualified name, Postgres finds the attacker's malicious version first and executes it, under the DEFINER's elevated privileges.

This is formally classified as CWE-426, Untrusted Search Path, and it's a real enough concern that a dedicated static-analysis rule, no-unsafe-search-path, now exists specifically to catch raw, unqualified schema references in SECURITY DEFINER bodies before they ship.

The fix is the same one-liner used in the billing example above:

SET search_path = pg_catalog, public

Pinning the search path means Postgres always checks the trusted system schema first, no matter what the calling session's search_path has been altered to. Some teams go further and use SET LOCAL search_path = pg_catalog, pg_temp; inside the function body, which scopes the change strictly to the current transaction and reverts automatically afterward. Either approach closes the door. Leaving it open does not throw an error, it just silently resolves to whatever object the attacker planted.

The Heroku rds_superuser Exploit

If search_path hijacking sounds theoretical, it isn't. It has been used to take over entire multi-tenant clusters in production.

Security researcher Alistair documented a real exploit against Heroku Postgres that used exactly this technique. The _heroku schema on Heroku's managed databases contained several SECURITY DEFINER functions owned by the highly privileged heroku_admin role. One of them, _heroku.validate_extension(), called pg_event_trigger_ddl_commands() without schema-qualifying it, and defined no explicit search path.

The attack followed the same five steps outlined above: create a malicious function named identically to the unqualified call, plant it in public, hijack the session's search_path to prioritize public over the system schema, then trigger execution by installing a benign extension. The malicious payload ran with heroku_admin privileges and granted the attacker the AWS-managed rds_superuser role, at which point they had full control over the entire multi-tenant cluster, not just one tenant's data.

Every layer of that exploit trace back to the two mistakes covered in this article: a SECURITY DEFINER function with an unqualified system call, and no pinned search_path. It's the exact same bug pattern that shows up in Supabase RPCs, just running on infrastructure with a much bigger blast radius.

Why AI Coding Assistants Keep Writing This Bug

There's a modern wrinkle to this problem that didn't exist a few years ago. AI coding assistants like Copilot and Cursor have a habit of reaching for SECURITY DEFINER the moment they hit a permission error while generating a Postgres function, because it's the fastest way to make the error go away.

The problem is the assistant almost never pins the search_path or adds a tenant ownership check when it does this. It solves the immediate compile error and introduces a much bigger one. Developers on r/Supabase have described this exact pattern: an AI tool writes a Postgres function, tags it SECURITY DEFINER to sidestep a permissions issue, and the function ends up running with the privileges of its creator, usually a superuser, instead of the user actually calling it.

That's not a knock on any specific tool. It's a reflection of what these models are optimizing for in the moment: making the error disappear, not modeling your tenant isolation boundaries. If your team uses AI-generated SQL anywhere in the RPC layer, treat every SECURITY DEFINER tag it produces as suspect until a human confirms the search_path is pinned and a tenant check exists. This is precisely why the audit scripts in the next section need to run as a habit, not a one-time exercise.

Auditing Your Database for SECURITY DEFINER Leaks

You don't need to manually review every function in your schema. Postgres tracks this information in its system catalogs, and a handful of queries will surface every risky function in your database in seconds.

Layer 1: Find Every SECURITY DEFINER Function

The pg_proc catalog stores a boolean column, prosecdef, that's set to true for every SECURITY DEFINER function in the database.

-- List every SECURITY DEFINER function and whether it has a pinned search_path
SELECT
  n.nspname AS schema_name,
  p.proname AS function_name,
  pg_get_userbyid(p.proowner) AS owned_by,
  p.proconfig AS config_settings,
  CASE
    WHEN p.proconfig::text LIKE '%search_path%' THEN 'search_path is pinned'
    ELSE 'DANGER: no pinned search_path'
  END AS search_path_status
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef = true
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY search_path_status DESC, schema_name, function_name;

Anything that comes back with DANGER: no pinned search_path is a candidate for the exact hijacking technique described earlier in this article. Fix those first.

Layer 2: Check Who Can Actually Execute Each Function

A pinned search path doesn't help if any authenticated user can still call the function and pass in someone else's tenant ID. This query checks execute privileges directly against the role that matters, rather than trusting that a REVOKE statement in a migration file actually took effect.

-- Check whether the 'authenticated' role can execute each SECURITY DEFINER function
SELECT
  n.nspname AS schema_name,
  p.proname AS function_name,
  has_function_privilege('authenticated', p.oid, 'EXECUTE') AS authenticated_can_execute,
  has_function_privilege('anon', p.oid, 'EXECUTE') AS anon_can_execute
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef = true
  AND n.nspname = 'public'
ORDER BY anon_can_execute DESC, authenticated_can_execute DESC;

Any row where anon_can_execute returns true on a function that touches tenant data is an immediate red flag. It means Postgres's default behavior, granting EXECUTE on new functions to PUBLIC, was never explicitly revoked.

A GitHub discussion on Supabase's own agent-skills repository flagged this exact gap: a security checklist correctly warned that Postgres grants EXECUTE to PUBLIC by default, but didn't caution that a schema-scoped ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ... FROM PUBLIC statement isn't sufficient on its own without verifying the resulting ACL directly against has_function_privilege(). Default privilege changes only apply to objects created after the statement runs. Anything created earlier keeps its original grants.

Layer 3: Automate It

Running these queries manually before every release doesn't scale. Two tools are worth wiring into your workflow instead of relying on memory:

• eslint-plugin-postgresql-security: includes a no-unsafe-search-path static analysis rule that catches unqualified schema references in SECURITY DEFINER function bodies before the migration ever reaches a database.
• tenant-guard style CI checks: runtime tests that assume a tenant's identity mid-pipeline and assert that isolation boundaries actually hold, catching the exact cross-tenant leak pattern shown earlier in this article before it reaches production.

If you're already testing RLS policies with pgTAP, as covered in our guide to testing RLS policies with pgTAP, extend the same test suite to call your SECURITY DEFINER RPCs as different simulated tenants and assert that cross-tenant calls fail. RLS tests alone won't catch this bug, because RLS is exactly what these functions are designed to skip.

Fixing and Locking Down SECURITY DEFINER Functions

Every fix in this article boils down to the same four checks. Run through this list for every SECURITY DEFINER function in your schema.

1. Pin the search_path. Add SET search_path = pg_catalog, public (or SET LOCAL search_path = pg_catalog, pg_temp; inside the body) to every SECURITY DEFINER function, with no exceptions.
2. Add an explicit tenant ownership check. Never trust a tenant ID passed as a parameter. Verify it against auth.uid() or your session's identity claim inside the function body before running the privileged query.
3. Revoke PUBLIC execute access. Run REVOKE EXECUTE ON FUNCTION your_function() FROM PUBLIC; immediately after creation, then grant execute only to the specific role that needs it.
4. Confirm with has_function_privilege(), not assumption. A REVOKE statement in a migration file doesn't guarantee the resulting ACL is what you expect. Query it directly using the Layer 2 audit script above.

Where possible, prefer SECURITY INVOKER and let RLS do the enforcement. Reach for SECURITY DEFINER only when there's a genuine reason to escalate privileges, and treat every one you write as a function that needs the same security review as authentication code, because functionally, that's what it is.

Quick Answers About SECURITY DEFINER

What Causes a SECURITY DEFINER Leak?

Simply put, a SECURITY DEFINER leak happens when a function owned by a superuser or BYPASSRLS role executes without a manual tenant check, so it returns rows RLS would normally have blocked. The database doesn't throw an error when this happens, it just returns the data as if the request were legitimate. This matters most in any multi-tenant SaaS database where RPCs calculate aggregates or run admin-style logic across tenant boundaries.

SECURITY DEFINER Risk at a Glance

When Does This Apply?

This applies to any SECURITY DEFINER function in a multi-tenant database, especially Supabase RPCs exposed through PostgREST. It does not apply to SECURITY INVOKER functions, which inherit the caller's own RLS restrictions and carry none of this risk.

Pros and Cons of Using SECURITY DEFINER

• Pro: Lets you run legitimate cross-tenant aggregates (billing, admin dashboards) without granting every user table-level superuser access
• Pro: Keeps privilege escalation logic centralized in one auditable function instead of scattered permission grants

• Con: Bypasses RLS entirely with no built-in safety net, unlike views which got a real fix in Postgres 15
• Con: Vulnerable to search_path hijacking if any internal call isn't schema-qualified
• Con: Callable by any authenticated user by default unless EXECUTE is explicitly revoked from PUBLIC

Frequently Asked Questions

What is the difference between SECURITY DEFINER and SECURITY INVOKER in Postgres?

SECURITY INVOKER, the default, runs a function with the calling user's own privileges. SECURITY DEFINER runs it with the privileges of whoever owns the function, which is often a superuser, making it the riskier of the two.

Does SECURITY DEFINER bypass Row Level Security?

Yes, when the function is owned by a superuser or a role with the BYPASSRLS attribute. Postgres always skips RLS checks for those roles, and that exemption applies inside SECURITY DEFINER functions too.

How do you exploit a search_path vulnerability in Postgres?

An attacker creates a malicious function with the same name as one unqualified call inside a SECURITY DEFINER function, plants it in a schema they control, then alters their session's search_path to prioritize that schema before calling the vulnerable function.

How do I restrict execute permissions on a Postgres function?

Run REVOKE EXECUTE ON FUNCTION your_function() FROM PUBLIC; then explicitly grant execute to only the roles that should have it, such as authenticated.

What is the default privilege for new functions in Postgres?

Postgres grants EXECUTE on newly created functions to the PUBLIC role by default. If you don't revoke it, any authenticated user can call the function, including SECURITY DEFINER functions with elevated privileges.

Can views be a SECURITY DEFINER leak too?

Historically yes. Views ran with the creator's privileges by default. Postgres 15 fixed this with WITH (security_invoker = true), but that option only exists for views, not for functions.

Does this affect Supabase specifically more than plain Postgres?

Yes, because Supabase exposes database functions directly as public API endpoints through PostgREST. A SECURITY DEFINER leak that would sit behind an internal network in a traditional setup is reachable from the public internet on Supabase.

Bringing It Back to the Bigger Picture

SECURITY DEFINER isn't a setting you stumble into by accident, someone always types it deliberately to solve a permissions problem. The trouble is that fixing one error this way quietly opens a much bigger one, and it does it silently, with no failed query, no exception, no line in the logs pointing back at the function.

If you're still working through your broader multi-tenant architecture, our complete guide to multi-tenant database design in Supabase covers where RLS fits into the bigger picture, and where functions like this should and shouldn't be used. Run the audit queries from this article against your own schema this week. It takes a few minutes, and a SECURITY DEFINER function with no pinned search_path and no tenant check is not a theoretical risk, it's a data breach waiting for someone to notice the RPC exists.