Supabase Data Handling

Overview

GDPR and CCPA compliance with Supabase uses a layered approach: Row Level Security (RLS) for tenant data isolation, supabase.auth.admin.deleteUser() for right-to-deletion, SQL-based exports for subject access requests, PII detection across columns, automated retention with pg_cron, and point-in-time recovery for backup/restore. Every requirement maps to a real Supabase SDK method or PostgreSQL feature.

When to use: Implement GDPR right-to-deletion, respond to data subject access requests (DSARs), audit PII in the database, configure automated retention, set up tenant isolation with RLS, or plan backup/restore procedures.

Prerequisites

@supabase/supabase-js v2+ with service role key for admin operations
Supabase project on Pro plan (for pg_cron and point-in-time recovery)
Understanding of GDPR Articles 15-17 (access, rectification, erasure)
Database access via SQL Editor or psql for schema changes

Instructions

Step 1: RLS for Data Isolation and PII Column Management

Enable RLS on every table holding user data, then classify PII columns so later deletion and export steps know what to touch. The RLS skeleton is one USING (auth.uid() = ...) policy per access pattern:

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_read_own_profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

Pair the policies with a PII audit — an information_schema.columns scan by naming pattern, COMMENT ON COLUMN tags, a pii_registry view, and an SDK-side regex scanner for emails, phones, SSNs, and IPs.

See RLS and PII reference for the full multi-tenant policy set, the PII audit SQL, the pii_registry view, and the scanTableForPII() SDK scanner.

Step 2: User Deletion and Data Export

Implement GDPR Article 17 (erasure) and Article 15 (access). Deletion runs in cascade order — application tables, then storage files, then the auth user, then an immutable audit-log entry that must survive the erasure:

// Cascade order matters: children before parents, auth user last
const tablesToPurge = ['comments', 'orders', 'documents', 'profiles'];
// ...delete rows, remove storage files, then:
await supabase.auth.admin.deleteUser(userId);
await supabase.from('gdpr_audit_log').insert({ action: 'USER_DELETION', subject_id: userId });

Export is the inverse: read every user-scoped table plus storage listings into one JSON payload and log a DATA_EXPORT audit row.

See deletion and export reference for the full deleteUserData() pipeline (with the gdpr_audit_log migration and locked-down RLS policy) and the exportUserData() DSAR builder.

Step 3: Retention Policies and Backup/Restore

See retention policies and backup/restore for pg_cron automated retention schedules (30/90/730-day tiers), SDK-based retention monitoring, pg_dump/pg_restore commands, and point-in-time recovery configuration.

Output

Completing this skill produces:

RLS tenant isolation — row-level policies ensuring users access only their own data
PII column registry — documented and classified PII columns across all tables
PII scanner — SDK-based pattern detection for emails, phones, SSNs, and IPs in text columns
User deletion pipeline — complete auth.admin.deleteUser() flow with cascade table deletion, storage cleanup, and audit logging
Data export — DSAR-compliant export of all user data from tables and storage
GDPR audit log — immutable log of all deletion and export operations with legal basis
Automated retention — pg_cron jobs for 30/90/730-day retention tiers
Backup/restore — pg_dump/pg_restore commands and PITR configuration

Error Handling

Error	Cause	Solution
`auth.admin.deleteUser()` returns 404	User already deleted or wrong ID	Check `auth.users` table; may have been deleted by another process
`violates foreign key constraint` during deletion	Child rows reference user	Delete in cascade order (comments → orders → profiles) or use `ON DELETE CASCADE`
`permission denied for function cron.schedule`	`pg_cron` not enabled or wrong plan	Enable `pg_cron` extension; requires Supabase Pro plan
`pg_dump: connection refused`	Using wrong port or pooler URL	Use direct connection (port 5432), not pooler (port 6543) for `pg_dump`
`RLS policy blocks admin operations`	Service role key not used	Use `createClient` with `SUPABASE_SERVICE_ROLE_KEY` to bypass RLS
Audit log entries missing	Table has RLS blocking inserts	Use `SECURITY DEFINER` function or service role for audit writes
Retention job not running	`pg_cron` job disabled or errored	Check `cron.job_run_details` for error messages

Examples

Each example uses the functions defined in the reference files above. See deletion and export reference for deleteUserData().

Example 1 — Handle a GDPR deletion request:

// Wraps deleteUserData() behind an API endpoint; GDPR requires completion within 30 days
async function handleDeletionRequest(userId: string) {
  const result = await deleteUserData(userId);
  return { status: 'completed', auditId: result.auditLogId };
}

Example 2 — Quick PII audit:

-- Count rows with email-like patterns in unexpected columns
SELECT 'profiles' AS table_name, 'bio' AS column_name, count(*) AS rows_with_email
FROM public.profiles
WHERE bio ~ '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}';

Example 3 — Verify retention job execution:

async function checkRetentionJobs() {
  const { data, error } = await supabase.rpc('get_cron_status');
  if (error) throw error;
  for (const job of data ?? []) {
    console.log(`Job "${job.jobname}": last_run=${job.last_run}, status=${job.status}`);
  }
}

Resources

Next Steps

For enterprise role-based access control, see supabase-enterprise-rbac
For security hardening and API key scoping, see supabase-security-basics
For observability and audit trail monitoring, see supabase-observability

supabase-data-handling