SSL error: certificate verify failed when using psql connect the Azure PostgreSQL - Flexible Server

Moary Chen (CSI Interfusion Inc) 20 Microsoft Vendor

Hi team,

I am trying to use Azure Database for PostgreSQL - Flexible Server within a private VNET, I followed the doc Connect using psql to test the connectivity using ssl mode is verify-full, the used certificate file DigiCertGlobalRootCA.crt.pem is downloaded from this link in the section Download root CA certificates , and it's converted to pem format through the tool openssl.

When execute the command, I found the below exception:

SSL error: certificate verify failed

Could you help to provide any doc to troubleshoot the root cause? See more from the original GitHub issue.

Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-18T13:48:43.4133333+00:00

Hi @Moary Chen (CSI Interfusion Inc),

Thank you for posting your query on Microsoft Q&A.

Based on your description, it seems you're encountering an error message that states "SSL error: certificate verify failed." I recommend checking whether your root certificate authority file exists at the following path: %APPDATA%\postgresql\root.crt. Please verify if you can find the root certificate in that location.Additionally, it's important to ensure that TLS 1.2 is enabled on your server. You can check the status of TLS 1.2 in the Registry Editor by navigating to: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]. The "Enabled" DWORD value should be set to 1 (i.e., "Enabled"=dword:00000001). If the value is 0, that indicates TLS 1.2 is not enabled.

To enable TLS 1.2 using PowerShell, please refer to the documentation. Enable TLS 1.2.

I hope this information helps, please do let us know if you have any Queries.

Thanks,
Sai Raghunadh M
Moary Chen (CSI Interfusion Inc) 20 Reputation points Microsoft Vendor

2024-09-19T00:59:51.5033333+00:00
Please help provide the further details, here are the results I checked from Windows 11 Pro:

I checked the local PostgreSQL 14.13 (download from the link) directory, there's no file %APPDATA%\postgresql\root.crt.

TLS1.2 is enabled on the Server side (Azure PostgreSQL flexible server version 14.12)

Registry path does not exist, I follow this section PowerShell script to enable TLS 1.2 and execute the Powershell code, the registry path is still the same.
Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-23T07:51:17.14+00:00

Hi @Moary Chen (CSI Interfusion Inc),

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-24T10:08:30.0433333+00:00

Hi @Moary Chen (CSI Interfusion Inc),

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Moary Chen (CSI Interfusion Inc) 20 Reputation points Microsoft Vendor

2024-09-25T08:02:25.6266667+00:00
Not yet resolved. I will start to verify the steps you provided. I am confusing this path %APPDATA%\postgresql\root.crt, what's the full path in your local PC? I am using postgresql-14.13.1-window-x64 for PostgreSQL Flexible 14.12, could you help double confirm if there are other prerequisites?

Actually, after the PSQL connection verification, I will use java client with jdbc url to connect the server, please help check how to generate the cert postgresql.crt? the jdbc url contains the below parameters:

sslmode=verify-full&sslrootcert=/postgresql.crt
Moary Chen (CSI Interfusion Inc) 20 Reputation points Microsoft Vendor

2024-09-25T08:03:11.24+00:00
Not yet resolved. I will start to verify the steps you provided. I am confusing this path %APPDATA%\postgresql\root.crt, what's the full path in your local PC? I am using postgresql-14.13.1-window-x64 for PostgreSQL Flexible 14.12, could you help double confirm if there are other prerequisites?

Actually, after the PSQL connection verification, I will use java client with jdbc url to connect the server, please help check how to generate the cert postgresql.crt? the jdbc url contains the below parameters:

sslmode=verify-full&sslrootcert=/postgresql.crt
Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-25T16:28:21.56+00:00

Hi @Moary Chen (CSI Interfusion Inc),

As you asked, the full path for %APPDATA%\postgresql\root.crt in local pc is " C:\Users<YourUsername>\AppData\Roaming."

so, the full path for root.crt would be C:\Users\<YourUsername>\AppData\Roaming\postgresql\root.crt

Prerequisites for PostgreSQL 14.13.1 on Windows:

Make sure you are running a compatible version of Windows.

To generate the postgresql.crt certificate, follow these steps

1.Generate a Private Key and Certificate Signing Request (CSR):

openssl req -new -nodes -text -out postgresql.csr -keyout postgresql.key

2.Generate a Self-Signed Certificate:

openssl x509 -req -in postgresql.csr -text -days 365 -out postgresql.crt -key postgresql.key

3.Copy the Certificates to the Appropriate Directory:

place postgresql.crt and root.crt in the %APPDATA%\postgresql\ directory.

By following these steps you will be able to generate postgresql.crt for JDBC URL.

Your JDBC URL should look something like: jdbc:postgresql://<hostname>:<port>/<database>?sslmode=verify-full&sslrootcert=C:/Users/<YourUsername>/AppData/Roaming/postgresql/root.crt.

Make sure to replace <hostname>, <port>, <database>, and <YourUsername> with your actual values.

I hope this information helps, please let us know if you have any further Queries.
Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-27T02:57:38.1466667+00:00

Hi @Moary Chen (CSI Interfusion Inc),

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Moary Chen (CSI Interfusion Inc) 20 Reputation points Microsoft Vendor

2024-09-27T03:05:42.78+00:00

The root.crt is the Microsoft RSA Root Certificate Authority 2017, then my Spring Boot app can normally connect the database server with sslmode=verify-full. For simplicity, it is recommended to use Microsoft's JDK, which includes several related certificates, I used the Microsoft jdk 17.0.11.

Accepted answer

Sai Raghunadh M 1,045 Reputation points Microsoft Vendor

2024-09-20T09:51:21.39+00:00
Hi @Moary Chen (CSI Interfusion Inc),

The %APPDATA%\postgresql\root.crt file is created automatically when you install PostgreSQL on your machine

As you said that there is no %APPDATA%\postgresql\root.crt. you can create the path manually.

Here are the steps for creating the path:

1.) Open Notepad.

Copy and paste the root certificate (Microsoft RSA Root Certificate Authority 2017) contents into Notepad.

Save the file as root.crt.

2.) Open the Run dialog:

Press Win + R, type %APPDATA%, and hit Enter.

Create the PostgreSQL folder (if it doesn't exist):

Right-click, select New > Folder, and name it postgresql.

3.) Create the Data folder:

Inside the postgresql folder, right-click, select New > Folder, and name it data.

4.) Move root.crt:

Place the root.crt file into the %APPDATA%\postgresql\data folder.

Your root.crt is now in %APPDATA%\postgresql\data, and certificate verification will work. I hope this information helps, please do let us know if you have any Queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSL error: certificate verify failed when using psql connect the Azure PostgreSQL - Flexible Server

0 additional answers

Your answer