Securing Supabase RPC Functions

What Are Supabase RPC Functions?

Supabase exposes PostgreSQL functions through the PostgREST API as Remote Procedure Calls (RPC). Any function in the public schema that is granted EXECUTE permission to the anon or authenticated roles can be called directly from client code:

const { data, error } = await supabase.rpc('my_function', { param1: 'value' });

This is powerful but dangerous. Unlike table access, which is protected by RLS, RPC functions have their own security model that is easy to misconfigure.

SECURITY DEFINER vs. SECURITY INVOKER

This is the most critical security decision for any RPC function.

SECURITY INVOKER (Default, Safer)

The function runs with the privileges of the calling user. RLS policies apply:

CREATE FUNCTION get_my_orders()
RETURNS SETOF orders
LANGUAGE sql
SECURITY INVOKER
AS $$
  SELECT * FROM orders;  -- RLS will filter to user's own orders
$$;

SECURITY DEFINER (Dangerous if Misused)

The function runs with the privileges of the function owner (typically postgres). RLS is bypassed:

-- DANGEROUS: Returns ALL orders, bypassing RLS
CREATE FUNCTION get_all_orders()
RETURNS SETOF orders
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT * FROM orders;
$$;

A SECURITY DEFINER function callable by the anon role is effectively giving unauthenticated users superuser access to whatever the function does.

When to Use SECURITY DEFINER

There are legitimate use cases, but they require careful controls:

-- Legitimate: Atomic credit deduction that needs to bypass RLS
-- Note the multiple security controls
CREATE FUNCTION deduct_credit(scan_id uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public  -- Prevent search path injection
AS $$
DECLARE
  calling_user uuid;
  current_credits integer;
BEGIN
  -- Manually verify the calling user
  calling_user := auth.uid();
  IF calling_user IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  -- Check ownership
  IF NOT EXISTS (
    SELECT 1 FROM scans WHERE id = scan_id AND user_id = calling_user
  ) THEN
    RAISE EXCEPTION 'Not authorized';
  END IF;

  -- Atomic credit deduction
  UPDATE profiles
  SET credits = credits - 1
  WHERE id = calling_user AND credits > 0
  RETURNING credits INTO current_credits;

  IF current_credits IS NULL THEN
    RAISE EXCEPTION 'Insufficient credits';
  END IF;

  RETURN true;
END;
$$;

Key safeguards for SECURITY DEFINER functions:

1. Always set search_path explicitly
2. Manually verify auth.uid() inside the function
3. Validate all inputs
4. Keep the function focused on a single operation

Controlling Access to RPC Functions

Revoke Public Access

By default, PostgreSQL grants EXECUTE on new functions to PUBLIC. Always revoke this:

-- Revoke from all roles
REVOKE EXECUTE ON FUNCTION sensitive_function FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION sensitive_function FROM anon;
REVOKE EXECUTE ON FUNCTION sensitive_function FROM authenticated;

-- Grant only to specific roles
GRANT EXECUTE ON FUNCTION sensitive_function TO authenticated;

Set Default Privileges

Prevent future functions from being automatically accessible:

-- Revoke default EXECUTE grant
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM anon;

Input Validation

RPC functions receive parameters directly from the client. Validate everything:

CREATE FUNCTION update_profile(
  display_name text,
  bio text
)
RETURNS void
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
  -- Validate inputs
  IF length(display_name) > 100 THEN
    RAISE EXCEPTION 'Display name too long';
  END IF;

  IF length(bio) > 1000 THEN
    RAISE EXCEPTION 'Bio too long';
  END IF;

  -- Sanitize: Strip HTML/script tags
  display_name := regexp_replace(display_name, '<[^>]*>', '', 'g');
  bio := regexp_replace(bio, '<[^>]*>', '', 'g');

  UPDATE profiles
  SET
    display_name = update_profile.display_name,
    bio = update_profile.bio
  WHERE id = auth.uid();
END;
$$;

Preventing SQL Injection in RPC Functions

Never build dynamic SQL from user input:

-- BAD: SQL injection vulnerability
CREATE FUNCTION search(term text)
RETURNS SETOF products
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT * FROM products WHERE name LIKE ''%' || term || '%''';
END;
$$;

-- GOOD: Parameterized query
CREATE FUNCTION search(term text)
RETURNS SETOF products
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT * FROM products WHERE name ILIKE '%' || term || '%';
END;
$$;

-- GOOD: If dynamic SQL is truly needed, use format() with %L
CREATE FUNCTION search_dynamic(table_name text, term text)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
BEGIN
  -- Validate table name against whitelist
  IF table_name NOT IN ('products', 'categories') THEN
    RAISE EXCEPTION 'Invalid table';
  END IF;

  RETURN QUERY EXECUTE format(
    'SELECT * FROM %I WHERE name ILIKE %L',
    table_name,
    '%' || term || '%'
  );
END;
$$;

Auditing RPC Functions

Find all functions accessible to the anon role:

SELECT
  p.proname AS function_name,
  CASE
    WHEN p.prosecdef THEN 'SECURITY DEFINER'
    ELSE 'SECURITY INVOKER'
  END AS security_type,
  pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
AND has_function_privilege('anon', p.oid, 'EXECUTE');

Any SECURITY DEFINER function accessible to anon should be carefully reviewed.

Rate Limiting RPC Functions

Since RPC functions can perform expensive operations, implement rate limiting:

CREATE FUNCTION rate_limited_operation(input_data jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  calling_user uuid := auth.uid();
  recent_calls integer;
BEGIN
  IF calling_user IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  -- Count recent calls
  SELECT count(*) INTO recent_calls
  FROM function_calls
  WHERE user_id = calling_user
    AND function_name = 'rate_limited_operation'
    AND called_at > now() - interval '1 minute';

  IF recent_calls >= 10 THEN
    RAISE EXCEPTION 'Rate limit exceeded';
  END IF;

  -- Log this call
  INSERT INTO function_calls (user_id, function_name, called_at)
  VALUES (calling_user, 'rate_limited_operation', now());

  -- Perform the actual operation
  -- ...
  RETURN '{"success": true}'::jsonb;
END;
$$;

Automated RPC Scanning

AuditYour.app scans your Supabase project for insecure RPC functions, including SECURITY DEFINER functions accessible to the anon role, missing input validation, and functions with overly broad permissions. Run a scan to identify vulnerable functions before they are exploited.