public inbox for [email protected]
help / color / mirror / Atom feedRe: How to debug: password authentication failed for user
4+ messages / 4 participants
[nested] [flat]
* Re: How to debug: password authentication failed for user
@ 2025-02-27 18:32 Tom Lane <[email protected]>
2025-02-27 18:57 ` Re: How to debug: password authentication failed for user Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-02-27 18:32 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Alexander Farber <[email protected]>; pgsql-general
Adrian Klaver <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to debug: password authentication failed for user
2025-02-27 18:32 Re: How to debug: password authentication failed for user Tom Lane <[email protected]>
@ 2025-02-27 18:57 ` Greg Sabino Mullane <[email protected]>
2025-02-27 21:07 ` Re: How to debug: password authentication failed for user Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Greg Sabino Mullane @ 2025-02-27 18:57 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Alexander Farber <[email protected]>; pgsql-general
On Thu, Feb 27, 2025 at 1:32 PM Tom Lane <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to debug: password authentication failed for user
2025-02-27 18:32 Re: How to debug: password authentication failed for user Tom Lane <[email protected]>
2025-02-27 18:57 ` Re: How to debug: password authentication failed for user Greg Sabino Mullane <[email protected]>
@ 2025-02-27 21:07 ` Adrian Klaver <[email protected]>
2025-02-28 15:15 ` Re: How to debug: password authentication failed for user Alexander Farber <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2025-02-27 21:07 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; Tom Lane <[email protected]>; +Cc: Alexander Farber <[email protected]>; pgsql-general
On 2/27/25 10:57, Greg Sabino Mullane wrote:
> On Thu, Feb 27, 2025 at 1:32 PM Tom Lane <[email protected]
> <mailto:[email protected]>> 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 <https://www.crunchydata.com;
> Enterprise Postgres Software Products & Tech Support
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to debug: password authentication failed for user
2025-02-27 18:32 Re: How to debug: password authentication failed for user Tom Lane <[email protected]>
2025-02-27 18:57 ` Re: How to debug: password authentication failed for user Greg Sabino Mullane <[email protected]>
2025-02-27 21:07 ` Re: How to debug: password authentication failed for user Adrian Klaver <[email protected]>
@ 2025-02-28 15:15 ` Alexander Farber <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Alexander Farber @ 2025-02-28 15:15 UTC (permalink / raw)
To: ; +Cc: pgsql-general
Thank you for the comments, I must think some time how to handle this.
My intention has been to generate 2 random strings in an Azure pipeline,
save them as secrets in an Azure Key Vault (so that our C# ASP.Net app can
fetch them when starting up in Kubernetes) and also pass them to the
pipeline tasks, which would pass them as the build arg to the "docker
build" command:
# Generate random passwords as save them as KV secrets
- task: AzurePowerShell@5
displayName: 'Write PostgreSQL passwords to KV'
inputs:
azureSubscription: '${{ parameters.ArmConnection }}'
ScriptType: 'InlineScript'
azurePowerShellVersion: 'LatestVersion'
Inline: |
# Generate a random password for PUSH_PULL_PASS and store it in the KV
$pushPullPass = ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A) |
Get-Random -Count 20 | % {[char]$_}) )
$pushPullSecret = ConvertTo-SecureString -String $pushPullPass
-AsPlainText -Force
Set-AzKeyVaultSecret -VaultName '${{ parameters.ResourceKeyVault }}'
-Name PushPullPass -SecretValue $pushPullSecret
# Generate a random password for TIMESHIFT_PASS and store it in the KV
$timeshiftPass = ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A)
| Get-Random -Count 20 | % {[char]$_}) )
$timeshiftSecret = ConvertTo-SecureString -String $timeshiftPass
-AsPlainText -Force
Set-AzKeyVaultSecret -VaultName '${{ parameters.ResourceKeyVault }}'
-Name TimeshiftPass -SecretValue $timeshiftSecret
# Set the pipeline vars for the 2 docker builds below
Write-Host "##vso[task.setvariable
variable=PushPullPass]$pushPullPass"
Write-Host "##vso[task.setvariable
variable=TimeshiftPass]$timeshiftPass"
# build Push Pull DB docker file
- task: Docker@2
displayName: Build Push Pull DB docker image
inputs:
command: build
repository: '$(PushPullReponame)'
dockerfile:
'$(Build.SourcesDirectory)/suuCcg/src/Services/SUU.PushPullDatabase/Dockerfile'
arguments: "--no-cache --build-arg PGPASSWORD=$(PushPullPass)"
tags: $(ImageTag)
buildContext: '$(Build.SourcesDirectory)/suuCcg/src/'
# build Timeshift DB docker file
- task: Docker@2
displayName: Build Timeshift DB docker image
inputs:
command: build
repository: '$(TimeshiftReponame)'
dockerfile:
'$(Build.SourcesDirectory)/suuCcg/src/Services/SUU.TimeshiftDatabase/Dockerfile'
arguments: "--no-cache --build-arg PGPASSWORD=$(TimeshiftPass)"
tags: $(ImageTag)
buildContext: '$(Build.SourcesDirectory)/suuCcg/src/'
And then I am not done yet :-) I need to pass that random string from the
Dockerfile to the 01-create-database.sql and I have tried it as env var:
# To build locally: docker build -f
Services/SUU.TimeshiftDatabase/Dockerfile --build-arg
PGPASSWORD=timeshift_pass .
# To run locally in Git Bash: winpty docker run --rm -it -p 5432:5432
sha256:...
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=all \
--set listen_addresses='*'"
ENV PGUSER=postgres
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 ./Services/SUU.TimeshiftDatabase/01-create-database.sql .
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-create-tables.sql
./02-create-tables.sql
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-create-functions.sql
./03-create-functions.sql
COPY
./Services/SUU.VehicleService/TimeshifCalculator/timeshift-smoke-tests.sql
./04-smoke-tests.sql
# Prepend \c timeshift_database to each SQL file using sed
RUN sed -i.bak '1i\\\\c timeshift_database' ./02-create-tables.sql
RUN sed -i.bak '1i\\\\c timeshift_database' ./03-create-functions.sql
RUN sed -i.bak '1i\\\\c timeshift_database' ./04-smoke-tests.sql
# Drop root privileges
USER postgres
But I see that the whole chain is tricky to implement and I'd like to
switch from a postgres:17-alpine3.21 based Docker image to the "Azure
PostgreSQL flexible server" product anyway, to have less maintenance.
Best regards
Alex
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-02-28 15:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-27 18:32 Re: How to debug: password authentication failed for user Tom Lane <[email protected]>
2025-02-27 18:57 ` Greg Sabino Mullane <[email protected]>
2025-02-27 21:07 ` Adrian Klaver <[email protected]>
2025-02-28 15:15 ` Alexander Farber <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox