Harden auth, sync, and Supabase security #10
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: 🗄️ SQL Schema Validation | |
| on: | |
| push: | |
| paths: | |
| - '*.sql' | |
| - 'sql/**' | |
| - '.github/workflows/schema-lint.yml' | |
| pull_request: | |
| paths: | |
| - '*.sql' | |
| - 'sql/**' | |
| - '.github/workflows/schema-lint.yml' | |
| workflow_dispatch: | |
| jobs: | |
| validate: | |
| name: Validate SQL files | |
| runs-on: ubuntu-latest | |
| services: | |
| postgres: | |
| image: postgres:16 | |
| env: | |
| POSTGRES_PASSWORD: testpass | |
| POSTGRES_USER: postgres | |
| POSTGRES_DB: isotope_test | |
| ports: | |
| - 5432:5432 | |
| options: >- | |
| --health-cmd pg_isready | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| steps: | |
| - name: ⬇️ Checkout | |
| uses: actions/checkout@v4 | |
| - name: 📊 Schema file stats | |
| run: | | |
| for f in isotope-complete.sql community-patch-v4.sql sql/*.sql performance-patch.sql; do | |
| if [ -f "$f" ]; then | |
| echo "=== $f ===" | |
| echo " Size: $(wc -c < $f) bytes" | |
| echo " Lines: $(wc -l < $f)" | |
| echo " CREATE TABLE: $(awk '/CREATE TABLE/{n++} END{print n+0}' "$f")" | |
| echo " CREATE FUNCTION: $(awk '/CREATE.*FUNCTION/{n++} END{print n+0}' "$f")" | |
| echo " CREATE POLICY: $(awk '/CREATE POLICY/{n++} END{print n+0}' "$f")" | |
| echo " CREATE INDEX: $(awk '/CREATE.*INDEX/{n++} END{print n+0}' "$f")" | |
| else | |
| echo "⚠️ $f not found — skipping" | |
| fi | |
| done | |
| - name: 🔍 Check for dangerous patterns | |
| run: | | |
| if [ ! -f isotope-complete.sql ]; then | |
| echo "⚠️ isotope-complete.sql not found — skipping pattern checks" | |
| exit 0 | |
| fi | |
| echo "Checking for DROP without IF EXISTS..." | |
| UNSAFE_DROPS=$(grep -nE '^\s*DROP (TABLE|FUNCTION|POLICY|INDEX|TRIGGER)\s' isotope-complete.sql | grep -v 'IF EXISTS' || true) | |
| if [ -n "$UNSAFE_DROPS" ]; then | |
| echo "⚠️ Found DROP without IF EXISTS — may fail on fresh installs:" | |
| echo "$UNSAFE_DROPS" | |
| else | |
| echo "✅ All DROPs use IF EXISTS" | |
| fi | |
| echo "" | |
| echo "Checking for SECURITY DEFINER functions..." | |
| SEC_DEF=$(awk '/SECURITY DEFINER/{n++} END{print n+0}' isotope-complete.sql) | |
| echo " $SEC_DEF SECURITY DEFINER functions" | |
| echo "" | |
| echo "Checking GRANT coverage..." | |
| FUNCTIONS=$(awk '/CREATE OR REPLACE FUNCTION/{n++} END{print n+0}' isotope-complete.sql) | |
| GRANTS=$(awk '/GRANT EXECUTE/{n++} END{print n+0}' isotope-complete.sql) | |
| echo " Functions: $FUNCTIONS | GRANT EXECUTE: $GRANTS" | |
| - name: 🗄️ Create Supabase compatibility shim | |
| env: | |
| PGPASSWORD: testpass | |
| run: | | |
| psql -h localhost -U postgres -d isotope_test -v ON_ERROR_STOP=1 -c " | |
| DO \$\$ BEGIN CREATE ROLE anon; EXCEPTION WHEN duplicate_object THEN NULL; END \$\$; | |
| DO \$\$ BEGIN CREATE ROLE authenticated; EXCEPTION WHEN duplicate_object THEN NULL; END \$\$; | |
| DO \$\$ BEGIN CREATE ROLE service_role; EXCEPTION WHEN duplicate_object THEN NULL; END \$\$; | |
| CREATE SCHEMA IF NOT EXISTS auth; | |
| CREATE TABLE IF NOT EXISTS auth.users ( | |
| id uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| email text, | |
| raw_user_meta_data jsonb DEFAULT '{}' | |
| ); | |
| CREATE OR REPLACE FUNCTION auth.uid() RETURNS uuid | |
| LANGUAGE sql STABLE AS \$\$ SELECT '00000000-0000-0000-0000-000000000000'::uuid; \$\$; | |
| CREATE OR REPLACE FUNCTION auth.role() RETURNS text | |
| LANGUAGE sql STABLE AS \$\$ SELECT 'authenticated'::text; \$\$; | |
| CREATE OR REPLACE FUNCTION auth.email() RETURNS text | |
| LANGUAGE sql STABLE AS \$\$ SELECT ''::text; \$\$; | |
| CREATE SCHEMA IF NOT EXISTS storage; | |
| CREATE TABLE IF NOT EXISTS storage.buckets ( | |
| id text PRIMARY KEY, | |
| name text, | |
| public boolean DEFAULT false, | |
| file_size_limit bigint, | |
| allowed_mime_types text[] | |
| ); | |
| CREATE TABLE IF NOT EXISTS storage.objects ( | |
| id uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| bucket_id text, | |
| name text, | |
| owner uuid, | |
| owner_id uuid, | |
| metadata jsonb DEFAULT '{}', | |
| created_at timestamptz DEFAULT now(), | |
| updated_at timestamptz DEFAULT now() | |
| ); | |
| CREATE OR REPLACE FUNCTION storage.foldername(name text) RETURNS text[] | |
| LANGUAGE plpgsql AS \$\$ BEGIN RETURN string_to_array(name, '/'); END; \$\$; | |
| CREATE SCHEMA IF NOT EXISTS extensions; | |
| CREATE EXTENSION IF NOT EXISTS pgcrypto; | |
| CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"; | |
| " && echo "✅ Auth/storage shim created" | |
| - name: 🗄️ Test isotope-complete.sql against PostgreSQL | |
| env: | |
| PGPASSWORD: testpass | |
| run: | | |
| if [ ! -f isotope-complete.sql ]; then | |
| echo "⚠️ isotope-complete.sql not found — skipping execution test" | |
| exit 0 | |
| fi | |
| echo "Stripping Supabase-cloud-only statements..." | |
| grep -v \ | |
| -e 'ALTER PUBLICATION' \ | |
| -e 'supabase_realtime' \ | |
| -e 'CREATE EVENT TRIGGER' \ | |
| -e 'DROP EVENT TRIGGER' \ | |
| -e 'pg_net\.' \ | |
| -e 'http_request' \ | |
| -e 'supabase_admin' \ | |
| -e 'net\.http' \ | |
| isotope-complete.sql > /tmp/schema_test.sql | |
| echo "Running schema against PostgreSQL 16..." | |
| set +e | |
| PSQL_OUT=$(psql -h localhost -U postgres -d isotope_test \ | |
| -v ON_ERROR_STOP=1 \ | |
| -f /tmp/schema_test.sql 2>&1) | |
| EXIT_CODE=$? | |
| set -e | |
| echo "$PSQL_OUT" | tail -40 | |
| echo "" | |
| echo "psql exit code: $EXIT_CODE" | |
| ERROR_COUNT=$(printf '%s\n' "$PSQL_OUT" | grep -c '^ERROR' || true) | |
| WARNING_COUNT=$(printf '%s\n' "$PSQL_OUT" | grep -c '^WARNING' || true) | |
| ERROR_COUNT=${ERROR_COUNT:-0} | |
| WARNING_COUNT=${WARNING_COUNT:-0} | |
| echo " Errors : $ERROR_COUNT" | |
| echo " Warnings: $WARNING_COUNT" | |
| if [ "$ERROR_COUNT" -gt 0 ]; then | |
| echo "" | |
| echo "=== Errors found ===" | |
| echo "$PSQL_OUT" | grep '^ERROR' | |
| fi | |
| echo "" | |
| echo "=== Created objects ===" | |
| psql -h localhost -U postgres -d isotope_test -c " | |
| SELECT table_schema AS schema, count(*) AS tables | |
| FROM information_schema.tables | |
| WHERE table_schema IN ('public', 'auth', 'storage') | |
| GROUP BY table_schema | |
| ORDER BY schema; | |
| " || true | |
| psql -h localhost -U postgres -d isotope_test -c " | |
| SELECT count(*) AS rls_policies | |
| FROM pg_policies | |
| WHERE schemaname = 'public'; | |
| " || true | |
| if [ "$EXIT_CODE" -ne 0 ] || [ "$ERROR_COUNT" -gt 0 ]; then | |
| echo "❌ isotope-complete.sql must execute cleanly against the Supabase compatibility shim" | |
| exit 1 | |
| fi | |
| echo "✅ Schema executed without errors" | |
| - name: 🔍 Validate migration SQL files | |
| env: | |
| PGPASSWORD: testpass | |
| run: | | |
| set -euo pipefail | |
| for f in sql/*.sql; do | |
| if [ -f "$f" ]; then | |
| echo "Testing $f..." | |
| OUT=$(psql -h localhost -U postgres -d isotope_test \ | |
| -v ON_ERROR_STOP=0 \ | |
| -f "$f" 2>&1 || true) | |
| ERRS=$(printf '%s\n' "$OUT" | grep -c '^ERROR' || true) | |
| ERRS=${ERRS:-0} | |
| echo " $f: $ERRS error(s)" | |
| if [ "$ERRS" -gt 0 ]; then | |
| echo "❌ $f failed against the compatibility database" | |
| exit 1 | |
| fi | |
| fi | |
| done | |
| for f in community-patch-v4.sql performance-patch.sql; do | |
| if [ -f "$f" ]; then | |
| echo "Legacy compatibility smoke: $f" | |
| OUT=$(psql -h localhost -U postgres -d isotope_test \ | |
| -v ON_ERROR_STOP=0 \ | |
| -f "$f" 2>&1 || true) | |
| ERRS=$(printf '%s\n' "$OUT" | grep -c '^ERROR' || true) | |
| ERRS=${ERRS:-0} | |
| echo " $f: $ERRS error(s)" | |
| if [ "$ERRS" -gt 20 ]; then | |
| echo "❌ $f has too many compatibility errors" | |
| exit 1 | |
| fi | |
| fi | |
| done | |
| echo "✅ Migration file validation complete" |