Обсуждение: SSL connectivity issue from PGAdmin to Google Cloud SQL

Hi Support team,

We have hosted PGAdmin on Google cloud run and are able to successfully connect to google cloud sql server. We are enforcing ssl connection on google cloud sql for which we copied the ssl certs to pgadmin(1. using volume mounts on cloud run, 2. Added a simple Dockerfile to copy certs) and then we used ssl parameters from PGAdmin console and specified the respective paths for client cert, client key and server cert. 

We get the following error when we select SSL mode as verify-full

The following error when we choose SSL mode as require or verify-ca

Note: We tried both copying the certs to custom path i.e. /var/lib/pgadmin/certs and also the default path /var/lib/pgadmin/.postgresql/, neither of them works and we cannot update the path for client certs from pgadmin console

Could you please take a look and help us to troubleshoot?

Thanks,

Prashanth Golla

Hi Yogesh,

Thanks for the reply. Since, we have hosted pgadmin on cloud run, which is serverless we cannot use terminal to connect. 

We have an application hosted on cloud run using the same database and are able to successfully connect when we enable ssl.

Note - We are using Google cloud SQL certificates which are managed by GCP.

Do you have any other steps I need to look into?

Thanks,

Prashanth Golla

On Fri, May 24, 2024 at 12:03 AM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

Error indicates the issue is with ssl certificates itself.

Can you please try connecting via terminal using these certificates?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Thu, May 23, 2024 at 6:46 PM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Support team,

We have hosted PGAdmin on Google cloud run and are able to successfully connect to google cloud sql server. We are enforcing ssl connection on google cloud sql for which we copied the ssl certs to pgadmin(1. using volume mounts on cloud run, 2. Added a simple Dockerfile to copy certs) and then we used ssl parameters from PGAdmin console and specified the respective paths for client cert, client key and server cert. 

We get the following error when we select SSL mode as verify-full

The following error when we choose SSL mode as require or verify-ca

Note: We tried both copying the certs to custom path i.e. /var/lib/pgadmin/certs and also the default path /var/lib/pgadmin/.postgresql/, neither of them works and we cannot update the path for client certs from pgadmin console

Could you please take a look and help us to troubleshoot?

Thanks,

Prashanth Golla

Hi Yogesh,

Sorry for the late reply, I was on vacation. 

I have attached the screenshot for connecting SSL enabled Postgresql(Cloud SQL) using psql client below and can successfully connect.

Could you please share further instructions?

Thanks,

Prashanth Golla

On Mon, May 27, 2024 at 12:36 AM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

Can you please try to connect PostgreSQL(Google cloud sql) using a terminal with psql utility where you can provide ssl certificates in psql command to connect?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Fri, May 24, 2024 at 8:10 PM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Yogesh,

Thanks for the reply. Since, we have hosted pgadmin on cloud run, which is serverless we cannot use terminal to connect. 

We have an application hosted on cloud run using the same database and are able to successfully connect when we enable ssl.

Note - We are using Google cloud SQL certificates which are managed by GCP.

Do you have any other steps I need to look into?

Thanks,

Prashanth Golla

On Fri, May 24, 2024 at 12:03 AM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

Error indicates the issue is with ssl certificates itself.

Can you please try connecting via terminal using these certificates?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Thu, May 23, 2024 at 6:46 PM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Support team,

We have hosted PGAdmin on Google cloud run and are able to successfully connect to google cloud sql server. We are enforcing ssl connection on google cloud sql for which we copied the ssl certs to pgadmin(1. using volume mounts on cloud run, 2. Added a simple Dockerfile to copy certs) and then we used ssl parameters from PGAdmin console and specified the respective paths for client cert, client key and server cert. 

We get the following error when we select SSL mode as verify-full

The following error when we choose SSL mode as require or verify-ca

Note: We tried both copying the certs to custom path i.e. /var/lib/pgadmin/certs and also the default path /var/lib/pgadmin/.postgresql/, neither of them works and we cannot update the path for client certs from pgadmin console

Could you please take a look and help us to troubleshoot?

Thanks,

Prashanth Golla

Hi,

1. Since we are hosting PGadmin on cloud run(serverless framework from GCP), we do not have specific OS

2. Server mode

Below is my Dockerfile which addresses point 3 and 4

Thanks,

Prashanth Golla

On Wed, Jun 5, 2024 at 11:37 PM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

What is the OS ?

What is the pgAdmin mode?(Desktop/Server)? 

What is the location for ssl certificates? 

Can you please list the permissions for ssl certificate and folder?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Thu, Jun 6, 2024 at 8:45 AM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Yogesh,

Sorry for the late reply, I was on vacation. 

I have attached the screenshot for connecting SSL enabled Postgresql(Cloud SQL) using psql client below and can successfully connect.

Could you please share further instructions?

Thanks,

Prashanth Golla

On Mon, May 27, 2024 at 12:36 AM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

Can you please try to connect PostgreSQL(Google cloud sql) using a terminal with psql utility where you can provide ssl certificates in psql command to connect?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Fri, May 24, 2024 at 8:10 PM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Yogesh,

Thanks for the reply. Since, we have hosted pgadmin on cloud run, which is serverless we cannot use terminal to connect. 

We have an application hosted on cloud run using the same database and are able to successfully connect when we enable ssl.

Note - We are using Google cloud SQL certificates which are managed by GCP.

Do you have any other steps I need to look into?

Thanks,

Prashanth Golla

On Fri, May 24, 2024 at 12:03 AM Yogesh Mahajan <yogesh.mahajan@enterprisedb.com> wrote:

Hi,

Error indicates the issue is with ssl certificates itself.

Can you please try connecting via terminal using these certificates?

Thanks,

Yogesh Mahajan

EnterpriseDB

On Thu, May 23, 2024 at 6:46 PM Prashanth Golla <prashanth@modiface.com> wrote:

Hi Support team,

We have hosted PGAdmin on Google cloud run and are able to successfully connect to google cloud sql server. We are enforcing ssl connection on google cloud sql for which we copied the ssl certs to pgadmin(1. using volume mounts on cloud run, 2. Added a simple Dockerfile to copy certs) and then we used ssl parameters from PGAdmin console and specified the respective paths for client cert, client key and server cert. 

We get the following error when we select SSL mode as verify-full

The following error when we choose SSL mode as require or verify-ca

Note: We tried both copying the certs to custom path i.e. /var/lib/pgadmin/certs and also the default path /var/lib/pgadmin/.postgresql/, neither of them works and we cannot update the path for client certs from pgadmin console

Could you please take a look and help us to troubleshoot?

Thanks,

Prashanth Golla