Local Dev Container fails to launch since version supabase/postgres:15.6.1.120

[sweigert]

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

I used to be able to run a local supabase/postgres using the postgres user and setting a password. This is helpful for running backend tests against an actual supabase instance.

However, since supabase/postgres:15.6.1.120 this does not work anymore and fails with the following error:

docker run --rm \
  --name test-db \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=my-backend \
  -p 5432:5432 \
  public.ecr.aws/supabase/postgres:15.6.1.120
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
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.
waiting for server to start.... 2024-09-18 12:36:38.255 UTC [49] LOG:  pgaudit extension initialized
 2024-09-18 12:36:38.260 UTC [49] LOG:  pgsodium primary server secret key loaded
 2024-09-18 12:36:38.275 UTC [49] LOG:  starting PostgreSQL 15.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
 2024-09-18 12:36:38.275 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
 2024-09-18 12:36:38.277 UTC [58] LOG:  database system was shut down at 2024-09-18 12:36:37 UTC
 2024-09-18 12:36:38.280 UTC [49] LOG:  database system is ready to accept connections
 2024-09-18 12:36:38.281 UTC [61] LOG:  TimescaleDB background worker launcher connected to shared catalogs
 2024-09-18 12:36:38.282 UTC [63] LOG:  pg_cron scheduler started
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/init-scripts

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/migrate.sh
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "supabase_admin"
[local] 2024-09-18 12:36:38.384 UTC [75] supabase_admin@my-backend FATAL:  password authentication failed for user "supabase_admin"
[local] 2024-09-18 12:36:38.384 UTC [75] supabase_admin@my-backend DETAIL:  Role "supabase_admin" does not exist.
	Connection matched pg_hba.conf line 82: "local all  supabase_admin     scram-sha-256"

The same works just fine using supabase/postgres:15.6.1.119

While I can get supabase/postgres:15.6.1.120 to start just fine using -e POSTGRES_USER=supabase_admin this does not work for me as the postgres user then has no right to write to the public schema in my-backend and I am not able to login with the supabase_admin

To Reproduce

This fails to start:

docker run --rm \
  --name test-db \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=my-backend \
  -p 5432:5432 \
  public.ecr.aws/supabase/postgres:15.6.1.120

This runs just fine:

docker run --rm \
  --name test-db \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=my-backend \
  -p 5432:5432 \
  public.ecr.aws/supabase/postgres:15.6.1.119

Expected behavior

I would expect the container to start without errors and be able to write to the public schema of my-backend using the postgres user

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: macOS but probably not the issue

Additional context

I used to have a similar issue in the supabase cloud recently which led to this incident (https://status.supabase.com/incidents/5b1z81cjm3lt). This happened when I upgraded from .119 to .121 and while I obviously do not run docker in the supabase cloud I had a similar problem, not being able to connect to supabase using psql since it could not authenticate with supabase_admin.

[sweigert]

Could this be related to #1125? Would also coincide with the incident in the supabase cloud and does exactly change what fails for me now.

Maybe I'm using it all wrong and I'm supposed to do it differently but I'm not sure how.

[sweigert]

Update: I got it working by not specifying a custom database and by either using supabase_admin as the POSTGRES_USER or not specifying this env var at all (since this seems to be the default now anyway).
It seems all the user role wrangling that is done here does not do all the work if I pass a custom DB.

This could turn out really bad for people who host supabase themselves and use custom databases (i.e., not postgres).

This is working now:

docker run --rm \
  --name test-db \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  public.ecr.aws/supabase/postgres:15.6.1.120