Supabase Database Security Best Practices

Supabase Database Security Model

Supabase exposes your PostgreSQL database directly to the internet through PostgREST. This architecture gives you the full power of Postgres, but it also means your database security configuration is your application security. There is no middleware to catch mistakes.

Essential Security Layers

Layer 1: Row Level Security (RLS)

RLS is the most critical security control. Every table accessible through the API must have RLS enabled:

-- Check all tables for RLS status
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

-- Enable RLS on any table that has it disabled
ALTER TABLE public.your_table ENABLE ROW LEVEL SECURITY;

Layer 2: Role Permissions

Supabase uses several PostgreSQL roles. Understanding them is critical:

• anon: Unauthenticated API access (uses the anon key)
• authenticated: Authenticated user API access
• service_role: Bypasses RLS (for server-side use only)
• postgres: Superuser (for migrations and admin)

Audit what each role can access:

-- Check table permissions for the anon role
SELECT table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'anon' AND table_schema = 'public';

-- Revoke unnecessary permissions
REVOKE ALL ON public.sensitive_table FROM anon;
REVOKE ALL ON public.sensitive_table FROM authenticated;
-- Now only service_role and postgres can access it

Layer 3: Column-Level Security

Sometimes entire rows should be accessible, but certain columns should be hidden:

-- Revoke access to specific columns
REVOKE SELECT (ssn, credit_card) ON users FROM authenticated;

-- Or use a view to expose only safe columns
CREATE VIEW public.user_profiles AS
  SELECT id, display_name, avatar_url, bio
  FROM users;

ALTER VIEW public.user_profiles OWNER TO postgres;
GRANT SELECT ON public.user_profiles TO authenticated;

Layer 4: Schema Isolation

Keep internal tables out of the public schema:

-- Create a private schema for internal tables
CREATE SCHEMA private;

-- Tables in the private schema are not accessible through the API
CREATE TABLE private.internal_config (
  key text PRIMARY KEY,
  value jsonb
);

-- Grant access only to specific roles
GRANT USAGE ON SCHEMA private TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA private TO service_role;

PostgREST only exposes tables in the schemas listed in its configuration (typically public and storage). Tables in other schemas are not accessible via the API.

Preventing SQL Injection

While PostgREST parameterizes queries, SQL injection can still occur through:

1. RPC functions that concatenate user input:

-- BAD: SQL injection via dynamic query
CREATE FUNCTION search_users(search_term text)
RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT * FROM users WHERE name LIKE ''%' || search_term || '%''';
END;
$$ LANGUAGE plpgsql;

-- GOOD: Parameterized query
CREATE FUNCTION search_users(search_term text)
RETURNS SETOF users AS $$
BEGIN
  RETURN QUERY SELECT * FROM users WHERE name ILIKE '%' || search_term || '%';
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

2. Edge Functions that build queries manually:

// BAD: String concatenation
const { data } = await supabase.rpc('raw_query', {
  query: `SELECT * FROM users WHERE email = '${userInput}'`
});

// GOOD: Use Supabase client methods (parameterized)
const { data } = await supabase
  .from('users')
  .select('*')
  .eq('email', userInput);

Trigger and Function Security

Security Definer vs. Security Invoker

-- SECURITY DEFINER: Runs with the privileges of the function owner (usually postgres)
-- Use cautiously -- this can bypass RLS
CREATE FUNCTION admin_action()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$ ... $$;

-- SECURITY INVOKER: Runs with the privileges of the calling user
-- Safer default for most functions
CREATE FUNCTION user_action()
RETURNS void
LANGUAGE plpgsql
SECURITY INVOKER
AS $$ ... $$;

Always set search_path explicitly on SECURITY DEFINER functions to prevent search path attacks.

Backup and Recovery

• Enable Point-in-Time Recovery (PITR) on your Supabase project
• Test restore procedures regularly
• Keep migration files version-controlled and tested
• Never store backups in public storage buckets

Monitoring and Alerts

Enable Supabase's built-in logging and set up alerts for:

• Failed authentication attempts
• Unusual query patterns (large result sets, unexpected tables)
• Schema modifications
• New role or permission changes

-- Create an audit log trigger
CREATE TABLE private.audit_log (
  id bigserial PRIMARY KEY,
  table_name text,
  action text,
  user_id uuid,
  old_data jsonb,
  new_data jsonb,
  timestamp timestamptz DEFAULT now()
);

CREATE FUNCTION private.audit_trigger()
RETURNS trigger AS $$
BEGIN
  INSERT INTO private.audit_log (table_name, action, user_id, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP, auth.uid(), row_to_json(OLD), row_to_json(NEW));
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Automated Security Auditing

AuditYour.app performs a comprehensive database security audit including RLS status, role permissions, function security, exposed schemas, and policy analysis. Run regular scans to catch misconfigurations before attackers do.