Fresh self-hosted Docker instance fails due to database users' passwords not being set

Question

Fresh self-hosted Docker instance fails due to database users' passwords not being set

krysialynne opened this issue 2 years ago · 18 comments

Bug report

I confirm this is a bug with Supabase, not with my own application.
I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When running a new self-hosted instance of Supabase with docker compose, the supabase-analytics container fails to start. Examining the docker compose logs for supabase-analytics reveals:

Kernel pid terminated (application_controller) ({application_start_failure,logflare,{{shutdown,{failed_to_start_child,'Elixir.Cainophile.Adapters.Postgres',invalid_password}},{'Elixir.Logflare.Application',start,[normal,[]]}}})

Crash dump is being written to: erl_crash.dump...done
sh: 19:50:15.648: not found
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 10973ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

  1. Ensuring your database is available and that you can connect to it
  2. Tracking down slow queries and making sure they are running fast enough
  3. Increasing the pool_size (although this increases resource consumption)
  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

See DBConnection.start_link/2 for more information

Checking the supabase-db container logs reveals:

172.18.0.3 2023-11-08 19:53:42.971 UTC [2578] supabase_admin@postgres FATAL:  password authentication failed for user "supabase_admin"
172.18.0.3 2023-11-08 19:53:42.971 UTC [2578] supabase_admin@postgres DETAIL:  User "supabase_admin" has no password assigned.
        Connection matched pg_hba.conf line 89: "host  all  all  172.16.0.0/12  scram-sha-256"

If I exec into supabase-db and run echo $POSTGRES_PASSWORD, the correct password is printed. This error occurs regardless of whether the default values in .env have been changed.

Logging into the postgres shell as user 'postgres' and running \du shows the following users configured on the database:

 List of roles
         Role name          |                         Attributes                         |                    Member of
----------------------------+------------------------------------------------------------+--------------------------------------------------
 anon                       | No inheritance, Cannot login                               | {}
 authenticated              | No inheritance, Cannot login                               | {}
 authenticator              | No inheritance                                             | {supabase_admin,anon,authenticated,service_role}
 dashboard_user             | Create role, Create DB, Cannot login, Replication          | {}
 pgbouncer                  |                                                            | {}
 postgres                   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 service_role               | No inheritance, Cannot login, Bypass RLS                   | {}
 supabase_admin             | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 supabase_auth_admin        | No inheritance, Create role                                | {}
 supabase_read_only_user    | Bypass RLS                                                 | {pg_read_all_data}
 supabase_replication_admin | Replication                                                | {}
 supabase_storage_admin     | No inheritance, Create role                                | {}

Note that supabase_functions_admin is not listed here while it is present in roles.sql.
If I manually set the passwords for each user listed in roles.sql and then run docker compose restart, the db and analytics containers build correctly but the auth container fails. Logs indicate an error while running the 00_init_auth_schema.up.sql: ERROR: must be owner of function uid

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Clone the supabase repository with git clone --depth 1 https://github.com/supabase/supabase
cd into supabase/docker and copy environment vars with cp .env.example .env
Pull the latest images with docker compose pull
Start the services with docker compose up -d

Expected behavior

supabase-database container should be configured correctly with the accounts listed in roles.sql and password from .env file.

System information

OS: Red Hat 8

Answer 1 · 2023-11-09T11:04:06.000Z

Hello, I've had same issue, take a look at solutions proposed there: supabase/issues/11957
For me, after a deep cleaning, install Supabase CLI and a supabase start in docker folder all was starting ok. Happy coding

Answer 2 · 2023-11-09T14:44:30.000Z

Hello, I've had same issue, take a look at solutions proposed there: supabase/issues/11957 For me, after a deep cleaning, install Supabase CLI and a supabase start in docker folder all was starting ok. Happy codingsupab

Yeah, I combed through some past issues and tried a few different proposed solutions before submitting this one:

Fully deleted the repo, ran docker system prune -a -f --volumes, and re-cloned the repo
After initial failed docker compose up -d, deleted volumes/db/data and ran docker compose up -d again
Commented out everything related to analytics in docker-compose.yml -- supabase-auth container was unable to start and logs showed similar permissions issues

Installing the CLI and running supabase start in the docker directory fails while setting up the realtime container:

14:27:52.864 [notice] Application realtime exited: Realtime.Application.start(:normal, []) returned an error: shutdown: failed to start child: RealtimeWeb.Endpoint
    ** (EXIT) shutdown: failed to start child: {:ranch_listener_sup, RealtimeWeb.Endpoint.HTTP}
        ** (EXIT) shutdown: failed to start child: :ranch_acceptors_sup
            ** (EXIT) {:listen_error, RealtimeWeb.Endpoint.HTTP, :eafnosupport}
14:27:52.870 [notice]     :alarm_handler: {:clear, :system_memory_high_watermark}
{"Kernel pid terminated",application_controller,"{application_start_failure,realtime,{{shutdown,{failed_to_start_child,'Elixir.RealtimeWeb.Endpoint',{shutdown,{failed_to_start_child,{ranch_listener_sup,'Elixir.RealtimeWeb.Endpoint.HTTP'},{shutdown,{failed_to_start_child,ranch_acceptors_sup,{listen_error,'Elixir.RealtimeWeb.Endpoint.HTTP',eafnosupport}}}}}}},{'Elixir.Realtime.Application',start,[normal,[]]}}}"}

Answer 3 · 2023-11-09T15:30:02.000Z

Update: I was able to get the auth and rest containers running successfully by doing the following:

docker exec -it supabase-db bash
psql -U postgres
Manually run each command from roles.sql with the actual value for $POSTGRES_PASSWORD; e.g. ALTER USER authenticator WITH PASSWORD 'your-super-secret-and-long-postgres-password';
Manually run each of the db init scripts from supabase\postgres
docker compose restart to restart those containers so they pick up the changes to db

However, supabase-realtime and supabase-analytics are still failing. The logs from the realtime container showed a problem with the password for supabase_admin:

realtime-dev.supabase-realtime  | 15:21:47.396 [error] Postgrex.Protocol (#PID<0.136.0>) failed to connect: ** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "supabase_admin"

So I went back into the db container and ran ALTER USER supabase_admin WITH PASSWORD 'your-super-secret-and-long-postgres-password'; but the realtime container is still failing:

realtime-dev.supabase-realtime  | Setting RLIMIT_NOFILE to 100000
realtime-dev.supabase-realtime  | 15:23:43.796 [error] Could not create schema migrations table. This error usually happens due to the following:
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  |   * The database does not exist
realtime-dev.supabase-realtime  |   * The "schema_migrations" table, which Ecto uses for managing
realtime-dev.supabase-realtime  |     migrations, was defined by another library
realtime-dev.supabase-realtime  |   * There is a deadlock while migrating (such as using concurrent
realtime-dev.supabase-realtime  |     indexes with a migration_lock)
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  | To fix the first issue, run "mix ecto.create".
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  | To address the second, you can run "mix ecto.drop" followed by
realtime-dev.supabase-realtime  | "mix ecto.create". Alternatively you may configure Ecto to use
realtime-dev.supabase-realtime  | another table and/or repository for managing migrations:
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  |     config :realtime, Realtime.Repo,
realtime-dev.supabase-realtime  |       migration_source: "some_other_table_for_schema_migrations",
realtime-dev.supabase-realtime  |       migration_repo: AnotherRepoForSchemaMigrations
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  | The full error report is shown below.
realtime-dev.supabase-realtime  |
realtime-dev.supabase-realtime  | ** (Postgrex.Error) ERROR 3F000 (invalid_schema_name) no schema has been selected to create in
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:932: Ecto.Adapters.SQL.raise_sql_call_error/1
realtime-dev.supabase-realtime  |     (elixir 1.14.3) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/adapters/sql.ex:1024: Ecto.Adapters.SQL.execute_ddl/4
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/migrator.ex:696: Ecto.Migrator.verbose_schema_migration/3
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/migrator.ex:510: Ecto.Migrator.lock_for_migrations/4
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/migrator.ex:422: Ecto.Migrator.run/4
realtime-dev.supabase-realtime  |     (ecto_sql 3.8.3) lib/ecto/migrator.ex:146: Ecto.Migrator.with_repo/3
realtime-dev.supabase-realtime  |     nofile:1: (file)

As well as the analytics container:

supabase-analytics  | ** (Postgrex.Error) ERROR 3F000 (invalid_schema_name) no schema has been selected to create in
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
supabase-analytics  |     (elixir 1.14.4) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:1005: Ecto.Adapters.SQL.execute_ddl/4
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/migrator.ex:738: Ecto.Migrator.verbose_schema_migration/3
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/migrator.ex:552: Ecto.Migrator.lock_for_migrations/4
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/migrator.ex:428: Ecto.Migrator.run/4
supabase-analytics  |     (ecto_sql 3.10.1) lib/ecto/migrator.ex:170: Ecto.Migrator.with_repo/3
supabase-analytics  |     nofile:1: (file)
supabase-analytics  | Kernel pid terminated (application_controller) ({application_start_failure,logflare,{{shutdown,{failed_to_start_child,'Elixir.Logflare.SystemMetricsSup',{shutdown,{failed_to_start_child,'Elixir.Logflare.SystemMetrics.AllLogsLogged',{#{'__exception__' => true,'__struct__' => 'Elixir.Postgrex.Error',connection_id => 299,message => nil,postgres => #{code => undefined_table,file => <<"parse_relation.c">>,line => <<"1392">>,message => <<"relation \"system_metrics\" does not exist">>,pg_code => <<"42P01">>,position => <<"89">>,routine => <<"parserOpenTable">>,severity => <<"ERROR">>,unknown => <<"ERROR">>},query => <<"SELECT s0.\"id\", s0.\"all_logs_logged\", s0.\"node\", s0.\"inserted_at\", s0.\"updated_at\" FROM \"system_metrics\" AS s0 WHERE (s0.\"node\" = $1)">>},[{'Elixir.Ecto.Adapters.SQL',raise_sql_call_error,1,[{file,"lib/ecto/adapters/sql.ex"},{line,913},{error_info,#{module => 'Elixir.Exception'}}]},{'Elixir.Ecto.Adapters.SQL',execute,6,[{file,"lib/ecto/adapters/sql.ex"},{line,828}]},{'Elixir.Ecto.Repo.Q
supabase-analytics  |
supabase-analytics  | Crash dump is being written to: erl_crash.dump...done
supabase-analytics  | sh: 15:24:24.716: not found

I'm not a Postgres expert by any means so while I'm sure these issues are all related to the database initialization/migration scripts, I'm not sure what else I need to do to correct these issues.

Answer 4 · 2023-11-10T17:09:36.000Z

To enable the analytics container you need to enable it in config.toml

[analytics]
enabled = true

Doc Link - https://supabase.com/docs/guides/cli/config#analytics-config

Answer 5 · 2023-11-14T14:18:04.000Z

To enable the analytics container you need to enable it in config.toml
[analytics]
enabled = true
Doc Link - supabase.com/docs/guides/cli/config#analytics-config

If that's the case, shouldn't that be noted in the Docker guide?

Answer 6 · 2023-11-18T13:26:44.000Z

To enable the analytics container you need to enable it in config.toml
[analytics]
enabled = true
Doc Link - supabase.com/docs/guides/cli/config#analytics-config
If that's the case, shouldn't that be noted in the Docker guide?

Did my answer work? I answer it in the context of Local development.

Answer 7 · 2023-11-19T09:53:52.000Z

Hello,

I have the same problem as @krysialynne. Is there any solution?

I've added the:

[analytics] enabled = true

but that didn't solve my problem.

***Reinstaling everything solved the problem

Answer 8 · 2023-11-21T14:48:39.000Z

Hey,

I am glad that after hours of trying things to make the setup work I find the problem here, so it’s not my incompetence. I am facing the exact same problem. Any updates so far?

Answer 9 · 2023-11-21T14:56:53.000Z

To enable the analytics container you need to enable it in config.toml
[analytics]
enabled = true
Doc Link - supabase.com/docs/guides/cli/config#analytics-config
If that's the case, shouldn't that be noted in the Docker guide?
Did my answer work? I answer it in the context of Local development.

No, this issue is in the context of the self-hosted setup instructions.

Answer 10 · 2023-12-17T06:55:25.000Z

I met the same issue. Just solve it by:

exec this command 【find . -name 'pg_hba.conf'】
Find this conf:

IPv4 external connections

host all all 10.0.0.0/8 scram-sha-256
host all all 172.16.0.0/12 scram-sha-256
host all all 192.168.0.0/16 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256

Changed conf:

IPv4 external connections

host all all 10.0.0.0/8 scram-sha-256
host all all 172.16.0.0/12 trust
host all all 192.168.0.0/16 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256

I hope it's helpful.

Answer 11 · 2024-01-18T08:49:22.000Z

I will close this one as it is resolved with better self-hosting instructions for analytics

Please comment if you are still experiencing issues or want to see more improvements

Answer 12 · 2024-02-16T15:24:42.000Z

I'm running through the Self-Hosting w/Docker instructions and hitting the same issue. After changing the the default POSTGRES_PASSWORD and attempting the solutions suggested in password authentication failed for user "supabase_auth_admin" - Docker Sefl Hosting #11957, the analytics and db containers fail to start with the following errors respectively:

17:10:03.190 [error] Postgrex.Protocol (#PID<0.4729.0>) failed to connect: ** (Postgrex.Error) FATAL 28P01 (invalid_password) password authentication failed for user "supabase_admin"

192.168.48.5 2024-02-08 17:31:32.953 UTC [118] supabase_admin@postgres FATAL:  password authentication failed for user "supabase_admin"

I can then run docker compose restart to get the some of the containers to start and the Supabase GUI to load in my browser, however several of the containers are in an unhealthy state and the logs show database connections errors.

CONTAINER ID   IMAGE                              COMMAND                   CREATED          STATUS                             PORTS                                                                                                      NAMES
d7680ea347e4   supabase/storage-api:v0.46.4       "docker-entrypoint.s…"    7 minutes ago    Restarting (1) 27 seconds ago                                                                                                                 supabase-storage
940ee1055630   supabase/edge-runtime:v1.33.5      "edge-runtime start …"    7 minutes ago    Up 5 minutes                                                                                                                                  supabase-edge-functions
146f42378a80   supabase/gotrue:v2.132.3           "auth"                    7 minutes ago    Restarting (1) 6 seconds ago                                                                                                                  supabase-auth
b0b5cd543ca7   supabase/postgres-meta:v0.75.0     "docker-entrypoint.s…"    7 minutes ago    Up 5 minutes (healthy)             8080/tcp                                                                                                   supabase-meta
568155ff7fe9   kong:2.8.1                         "bash -c 'eval \"echo…"   7 minutes ago    Up 5 minutes (healthy)             0.0.0.0:8000->8000/tcp, :::8000->8000/tcp, 8001/tcp, 0.0.0.0:8443->8443/tcp, :::8443->8443/tcp, 8444/tcp   supabase-kong
a11c661fd9a9   supabase/realtime:v2.25.50         "/usr/bin/tini -s -g…"    7 minutes ago    Up 10 seconds (health: starting)                                                                                                              realtime-dev.supabase-realtime
3391cbfbcb53   supabase/studio:20240101-8e4a094   "docker-entrypoint.s…"    7 minutes ago    Up 5 minutes (unhealthy)           3000/tcp                                                                                                   supabase-studio
a240a469f4d4   postgrest/postgrest:v12.0.1        "postgrest"               7 minutes ago    Restarting (1) 5 seconds ago                                                                                                                  supabase-rest
47d642cdf029   supabase/logflare:1.4.0            "sh run.sh"               7 minutes ago    Up 7 seconds (health: starting)    0.0.0.0:4000->4000/tcp, :::4000->4000/tcp                                                                  supabase-analytics
4c9e82a3c0f0   supabase/postgres:15.1.0.147       "docker-entrypoint.s…"    7 minutes ago    Up 5 minutes (healthy)             0.0.0.0:5432->5432/tcp, :::5432->5432/tcp                                                                  supabase-db
3489ef40782c   darthsim/imgproxy:v3.8.0           "imgproxy"                7 minutes ago    Up 5 minutes (healthy)             8080/tcp                                                                                                   supabase-imgproxy
36c351ef306c   timberio/vector:0.28.1-alpine      "/usr/local/bin/vect…"    30 minutes ago   Up 4 minutes (healthy)                                                                                                                        supabase-vector

$ docker logs 146f42378a80
[...]
{"level":"fatal","msg":"running db migrations: Migrator: problem creating schema migrations: couldn't start a new transaction: could not create new transaction: failed to connect to `host=db user=supabase_auth_admin database=postgres`: failed SASL auth (FATAL: password authentication failed for user \"supabase_auth_admin\" (SQLSTATE 28P01))","time":"2024-02-08T17:34:50Z"}
{"level":"info","msg":"Go runtime metrics collection started","time":"2024-02-08T17:35:50Z"}
{"args":[0.064195059],"component":"pop","level":"info","msg":"%.4f seconds","time":"2024-02-08T17:35:50Z"}
{"level":"fatal","msg":"running db migrations: Migrator: problem creating schema migrations: couldn't start a new transaction: could not create new transaction: failed to connect to `host=db user=supabase_auth_admin database=postgres`: failed SASL auth (FATAL: password authentication failed for user \"supabase_auth_admin\" (SQLSTATE 28P01))","time":"2024-02-08T17:35:50Z"}

Docker version 25.0.2, build 29cf629
Debian GNU/Linux 12

Answer 13 · 2024-04-09T11:55:55.000Z

Did this ever get resolved. Having the same issue as @timzl

Answer 14 · 2024-04-10T12:21:11.000Z

Yep, see the closing comment. If you have changed the password, ensure there are no special chars (or it is encoded properly) and that each container is picking up the change

Answer 15 · 2024-06-26T01:37:55.000Z

Hi all, i want to test supabase on my own ubuntu VM. i just follow the instruction here https://supabase.com/docs/guides/self-hosting/docker. Without changing anything, i follow these steps.

These are the container status:

This is the supabase-analytics logs :

I suspect it crashed in supabase-analytics which contribute to the storage-api, realtime-dev.supabase-realtime, supabase-rest, supabase-kong keep restarting.

May i know what should i do to solve this issue?

I had try to resolve by:
docker system prune -a
rm -rf docker/volumes/db/data
docker compose up

I also try to exec it into supabase-db:
ALTER USER supabase_admin WITH PASSWORD 'your-super-secret-and-long-postgres-password';

But the problem stil presists. My situation is difference from the other post is that i did not change any credential, i followed the official guide to fresh install.

Answer 16 · 2024-07-31T22:29:20.000Z

@joewin319 Same issue here, and I did not find a proper solution.

Sometimes it just worked to restart docker with
sudo docker compose restart

But the safer approach is to reset the whole database:

Local machine Dump data from the db like this:
npx supabase db dump -s public -f supabase/seed.sql --db-url "postgres://postgres:PASSWORD@IP:5432/postgres"
Ubuntu VM Stop all containers like this: sudo docker compose -f docker-compose.yml -f docker-compose.s3.yml down
Ubuntu VM Remove all the db volumes like this: cd volumes/db then sudo rm -rf /data and go back cd ../..
Ubuntu VM Start all containers again like this: sudo docker compose -f docker-compose.yml -f docker-compose.s3.yml up -d
Local machine Push the dumped data again: npx supabase db push --include-seed --db-url postgres://postgres:PASSWORD@IP:5432/postgres

Take care especially with the last step to run it on a local db first.

Answer 17 · 2024-10-25T09:19:45.000Z

I have the similar issues when I tried to update the supabase containers. I removed the old data of database since I don't have much data into it, then docker compose down -v && docker compose up -d works.

Maybe a better solution is need to update/migrate the database to fit the new database structure.

Answer 18 · 2025-01-10T17:53:22.000Z

Same issue, backing out changes finally showed me that NOT changing the POSTGRES_PASSWORD allowed everything to start. That said, I would actually like to have a password that was not default. I read through the fix that closed this @encima , but saw no mention of the basic install in docker with the exception that it never told me to change the passwords, even though they are listed in .env.example as "your-super-secret-and-long-postgres-password"