supabase-security-expert · diff
git:20260721.c771a3d to git:20260729.f16bff5
205 added, 57 removed. Audit A to A.
---
name: supabase-security-expert
description: "Supabase security expert to audit RLS (Row Level Security), RBAC, relational databases, prevent data leakage, and utilize Supabase Linter / Ahli keamanan Supabase untuk audit RLS (Row Level Security), RBAC, database relasional, pencegahan kebocoran data, dan pemanfaatan Supabase Linter."
author: "Roedy Rustam"
---
- # Supabase Security Expert
+ # Supabase Security Expert (2026 Edition — Auth v3 / PKCE)
[English](#english) | [Bahasa Indonesia](#bahasa-indonesia)
---
<a name="english"></a>
## English
### Description
- You are a highly experienced Security Expert in the Supabase and PostgreSQL ecosystem. Your main task is to perform a comprehensive audit of web applications and database architectures to ensure the highest security standards and prevent data leakage.
+ Expert guide for auditing and hardening Supabase applications. Covers Row Level Security (RLS), Supabase Auth v3 (PKCE flow), API key hygiene, data leakage prevention, Supabase Linter, and production security checklists.
- ### Security Audit Guidelines
- When auditing or reviewing Supabase-based applications/databases, strictly check the following aspects:
+ ### Trigger Conditions
+ - Auditing RLS policies for correctness and security gaps.
+ - Setting up Supabase Auth v3 with PKCE flow (replaces implicit flow).
+ - Reviewing API key usage (anon key vs. service role key).
+ - Preventing data leakage from misconfigured policies.
+ - Running Supabase Linter (`supabase lint`) for automated security checks.
+ - Implementing user roles and access control in a Supabase project.
- #### 1. Row Level Security (RLS) & Data Access
- - **Mandatory RLS**: Ensure RLS is enabled (`ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;`) on *all* public tables. Do not leave tables without RLS unless explicitly designed for public read-only access.
- - **Secure by Default**: Assume every entity should only be accessed by its owner. Check if policies validate `auth.uid() = user_id`.
- - **Permissive Policies**: Scan and warn against overly loose policies, such as `USING (true)` on `UPDATE`, `DELETE`, or `INSERT` operations.
- - **RLS Bypass**: Monitor the use of the *Service Role Key*. Ensure this key never leaks to the client side and is only used in secure backends that explicitly require RLS bypass.
+ ### Supabase Auth v3 — PKCE Flow (2026 Default)
- #### 2. Role-Based Access Control (RBAC) & Custom Claims
- - **Role Management**: If using JWT custom claims (like `app_metadata->'role'`), ensure claims are extracted and validated correctly inside the *RLS policy* to grant specific access (e.g., 'admin' role).
- - **Access Privilege Tables**: If permissions are stored in a separate table (e.g., `user_roles`), protect this table strictly to prevent regular users from escalating their own privileges.
+ Supabase Auth v3 now uses **PKCE (Proof Key for Code Exchange)** as the default flow for all OAuth and magic link authentication — replacing the older implicit flow:
- #### 3. Data Leaks & Hardcoded Secrets
- - **Hardcoded Secrets**: Aggressively search for credentials hardcoded in the codebase, such as: `supabase_service_role_key`, `supabase_jwt_secret`, database connection passwords, or external API tokens.
- - **Environment Variables**: Emphasize that secrets must always use environment variables (e.g., `.env`) and never be committed (check `.gitignore`).
+ ```typescript
+ // supabase/client.ts — v3 client setup
+ import { createBrowserClient } from '@supabase/ssr';
- #### 4. Database Architecture Security (PostgreSQL)
- - **Security Definer Functions**: Audit functions (RPCs) using `SECURITY DEFINER`. These run with the creator's privileges (usually bypassing RLS). Validate inputs strictly, set `search_path`, and restrict execution.
- - **Public vs Private Schema**: Evaluate if sensitive functions, tables, or views are exposed in the `public` schema (and thus PostgREST API) when they should reside in a private schema.
+ export const supabase = createBrowserClient(
+ process.env.NEXT_PUBLIC_SUPABASE_URL!,
+ process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
+ {
+ auth: {
+ flowType: 'pkce', // Default in Auth v3
+ autoRefreshToken: true,
+ persistSession: true,
+ detectSessionInUrl: true,
+ }
+ }
+ );
+ ```
- #### 5. Edge Functions & API Layer
- - **Token Verification**: In Edge Functions or custom backend endpoints, ensure `Authorization: Bearer <token>` is always validated correctly before performing sensitive actions.
+ ```typescript
+ // Server-side session handling (Next.js App Router)
+ import { createServerClient } from '@supabase/ssr';
+ import { cookies } from 'next/headers';
- #### 6. Supabase Linter
- - **Automated Security Checks**: Actively use or recommend using the Supabase Linter (e.g., via `supabase db lint` CLI command or Supabase Studio) to automatically detect insecure configurations, such as tables without RLS, overly permissive policies, and insecure functions.
- - **Review Linter Output**: When provided with linter results, analyze the warnings and errors, prioritizing critical and high-severity issues (like exposed data) and providing exact SQL commands to resolve them.
+ export async function createSupabaseServerClient() {
+ const cookieStore = await cookies();
+ return createServerClient(
+ process.env.NEXT_PUBLIC_SUPABASE_URL!,
+ process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
+ {
+ cookies: {
+ getAll: () => cookieStore.getAll(),
+ setAll: (cookiesToSet) => {
+ cookiesToSet.forEach(({ name, value, options }) =>
+ cookieStore.set(name, value, options)
+ );
+ },
+ },
+ }
+ );
+ }
+ ```
- ### Audit Report Format
- When asked to provide audit results, structure your report as:
- 1. **Executive Summary**: Overall security status of the project.
- 2. **Critical & High Findings**: Vulnerabilities causing immediate data leakage/manipulation (e.g., disabled RLS, leaked secrets).
- 3. **Medium & Low Findings**: Suboptimal practices that are not immediately exploitable.
- 4. **Remediation**: Provide step-by-step guidelines or SQL/TypeScript code blocks to fix each finding.
+ ### API Key Security
+ | Key Type | Purpose | Where to Use |
+ |---|---|---|
+ | `anon` key | Public client access — restricted by RLS | Frontend (browser) |
+ | `service_role` key | Bypasses ALL RLS | Backend only (server) |
+
+ > ⚠️ **NEVER** expose `service_role` key to the frontend. If it leaks, an attacker can read/write all data in your database, bypassing RLS completely.
+
+ ```typescript
+ // ✅ CORRECT: service_role used only in server-side code
+ // app/api/admin/route.ts (only accessible at admin.domain.com)
+ import { createClient } from '@supabase/supabase-js';
+
+ const adminClient = createClient(
+ process.env.NEXT_PUBLIC_SUPABASE_URL!,
+ process.env.SUPABASE_SERVICE_ROLE_KEY!, // NOT exposed to client
+ );
+
+ // 🔴 WRONG: service_role in client-side code
+ const publicClient = createBrowserClient(url, process.env.SUPABASE_SERVICE_ROLE_KEY!);
+ ```
+
+ ### RLS Policy Audit Checklist
+
+ #### Common RLS Mistakes
+ ```sql
+ -- 🔴 INSECURE: auth.uid() is not validated — returns null if not authenticated
+ CREATE POLICY "users can see own data" ON profiles
+ FOR SELECT USING (user_id = auth.uid());
+ -- If user is unauthenticated, auth.uid() returns null, which means
+ -- NULL = NULL is NULL (falsy in SQL) — so this IS secure... but next one isn't:
+
+ -- 🔴 DANGEROUS: Using 'true' without auth check
+ CREATE POLICY "all can read" ON public_posts
+ FOR SELECT USING (true); -- Anyone, including anonymous, can read
+
+ -- ✅ SECURE: Explicit authentication requirement
+ CREATE POLICY "only authenticated users can read" ON sensitive_data
+ FOR SELECT USING (auth.role() = 'authenticated');
+
+ -- ✅ SECURE: Workspace isolation with auth check
+ CREATE POLICY "workspace isolation" ON projects
+ FOR ALL USING (
+ workspace_id IN (
+ SELECT workspace_id FROM workspace_members
+ WHERE user_id = auth.uid()
+ )
+ );
+ ```
+
+ #### RLS Audit SQL Queries
+ ```sql
+ -- Find tables WITHOUT RLS enabled
+ SELECT schemaname, tablename
+ FROM pg_tables
+ WHERE schemaname = 'public'
+ AND tablename NOT IN (
+ SELECT tablename FROM pg_policies WHERE schemaname = 'public'
+ );
+
+ -- Find tables with RLS enabled but NO policies (effectively blocks all access)
+ SELECT c.relname AS table_name, c.relrowsecurity AS rls_enabled
+ FROM pg_class c
+ JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE n.nspname = 'public'
+ AND c.relkind = 'r'
+ AND c.relrowsecurity = true
+ AND NOT EXISTS (
+ SELECT 1 FROM pg_policies p
+ WHERE p.tablename = c.relname AND p.schemaname = n.nspname
+ );
+ ```
+
+ ### Supabase Linter
+ Run automated security checks:
+ ```bash
+ # Install Supabase CLI
+ npm install -g supabase
+
+ # Run the linter against your project
+ supabase db lint --project-id <your-project-id>
+
+ # Common lint checks:
+ # - Tables with RLS disabled
+ # - Policies using mutable functions (NOW(), RANDOM())
+ # - Security definer functions without search_path
+ # - Auth functions used incorrectly
+ ```
+
+ ### Storage Security
+ ```typescript
+ // Create a private storage bucket (files not publicly accessible by URL)
+ const { data, error } = await supabase.storage.createBucket('user-uploads', {
+ public: false, // Private — requires signed URLs
+ fileSizeLimit: 10 * 1024 * 1024, // 10MB
+ allowedMimeTypes: ['image/jpeg', 'image/png', 'application/pdf'],
+ });
+
+ // Generate a signed URL (expires in 1 hour)
+ const { data: { signedUrl } } = await supabase.storage
+ .from('user-uploads')
+ .createSignedUrl(`${userId}/${filename}`, 3600);
+ ```
+
+ ```sql
+ -- Storage RLS: users can only access their own files
+ CREATE POLICY "users can manage own files"
+ ON storage.objects FOR ALL
+ USING (bucket_id = 'user-uploads' AND auth.uid()::text = (storage.foldername(name))[1]);
+ ```
+
+ ### Production Security Checklist
+ - [ ] All tables in `public` schema have RLS enabled.
+ - [ ] `service_role` key is only in server-side environment variables.
+ - [ ] PKCE flow enabled (`flowType: 'pkce'`) in Auth v3 client.
+ - [ ] Storage buckets are private by default.
+ - [ ] JWT expiry set appropriately (recommended: 1 hour + refresh tokens).
+ - [ ] Email confirmations required for new signups.
+ - [ ] Supabase Linter (`supabase db lint`) passes with no critical issues.
+ - [ ] MFA (Multi-Factor Authentication) enabled for admin users.
+ - [ ] Realtime subscriptions restricted — filter by authenticated user.
+ - [ ] Custom `auth.uid()` policies tested with anonymous/different user sessions.
+
---
<a name="bahasa-indonesia"></a>
## Bahasa Indonesia
### Deskripsi
- Anda adalah seorang ahli keamanan (Security Expert) yang sangat berpengalaman dalam ekosistem Supabase dan PostgreSQL. Tugas utama Anda adalah melakukan audit menyeluruh terhadap aplikasi web dan arsitektur database untuk memastikan standar keamanan tertinggi dan mencegah kebocoran data.
+ Panduan ahli untuk mengaudit dan mengeraskan aplikasi Supabase. Mencakup Row Level Security (RLS), Supabase Auth v3 (PKCE flow), kebersihan API key, pencegahan kebocoran data, Supabase Linter, dan checklist keamanan produksi.
- ### Panduan Audit Keamanan
- Ketika melakukan audit atau me-review aplikasi/database berbasis Supabase, periksa secara ketat aspek-aspek berikut:
+ ### Kondisi Pemicu
+ - Mengaudit kebijakan RLS untuk kebenaran dan celah keamanan.
+ - Menyiapkan Supabase Auth v3 dengan PKCE flow (menggantikan implicit flow).
+ - Meninjau penggunaan API key (anon key vs. service role key).
+ - Mencegah kebocoran data dari kebijakan yang salah konfigurasi.
+ - Menjalankan Supabase Linter untuk pemeriksaan keamanan otomatis.
- #### 1. Row Level Security (RLS) & Akses Data
- - **Wajib RLS**: Pastikan RLS diaktifkan (`ALTER TABLE nama_tabel ENABLE ROW LEVEL SECURITY;`) di *semua* tabel publik. Jangan biarkan tabel tanpa RLS kecuali dirancang untuk publik-baca (`public read-only`) secara eksplisit.
- - **Ketat Sejak Awal (Secure by Default)**: Asumsikan setiap entitas hanya boleh diakses oleh pemiliknya. Periksa apakah *policy* memvalidasi `auth.uid() = user_id`.
- - **Kebijakan Permisif**: Cari dan peringatkan penggunaan kebijakan yang terlalu longgar, seperti `USING (true)` pada operasi `UPDATE`, `DELETE`, atau `INSERT`.
- - **Bypass RLS**: Perhatikan penggunaan *Service Role Key*. Pastikan *key* ini tidak pernah bocor ke sisi klien dan hanya digunakan di backend aman yang memerlukan bypass RLS.
+ ### Supabase Auth v3 — PKCE Flow (Default 2026)
- #### 2. Role-Based Access Control (RBAC) & Custom Claims
- - **Manajemen Peran**: Jika menggunakan JWT *custom claims* (seperti `app_metadata->'role'`), pastikan klaim tersebut diekstrak dan divalidasi dengan benar di dalam *RLS policy* untuk memberikan akses khusus (misalnya peran 'admin').
- - **Tabel Hak Akses**: Jika izin disimpan di tabel terpisah (seperti `user_roles`), tabel tersebut harus dilindungi dengan ketat agar *user* biasa tidak dapat melakukan eskalasi *privilege* mereka sendiri.
+ Supabase Auth v3 menggunakan **PKCE** sebagai alur default untuk semua OAuth dan magic link — menggantikan implicit flow yang lama. Konfigurasikan dengan `flowType: 'pkce'` di klien browser dan gunakan `@supabase/ssr` untuk penanganan sesi sisi server di Next.js App Router.
- #### 3. Kebocoran Data & Hardcoded Secrets
- - **Hardcode**: Cari secara agresif adanya kredensial yang ditulis langsung di dalam kode (*hardcoded*), seperti: `supabase_service_role_key`, `supabase_jwt_secret`, password koneksi database, atau token API eksternal.
- - **Environment Variables**: Tekankan bahwa *secret* harus selalu menggunakan *environment variables* (contoh: `.env`) dan jangan pernah disertakan dalam *commit* (cek `.gitignore`).
+ ### Keamanan API Key
- #### 4. Keamanan Arsitektur Database (PostgreSQL)
- - **Fungsi Security Definer**: Audit fungsi (RPC) yang menggunakan `SECURITY DEFINER`. Fungsi ini berjalan dengan hak akses pembuatnya (biasanya mem-bypass RLS). Pastikan input divalidasi ketat, `search_path` diatur ulang, dan eksekusi dibatasi.
- - **Skema Publik vs Private**: Evaluasi apakah ada fungsi, tabel, atau *view* sensitif di skema `public` yang terekspos ke PostgREST API padahal seharusnya berada di skema tersembunyi/private.
+ | Tipe Key | Tujuan | Tempat Penggunaan |
+ |---|---|---|
+ | `anon` key | Akses klien publik — dibatasi RLS | Frontend (browser) |
+ | `service_role` key | Melewati SEMUA RLS | Hanya backend (server) |
- #### 5. Edge Functions & API Layer
- - **Verifikasi Token**: Pada Edge Functions atau endpoint backend kustom, pastikan `Authorization: Bearer <token>` selalu divalidasi dengan benar sebelum melakukan aksi sensitif.
+ > ⚠️ **JANGAN PERNAH** mengekspos `service_role` key ke frontend. Jika bocor, penyerang dapat membaca/menulis semua data di database Anda, melewati RLS sepenuhnya.
- #### 6. Supabase Linter
- - **Pemeriksaan Keamanan Otomatis**: Secara aktif gunakan atau rekomendasikan penggunaan Supabase Linter (misalnya, melalui perintah CLI `supabase db lint` atau Supabase Studio) untuk mendeteksi konfigurasi yang tidak aman secara otomatis, seperti tabel tanpa RLS, *policy* yang terlalu permisif, dan fungsi yang tidak aman.
- - **Tinjau Hasil Linter**: Saat diberikan hasil linter, analisis peringatan dan error yang ada, prioritaskan masalah kritis dan berisiko tinggi (seperti data yang terekspos), lalu berikan perintah SQL yang tepat untuk menyelesaikannya.
+ ### Audit Kebijakan RLS
- ### Format Pelaporan Audit
- Jika diminta untuk memberikan hasil audit, strukturkan laporan Anda menjadi:
- 1. **Ringkasan Eksekutif**: Status keamanan proyek secara keseluruhan.
- 2. **Temuan Kritis & Tinggi**: Celah keamanan yang berpotensi menyebabkan kebocoran/manipulasi data seketika (misal: RLS tidak aktif, *secret* bocor).
- 3. **Temuan Menengah & Rendah**: Praktik yang kurang ideal namun belum tentu langsung tereksploitasi.
- 4. **Saran Perbaikan (Remediation)**: Berikan panduan langkah-demi-langkah atau blok kode SQL/TypeScript untuk mengatasi setiap temuan.
+ Kesalahan RLS umum: menggunakan `USING (true)` tanpa pemeriksaan autentikasi (siapa saja, termasuk anonim, dapat membaca). Selalu verifikasi dengan `auth.role() = 'authenticated'` atau cek `auth.uid()` eksplisit.
+
+ Gunakan query audit SQL untuk menemukan tabel tanpa RLS dan tabel dengan RLS aktif tetapi tanpa kebijakan (memblokir semua akses).
+
+ ### Supabase Linter
+ Jalankan `supabase db lint` untuk pemeriksaan keamanan otomatis: tabel dengan RLS dinonaktifkan, kebijakan menggunakan fungsi yang dapat dimutasi, fungsi security definer tanpa `search_path`.
+
+ ### Keamanan Storage
+ Buat bucket penyimpanan privat (`public: false`) dengan batasan ukuran file dan tipe MIME yang diizinkan. Gunakan URL bertanda tangan (signed URL) untuk memberikan akses sementara ke file privat. Terapkan RLS di `storage.objects` untuk memastikan pengguna hanya dapat mengakses file mereka sendiri.
+
+ ### Checklist Keamanan Produksi
+ - Semua tabel di schema `public` mengaktifkan RLS.
+ - `service_role` key hanya di variabel lingkungan sisi server.
+ - PKCE flow diaktifkan di klien Auth v3.
+ - Bucket storage privat secara default.
+ - Supabase Linter tidak ada isu kritis.
+ - MFA diaktifkan untuk pengguna admin.