Self-host Supabase on external host Postgres (bacmr_rag)

Goal: run BacMR unchanged while the data lives in the aaPanel PG16 cluster database bacmr_rag (durable, survives any Docker wipe). The app only speaks the Supabase REST/RPC/Auth/Storage API, so we run a trimmed self-hosted Supabase stack in Docker and point it at the external host Postgres.

Stack (only what the app uses): Kong (gateway) + GoTrue (auth) + PostgREST (data/rpc) + Storage. No Realtime, Studio, pg_graphql, or bundled Postgres. Artifacts live in infra/supabase/, db/supabase/.

No backend code change is required. Auth is verified via GoTrue (supabase.auth.get_user), data/rpc via PostgREST, storage via the Storage API — all self-hosted-compatible. Only .env changes (keys + SUPABASE_URL).

---

⚠️ Ordering correction vs. the naive 5-step plan

The naive order was: roles → migrations → bring up stack. But two app migrations depend on the service-owned schemas that GoTrue/Storage create on their first boot:

• 20260216000007_profiles_trigger.sql → trigger on auth.users (GoTrue)
• 20260223000027_storage_buckets.sql → inserts into storage.buckets (Storage)

So the correct order is roles → start auth+storage (they self-migrate) → app schema → start gateway → point app:

#	Step	Who	Command
1	Enable pgvector + roles + auth helpers in bacmr_rag	You (superuser)	00_roles.sql, 01_auth_helpers.sql
2	Host PG: allow docker → host connections	You (host config)	pg_hba.conf + listen_addresses
3	Generate keys, fill both env files	You (paste) / me (template)	gen_keys.py
4	Start GoTrue + Storage (creates auth.*, storage.*)	You / me	docker compose ... up -d auth storage
5	Apply app schema + migrations	You (DB mutation)	db/supabase/apply_schema.sh
6	Start the rest of the stack (Kong, PostgREST)	You / me	docker compose ... up -d
7	Point backend .env at the local gateway	You (paste)	see below
8	(optional) wire the custom-access-token hook	You	GoTrue hook env

---

Step 1 — pgvector + roles + auth helpers (superuser)

Pick three role passwords first; they must match infra/supabase/.env.

cd /home/mido/Projects/BacMR/backend
sudo -u postgres /www/server/pgsql/bin/psql -d bacmr_rag \
  -v authenticator_pw="'<AUTHENTICATOR_PASSWORD>'" \
  -v auth_admin_pw="'<AUTH_ADMIN_PASSWORD>'" \
  -v storage_admin_pw="'<STORAGE_ADMIN_PASSWORD>'" \
  -f db/supabase/00_roles.sql
sudo -u postgres /www/server/pgsql/bin/psql -d bacmr_rag -f db/supabase/01_auth_helpers.sql

This enables vector (Step 1), creates the Supabase roles, the auth/storage schemas, and the auth.uid()/role()/jwt()/email() helpers the RLS policies need.

Step 2 — let the containers reach host Postgres

The containers connect to host.docker.internal (the docker bridge gateway). The host PG must listen there and allow it.

• postgresql.conf: listen_addresses = '*' (or add the bridge IP 172.17.0.1).
• pg_hba.conf: allow the docker bridge subnet, e.g.

  host  bacmr_rag  authenticator,supabase_auth_admin,supabase_storage_admin  172.16.0.0/12  scram-sha-256
  

• Reload: sudo -u postgres /www/server/pgsql/bin/pg_ctl reload (or via aaPanel).

aaPanel firewall: ensure 5432 is reachable from docker (loopback/bridge only — do not expose 5432 publicly).

Step 3 — keys + env

python3 infra/supabase/gen_keys.py        # prints JWT_SECRET / ANON_KEY / SERVICE_ROLE_KEY
cp infra/supabase/.env.example infra/supabase/.env
# edit infra/supabase/.env: paste the 3 keys + the 3 role passwords from Step 1

Step 4 — start auth + storage (they run their own migrations)

docker compose -f infra/supabase/docker-compose.yml --env-file infra/supabase/.env up -d auth storage
docker compose -f infra/supabase/docker-compose.yml logs -f auth storage   # watch migrations finish

After this, auth.users and storage.* exist in bacmr_rag.

Step 5 — apply the app schema (DB mutation — you run it)

export DATABASE_URL='postgresql://postgres@127.0.0.1:5432/bacmr_rag'
./db/supabase/apply_schema.sh

Step 6 — start the gateway + PostgREST

docker compose -f infra/supabase/docker-compose.yml --env-file infra/supabase/.env up -d
docker compose -f infra/supabase/docker-compose.yml ps

Step 7 — point the backend at the local stack

Set these in the backend .env (the values come from gen_keys.py):

SUPABASE_URL=http://localhost:8000
SUPABASE_SERVICE_ROLE_KEY=<SERVICE_ROLE_KEY>
SUPABASE_JWT_SECRET=<JWT_SECRET>
STORAGE_BACKEND=supabase

If the backend runs in its own container (docker-compose.yml at repo root) rather than on the host, use http://host.docker.internal:8000 (or join it to the same docker network and use http://kong:8000).

Step 8 — (optional) custom access-token hook

Migration 20260217000018 adds role to the JWT via a Postgres hook; the app reads app_metadata.role with a user_metadata fallback, so it works without the hook but defaults non-hooked users to student. To enable, point GoTrue at the hook function (uncomment GOTRUE_HOOK_CUSTOM_ACCESS_TOKEN_* in the compose; confirm the function name created by that migration) and recreate the auth container.

---

Resolved iteration points (what actually happened on first bring-up)

All now baked into the committed artifacts; listed so the history is clear:

1. pgvector + pgcrypto were source-built into the aaPanel PG16 cluster. The build ships neither in contrib; pgcrypto needs OpenSSL at link time but the cluster was compiled --without-openssl, so it was rebuilt with make USE_PGXS=1 PG_CONFIG=/www/server/pgsql/bin/pg_config SHLIB_LINK='-lcrypto -lz' install. Migrations 28/29 use digest('sha256'); 00_roles.sql only enables vector.
2. :var substitution does not work inside DO $$ … $$ blocks — role passwords are set via top-level ALTER ROLE … PASSWORD :'var'. (00_roles.sql fixed.)
3. GoTrue/Storage migration tables landed in public and failed (PG15+ forbids non-owner CREATE in public). Fixed with ALTER ROLE … SET search_path to each service's own schema, plus GRANT CREATE ON DATABASE for Storage. (00_roles.sql.)
4. references is a reserved keyword. Several migrations used the table name bare (ALTER TABLE references, REFERENCES references(id), ON references, COMMENT ON COLUMN references.x). All quoted to "references"; schema-qualified public.references already parses and was left as-is.
5. bootstrap.sql vs migrations — apply MIGRATIONS ONLY (see apply_schema.sh header); bootstrap is a conflicting tables-only snapshot.
6. Migration 27 storage policies weren't idempotent — added DROP POLICY IF EXISTS before each CREATE POLICY ON storage.objects.
7. Kong declarative config — _format_version must be SINGLE-quoted (the entrypoint eval strips double quotes), and the acl plugin field is hide_groups_header, not hidden_groups. (kong.yml fixed.)
8. handle_new_user trigger inserted text into the now-enum profiles.role — every signup failed with 42804. Migration 33 now re-creates the function casting ... ->> 'role' to ::user_role (the type only exists from migration 33 onward).

Pinned images: gotrue v2.158.1, postgrest v12.2.3, storage v1.11.13, kong 2.8.1.

Verified working

auth/v1/health → 200; PostgREST via Kong with the service key → 200; missing apikey → 401; anon key → 200; chunks.embedding vector(1536) + HNSW index + the search_chunks_hybrid RPC all present and exposed in PostgREST's schema cache.

The backend itself (run in Docker via docker-compose.override.yml, which adds host.docker.internal:host-gateway, with SUPABASE_URL=http://host.docker.internal:8000) boots clean and round-trips: GET /health → 200; POST /auth/signup creates the auth.users row + trigger-driven profiles (role=student enum) + wallet (500 tokens); POST /auth/signin returns a valid access token.