External DB init error

[muellerelias]

Bug report

Describe the bug

On a self-hosted Supabase deployment, the auth.uid() / auth.role() /
auth.email() helper functions installed by the init/bootstrap SQL only read
the legacy per-claim GUCs (request.jwt.claim.sub, etc.). Modern PostgREST
(v9+, here 14.8) no longer sets those — it only sets the aggregated JSON GUC
request.jwt.claims. As a result auth.uid() returns NULL for every
authenticated request, so every RLS policy of the form
user_id = auth.uid() matches zero rows. All tables (profiles, report_labels,
reports, histories) become effectively unreadable/unwritable for authenticated
users, even though the JWT is valid and verified.

The init should ship the official coalesce-based definition that supports both
the old per-claim GUC and the new request.jwt.claims JSON, so it stays
compatible with the bundled PostgREST version.

To Reproduce

1. Deploy self-hosted Supabase (Helm chart, PostgREST 14.8) and create the
   standard RLS policies, e.g.:

   create policy "profiles_select_own" on profiles
     for select using (user_id = auth.uid());

2. Sign in a normal user (GoTrue password grant) and call REST with the user's
   access token (NOT the postgres/service_role key — those bypass RLS):

   curl -s -H "apikey: $PUB" -H "Authorization: Bearer $USER_TOKEN" \
     "$URL/rest/v1/profiles?select=id"

   → returns [] although a matching row exists (visible in Studio, which runs
   as postgres and bypasses RLS).
3. Diagnose what the DB sees for that request:

   create or replace function public.debug_jwt() returns jsonb
   language sql stable as $$
     select jsonb_build_object(
       'uid', auth.uid(),
       'role', auth.role(),
       'claims', current_setting('request.jwt.claims', true)
     );
   $$;
   grant execute on function public.debug_jwt() to anon, authenticated;

   curl -s -X POST "$URL/rest/v1/rpc/debug_jwt" \
     -H "apikey: $PUB" -H "Authorization: Bearer $USER_TOKEN"

   Actual output:

   {
     "uid": null,
     "role": null,
     "claims": "{\"sub\":\"ae971efe-...\",\"role\":\"authenticated\",\"aud\":\"authenticated\", ...}"
   }

   → request.jwt.claims is populated (contains sub and role), but
   auth.uid() and auth.role() are NULL, proving the helper functions read
   the wrong (now-empty) GUC.

Expected behavior

auth.uid() should return the sub claim of the verified JWT regardless of how
the bundled PostgREST version exposes claims, so RLS policies like
user_id = auth.uid() work out of the box. The init/bootstrap SQL should install
the GUC-compatible definition:

create or replace function auth.uid() returns uuid language sql stable as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.sub', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
  )::uuid
$$;

create or replace function auth.role() returns text language sql stable as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.role', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'role')
  )
$$;

create or replace function auth.email() returns text language sql stable as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.email', true), ''),
    (nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
  )
$$;

Applying the above immediately fixes debug_jwt (uid becomes the user's UUID)
and all RLS policies start returning the correct rows — no policy or schema-cache
changes required.

Screenshots

N/A (CLI output included above).

System information

• OS: Linux (Kubernetes, self-hosted)
• Deployment: self-hosted Supabase via Helm chart on StackGres
• PostgreSQL: 17.4 (StackGres)
• PostgREST: 14.8
• GoTrue (Auth): v2.186.0
• JWT: HS256 (symmetric, shared JWT_SECRET)
• Client: supabase-flutter 2.14.2 (Brick offline-first), but reproducible with
  plain curl as shown

Additional context

• The standard/managed Supabase stack and the Supabase CLI ship the
  coalesce-based auth.uid(), so the issue does not appear there — it only
  surfaces on hand-assembled self-hosted stacks where an older auth.uid()
  definition is paired with a newer PostgREST.
• Studio (role postgres) bypasses RLS, so the rows look present there, which
  makes the bug easy to misattribute to the app/policies rather than to
  auth.uid().
• Root cause is the PostgREST change from per-claim GUCs (request.jwt.claim.*)
  to the aggregated request.jwt.claims JSON; the init SQL must use a definition
  that reads the JSON (with a fallback) to stay version-compatible.

[aantti]

Looks like an issue in how the external database is being initialized and the related migration in Auth that runs prior (migrations/20220224000811_update_auth_functions.up.sql)? Cc @luizfelmach

[luizfelmach]

Hey @muellerelias, thanks for the detailed bug report, the diagnosis you provided made it much easier to trace the root cause.

This should now be fixed by #213, which removes the external database auto-initialization logic entirely.

What was happening

The chart shipped a post-install,post-upgrade Job that ran SQL scripts (including 01-auth-schema.sql) directly against the external database on every helm upgrade. That script contained a legacy definition of auth.uid() that only read the old per-claim GUCs (request.jwt.claim.sub, etc.) and ignored the new aggregated request.jwt.claims JSON that PostgREST 9+ sets.

GoTrue applies its own migrations on startup and tracks them in schema_migrations. When the auth pod first starts, GoTrue installs the correct coalesce-based definition of auth.uid() and marks that migration as done. However, any subsequent helm upgrade re-triggered the init Job, which blindly ran CREATE OR REPLACE FUNCTION auth.uid() ... with the outdated definition. Since GoTrue's migration was already recorded, it would never re-run, leaving the database permanently broken.

This is the exact sequence you observed:

1. helm install → init Job writes legacy auth.uid() → NULL
2. Auth pod starts → GoTrue migrates to correct definition → works
3. helm upgrade → init Job overwrites back to legacy → NULL again
4. Auth pod restarts → migration already applied, skips → stays broken

The fix

PR #213 removes the init Job, ConfigMap, and all bundled SQL scripts for external databases. The chart now connects Supabase services to your external database without running any migrations, schema creation, or role management. This eliminates the conflict between chart-provided SQL and the canonical migrations run by each Supabase service (GoTrue, Storage, Realtime, etc.).

Alignment with the official setup

This approach aligns with how the official Supabase docker setup handles external databases. The docker compose does not attempt to run migrations, create roles, or manage grants for you, it expects the database to be pre-configured. The chart now follows the same pattern: it connects the services to your database and lets each Supabase service apply its own migrations on startup.

[kastakhov]

Hi @luizfelmach,

I see an issue here. In my case, I am using the official supabase/postgres image. This is the same database image used by the chart.

However, I deploy the database separately because I use the CNPG Operator to manage the database lifecycle, backups, switchovers, and related operations.

As a result, I need to run the migrations included in the official Supabase self-hosted Docker example. The chart already includes these migrations, but it only runs them for the built-in database. I do not see a reason to manage these migrations separately when the chart already provides them.

The chart should provide an option to run its built-in migrations against an external database as well.

[luizfelmach]

I need more time to think about this. For now, if you are running this with an external supabase/postgres image, you can run these migrations manually (you only need to do this once).

I'll leave this issue open.