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 1to25W-00549N-OT for pgsql-general@arkaria.postgresql.org; Fri, 28 Feb 2025 15:15:43 +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 1to25X-008cjZ-HS for pgsql-general@arkaria.postgresql.org; Fri, 28 Feb 2025 15:15:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1to25X-008ch5-4t for pgsql-general@lists.postgresql.org; Fri, 28 Feb 2025 15:15:41 +0000 Received: from mail-ed1-x532.google.com ([2a00:1450:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1to25S-000C5f-0q for pgsql-general@postgresql.org; Fri, 28 Feb 2025 15:15:41 +0000 Received: by mail-ed1-x532.google.com with SMTP id 4fb4d7f45d1cf-5e4ce6e3b8cso2740039a12.1 for ; Fri, 28 Feb 2025 07:15:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740755739; x=1741360539; darn=postgresql.org; h=cc:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=D2jT/OwZOxvhZtemq4Sq+1wvBDmb2dpkP5Rhwn1rwSQ=; b=IDENu8+Z5wcVi5KaCdSL83uIlMrL1WPiy9t/6IU5KzNGXkX3AUsgGncINS/Zrz5Vqq /QqwC+eQXdUllj7ljK2PfiSws4Ew3V8m190kA9PMXvzDOrp9pln9mdrQ4YSfhDuspaVw DVp37WJgOfoulL4su3HOKZw7/pXe4i5Syta8/eartMDY+/PBRNj1eTGrgTnyhpY4GhSY JH5UNXaAE/tQiZxScF7fsOizP+C0CbL18DrdREqth1W0zlu70k2b6N1RA5cbczSjVthh V//zs+rsJ5XFWq+hd2F484mSm7b8NYFPSq98A8Ml3KZnVmsR9KL3PcNLZBJ4Uygut+NJ QKnw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740755739; x=1741360539; h=cc:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=D2jT/OwZOxvhZtemq4Sq+1wvBDmb2dpkP5Rhwn1rwSQ=; b=Ist39ti3Nn+mhUUUuX4norWfZ8Gn4ub3vt+aBoHhBW4EoJ090TBYYfMDFQ7W1mKzZF cuon8GTqC+4Bm9HIelVwQCsBTGNmXMsgHfsdU8TKtk+eE8N2zgLDSEnt1tlln7hJk+IE Fop84q39BCGRtSJdE3LYRqP6unixhxhuptB3AQFxENUazT5ubSao8bMeahK5HLqNoym/ V/DmR7aYXKfSJuK2U7kN6p/5thcpvlhVC3WMddUb1XoPEjpqRP+KiWZRLwKzAZXXk7DV 198j1/p8sBFWI3FhpvQvmyvHCcRmQD1G0VIaL+XfxQA5TLIkXX6lY7fXAzeoWS+TqJSL q1Tw== X-Gm-Message-State: AOJu0YwoHqmMPdS04NFAJY8xbU6l+Bz3VaoeLTKe4cO5FMoQoHIq89XX nKuH4sNcbqjXaXj+mTGoree5Fwl0lBqqPq38AdjPhdX9Fe0BApaIPVjU3RFSJ/SaGb3ttKHIR+0 L939cQHR5/LWeELyOZPzMkC7tzgDj3a5We7U= X-Gm-Gg: ASbGncuzJp98Go6QMtMfG7wcH//dATZz2SyGqNPfbUirCXssOYHIgTOwzVhVC792Tts FFMKAYLNnikX/b4mEwTC7KTWYjUz6Y6n46GsHHtyFsqXqRftqLz0Y3Z/2XSaGrUIZuGNMOZwmM+ JZD1CgWRj9 X-Google-Smtp-Source: AGHT+IGQCd0EP9NNi8E1p1Y7XsEe8VINApbXOmzpeWZbTsoQSOFLbxD6HMoYB9AxHTHMVzp4GZek9qZudXnzGJGJMKU= X-Received: by 2002:a05:6402:270f:b0:5d3:e99c:6bda with SMTP id 4fb4d7f45d1cf-5e4bfbb6408mr9606113a12.16.1740755738284; Fri, 28 Feb 2025 07:15:38 -0800 (PST) MIME-Version: 1.0 References: <056ebaa8-94bb-461d-905c-4035eab9ceac@aklaver.com> <2602739.1740681158@sss.pgh.pa.us> <49036afe-bfc0-48fc-9cef-971397efb426@aklaver.com> In-Reply-To: <49036afe-bfc0-48fc-9cef-971397efb426@aklaver.com> From: Alexander Farber Date: Fri, 28 Feb 2025 16:15:26 +0100 X-Gm-Features: AQ5f1JoyVd_S7xnJd9aLrig8otZ6rTyq_3vneDmmNHke_sZdl4fOEd0QFBEAuf8 Message-ID: Subject: Re: How to debug: password authentication failed for user Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000006bdd19062f354732" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006bdd19062f354732 Content-Type: text/plain; charset="UTF-8" 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 --0000000000006bdd19062f354732 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for = the comments, I must think some time how to handle this.

My intentio= n 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 wh= en starting up in Kubernetes) and also pass them to the pipeline tasks, whi= ch would pass them as the build arg to the "docker build" command= :

# Generate random passwords as save them as KV secrets
- task: AzurePowerShell@5
=C2=A0 displayName: 'Write= PostgreSQL passwords to KV'
=C2=A0 inputs:
=C2=A0 = =C2=A0 azureSubscription: '${{ parameters.ArmConnection }}'
=C2=A0 =C2=A0 ScriptType: 'InlineScript'
=C2=A0 =C2=A0= azurePowerShellVersion: 'LatestVersion'
=C2=A0 =C2=A0 In= line: |
=C2=A0 =C2=A0 =C2=A0 # Generate a random password for PUS= H_PULL_PASS and store it in the KV
=C2=A0 =C2=A0 =C2=A0 $pushPull= Pass =3D ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A) | Get-Random -= Count 20 | % {[char]$_}) )
=C2=A0 =C2=A0 =C2=A0 $pushPullSecret = =3D ConvertTo-SecureString -String $pushPullPass -AsPlainText -Force
<= div>=C2=A0 =C2=A0 =C2=A0 Set-AzKeyVaultSecret -VaultName '${{ parameter= s.ResourceKeyVault }}' -Name PushPullPass -SecretValue $pushPullSecret<= /div>
=C2=A0 =C2=A0 =C2=A0 # Generate a random password for TIMESHIFT_P= ASS and store it in the KV
=C2=A0 =C2=A0 =C2=A0 $timeshiftPass = =3D ( -join ((0x30..0x39) + (0x41..0x5A) + (0x61..0x7A) | Get-Random -Count= 20 | % {[char]$_}) )
=C2=A0 =C2=A0 =C2=A0 $timeshiftSecret =3D C= onvertTo-SecureString -String $timeshiftPass -AsPlainText -Force
= =C2=A0 =C2=A0 =C2=A0 Set-AzKeyVaultSecret -VaultName '${{ parameters.Re= sourceKeyVault }}' -Name TimeshiftPass -SecretValue $timeshiftSecret
=C2=A0 =C2=A0 =C2=A0 # Set the pipeline vars for the 2 docker build= s below
=C2=A0 =C2=A0 =C2=A0 Write-Host "##vso[task.setvaria= ble variable=3DPushPullPass]$pushPullPass"
=C2=A0 =C2=A0 =C2= =A0 Write-Host "##vso[task.setvariable variable=3DTimeshiftPass]$times= hiftPass"

# build Push Pull DB docker file
- task: Docker@2
=C2=A0 displayName: Build Push Pull DB d= ocker image
=C2=A0 inputs:
=C2=A0 =C2=A0 command: build=
=C2=A0 =C2=A0 repository: '$(PushPullReponame)'
=C2=A0 =C2=A0 dockerfile:=C2=A0 '$(Build.SourcesDirectory)/suuCcg/src= /Services/SUU.PushPullDatabase/Dockerfile'
=C2=A0 =C2=A0 argu= ments: "--no-cache --build-arg PGPASSWORD=3D$(PushPullPass)"
=C2=A0 =C2=A0 tags: $(ImageTag)
=C2=A0 =C2=A0 buildContext:= '$(Build.SourcesDirectory)/suuCcg/src/'

#= build Timeshift DB docker file
- task: Docker@2
=C2=A0= displayName: Build Timeshift DB docker image
=C2=A0 inputs:
=C2=A0 =C2=A0 command: build
=C2=A0 =C2=A0 repository: '= ;$(TimeshiftReponame)'
=C2=A0 =C2=A0 dockerfile:=C2=A0 '$= (Build.SourcesDirectory)/suuCcg/src/Services/SUU.TimeshiftDatabase/Dockerfi= le'
=C2=A0 =C2=A0 arguments: "--no-cache --build-arg PGP= ASSWORD=3D$(TimeshiftPass)"
=C2=A0 =C2=A0 tags: $(ImageTag)<= /div>
=C2=A0 =C2=A0 buildContext: '$(Build.SourcesDirectory)/suuCcg= /src/'

And then I am not done yet :-) I need to pass that random= string from the Dockerfile to the=C2=A001-create-database.sql and I have t= ried it as env var:

# To build locally: docker build -f Service= s/SUU.TimeshiftDatabase/Dockerfile --build-arg PGPASSWORD=3Dtimeshift_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 a= dd --no-cache pg_top

ARG PGPASSWORD

=
# Tell docker-entrypoint.sh to create superuser "postgres&q= uot;
# with password passed as build arg and database "postg= res"
ENV POSTGRES_PASSWORD=3D$PGPASSWORD

# Tell docker-entrypoint.sh to change these params in postgresql.conf=
ENV POSTGRES_INITDB_ARGS=3D"--set max_connections=3D200 \
=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 --set shared_buffers=3D16GB \
=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 --set work_mem=3D8MB \
=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 --set maintenance_w= ork_mem=3D128MB \
=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 --set effective_cache_size=3D= 8GB \
=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 --set from_collapse_limit=3D24 \
= =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 --set join_collapse_limit=3D24 \
=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 --set log_min_messages=3Dnotice \
=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 --set log_c= onnections=3Don \
=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 --set log_statement=3Dall \
=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 --set listen_addresses=3D'*'"

ENV PGUSER=3Dpostgres
ENV PGPASSWORD=3D$PGPASSW= ORD
ENV PGDATABASE=3Dtimeshift_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.Tim= eshiftDatabase/01-create-database.sql .

COPY ./Ser= vices/SUU.VehicleService/TimeshifCalculator/timeshift-create-tables.sql ./0= 2-create-tables.sql
COPY ./Services/SUU.VehicleService/TimeshifCa= lculator/timeshift-create-functions.sql ./03-create-functions.sql
COPY ./Services/SUU.VehicleService/TimeshifCalculator/timeshift-smoke-test= s.sql ./04-smoke-tests.sql

# Prepend \c timeshift_= database to each SQL file using sed
RUN sed -i.bak '1i\\\\c t= imeshift_database' ./02-create-tables.sql
RUN sed -i.bak '= ;1i\\\\c timeshift_database' ./03-create-functions.sql
RUN se= d -i.bak '1i\\\\c timeshift_database' ./04-smoke-tests.sql

# Drop root privileges
USER postgres
<= br>But I see that the whole chain is tricky to implement and I'd like t= o switch from a postgres:17-alpine3.21 based Docker image to the "Azur= e PostgreSQL flexible server" product anyway, to have less maintenance= .

Best regards
Alex



--0000000000006bdd19062f354732--