Re: How to debug: password authentication failed for user

[Adrian Klaver]

On 2/27/25 10:14, Alexander Farber wrote:

That seems to be the reason, thank you Adrian.

At first connecting from the other pod in the AKS cluster does not work:

$ PGPASSWORD=timeshift_pass psql --host=timeshiftservice --port=5432 
--dbname=timeshift_database --username=timeshift_user
psql: error: connection to server at "timeshiftservice" (10.0.120.194), 
port 5432 failed: FATAL:  password authentication failed for user "t

imeshift_user"

But once I connect to my PostgreSQL pod (or is it called "container"? I 
am always confused by that) and run locally


$ psql -U postgres
psql (17.4)
Type "help" for help.

timeshift_database=# ALTER ROLE timeshift_user PASSWORD 'timeshift_pass';
ALTER ROLE

Then the connection from the other pod works immediately


My guess it has to do with:

-c 'ALTER USER timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'

I am still trying to work out what that quoting is doing?







--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to debug: password authentication failed for user

[Alexander Farber]

That seems to be the reason, thank you Adrian.

At first connecting from the other pod in the AKS cluster does not work:

$ PGPASSWORD=timeshift_pass psql --host=timeshiftservice --port=5432
--dbname=timeshift_database --username=timeshift_user
psql: error: connection to server at "timeshiftservice" (10.0.120.194),
port 5432 failed: FATAL:  password authentication failed for user "t
imeshift_user"

But once I connect to my PostgreSQL pod (or is it called "container"? I am
always confused by that) and run locally

$ psql -U postgres
psql (17.4)
Type "help" for help.

timeshift_database=# ALTER ROLE timeshift_user PASSWORD 'timeshift_pass';
ALTER ROLE

Then the connection from the other pod works immediately

How to debug: password authentication failed for user

[Alexander Farber]

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  DATABASEUSERADDRESS METHOD
# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32trust
hostreplication 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

Re: How to debug: password authentication failed for user

[Adrian Klaver]

On 2/27/25 08:46, Alexander Farber wrote:

Good evening,



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)


Why set PGPASSWORD=timeshift_pass and use --password?

The password should be picked up from the env variable.

psql: error: connection to server at "timeshiftservice" (10.0.120.194), 
port 5432 failed: FATAL:  password authentication failed for user 
"timeshift_user"




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)


What happens if, when logged in via local, you do:

ALTER ROLE timeshift_user PASSWORD 'timeshift_pass';

and then try logging in via the remote connection?

Seeing if maybe the script is passing in or mangling the password.




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


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to debug: password authentication failed for user

[Tom Lane]

Adrian Klaver  writes:
> My guess it has to do with:
> -c 'ALTER USER timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'
> I am still trying to work out what that quoting is doing?

That makes my head hurt, too.  Using log_statement to see what's
actually getting sent to the server, I can see that as given
it looks to be fine --- but if you put a shell variable in for
the password as per the original intention, it's not expanded.
I think what you need is

 -c 'ALTER USER timeshift_user PASSWORD '"'$PASSWORD'"';'

Note this will fall over with potential for SQL injection if there's a
single quote in the password, so better not use it with untrusted
input.  On the whole I'd say "find some other way to do that".

regards, tom lane

Re: How to debug: password authentication failed for user

[Greg Sabino Mullane]

On Thu, Feb 27, 2025 at 1:32 PM Tom Lane  wrote:

> > -c 'ALTER USER timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'
> > I am still trying to work out what that quoting is doing?
>

That's standard for -x output for some versions of bash. FWIW, none of the
shells I had access to output it quite like that, but who knows what shell
the OP has. It's basically trying to stop the current ALTER USER statement,
add a new single quote (but wrap it in double quotes!), then start a new
single-quoted string (the actual password). (So single,
double-single-double, single). Then do it all again at the end. It's valid,
and it should really be equivalent to PASSWORD 'timeshift_pass' so it's
hard to see what the problem is.

The fact that a manual ALTER ROLE cleared it up certainly suggests that
something is going wrong, however, and the SELECT rolpassword output
definitely means it had some password. A possibility is that somehow the
user password was already set and this particular statement was not run (or
run on a different cluster).

Another debug technique might be to have the shell script write the
ALTER USER command to a temp file, then slurp it in via psql -f. Then you
can cat that file as part of the script's output

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

Re: How to debug: password authentication failed for user

[Adrian Klaver]

On 2/27/25 10:57, Greg Sabino Mullane wrote:
On Thu, Feb 27, 2025 at 1:32 PM Tom Lane > wrote:


 > -c 'ALTER USER timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'
 > I am still trying to work out what that quoting is doing?


That's standard for -x output for some versions of bash. FWIW, none of 
the shells I had access to output it quite like that, but who knows what 
shell the OP has. It's basically trying to stop the current ALTER USER 


I have to believe it is is related to this sequence:

docker build -f ./Dockerfile --build-arg PGPASSWORD=timeshift_pass

[...]

ARG PGPASSWORD

ENV POSTGRES_PASSWORD=$PGPASSWORD

[...]

ENV PGUSER=timeshift_user
ENV PGPASSWORD=$PGPASSWORD

[...]

RUN chmod +x ./01-create-database.sh ./04-alter-owner.sh

Where /01-create-database.sh has:

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


I just don't know enough about Docker to really understand all the hoops 
that are being jumped through in the above.



statement, add a new single quote (but wrap it in double quotes!), then 
start a new single-quoted string (the actual password). (So single, 
double-single-double, single). Then do it all again at the end. It's 
valid, and it should really be equivalent to PASSWORD 'timeshift_pass' 
so it's hard to see what the problem is.


The fact that a manual ALTER ROLE cleared it up certainly suggests that 
something is going wrong, however, and the SELECT rolpassword output 
definitely means it had some password. A possibility is that somehow the 
user password was already set and this particular statement was not run 
(or run on a different cluster).


Another debug technique might be to have the shell script write the 
ALTER USER command to a temp file, then slurp it in via psql -f. Then 
you can cat that file as part of the script's output


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com 
Enterprise Postgres Software Products & Tech Support



--
Adrian Klaver
adrian.kla...@aklaver.com