Npgsql - Connection String for Google SQL PostgreSQL instance using SSL-CodePudding

I cannot for the life of me figure out how to prepare a working connection string for a Google SQL PostgreSQL instance when using SSL. It works fine with a public IP. But I cannot figure out how to prepare a correct connection string for Npgsql when using SSL.

Not sure if the problem is my client machine configuration or the connection string parameters.

Sanitized version:

Include Error Detail=True;Client Certificate=client-cert.pem;Root Certificate=server-ca.pem;Client Certificate Key=client-key.pem;Trust Server Certificate=true;SSL Mode=Prefer;Persist Security Info=True;Password=[secret];Username=[secret];Database=[secret];Host=[secret];

Error and stack trace:

28000: connection requires a valid client certificate at Npgsql.NpgsqlConnector.<g__ReadMessageLong|194_0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.NpgsqlConnector.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at Npgsql.NpgsqlConnector.d__175.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.ConnectorPool.d__41.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<g__RentAsync|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.NpgsqlConnection.<>c__DisplayClass41_0.<g__OpenAsync|0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.NpgsqlConnection.Open() at MavPeople.Common.GetAuthObject(String login) in C:\Projects\MavPeople\MavPeople\Common.vb:line 326 at MavPeople.MainForm.MainForm_Load(Object sender, EventArgs e) in C:\Projects\MavPeople\MavPeople\MainForm.vb:line 86

All certificates are as downloaded from Google Cloud Console: *.pem files.

CodePudding user response：

Okay, finally found my way through this rat's nest. For the benefit of others I will try and describe what is required to make this work.

Firstly, a short description of what we are trying to do:

We have a WinForms .Net Framework (not .Net Core) application using Npgsql provider for PostgreSQL to connect to a Google SQL PostgreSQL instance which is configured to require SSL/TLS connections: server configuration

As part of the server configuration above, you will be downloading:

client-key.pem - client key file
client-cert.pem - client certificate file
server-ca.pem - root certificate file

Before we can use these files in our application we need to generate a certificate in a format our application can use (PEM format files are only usable in .Net Framework 5 and above). We will convert to pkcs12 format.

To do this we will use the Git Bash environment (there may be other utilities to do this, but I fought for 2 days to figure this out, and this works!).

Open a Git Bash environment in the folder where your pem certificates were downloaded.

Assuming the files were named as shown above, execute the following command to generate a new pkcs12 certificate:

winpty openssl pkcs12 -export -out client.pfx -inkey client-key.pem -in client-cert.pem -certfile server-ca.pem

As part of the processing you will be asked to create a password to sign the resulting file with and will be asked to confirm it.

The resulting certificate (client.pfx in my case) and password can be used within our application connection string.

Now, this may not be the most secure way to do it, but it works for me. I somebody has a better way to do this please share:

I added the client.pfx to my project and set it's properties to:

Build Action = Content
Copy to Output Directory = Copy if newer

This ensures the client.pfx is deployed with my application and is available at runtime for my database connection string.

Now, for my database connection string used for the Npgsql provider. I will display the connection string parameters in list form:

Include Error Detail=True;
SSL Certificate=client.pfx;
SSL Password=[whatever you specified as a password];
Trust Server Certificate=true;
SSL Mode=Require;
Persist Security Info=True;
Password=[secret];
Username=[secret];
Database=[secret];
Host=[secret];

The connection string in full (sanitized) form:

Include Error Detail=True;SSL Certificate=client.pfx;SSL Password=[whatever you specified as a password];Trust Server Certificate=true;SSL Mode=Require;Persist Security Info=True;Password=[secret];Username=[secret];Database=[secret];Host=[secret];

Special thanks to @shay and contributors for the Npgsql provider.