auth
auth copied to clipboard
supabase
Issue with migrations
Getting the below error on running the build - created using make build
{"args":[0.014014875],"component":"pop","level":"info","msg":"%.4f seconds","time":"2024-08-20T19:06:21Z"}
{"level":"fatal","msg":"running db migrations: error executing migrations/20240729123726_add_mfa_phone_config.up.sql, sql: do $$ begin\n alter type auth.factor_type add value 'phone';\nexception\n when duplicate_object then null;\nend $$;\n\n\nalter table auth.mfa_factors add column if not exists phone text unique default null;\nalter table auth.mfa_challenges add column if not exists otp_code text null;\n\n\ncreate unique index if not exists unique_verified_phone_factor on auth.mfa_factors (user_id, phone);\n: ERROR: type \"auth.factor_type\" does not exist (SQLSTATE 42704)","time":"2024-08-20T19:06:21Z"}
Below is my .env.example - I have added these variables, please let me know if I am missing anything.
GOTRUE_JWT_SECRET=
GOTRUE_JWT_EXP=
GOTRUE_JWT_AUD=
GOTRUE_JWT_DEFAULT_GROUP_NAME=
GOTRUE_JWT_ADMIN_ROLES=
GOTRUE_DB_DRIVER=
DB_NAMESPACE=
DATABASE_URL=
API_EXTERNAL_URL=
GOTRUE_API_HOST=
PORT=
GOTRUE_SMTP_HOST=
GOTRUE_SMTP_PORT=
GOTRUE_SMTP_USER=
GOTRUE_SMTP_MAX_FREQUENCY=
GOTRUE_SMTP_PASS=
GOTRUE_SMTP_ADMIN_EMAIL=
GOTRUE_SMTP_SENDER_NAME=
# Google OAuth config
GOTRUE_EXTERNAL_GOOGLE_ENABLED=
GOTRUE_EXTERNAL_GOOGLE_CLIENT_ID=
GOTRUE_EXTERNAL_GOOGLE_SECRET=
GOTRUE_EXTERNAL_GOOGLE_REDIRECT_URI=
# Signup config
GOTRUE_DISABLE_SIGNUP=
GOTRUE_SITE_URL=
GOTRUE_EXTERNAL_EMAIL_ENABLED=
GOTRUE_EXTERNAL_PHONE_ENABLED=
GOTRUE_EXTERNAL_IOS_BUNDLE_ID=
GOTRUE_URI_ALLOW_LIST=
# Additional Security config
GOTRUE_LOG_LEVEL=
GOTRUE_SECURITY_REFRESH_TOKEN_ROTATION_ENABLED=
GOTRUE_SECURITY_REFRESH_TOKEN_REUSE_INTERVAL=
GOTRUE_SECURITY_UPDATE_PASSWORD_REQUIRE_REAUTHENTICATION=
GOTRUE_OPERATOR_TOKEN=
GOTRUE_RATE_LIMIT_HEADER=
@siddharth-sable if you're using the docker setup, maybe try clearing the schema and starting from scratch?
Hey @kangmingtay, I am not using docker. Any workaround for local setup?
Hey @siddharth-sable,
Thanks for flagging the issue. Could you try using docker? Once it's downloaded one can run make dev
Alternatively, to continue down the local route if you have no existing data it should be possible to reset the db (e.g. drop the database and recreate).
If that is not an option either you can try running
do $$ begin
create type auth.factor_type as enum('totp', 'webauthn');
exception
when duplicate_object then null;
end $$;
and see if that works for you
Let us know
Hey @siddharth-sable,
I'm going to close the issue for now but if the issue persists feel free to re-open the thread or reach out to us at supabase.help anytime
@kangmingtay Hi there, I'm using Docker and am facing the same issue.
More specifically I'm trying to self-host Supabase with a docker-compose.yml configuration
docker-compose.yml
# Ref: https://github.com/supabase/supabase/blob/master/docker/docker-compose.yml
services:
supabase-db:
image: supabase/postgres:15.6.1.118
container_name: supabase-db
ports:
- "${SUPABASE_DB_PORT}:5432"
volumes:
- supabase-db-data:/var/lib/postgresql/data:Z
healthcheck:
test: pg_isready -U postgres -h localhost
interval: 5s
timeout: 5s
retries: 10
command:
- postgres
- -c
- config_file=/etc/postgresql/postgresql.conf
- -c
- log_min_messages=fatal # prevents Realtime polling queries from appearing in logs
environment:
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_DB: ${POSTGRES_DB}
supabase-auth:
image: supabase/gotrue:v2.160.0
container_name: supabase-auth
ports:
- "${SUPABASE_AUTH_PORT}:9999"
depends_on:
supabase-db:
condition: service_healthy
healthcheck:
test: ["CMD", "curl -f http://localhost:9999/health || exit 1"]
timeout: 5s
interval: 5s
retries: 3
environment:
GOTRUE_DB_DRIVER: "postgres"
GOTRUE_DB_DATABASE_URL: "postgres://postgres:${POSTGRES_PASSWORD}@supabase-db:5432/${POSTGRES_DB}"
GOTRUE_SITE_URL: "http://localhost:3000"
GOTRUE_JWT_SECRET: ${GOTRUE_JWT_SECRET}
API_EXTERNAL_URL: "http://localhost:${SUPABASE_AUTH_PORT}"
supabase-studio:
image: supabase/studio:latest
container_name: supabase-studio
ports:
- "${SUPABASE_STUDIO_PORT}:3000"
depends_on:
supabase-db:
condition: service_healthy
supabase-auth:
condition: service_healthy
healthcheck:
test:
[
"CMD",
"node",
"-e",
"require('http').get('http://localhost:3000/api/profile', (r) => {if (r.statusCode !== 200) throw new Error(r.statusCode)})",
]
timeout: 5s
interval: 5s
retries: 3
environment:
SUPABASE_URL: "http://localhost:${SUPABASE_STUDIO_PORT}"
SUPABASE_KEY: ${SUPABASE_KEY}
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRES_DB: ${POSTGRES_DB}
volumes:
supabase-db-data:
.env
SUPABASE_STUDIO_PORT=...
SUPABASE_DB_PORT=...
SUPABASE_AUTH_PORT=...
POSTGRES_PASSWORD=...
POSTGRES_DB=...
GOTRUE_JWT_SECRET=...
SUPABASE_KEY=...
But running docker-compose up is failing with the same error:
running db migrations: error executing /usr/local/etc/auth/migrations/20240729123726_add_mfa_phone_config.up.sql, sql: do $$ begin
alter type auth.factor_type add value 'phone';
exception
when duplicate_object then null;
end $$;
alter table auth.mfa_factors add column if not exists phone text unique default null;
alter table auth.mfa_challenges add column if not exists otp_code text null;
create unique index if not exists unique_verified_phone_factor on auth.mfa_factors (user_id, phone);
: ERROR: type "auth.factor_type" does not exist (SQLSTATE 42704)
Full docker-compose up log
Attaching to supabase-auth, supabase-db, supabase-studio
supabase-db | The files belonging to this database system will be owned by user "postgres".
supabase-db | This user must also own the server process.
supabase-db |
supabase-db | The database cluster will be initialized with locale "en_US.UTF-8".
supabase-db | The default database encoding has accordingly been set to "UTF8".
supabase-db | The default text search configuration will be set to "english".
supabase-db |
supabase-db | Data page checksums are disabled.
supabase-db |
supabase-db | fixing permissions on existing directory /var/lib/postgresql/data ... ok
supabase-db | creating subdirectories ... ok
supabase-db | selecting dynamic shared memory implementation ... posix
supabase-db | selecting default max_connections ... 100
supabase-db | selecting default shared_buffers ... 128MB
supabase-db | selecting default time zone ... Etc/UTC
supabase-db | creating configuration files ... ok
supabase-db | running bootstrap script ... ok
supabase-db | performing post-bootstrap initialization ... ok
supabase-db | initdb: warning: enabling "trust" authentication for local connections
supabase-db | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
supabase-db | syncing data to disk ... ok
supabase-db |
supabase-db |
supabase-db | Success. You can now start the database server using:
supabase-db |
supabase-db | /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
supabase-db |
supabase-db | waiting for server to start.... done
supabase-db | server started
supabase-db | CREATE DATABASE
supabase-db |
supabase-db |
supabase-db | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/init-scripts
supabase-db |
supabase-db | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/migrate.sh
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/init-scripts/00-schema.sql
supabase-db | CREATE ROLE
supabase-db | REVOKE
supabase-db | CREATE SCHEMA
supabase-db | CREATE FUNCTION
supabase-db | REVOKE
supabase-db | GRANT
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/init-scripts/00000000000000-initial-schema.sql
supabase-db | CREATE PUBLICATION
supabase-db | CREATE ROLE
supabase-db | ALTER ROLE
supabase-db | CREATE ROLE
supabase-db | CREATE ROLE
supabase-db | GRANT ROLE
supabase-db | CREATE SCHEMA
supabase-db | CREATE EXTENSION
supabase-db | CREATE EXTENSION
supabase-db | CREATE EXTENSION
supabase-db | CREATE ROLE
supabase-db | CREATE ROLE
supabase-db | CREATE ROLE
supabase-db | CREATE ROLE
supabase-db | GRANT ROLE
supabase-db | GRANT ROLE
supabase-db | GRANT ROLE
supabase-db | GRANT ROLE
supabase-db | GRANT
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | GRANT
supabase-db | ALTER ROLE
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER ROLE
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/init-scripts/00000000000001-auth-schema.sql
supabase-db | CREATE SCHEMA
supabase-db | CREATE TABLE
supabase-db | CREATE INDEX
supabase-db | CREATE INDEX
supabase-db | COMMENT
supabase-db | CREATE TABLE
supabase-db | CREATE INDEX
supabase-db | CREATE INDEX
supabase-db | CREATE INDEX
supabase-db | COMMENT
supabase-db | CREATE TABLE
supabase-db | COMMENT
supabase-db | CREATE TABLE
supabase-db | CREATE INDEX
supabase-db | COMMENT
supabase-db | CREATE TABLE
supabase-db | COMMENT
supabase-db | INSERT 0 7
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | GRANT
supabase-db | CREATE ROLE
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER ROLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/init-scripts/00000000000002-storage-schema.sql
supabase-db | CREATE SCHEMA
supabase-db | GRANT
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | CREATE TABLE
supabase-db | CREATE INDEX
supabase-db | CREATE TABLE
supabase-db | CREATE INDEX
supabase-db | CREATE INDEX
supabase-db | ALTER TABLE
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE TABLE
supabase-db | CREATE ROLE
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER ROLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/init-scripts/00000000000003-post-setup.sql
supabase-db | ALTER ROLE
supabase-db | ALTER ROLE
supabase-db | CREATE FUNCTION
supabase-db | CREATE EVENT TRIGGER
supabase-db | COMMENT
supabase-db | CREATE FUNCTION
supabase-db | COMMENT
supabase-db | DO
supabase-db | CREATE ROLE
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/00-extension.sql
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/00-extension.sql:1: NOTICE: schema "extensions" already exists, skipping
supabase-db | CREATE SCHEMA
supabase-db | CREATE EXTENSION
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/10000000000000_demote-postgres.sql
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20211115181400_update-auth-permissions.sql
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | ALTER TABLE
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20211118015519_create-realtime-schema.sql
supabase-db | CREATE SCHEMA
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20211122051245_update-realtime-permissions.sql
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20211124212715_update-auth-owner.sql
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20211130151719_update-realtime-permissions.sql
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220118070449_enable-safeupdate-postgrest.sql
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220126121436_finer-postgrest-triggers.sql
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220126121436_finer-postgrest-triggers.sql:3: NOTICE: event trigger "api_restart" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220126121436_finer-postgrest-triggers.sql:4: NOTICE: function extensions.notify_api_restart() does not exist, skipping
supabase-db | DROP FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220126121436_finer-postgrest-triggers.sql:59: NOTICE: event trigger "pgrst_ddl_watch" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220126121436_finer-postgrest-triggers.sql:64: NOTICE: event trigger "pgrst_drop_watch" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220224211803_fix-postgrest-supautils.sql
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220317095840_pg_graphql.sql
supabase-db | CREATE SCHEMA
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220317095840_pg_graphql.sql:5: NOTICE: function graphql_public.graphql(text,text,jsonb) does not exist, skipping
supabase-db | DROP FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | GRANT
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | CREATE FUNCTION
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220317095840_pg_graphql.sql:90: NOTICE: event trigger "issue_pg_graphql_access" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | COMMENT
supabase-db | CREATE FUNCTION
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220317095840_pg_graphql.sql:143: NOTICE: event trigger "issue_graphql_placeholder" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | COMMENT
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220321174452_fix-postgrest-alter-type-event-trigger.sql
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220321174452_fix-postgrest-alter-type-event-trigger.sql:3: NOTICE: event trigger "api_restart" does not exist, skipping
supabase-db | DROP EVENT TRIGGER
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220321174452_fix-postgrest-alter-type-event-trigger.sql:4: NOTICE: function extensions.notify_api_restart() does not exist, skipping
supabase-db | DROP FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220322085208_gotrue-session-limit.sql
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220404205710_pg_graphql-on-by-default.sql
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | CREATE FUNCTION
supabase-db | psql:/docker-entrypoint-initdb.d/migrations/20220404205710_pg_graphql-on-by-default.sql:142: NOTICE: extension "pg_graphql" does not exist, skipping
supabase-db | DROP EXTENSION
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220609081115_grant-supabase-auth-admin-and-supabase-storage-admin-to-postgres.sql
supabase-db | GRANT ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220613123923_pg_graphql-pg-dump-perms.sql
supabase-db | CREATE FUNCTION
supabase-db | DROP EXTENSION
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20220713082019_pg_cron-pg_net-temp-perms-fix.sql
supabase-db | DO
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20221028101028_set_authenticator_timeout.sql
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20221103090837_revoke_admin.sql
supabase-db | REVOKE ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20221207154255_create_pgsodium_and_vault.sql
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20230201083204_grant_auth_roles_to_postgres.sql
supabase-db | GRANT ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20230224042246_grant_extensions_perms_for_postgres.sql
supabase-db | GRANT
supabase-db | GRANT
supabase-db | GRANT
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | ALTER DEFAULT PRIVILEGES
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20230306081037_grant_pg_monitor_to_postgres.sql
supabase-db | GRANT ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20230327032006_grant_auth_roles_to_supabase_storage_admin.sql
supabase-db | GRANT ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20230529180330_alter_api_roles_for_inherit.sql
supabase-db | ALTER ROLE
supabase-db | ALTER ROLE
supabase-db | ALTER ROLE
supabase-db | GRANT ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20231013070755_grant_authenticator_to_supabase_storage_admin.sql
supabase-db | GRANT ROLE
supabase-db | REVOKE ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20231017062225_grant_pg_graphql_permissions_for_custom_roles.sql
supabase-db | CREATE FUNCTION
supabase-db | DROP EXTENSION
supabase-db | DO
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20231020085357_revoke_writes_on_cron_job_from_postgres.sql
supabase-db | DO
supabase-db | CREATE FUNCTION
supabase-db | DROP EVENT TRIGGER
supabase-db | CREATE EVENT TRIGGER
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20231130133139_set_lock_timeout_to_authenticator_role.sql
supabase-db | ALTER ROLE
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20240124080435_alter_lo_export_lo_import_owner.sql
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | ALTER FUNCTION
supabase-db | /docker-entrypoint-initdb.d/migrate.sh: running /docker-entrypoint-initdb.d/migrations/20240606060239_grant_predefined_roles_to_postgres.sql
supabase-db | GRANT ROLE
supabase-db | pg_stat_statements_reset
supabase-db | --------------------------
supabase-db |
supabase-db | (1 row)
supabase-db |
supabase-db | pg_stat_reset
supabase-db | ---------------
supabase-db |
supabase-db | (1 row)
supabase-db |
supabase-db |
supabase-db | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/migrations
supabase-db |
supabase-db | waiting for server to shut down.... 2024-09-10 07:37:41.832 UTC [60] FATAL: terminating background worker "TimescaleDB Background Worker Launcher" due to administrator command
supabase-db | 2024-09-10 07:37:41.832 UTC [62] LOG: pg_cron scheduler shutting down
supabase-db | done
supabase-db | server stopped
supabase-db |
supabase-db | PostgreSQL init process complete; ready for start up.
supabase-db |
supabase-auth | {"level":"info","msg":"Go runtime metrics collection started","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["init_auth_schema"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["alter_users"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["adds_confirmed_at"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["add_email_change_confirmed"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["create_identities_table"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:45Z"}
supabase-auth | {"args":["add_refresh_token_parent"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["create_user_id_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["update_auth_functions"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["update_auth_uid"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["update_user_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_banned_until"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["update_auth_functions"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_user_reauthentication"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_unique_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_auth_jwt_function"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_ip_address_to_audit_log"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_sessions_table"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_mfa_schema"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_aal_and_factor_id_to_sessions"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_mfa_indexes"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_sessions_user_id_index"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_refresh_tokens_session_id_revoked_index"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_saml"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_identities_user_id_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_session_not_after_column"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["remove_parent_foreign_key_refresh_tokens"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["backfill_email_identity"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["backfill_email_last_sign_in_at"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["modify_users_email_unique_index"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_identities_email_column"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["remove_sso_sessions"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["alter_phone_type"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_deleted_at"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["backfill_invite_identities"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_flow_state_table"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_authentication_method_to_flow_state_table"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["remove_duplicate_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_cleanup_indexes"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_mfa_challenge_cleanup_index"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_flow_state_to_relay_state"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_mfa_factors_user_id_idx"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_session_refresh_columns"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_sessions_tag"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_id_pkey_identities"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["remove_ip_address_from_saml_relay_state"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_is_anonymous_column"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_issued_at_to_flow_state"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_saml_name_id_format"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["add_one_time_tokens_table"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":["enable_rls_update_grants"],"component":"pop","level":"info","msg":"\u003e %s","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"args":[0.187794667],"component":"pop","level":"info","msg":"%.4f seconds","time":"2024-09-10T07:37:46Z"}
supabase-auth | {"level":"fatal","msg":"running db migrations: error executing /usr/local/etc/auth/migrations/20240729123726_add_mfa_phone_config.up.sql, sql: do $$ begin\n alter type auth.factor_type add value 'phone';\nexception\n when duplicate_object then null;\nend $$;\n\n\nalter table auth.mfa_factors add column if not exists phone text unique default null;\nalter table auth.mfa_challenges add column if not exists otp_code text null;\n\n\ncreate unique index if not exists unique_verified_phone_factor on auth.mfa_factors (user_id, phone);\n: ERROR: type \"auth.factor_type\" does not exist (SQLSTATE 42704)","time":"2024-09-10T07:37:46Z"}
supabase-auth exited with code 1
Gracefully stopping... (press Ctrl+C again to force)
dependency failed to start: container supabase-auth exited (1)
Full clear of everything docker volume rm <name> doesn't help.
I am having a similar same issue, I ran a db upgrade today from the dashboard and since then the a bunch of services stopped working.
Trying to start Supabase locally I am getting:
running db migrations: error executing /usr/local/etc/auth/migrations/20240729123726_add_mfa_phone_config.up.sql, sql: do $$ begin\n
alter type auth.factor_type add value 'phone';\nexception\n
when duplicate_object then null;\nend $$;\n\n\nalter table auth.mfa_factors
add column if not exists phone text unique default null;\nalter table auth.mfa_challenges add column if not exists otp_code text null;\n\n\ncreate unique index
if not exists unique_verified_phone_factor on auth.mfa_factors (user_id, phone);\n: ERROR: must be owner of type factor_type (SQLSTATE 42501)","time":"2024-09-11T21:3...
This error means you used the wrong user for migration or maybe don't have permissions on that action Remember to run init_postgres, to create supabase_auth, etc... Then connect to db supabase_auth_admin follow sample env docker file I have the same issue because I don't realize that p/s: I deploy in rds aws so I can't run the init script directly, so I run by SQL console in db manager app (pgadmin,...) through default user postgres with some adjustment
CREATE USER supabase_admin LOGIN CREATEROLE CREATEDB BYPASSRLS;
-- adjustment to make postgres user can do below action
grant supabase_admin to postgres;
grant supabase_auth_admin to postgres;
-- Supabase super admin
CREATE USER supabase_auth_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION PASSWORD 'root';
CREATE SCHEMA IF NOT EXISTS auth AUTHORIZATION supabase_auth_admin;
GRANT CREATE ON DATABASE postgres TO supabase_auth_admin;
ALTER USER supabase_auth_admin SET search_path = 'auth';
I've run into this issue also... trying to run on AWS RDS also...
The problem is related to how enum types are created in PostgreSQL. Looking at the search results, I can see that the factor_type enum is supposed to be created in migration 20221003041349_add_mfa_schema.up.sql. This migration creates the enum type as: ; However, in your migration that's failing (20240729123726_add_mfa_phone_config.up.sql), it's trying to reference the type as auth.factor_type (with the schema prefix).
This is the key difference: The original migration creates the type in the current schema without the auth. prefix Your failing migration is trying to modify it with the auth. prefix The reason other migrations might work is that they're likely operating on tables (which properly use the schema prefix through the templating system) rather than types. To fix this, you should modify the migration to remove the schema prefix from the type name. The migration should be: ; This is happening because PostgreSQL enum types are created in a different way than tables. When you create an enum type, it's created in the current schema, and when referencing it later, you typically don't need (and sometimes shouldn't use) the schema prefix.
In my case the problem was that supabase_auth_admin should have SET search_path = 'auth' so queries without a schema prefix would not be used over public schema.
In other words, check if that's type isn't present in the public scheme.
I fixed this issue locally, after reading:
Then connect to db supabase_auth_admin follow sample env docker ...
I realized I was using the default postgres user to connect to the DB (in DATABASE_URL), that was of course not using the search path. After using supabase_auth_admin user instead things started working.
Thank you @daklod2k3 :)
Hi, I am trying to create a self hosting on AWS with all the standard versions of the supabase docker containers and running into this issue. The auth container is getting this error. Should I add
supabase_auth_admin should have SET search_path = 'auth' in the auth container Env variables? I tried setting auth container to use supabase_auth_admin for GOTRUE_DB_DATABASE_URL but now it says
could not create new transaction: failed to connect to host=postgres.supabase.dev.local user=supabase_auth_admin database=postgres: failed SASL auth (FATAL: password authentication failed for user \"supabase_auth_admin\" (SQLSTATE 28P01)
In my case the problem was that supabase_auth_admin should have SET search_path = 'auth' so queries without a schema prefix would not be used over public schema.
In other words, check if that's type isn't present in the public scheme.
THANK YOU SO MUCH I WAS GOING CRAZY!!!
