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:
const tablesToPurge = ['comments', 'orders', 'documents', 'profiles'];
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:
async function handleDeletionRequest(userId: string) {
const result = await deleteUserData(userId);
return { status: 'completed', auditId: result.auditLogId };
}
Example 2 — Quick PII audit:
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
