Good evening,
I am building the following Dockerfile by the command:
# docker build -f ./Dockerfile --build-arg PGPASSWORD=timeshift_pass .
FROM postgres:17-alpine3.21
RUN apk update && apk upgrade && apk add --no-cache pg_top
ARG PGPASSWORD
# Tell docker-entrypoint.sh to create superuser "postgres"
# with password passed as build arg and database "postgres"
ENV POSTGRES_PASSWORD=$PGPASSWORD
# Tell docker-entrypoint.sh to change these params in postgresql.conf
ENV POSTGRES_INITDB_ARGS="--set max_connections=200 --set
shared_buffers=16GB --set work_mem=8MB --set maintenance_work_mem=128MB
--set effective_cache_size=8GB --set from_collapse_limit=24 --set
join_collapse_limit=24 --set log_min_messages=notice --set
log_connections=on --set log_statement=mod --set listen_addresses='*'"
ENV PGUSER=timeshift_user
ENV PGPASSWORD=$PGPASSWORD
ENV PGDATABASE=timeshift_database
# The files below are executed by the DB superuser "postgres"
# in alphabetical order after the database has been initialized
WORKDIR /docker-entrypoint-initdb.d
COPY 01-create-database.sh .
# Skipped few SQL files inbetween
COPY ./04-alter-owner.sh .
RUN chmod +x ./01-create-database.sh ./04-alter-owner.sh
# Drop root privileges
USER postgres
The 01-create-database.sh script sets the passwords for the users
"postgres" and "timeshift_user":
#!/bin/sh -eux
echo "Creating user $PGUSER"
createuser --username=postgres $PGUSER
echo "Granting usage on schema public to $PGUSER"
psql --username=postgres --dbname=postgres -c "GRANT USAGE ON SCHEMA public
TO $PGUSER;"
echo "Setting password for $PGUSER to $PGPASSWORD"
psql --username=postgres --dbname=postgres -c "ALTER USER $PGUSER PASSWORD
'$PGPASSWORD';"
echo "Setting password for postgres to $PGPASSWORD"
psql --username=postgres --dbname=postgres -c "ALTER USER postgres PASSWORD
'$PGPASSWORD';"
echo "Creating database $PGDATABASE owned by $PGUSER"
createdb --username=postgres --owner=$PGUSER $PGDATABASE
Then I run the built image either using Docker Desktop on my Windows
notebook or in the Azure AKS cluster:
winpty docker run --rm -it -p 5432:5432
sha256:ead13c0a5e3fd9fc48a7f3ac005bb11d2b5483efa94e65d76d24853566526d9f
My problem is that the local "trust" connection all work fine, but remote
connection from another pod in the AKS fails with:
PGPASSWORD=timeshift_pass psql --host=timeshiftservice --port=5432
--dbname=timeshift_database --username=timeshift_user --password
Password: (here I enter the "timeshift_pass" and press enter)
psql: error: connection to server at "timeshiftservice" (10.0.120.194),
port 5432 failed: FATAL: password authentication failed for user
"timeshift_user"
The failure in the logs
2025-02-27 16:27:32.850 UTC [87] LOG: connection received: host=127.0.0.6
port=59969
2025-02-27 16:27:32.861 UTC [87] FATAL: password authentication failed for
user "timeshift_user"
2025-02-27 16:27:32.861 UTC [87] DETAIL: Connection matched file
"/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all
scram-sha-256"
The /var/lib/postgresql/data/pg_hba.conf contains:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all all scram-sha-256
The /var/lib/postgresql/data/postgresql.conf contains: listen_addresses =
'*'
While the pod is being built I do see the log from the
01-create-database.sh script:
+ psql '--username=postgres' '--dbname=postgres' -c 'ALTER USER
timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'
And also when I login locally (per "trust") as postgresql, I can see the
timeshift_user having the password set:
$ psql -U postgres
psql (17.4)
Type "help" for help.
timeshift_database=# SELECT rolname, rolpassword FROM pg_authid WHERE
rolname = 'timeshift_user';
rolname |
rolpassword
----------------+---------------------------------------------------------------------------------------------------------------------------------------
timeshift_user |
SCRAM-SHA-256$4096:kQisEuaKSpuJK4kmpqoq2w==$nNNngQozh11kpDeW43ETrVUe1eNvKuKWvU/nb1etxEI=:537RuSYGRHEVJL4PyUxfAYIXNdA8cOp+QGnvNjKWWvQ=
(1 row)
Does anybody have an idea, what else could be wrong?
What could I check to make the remote connection as timeshift_user work?
Thank you
Alex
