Subject: Re: Azure Database for PostgreSQL flexible server: password authentication failed

From: Alexander Farber <[email-masked]>
Date: Fri, 28 Feb 2025 15:56:43 +0100

Sorry, correction for the openssl command output:

$ openssl s_client -connect 172.21.32.4:5432 -starttls postgres
Connecting to 172.21.32.4
CONNECTED(00000003)
Can't use SSL_get_servername
depth=2 C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root
G2
verify return:1
depth=1 C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing
CA 07
verify return:1
depth=0 C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=
c1fba9900d4d.database.azure.com
verify return:1
---
Certificate chain
0 s:C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=
c1fba9900d4d.database.azure.com
   i:C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA384
   v:NotBefore: Feb 25 14:04:55 2025 GMT; NotAfter: Aug 24 14:04:55 2025 GMT
1 s:C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07
   i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
   a:PKEY: rsaEncryption, 4096 (bit); sigalg: RSA-SHA384
   v:NotBefore: Jun  8 00:00:00 2023 GMT; NotAfter: Aug 25 23:59:59 2026 GMT
2 s:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
   i:C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
   v:NotBefore: Aug  1 12:00:00 2013 GMT; NotAfter: Jan 15 12:00:00 2038 GMT
---
Server certificate
-----BEGIN CERTIFICATE-----
-----END CERTIFICATE-----
subject=C=US, ST=WA, L=Redmond, O=Microsoft Corporation, CN=
c1fba9900d4d.database.azure.com
issuer=C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA
07
---
Acceptable client certificate CA names
DC=GBL, DC=AME, CN=AME INFRA CA 01
DC=GBL, DC=AME, CN=AME Infra CA 02
DC=GBL, DC=AME, CN=AME Infra CA 03
DC=GBL, DC=AME, CN=AME Infra CA 04
DC=GBL, DC=AME, CN=AME Infra CA 05
DC=GBL, DC=AME, CN=AME Infra CA 06
CN=AME G1 TLS RSA 2048 SHA256 2024 CUS CA 07
CN=AME G1 TLS RSA 2048 SHA256 2024 EUS2 CA 07
CN=AME G1 TLS RSA 2048 SHA256 2024 EUS2EUAP CA 07
CN=AME G1 TLS RSA 2048 SHA256 2024 WCUS CA 07
CN=AME G1 TLS RSA 2048 SHA256 2024 WUS2 CA 07
CN=MSIT CA Z2
C=US, O=Microsoft Corporation, CN=MSFT BALT RS256 CA
C=US, O=Microsoft Corporation, CN=MSFT RS256 CA-1
C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 03
C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 04
C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 07
C=US, O=Microsoft Corporation, CN=Microsoft Azure RSA TLS Issuing CA 08
C=US, O=DigiCert Inc, CN=DigiCert Global G2 TLS RSA SHA256 2020 CA1
C=US, O=DigiCert Inc, CN=DigiCert SHA2 Secure Server CA
C=US, O=DigiCert Inc, CN=DigiCert TLS RSA SHA256 2020 CA1
C=US, O=DigiCert Inc, CN=DigiCert TLS Hybrid ECC SHA384 2020 CA1
C=US, O=Microsoft Corporation, CN=Microsoft Azure ECC TLS Issuing CA 03
C=US, O=Microsoft Corporation, CN=Microsoft Azure ECC TLS Issuing CA 04
C=US, O=Microsoft Corporation, CN=Microsoft Azure ECC TLS Issuing CA 07
C=US, O=Microsoft Corporation, CN=Microsoft Azure ECC TLS Issuing CA 08
C=US, O=Microsoft Corporation, CN=Microsoft ECC TLS Issuing AOC CA 01
C=US, O=Microsoft Corporation, CN=Microsoft ECC TLS Issuing AOC CA 02
C=US, O=Microsoft Corporation, CN=Microsoft ECC TLS Issuing EOC CA 02
C=US, O=Microsoft Corporation, CN=Microsoft ECC TLS Issuing EOC CA 01
C=US, O=Microsoft Corporation, CN=Microsoft RSA TLS Issuing EOC CA 01
C=US, O=Microsoft Corporation, CN=Microsoft RSA TLS Issuing AOC CA 01
C=US, O=Microsoft Corporation, CN=Microsoft RSA TLS Issuing AOC CA 02
C=US, O=Microsoft Corporation, CN=Microsoft RSA TLS Issuing EOC CA 02
C=US, O=Entrust, Inc., OU=See www.entrust.net/legal-terms, OU=(c) 2012
Entrust, Inc. - for authorized use only, CN=Entrust Certificat
ion Authority - L1K
C=US, O=Entrust, Inc., OU=See www.entrust.net/legal-terms, OU=(c) 2014
Entrust, Inc. - for authorized use only, CN=Entrust Certificat
ion Authority - L1M
CN=CCME G1 TLS RSA 2048 SHA256 2049 CUS CA 01
CN=CCME G1 TLS RSA 2048 SHA256 2049 EUS2 CA 01
CN=CCME G1 TLS RSA 2048 SHA256 2049 EU2C CA 01
CN=CCME G1 TLS RSA 2048 SHA256 2049 WCUS CA 01
CN=CCME G1 TLS RSA 2048 SHA256 2049 WUS2 CA 01
DC=GBL, DC=AME, CN=ameroot
CN=Microsoft Internal Corporate Root
C=IE, O=Baltimore, OU=CyberTrust, CN=Baltimore CyberTrust Root
C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G2
C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root CA
C=US, O=DigiCert Inc, OU=www.digicert.com, CN=DigiCert Global Root G3
C=US, O=Microsoft Corporation, CN=Microsoft ECC Root Certificate Authority
2017
C=US, O=Microsoft Corporation, CN=Microsoft RSA Root Certificate Authority
2017
C=US, O=Entrust, Inc., OU=See www.entrust.net/legal-terms, OU=(c) 2009
Entrust, Inc. - for authorized use only, CN=Entrust Root Certification
Author
ity - G2
C=US, O=Microsoft Corporation, CN=Commercial Cloud Root CA R1
Requested Signature Algorithms:
ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:ed25519:ed448:rsa_pss_pss_sha256:rsa_pss_pss_sha384:rsa_pss_pss_sha512:RSA-PS
S+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:ECDSA+SHA1:RSA+SHA224:RSA+SHA1
Shared Requested Signature Algorithms:
ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:ed25519:ed448:rsa_pss_pss_sha256:rsa_pss_pss_sha384:rsa_pss_pss_sha512
:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: ECDH, prime256v1, 256 bits
---
SSL handshake has read 9903 bytes and written 749 bytes
Verification: OK
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
This TLS version forbids renegotiation.
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---

But then psql fails:

$ psql "postgresql://postgres:password12345!!@
172.21.32.4:5432/postgres?sslmode=require"
psql: error: connection to server at "172.21.32.4", port 5432 failed:
FATAL:  password authentication failed for user "postgres"

---

From: Alexander Farber <[email-masked]>
Date: Fri, 28 Feb 2025 16:05:20 +0100

Ah my team mate has spotted the error for me.

And it paid off, that he has no experience with PostgreSQL yet :-)

Because the admin user for Azure Database for PostgreSQL flexible server is
not "postgres" but "postgresql"

Now the connection from a container in AKS works for me:

$ psql "postgresql://postgresql:password12345!!@
172.21.32.4:5432/postgres?sslmode=require"
psql (17.4, server 16.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: none)
Type "help" for help.

postgres=>

I must find now how to add a non-super user to it next.

Best regards
Alex

---

From: Alicja Kucharczyk <[email-masked]>
Date: Fri, 28 Feb 2025 16:16:38 +0100

Hi Alex,

> compression: off, ALPN: none)

good it finally worked ;)

> I must find now how to add a non-super user to it next.
You are adding users there as in any other postgres instance, just use
CREATE ROLE/USER from SQL prompt or use createuser utility.