[Bug] Supabase Auth / gotrue migration 20240612123726 fails on Postgres container without postgres role

[almereyda]

Describe the bug

The Supabase Auth / gotrue migration https://github.com/supabase/auth/blob/master/migrations/20240612123726_enable_rls_update_grants.up.sql hard codes a postgres role, which is non-existent on Postgres containers which set the POSTGRES_USER variable.

To Reproduce

Steps used to reproduce the behavior in with #622:

1. docker compose pull
2. docker compose rm -sf
3. docker compose build
4. docker compose up -d
5. docker compose logs gotrue

Expected behavior

All migrations run.

Evidence

{"level":"fatal","msg":"running db migrations: error executing migrations/20240612123726_enable_rls_update_grants.up.sql, sql: do $$ begin\n    -- enable RLS policy on auth tables\n    alter table auth.schema_migrations enable row level security;\n    alter table auth.instances enable row level security;\n    alter table auth.users enable row level security;\n    alter table auth.audit_log_entries enable row level security;\n    alter table auth.saml_relay_states enable row level security;\n    alter table auth.refresh_tokens enable row level security;\n    alter table auth.mfa_factors enable row level security;\n    alter table auth.sessions enable row level security;\n    alter table auth.sso_providers enable row level security;\n    alter table auth.sso_domains enable row level security;\n    alter table auth.mfa_challenges enable row level security;\n    alter table auth.mfa_amr_claims enable row level security;\n    alter table auth.saml_providers enable row level security;\n    alter table auth.flow_state enable row level security;\n    alter table auth.identities enable row level security;\n    alter table auth.one_time_tokens enable row level security;\n    -- allow postgres role to select from auth tables and allow it to grant select to other roles\n    grant select on auth.schema_migrations to postgres with grant option;\n    grant select on auth.instances to postgres with grant option;\n    grant select on auth.users to postgres with grant option;\n    grant select on auth.audit_log_entries to postgres with grant option;\n    grant select on auth.saml_relay_states to postgres with grant option;\n    grant select on auth.refresh_tokens to postgres with grant option;\n    grant select on auth.mfa_factors to postgres with grant option;\n    grant select on auth.sessions to postgres with grant option;\n    grant select on auth.sso_providers to postgres with grant option;\n    grant select on auth.sso_domains to postgres with grant option;\n    grant select on auth.mfa_challenges to postgres with grant option;\n    grant select on auth.mfa_amr_claims to postgres with grant option;\n    grant select on auth.saml_providers to postgres with grant option;\n    grant select on auth.flow_state to postgres with grant option;\n    grant select on auth.identities to postgres with grant option;\n    grant select on auth.one_time_tokens to postgres with grant option;\nend $$;\n: ERROR: role \"postgres\" does not exist (SQLSTATE 42704)","time":"2024-09-24T13:40:55Z"}

Workaround

A monkey patched workaround with using a static user name (and not environmental variable substitution) with the mentioned Compose setup reads the following:

mkdir gotrue-migrations
wget -P gotrue-migrations/ https://github.com/supabase/auth/raw/refs/heads/master/migrations/20240612123726_enable_rls_update_grants.up.sql
sed 's/postgres/supabase_auth_admin/g' -i migrations/20240612123726_enable_rls_update_grants.up.sql

Together with

83a84,85
>     volumes:
>       - ./gotrue-migrations/20240612123726_enable_rls_update_grants.up.sql:/migrations/20240612123726_enable_rls_update_grants.up.sql

in the gotrue service of the Compose manifest, this allows us to run the migration successfully:

$ docker-compose up gotrue    
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
appflowyallmendeio_postgres_1 is up-to-date
Recreating appflowyallmendeio_gotrue_1 ... done
Attaching to appflowyallmendeio_gotrue_1
gotrue_1            | {"level":"info","msg":"Go runtime metrics collection started","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":["enable_rls_update_grants"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":["add_mfa_phone_config"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":["add_mfa_factors_column_last_challenged_at"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":["drop_uniqueness_constraint_on_phone"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":[4],"component":"pop","level":"info","msg":"Successfully applied %d migrations.","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"args":[0.127852464],"component":"pop","level":"info","msg":"%.4f seconds","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"level":"info","msg":"GoTrue migrations applied successfully","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"component":"api","level":"warning","msg":"DEPRECATION NOTICE: GOTRUE_JWT_ADMIN_GROUP_NAME not supported by Supabase's GoTrue, will be removed soon","time":"2024-09-24T14:05:47Z"}
gotrue_1            | {"level":"info","msg":"GoTrue API started on: :9999","time":"2024-09-24T14:05:47Z"}

This could eventually be upstreamed to supabase/auth, asking for parametrisation with an environmental variable.

[khorshuheng]

Thanks @almereyda , i originally thought that this is something that we could have fixed by modifying the migration script on our end, just realized that this is being hard coded on supabase-auth side as well.

Will need some time to figure out how we can patch this on our patched-gotrue service (not sure if it is actually possible). Otherwise monkey patching is probably our only solution.

[khorshuheng]

Actually, perhaps the suggestion that you proposed in https://github.com/AppFlowy-IO/AppFlowy-Cloud/issues/823#issuecomment-2380247083 will work. Will investigate this.

[almereyda]

Yes, it's this line in the above example:

sed 's/postgres/supabase_auth_admin/g' -i migrations/20240612123726_enable_rls_update_grants.up.sql

[khorshuheng]

Fixed in appflowyinc/gotrue:0.6.48.