How do I connect a Google App Engine API (ASP.NET Core 3.1) to Google Cloud SQL (Postgres) on SSL?

Question

This is the most frustrating question I've never had to ask. How do I connect these two?

I have an API (ASP.NET Core 3.1) and I deploy this application onto Google's App Engine. I also have a database on Cloud SQL (PostgreSQL engine 12) that I connect to. Without SSL, the connection works perfectly. Adding SSL is a tremendous pain. I continually get the following errors:

Error Connecting to database FATAL : no pg_hba.conf entry for host"x.x.x.x", user"jiradbuser", database"jiradb", SSL off

The above error doesn't really make any sense as I don't hav any options on GCP to modify such a file.

I have downloaded the client-cert.pem, client-key.pem, and server-ca.pem files that Google gave me when I configured the database to accept only SSL connections, and when I run the following command in PSQL, I connect just fine over SSL:

psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=<cloud sql public ip> port=5432 user=< my username> dbname=<database inside cloud sql instance>"

I have tried using NpgSqlConnectionStringBuilder where I grab the related ----BEGIN CERTIFICATE-------END CERTIFICATE--- files from a configuration file to generate a connection string with that info in it, but that does not work:

    var connectionStringBuilder = new NpgsqlConnectionStringBuilder(connectionString)
    {
        RootCertificate = Configuration["Google:ServerCA"],
        ClientCertificate = Configuration["Google:ClientCert"],
        ClientCertificateKey = Configuration["Google:ClientKey"],
        SslMode = SslMode.Require,
        TrustServerCertificate = true,
        IncludeErrorDetails = true
    };

Below is the error message:

"exception\":{\"StackTrace\":\"   at Interop.Crypto.CheckValidOpenSslHandle(SafeHandle handle)\\n   at Internal.Cryptography.Pal.OpenSslX509CertificateReader.FromFile(String fileName, SafePasswordHandle password, X509KeyStorageFlags keyStorageFlags)\\n   at System.Security.Cryptography.X509Certificates.X509Certificate..ctor(String fileName, String password, X509KeyStorageFlags keyStorageFlags)\\n   at System.Security.Cryptography.X509Certificates.X509Certificate2..ctor(String fileName, String password)\\n   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)\\n   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)\\n   at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)\\n   at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<<Rent>g__RentAsync|0>d.MoveNext()\\n--- End of stack trace from previous location where exception was thrown ---

Is there a step I am missing? Do I need to convert these files to a different format (such as .pfx or .crt or .key)? I do not think I would have to because otherwise why would Google give me .pem files to begin with? Additionally, one thing I am leaning toward is storing the files themselves inside Google Secret Manager ... would that change anything?

I am open to answering your questions or adding additional info as appropriate. Thanks!

Answer 1

Working solution

I have been suffering with the same issue for the last 24 hours. I lay out below a working solution. Apologies for first attempt... that was me thinking aloud which is not appropriate in these forums.

This solution is based around a C# dotnet core SDK 5.0.201 Console Application running on a Windows 10 Professional with a Google SQL Cloud PostgreSQL 13 backend database.

PEM files

Google SQL Cloud supplies PEM files; three of them. Two certificates and a key. There is a Server Certificate Authority certificate (server-ca.pem) and a Client certificate with a corresponding Key (client-cert.pem and client-key.pem respectively).

As per your example, the psql command suggests all three of these are required to connect to SQL Cloud. I'm using pgAdmin which does very much the same.

The Client certificate and corresponding key will need to be converted into a PFX (Personal Information eXchange) format. This file format essentially pairs the Client certificate and key into a single file.

There are two ways to perform this conversion... using the openssl command or doing it in code with C#.

You can find both of these methods in the "Answer" of this StackOverflow post...

Npgsql connection with ssl certificates in .net core web api

Credit: Anish-V

How to correctly pass the Certificates and Key to the Npgsql Connection

Connection string

Firstly, set up your connection string. As well as the SSL certificates, you still need to provide information such as host, database name, and database username and password. Also, because we are targeting SSL connections with Google SQL Cloud, you will likely have ticked the "Allow only SSL connections" flag within the Google Console. If so, you will also need to set SslMode to "Require" (or perhaps "Prefer" depending on your requirement/configuration).

var _connectionString = new NpgsqlConnectionStringBuilder()
{
    Host = settings.Host,
    Database = settings.Database,
    Username = settings.Username,
    Password = settings.Password,
    SslMode = SslMode.Require
};
var _pgdb = new NpgsqlConnection(_connectionString.ConnectionString);

Client Certificate and Key

Looking at the Client certificate and key, the StackOverflow post above takes the Client certificate (client-cert.pem) and key (client-key.pem) and creates a combined PFX certificate. As mentioned above, you can do this either with openssl or in code. At the moment I am pursuing the "in code" approach to reduce any manual steps in the future when I release the application. However, I have tested the openssl approach and generated a PFX file... that also works.

To add the PFX certificate to the connection, you use the ProvideClientCertificatesCallback method of the NpgsqlConnection class...

_pgdb.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback;
_pgdb.Open();

We now overload the ProvideClientCertificatesCallback method with our own implemetation as follows...

public void ProvideClientCertificatesCallback(X509CertificateCollection certificates)
{
    var clientCert = 
        GetCombinedCertificateAndKey(
            $@"{certificatePath}\client-cert.pem",
            $@"{certificatePath}\client-key.pem");
    certificates.Add(clientCert);
    Console.WritLine($"client-cert.pem/client-key.perm: {clientCert.Issuer}");
}

The method GetCombinedCertificateAndKey takes two parameters... the filenames for the Client certificate and Client key. This method is borrowed from the StackOverflow post mentioned above... I tweeked it slightly...

private X509Certificate2 GetCombinedCertificateAndKey(string clientCertPath, string clientKeyPath)
{
    using var publicKey = new X509Certificate2(clientCertPath); ;

    var privateKeyText = System.IO.File.ReadAllText(clientKeyPath);
    var privateKeyBlocks = privateKeyText.Split("-", StringSplitOptions.RemoveEmptyEntries);
    var privateKeyBytes = Convert.FromBase64String(privateKeyBlocks[1]);
    using var rsa = RSA.Create();

    switch (privateKeyBlocks[0]) {
        case "BEGIN PRIVATE KEY":
            rsa.ImportPkcs8PrivateKey(privateKeyBytes, out _);
            break;
        case "BEGIN RSA PRIVATE KEY":
            rsa.ImportRSAPrivateKey(privateKeyBytes, out _);
            break;
    }

    var keyPair = publicKey.CopyWithPrivateKey(rsa);
    var Certificate = new X509Certificate2(keyPair.Export(X509ContentType.Pfx));
    return Certificate;
}

It's nice to run this through the Visual Studio debugger... you can examine the contents of the certificates. In my case it seems that the Google client-key.pem is an RSA key which is picked up by this method in the switch statement. I suspect that will be same for all Google keys.

Also note, the line before the return statement... it exports the Client certificate/key pair in a combined PFX format and returns that as a certificate to the calling method.

Server Certificate Authority certificate

For the Server CA certificate, my current solution is to bypass the validation of this certificate. To do this simply add the TrustServerCertificate property to the connection string as follows. As the name suggests, this determines whether to trust the server certificate without validating it. My thinking is that in this situation I trust that the certificate is provided by reputible source... Google. However, I will keep looking for a more complete solution to this whereby I can validate the Server CA certificate.

var _connectionString = new NpgsqlConnectionStringBuilder()
{
    Host = settings.Host,
    Database = settings.Database,
    Username = settings.Username,
    Password = settings.Password,
    SslMode = SslMode.Require,
    TrustServerCertificate = true,
};
var _pgdb = new NpgsqlConnection(_connectionString.ConnectionString);

Conclusion...

And that's it. It works! :-) I hope it works for you and saves you a day of your life.

In answer to the question about storing the certificates in Google Secret Manager, I would recommend that although that's whole other topic.