Docker entrypoint fails to run shell scripts that contain the psql command if PGHOST environment variable is set

[mishaschwartz]

The pgstac docker entrypoint has a process_init_files function that can run or source shell scripts or run psql files using the docker_process_sql as a drop-in replacement for the psql command.

Before this command is run, a temporary server is started with the docker_temp_server_start function which starts the server at a socket. docker_process_sql ensures that psql connects to the socket by setting PGHOST and PGHOSTADDR to be empty:

pgstac/docker/pgstac/dbinit/docker-entrypoint.sh

Line 197 in 6c569c0

PGHOST= PGHOSTADDR= "${query_runner[@]}" "$@"

If the shell script is run (not sourced) and if the shell script contains the psql command, then PGHOST and PGHOSTADDR are not set to be empty for the psql command in the shell script (which is run in a subprocess). This means that if PGHOST and PGHOSTADDR are set to anything other than empty, psql will try to connect to a probably non-existent host and will fail.

STEPS TO REPRODUCE

This works fine:

docker run --rm -e 'POSTGRES_USER=username' -e 'PGUSER=username' -e 'POSTGRES_PASSWORD=password' -e 'POSTGRES_DB=postgis' -e 'PGDATABASE=postgis' ghcr.io/stac-utils/pgstac:v0.9.1 postgres

The same command with the PGHOST environment variable set:

docker run --rm -e 'POSTGRES_USER=username' -e 'PGUSER=username' -e 'POSTGRES_PASSWORD=password' -e 'POSTGRES_DB=postgis' -e 'PGDATABASE=postgis' -e 'PGHOST=localhost' ghcr.io/stac-utils/pgstac:v0.9.1 postgres

results in the following logs:

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.utf8".
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

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.

Success. You can now start the database server using:

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

waiting for server to start....2024-11-18 20:41:43.500 UTC [47] LOG:  starting PostgreSQL 15.7 (Debian 15.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-11-18 20:41:43.502 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-11-18 20:41:43.506 UTC [50] LOG:  database system was shut down at 2024-11-18 20:41:42 UTC
2024-11-18 20:41:43.510 UTC [47] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-preinitdb.d/*

2024-11-18 20:41:43.720 UTC [47] LOG:  received fast shutdown request
waiting for server to shut down....2024-11-18 20:41:43.722 UTC [47] LOG:  aborting any active transactions
2024-11-18 20:41:43.724 UTC [47] LOG:  background worker "logical replication launcher" (PID 53) exited with exit code 1
2024-11-18 20:41:43.725 UTC [48] LOG:  shutting down
2024-11-18 20:41:43.726 UTC [48] LOG:  checkpoint starting: shutdown immediate
2024-11-18 20:41:43.905 UTC [48] LOG:  checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.040 s, sync=0.134 s, total=0.180 s; sync files=301, longest=0.004 s, average=0.001 s; distance=4215 kB, estimate=4215 kB
2024-11-18 20:41:43.912 UTC [47] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2024-11-18 20:41:43.958 UTC [64] LOG:  starting PostgreSQL 15.7 (Debian 15.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-11-18 20:41:43.960 UTC [64] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-11-18 20:41:43.964 UTC [67] LOG:  database system was shut down at 2024-11-18 20:41:43 UTC
2024-11-18 20:41:43.969 UTC [64] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/990_pgstac.sh
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Cannot assign requested address
	Is the server running on that host and accepting TCP/IP connections?

[bitner]

@mishaschwartz I'm not sure what your use case is here? You can just stick any shell script into /docker-entrypoint-initdb.d/ and you can execute sql using psql and the entrypoint script will make sure that the database is running when that script is run.

I'm actually planning on moving all the docker base images out to it's own repo so that we don't need to deal with rebuilding the images so often either locally or in CI when those base images rarely change. This will also let us make sure to update those base images on a cron to make sure that we are getting updates from upstream better.

[mishaschwartz]

Thanks for the reply @bitner

I'm not sure what your use case is here?

There's no specific use-case, the behaviour is just unexpected. I would never have guessed that the init scripts would fail depending on whether these environment variables are set or not.

I proposed a programmatic solution to this in #319 but I'm also happy to just make sure that this behaviour is documented somewhere so that developers aren't surprised by this behaviour like I was.

I'm actually planning on moving all the docker base images out to it's own repo

That sounds great! But won't this surprising behaviour still exist, the code will just be in a different repo?

[bitner]

The reality is that I only included that script because I misunderstood how I could be doing something else, so it will go away completely. That script is inherited from the official Postgres Docker image, if there is anything that you'd like changed there, I would direct you to address this there.

[mishaschwartz]

Ok good to know. Thanks for the help. Should I close #319 then?