Sometimes we want to connect to a PostgreSQL database through SSL/TLS, but the database's CA Certificate aren't trusted by the computer. This is the case with DigitalOcean's Managed Database offerings. There are a couple different ways to solve this. First, you can simply use sslmode=require to skip verification; however, this opens the application up to man-in-the-middle attacks, as it can't verify the server's identity. The second way is to verify the root certificate ourselves by hooking one of Npgsql's callbacks.

To start, we need the CA Certificate, which we'll save in the project as ca-certificate.crt. If you're using Digital Ocean, you can find the cert on the overview page for your cluster, underneath Connection Details:

Since the CA certificate isn't trusted by the computer, we have to verify the certificate ourselves. We'll do this by hooking NpgsqlConnection.UserCertificateValidationCallback and determining whether the certificate is valid:

NpgsqlConnection connection = new NpgsqlConnection(connectionString);
connection.UserCertificateValidationCallback += ValidatePgServerCertificate;

Where the signature of ValidatePgServerCertificate is

bool ValidatePgServerCertificate
(
    object sender,
    X509Certificate cert,
    X509Chain chain,
    SslPolicyErrors policyErrors
)

If there aren't any errors, then the certificate has already been validated.

if (policyErrors == SslPolicyErrors.None)
    return true;

We only care about handle chain errors, so fail if there's any other kind of error.

if (policyErrors != SslPolicyErrors.RemoteCertificateChainErrors)
    return false;

We'll also check the chain status. Again, we don't care about any status other than UntrustedRoot, so fail if that occurs.

if (chain.ChainStatus.Length != 1)
    return false;
if (chain.ChainStatus[0].Status != X509ChainStatusFlags.UntrustedRoot)
    return false;

Since we trust the CA, we'll check the chain elements to ensure that the root certificate in the chain matches the CA cert.

// Load the CA Certificate - this can come from a variety of sources.
// For simplicity, we'll just load it directly from the file.
X509Certificate2 caCert = new X509Certificate2("./ca-certificate.crt");

// The last cert in the chain is the root cert.
// If it equals the CA Cert, we consider the certificate and chain to be valid.
int lastIndex = chain.ChainElements.Count - 1;
return chain.ChainElements[lastIndex].Certificate.Equals(caCert);

Putting it all together, we get:

private bool ValidatePgServerCertificate
(
    Object sender,
    X509Certificate cert,
    X509Chain chain,
    SslPolicyErrors policyErrors
)
{
    if (policyErrors == SslPolicyErrors.None)
        return true;
    if (policyErrors != SslPolicyErrors.RemoteCertificateChainErrors)
        return false;
    if (chain.ChainStatus.Length != 1)
        return false;
    if (chain.ChainStatus[0].Status != X509ChainStatusFlags.UntrustedRoot)
        return false;

    X509Certificate2 caCert = new X509Certificate2("./ca-certificate.crt");
    int lastIndex = chain.ChainElements.Count - 1;
    return chain.ChainElements[lastIndex].Certificate.Equals(caCert);
}