Unable to override login credentials for already initialized database

[CitationGraham]

I run the command:

docker run -it \
                     -p 5432:5432 \
                     -e POSTGRES_PASSWORD=first \
                     -e POSTGRES_USER=first \
                     -v `pwd`/temp:/var/lib/postgresql/data \
                      postgres:16

And I can log into the database with the username first and the password first:

~$ PGPASSWORD=first psql -h 127.0.0.1 -U first -d first
psql (15.6 (Debian 15.6-0+deb12u1), server 16.1 (Debian 16.1-1.pgdg120+1))
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
Type "help" for help.

first=#

I then stop that container and run the following command in the same directory:

docker run -it \
                     -p 5432:5432 \
                     -e POSTGRES_PASSWORD=second\
                     -e POSTGRES_USER=second \
                     -v `pwd`/temp:/var/lib/postgresql/data \
                     postgres:16

And I cannot log in as second!! (I can still log in as first)

~$ PGPASSWORD=second psql -h 127.0.0.1 -U second
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "second"

[LaurentGoderre]

@CitationGraham that is by design. This is how database initialization works.

[CitationGraham]

@LaurentGoderre I am not sure the inability to override the username or at least the password after first running makes any sense in a Docker context...

You can hack around with the ENTRYPOINT to accomplish the same outcome; the expectation is to be able to do it more simply and cleanly with the same ENV used earlier in the process.

[Harryalways317]

did you tried doing that without the volume?

[tianon]

See also https://hub.docker.com/_/postgres, especially:

Warning: the Docker specific variables will only have an effect if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup.

I agree that not being able to reconfigure is a bit unusual, but a database without persistence is even more unusual and users/authentication are a property of the initialized database (and maintained via the data directory/database itself after initialization). :smile:

[CitationGraham]

@tianon the specific use case for this, is baking data into a testing database that can be shipped as an image for parallel test runs.

Currently we face a choice between really really slow startup (copying into /docker-entrypoint-initdb.d at build then loading at run) or loading at build-time and having essentially broken authentication.

The three other database products we use all allow overriding credentials at runtime.

[LaurentGoderre]

@CitationGraham creating another user wouldn't work in your use case?