Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tnh2g-000V1T-JO for pgsql-general@arkaria.postgresql.org; Thu, 27 Feb 2025 16:47:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tnh1i-000Iq4-Jf for pgsql-general@arkaria.postgresql.org; Thu, 27 Feb 2025 16:46:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tnh1i-000Ipw-0c for pgsql-general@lists.postgresql.org; Thu, 27 Feb 2025 16:46:20 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tnh1e-0000Y5-1T for pgsql-general@postgresql.org; Thu, 27 Feb 2025 16:46:19 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-5e4ce6e3b8cso1013377a12.1 for ; Thu, 27 Feb 2025 08:46:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740674777; x=1741279577; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=AsxK0YmLcgDiOdhdiO9F3TthW+QfUL+5IEEY1YQEXIU=; b=nEE+fP/wgr4ZLmaxIMPXhbMe1TpnGg+MHHohSc5Y/u+uuGe+/3F5UCJYX2Wh8VxlJX RDHGfOGw1uX4xAbpDc2/96n6bCrNaM2a3gfXLbP/X1QTbVgmjCAQDBRgDvu/6qIuQ2Rn LmRoLlST68NoEAHz3pFHGREcNekoF0ZAQBD/x8N/5xsgK6x86LE4/Ps/znimOHrxEdWJ vh/mSPmHioA7xXLZE8IkKybzLNP7a794YGe4tI5D/BRvL8iRt+qtuqIjNg856OZPw0tS 9x9+OV8GEJ6YZjQmBjQUn+qULk3m7j4v1BsurtQN+QG5J0yWhtknKi7cKYqIJW4/vHdx +vCg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740674777; x=1741279577; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=AsxK0YmLcgDiOdhdiO9F3TthW+QfUL+5IEEY1YQEXIU=; b=AXdSppS36jki2lg9TJqeomDpdwUi2lCD1NfGvdfaM+SPBkdpZDSd/7etdLe6EqLYu/ yW8k3b3sTe61siPe2cWLbWYqCQy6ob8XoUguuRpX/iCv2khTOmoBhAVAzZDk/O7IJCOV z2nKxF42oTD3iOHbfs/xrF5Td6Na0CIR6/Rjp8IHTZlgUVgo5y5HrhwRFOwR/3La/8Od kSygAiWIe5WPs8Nhi9XeiK7UyCSE5BadacBOoRDpjD/T3yt5Wa/CQhmE0RGF9GPj1B7n WLVcxQ7oSyLI+jhuQUokPnqvt2Da9SWE+SbX7n+CbMWAxdRz554TeNEmS6KNV+cI1uJu mLGA== X-Gm-Message-State: AOJu0Yy6ccGBOmvJjp7ClHW7nIICFQcHuWEv3Jjtxig+whz0L+faP9a4 U7OTvrBt3ojkZgUaIrIjvbxchC6JOIllQbjOV2vXt5MRv5YLdKV5xKP3kvL49moSggBDIDEvqyO 29DW1RQe0DiL0VikixwSZ2U+UCD4JaJ64FGc= X-Gm-Gg: ASbGnctxxfRqegworh0i5Kp9OGZ+QK+8SxYlk03MEVbbAI8JRcLijOw3Ab9b57gr4TD yKtX4sX52HcTHIDm8xW6sCxIQ3J8cQNbvQD56wuvcpcmZ0PL9CrzomBfTZMmpIfShVNjBkGY4D+ kofHoLZJFw X-Google-Smtp-Source: AGHT+IGcavy7KH50N7OEgg6aS9rH7G0SvIjPLxi0kpAYZ3KS26Gjb9wclyPhjPsaehoxhDmMbdUxgc2yMHN4lZkeH+E= X-Received: by 2002:a05:6402:26d6:b0:5e0:8a69:c05e with SMTP id 4fb4d7f45d1cf-5e4d52cb12fmr129588a12.8.1740674776657; Thu, 27 Feb 2025 08:46:16 -0800 (PST) MIME-Version: 1.0 From: Alexander Farber Date: Thu, 27 Feb 2025 17:46:04 +0100 X-Gm-Features: AQ5f1JqrZknWW-jBNaq-RZYKaCKarDmllukn-1MpISU7WG9-o3NQgibbrOj1ZtU Message-ID: Subject: How to debug: password authentication failed for user To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000bb770b062f226d88" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bb770b062f226d88 Content-Type: text/plain; charset="UTF-8" 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 --000000000000bb770b062f226d88 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Good evenin= g,

I am building the following Dockerfile by the command:

# d= ocker build -f ./Dockerfile --build-arg PGPASSWORD=3Dtimeshift_pass .
FROM postgres:17-alpine3.21
RUN ap= k update && apk upgrade && apk add --no-cache pg_top
<= div dir=3D"ltr">
ARG PGPASSWORD

# Tell docker-entrypoint.sh to create super= user "postgres"
# with password passed as b= uild arg and database "postgres"
ENV POSTGR= ES_PASSWORD=3D$PGPASSWORD

= # Tell docker-entrypoint.sh to change these params in postgresql.conf
=
ENV POSTGRES_INITDB_ARGS=3D"--set max_connections=3D2= 00 --set shared_buffers=3D16GB --set work_mem=3D8MB --set maintenance_work_= mem=3D128MB --set effective_cache_size=3D8GB --set from_collapse_limit=3D24= --set join_collapse_limit=3D24 --set log_min_messages=3Dnotice --set log_c= onnections=3Don --set log_statement=3Dmod --set listen_addresses=3D'*&#= 39;"

ENV PGUSER=3Dtim= eshift_user
ENV PGPASSWORD=3D$PGPASSWORD
ENV PGDATABASE=3Dtimeshift_database

# The files below are executed by the DB superuser &quo= t;postgres"
# in alphabetical order after the da= tabase has been initialized
WORKDIR /docker-entrypoin= t-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.s= h

# Drop root privileges
USER postgres

The 01-create-database.sh script = sets the passwords for the users "postgres" and "timeshift_u= ser":

#!/bin/sh -eux

echo &= quot;Creating user $PGUSER"
createuser --username=3Dpostgres= $PGUSER

echo "Granting usage on schema publi= c to $PGUSER"
psql --username=3Dpostgres --dbname=3Dpostgres= -c "GRANT USAGE ON SCHEMA public TO $PGUSER;"

echo "Setting password for $PGUSER to $PGPASSWORD"
psql --username=3Dpostgres --dbname=3Dpostgres -c "ALTER USER $PGUS= ER PASSWORD '$PGPASSWORD';"

echo &quo= t;Setting password for postgres to $PGPASSWORD"
psql --usern= ame=3Dpostgres --dbname=3Dpostgres -c "ALTER USER postgres PASSWORD &#= 39;$PGPASSWORD';"

echo "Creating dat= abase $PGDATABASE owned by $PGUSER"
createdb --username=3Dpo= stgres --owner=3D$PGUSER $PGDATABASE

Then I run t= he 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:e= ad13c0a5e3fd9fc48a7f3ac005bb11d2b5483efa94e65d76d24853566526d9f

My p= roblem is that the local "trust" connection all work fine, but re= mote connection from another pod in the AKS fails with:

PGPASSWORD=3Dtimeshift_pass psql --host=3Dtimeshiftservice --port=3D54= 32 --dbname=3Dtimeshift_database --username=3Dtimeshift_user --password
Password: (here I enter the "timeshift_pass" a= nd press enter)
psql: error: connection to server at = "timeshiftservice" (10.0.120.194), port 5432 failed: FATAL:=C2=A0= password authentication failed for user "timeshift_user"

The failure in the logs

2025-02-27 16:= 27:32.850 UTC [87] LOG:=C2=A0 connection received: host=3D127.0.0.6 port=3D= 59969
2025-02-27 16:27:32.861 UTC [87] FATAL:=C2=A0 p= assword authentication failed for user "timeshift_user"
2025-02-27 16:27:32.861 UTC [87] DETAIL:=C2=A0 Connection matc= hed file "/var/lib/postgresql/data/pg_hba.conf" line 128: "h= ost all all all scram-sha-256"

The /var/lib/postgresql/data/pg_= hba.conf contains:

# TYPE=C2=A0 DATABASE=C2=A0 =C2= =A0 =C2=A0 =C2=A0 USER=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ADDRESS=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0METHOD
# "local" is for Unix domain socket connections only=
local=C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0trust
# IPv4 local connections:
host=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0al= l=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0127.0.0.1/32=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
=
# IPv6 local connections:
host=C2=A0= =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0trust
# Allow replicat= ion connections from localhost, by a user with the
# = replication privilege.
local=C2=A0 =C2=A0replication= =C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0trust
host=C2=A0 =C2=A0 replication=C2=A0 =C2= =A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0127.0.0.1/32=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= trust
host=C2=A0 =C2=A0 replication=C2=A0 =C2=A0 =C2= =A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0::1/128=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0trust
host all all all scram-sha-256

The=C2=A0=C2=A0/var/lib/postgresql= /data/postgresql.conf contains:=C2=A0listen_addresses =3D '*'
While the pod is being built I do see the log from the 01-create-database= .sh script:

+ psql '--username=3Dpostgres'= ; '--dbname=3Dpostgres' -c 'ALTER USER timeshift_user PASSWORD = '"'"'timeshift_pass'"'"';'<= br>
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=3D# SELECT rolname, rolpassword FROM pg_authid= WHERE rolname =3D 'timeshift_user';
=C2=A0 = =C2=A0 rolname=C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rolpassword

----------------+----------------------------------------= ---------------------------------------------------------------------------= --------------------
=C2=A0timeshift_user | SCRAM-SHA= -256$4096:kQisEuaKSpuJK4kmpqoq2w=3D=3D$nNNngQozh11kpDeW43ETrVUe1eNvKuKWvU/n= b1etxEI=3D:537RuSYGRHEVJL4PyUxfAYIXNdA8cOp+QGnvNjKWWvQ=3D
(1 row)

Does anybody have an idea= , what else could be wrong?

What could I check to make the remote c= onnection as timeshift_user work?

Thank you
Alex
--000000000000bb770b062f226d88--