Supabase Postgres Hardening Guide

Why Postgres Hardening Matters for Supabase

Supabase gives you a full PostgreSQL 17 database that is directly accessible through REST and GraphQL APIs. While Supabase manages the infrastructure, the security configuration of the database itself is your responsibility. Default configurations prioritize ease of use over security. Hardening your Postgres instance closes gaps that attackers exploit.

Role and Permission Hardening

Audit Existing Role Permissions

Start by understanding what each role can currently do:

-- List all role memberships
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid;

-- List all table privileges for anon and authenticated
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee IN ('anon', 'authenticated')
AND table_schema = 'public'
ORDER BY table_name, grantee;

Lock Down the Anon Role

-- Remove unnecessary schema access
REVOKE CREATE ON SCHEMA public FROM anon;

-- Remove access to system functions
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM anon;

-- Grant back only specific functions that anon needs
GRANT EXECUTE ON FUNCTION public.login_function TO anon;
GRANT EXECUTE ON FUNCTION public.signup_function TO anon;

Restrict the Authenticated Role

-- Don't grant blanket table access
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM authenticated;

-- Grant access table by table
GRANT SELECT ON public.user_profiles TO authenticated;
GRANT SELECT, INSERT ON public.user_posts TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.user_drafts TO authenticated;
-- RLS policies further restrict within these grants

Schema Security

Isolate Internal Data

-- Create schemas for internal use
CREATE SCHEMA IF NOT EXISTS internal;
CREATE SCHEMA IF NOT EXISTS analytics;

-- Ensure PostgREST doesn't expose them
-- (Verify in supabase/config.toml that only 'public' and 'storage' are in the exposed schemas)

-- Revoke all access from API roles
REVOKE ALL ON SCHEMA internal FROM anon, authenticated;
REVOKE ALL ON SCHEMA analytics FROM anon, authenticated;

-- Only service_role can access internal schemas
GRANT USAGE ON SCHEMA internal TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA internal TO service_role;

Secure the Auth Schema

The auth schema contains sensitive user data. Verify it is not accessible:

-- This should return no rows for anon/authenticated
SELECT grantee, privilege_type
FROM information_schema.schema_privileges
WHERE schema_name = 'auth'
AND grantee IN ('anon', 'authenticated');

Connection Security

Database Password

Change the default database password to a strong, unique password:

-- In Supabase Dashboard: Settings > Database > Database password
-- Or via SQL:
ALTER USER postgres PASSWORD 'new-very-strong-password-here';

Connection Limits

Set connection limits per role to prevent resource exhaustion:

ALTER ROLE anon CONNECTION LIMIT 100;
ALTER ROLE authenticated CONNECTION LIMIT 200;

SSL Mode

Ensure all connections use SSL. In Supabase, this is enforced by default, but verify your client connections use sslmode=require or stricter.

Extension Security

Audit Installed Extensions

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Remove unnecessary extensions. Each extension increases the attack surface. Pay special attention to:

• dblink and postgres_fdw: Allow connecting to external databases
• file_fdw: Allows reading files from the server filesystem
• adminpack: Provides administrative functions

-- Remove unused extensions
DROP EXTENSION IF EXISTS dblink;
DROP EXTENSION IF EXISTS postgres_fdw;

pg_cron Security

If using pg_cron for scheduled jobs, ensure the jobs table is not accessible through the API:

-- Verify cron schema is not exposed
REVOKE ALL ON SCHEMA cron FROM anon, authenticated;

Query Security

Prevent Statement Timeout Abuse

Set statement timeouts to prevent long-running queries from consuming resources:

-- Set timeout for API roles
ALTER ROLE anon SET statement_timeout = '10s';
ALTER ROLE authenticated SET statement_timeout = '30s';

Limit Result Set Sizes

Use PostgREST configuration to set maximum result set sizes. In supabase/config.toml:

[api]
max_rows = 1000

This prevents attackers from dumping entire tables in a single request.

Prevent COPY and Large Exports

Ensure API roles cannot use COPY or other bulk export mechanisms:

-- Verify the anon role cannot execute COPY
-- (PostgREST does not expose COPY, but direct connections might)

Trigger Security

Audit Triggers

-- List all triggers in the public schema
SELECT trigger_name, event_object_table, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public';

Ensure triggers:

• Do not execute SECURITY DEFINER functions unnecessarily
• Do not expose sensitive data through side channels
• Cannot be manipulated by user input to escalate privileges

Validate Trigger Functions

-- Check if trigger functions are SECURITY DEFINER
SELECT p.proname, p.prosecdef
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
WHERE p.pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

Logging and Monitoring

Enable Query Logging

-- Log all DDL statements
ALTER SYSTEM SET log_statement = 'ddl';

-- Log slow queries (queries taking more than 1 second)
ALTER SYSTEM SET log_min_duration_statement = 1000;

-- Reload configuration
SELECT pg_reload_conf();

Create an Audit Trail

CREATE TABLE internal.audit_log (
  id bigserial PRIMARY KEY,
  event_time timestamptz DEFAULT now(),
  user_id uuid,
  action text NOT NULL,
  table_name text,
  row_id text,
  old_data jsonb,
  new_data jsonb
);

CREATE OR REPLACE FUNCTION internal.log_change()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = internal
AS $$
BEGIN
  INSERT INTO internal.audit_log (user_id, action, table_name, row_id, old_data, new_data)
  VALUES (
    auth.uid(),
    TG_OP,
    TG_TABLE_NAME,
    COALESCE(NEW.id::text, OLD.id::text),
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::jsonb END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::jsonb END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Apply to sensitive tables
CREATE TRIGGER audit_profiles
  AFTER INSERT OR UPDATE OR DELETE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION internal.log_change();

Backup Security

• Enable Point-in-Time Recovery (PITR) in Supabase dashboard
• Verify backups are encrypted at rest
• Test restore procedures quarterly
• Do not store database dumps in publicly accessible storage

Regular Hardening Checklist

1. Audit role permissions monthly
2. Review new tables for RLS and policies
3. Check for new functions accessible to anon
4. Verify schema exposure settings
5. Review extension list
6. Test statement timeouts
7. Check audit log for anomalies
8. Rotate database password quarterly

Automated Scanning

AuditYour.app performs comprehensive Postgres security audits for Supabase projects, checking role permissions, schema exposure, function security, RLS coverage, and more. Schedule regular scans to maintain a hardened database configuration.