Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
2 tasks done
krysialynne opened this issue Nov 8, 2023 · 19 comments
Closed
2 tasks done
Labels
bug Something isn't working documentation Improvements or additions to documentation self-hosted Issues related to self hosting

Comments

@krysialynne
Copy link

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:

  1. Clone the supabase repository with git clone --depth 1 https://github.com/supabase/supabase
  2. cd into supabase/docker and copy environment vars with cp .env.example .env
  3. Pull the latest images with docker compose pull
  4. 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
@krysialynne krysialynne added the bug Something isn't working label Nov 8, 2023
@yosva
Copy link

yosva commented Nov 9, 2023

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

@krysialynne
Copy link
Author

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:

  1. Fully deleted the repo, ran docker system prune -a -f --volumes, and re-cloned the repo
  2. After initial failed docker compose up -d, deleted volumes/db/data and ran docker compose up -d again
  3. 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,[]]}}}"}

@krysialynne
Copy link
Author

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

  1. docker exec -it supabase-db bash
  2. psql -U postgres
  3. 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';
  4. Manually run each of the db init scripts from supabase\postgres
  5. 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.

@Akash187
Copy link
Contributor

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

@krysialynne
Copy link
Author

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?

@Akash187
Copy link
Contributor

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.

@Small-Clown
Copy link

Small-Clown commented Nov 19, 2023

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

@prime-rue
Copy link

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?

@krysialynne
Copy link
Author

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.

@Kaffiter
Copy link

I met the same issue. Just solve it by:

  1. exec this command 【find . -name 'pg_hba.conf'】
  2. 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

  1. 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.

@encima
Copy link
Member

encima commented Jan 18, 2024

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

@encima encima closed this as completed Jan 18, 2024
@encima encima added documentation Improvements or additions to documentation self-hosted Issues related to self hosting labels Jan 18, 2024
@timzl
Copy link

timzl commented Feb 16, 2024

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

@Fnux8890
Copy link

Fnux8890 commented Apr 9, 2024

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

@encima
Copy link
Member

encima commented Apr 10, 2024

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

@joewin319
Copy link

joewin319 commented Jun 26, 2024

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.
image

These are the container status:
image

This is the supabase-analytics logs :
image

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.

@molti-tasking
Copy link

@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:

  1. 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"
  2. Ubuntu VM Stop all containers like this: sudo docker compose -f docker-compose.yml -f docker-compose.s3.yml down
  3. Ubuntu VM Remove all the db volumes like this: cd volumes/db then sudo rm -rf /data and go back cd ../..
  4. Ubuntu VM Start all containers again like this: sudo docker compose -f docker-compose.yml -f docker-compose.s3.yml up -d
  5. 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.

@liudonghua123
Copy link

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.

@lamachine
Copy link

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"

@pweibert
Copy link

pweibert commented Apr 3, 2025

Seems to be an issue with /docker-entrypoint-initdb.d/init-scripts/00000000000000-initial-schema.sql in the postgres / db image

As a workaround: Patch your /docker-entrypoint-initdb.d/init-scripts/00000000000000-initial-schema.sql in your db container by adding
create user supabase_admin before alter user supabase_admin ... so that it looks like this: https://github.com/supabase/postgres/blob/e9e2d50bbdbb0fcbbab8b51d82aa38afcf4fa588/migrations/db/init-scripts/00000000000000-initial-schema.sql#L8

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working documentation Improvements or additions to documentation self-hosted Issues related to self hosting
Projects
None yet
Development

No branches or pull requests