How to debug: password authentication failed for user - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | How to debug: password authentication failed for user |
| Date | |
| Msg-id | CAADeyWjQNmfiQxaiRF0tkZ-kZyApPJT00xbmcMtj+oToSPGoWQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: How to debug: password authentication failed for user
|
| List | pgsql-general |
Good evening,
I am building the following Dockerfile by the command:
# docker build -f ./Dockerfile --build-arg PGPASSWORD=timeshift_pass .
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 .
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":
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:
The failure in the logs
+ 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:
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
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
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:
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
What could I check to make the remote connection as timeshift_user work?
Thank you
Alex
pgsql-general by date: