Efcore.pg: How to connect via client certificate?

[FrameBassman]

I have postgresql cluster from https://cloud.yandex.ru.
For connection via psql util, firstly I should download certificate from yandex:

mkdir ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/root.crt && \
chmod 0600 ~/.postgresql/root.crt

And then I should use the following command:

psql "host=xxx.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=MY_DATABASE \
      user=MY_USERNAME \
      target_session_attrs=read-write"

I try to connect via npgsql with the following connection string:

Host=rc1a-xxx.mdb.yandexcloud.net;Port=6432;SSL Mode=Require;Trust Server Certificate=true;Database=MY_DATABASE;Username=MY_USERNAME

and it fails with the following error:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.2.3-servicing-35854 initialized 'ApplicationContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
System.Net.Internals.SocketExceptionFactory+ExtendedSocketException (00000005, 6): No such device or address
   at System.Net.Dns.InternalGetHostByName(String hostName)
   at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)
   at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout) in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 663
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 555
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 414
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 273
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnection.Open() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 153
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists() in C:\projects\npgsql-entityframeworkcore-postgresql\src\EFCore.PG\Storage\Internal\NpgsqlDatabaseCreator.cs:line 190
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
No such device or address

My dotnet info:

dotnet --info
.NET Core SDK (reflecting any global.json):
 Version:   2.2.105
 Commit:    7cecb35b92

Runtime Environment:
 OS Name:     ubuntu
 OS Version:  18.04
 OS Platform: Linux
 RID:         ubuntu.18.04-x64
 Base Path:   /usr/share/dotnet/sdk/2.2.105/

Host (useful for support):
  Version: 2.2.3
  Commit:  6b8ad509b6

.NET Core SDKs installed:
  2.2.105 [/usr/share/dotnet/sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.2.3 [/usr/share/dotnet/shared/Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.2.3 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.2.3 [/usr/share/dotnet/shared/Microsoft.NETCore.App]

[YohDeadfall]

If you want to use a client certificate, set the ProvideClientCertificatesCallback property as described in the docs. Closing as duplicate of npgsql/npgsql#1721.

[FrameBassman]

@YohDeadfall, I cannot find #1721 issue, could you drop link of this issue here?

[YohDeadfall]

Sorry, fixed the link. The issue is in the main repo.

[mifopen]

@FrameBassman, Have you finally found out how to connect to yandex cloud's postgresql using those callbacks? For now I am getting System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

[roji]

One more note... From the code sample above it seems that you're using Entity Framework Core. In that case you need to specify the client certificate callback inside your context's UseNpgsql():

c# protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql("connection string", b => b.ProvideClientCertificatesCallback(...));

This will make sure that the ProvideClientCertificatesCallback is internally set on NpgsqlConnection.

[FrameBassman]

@FrameBassman, Have you finally found out how to connect to yandex cloud's postgresql using those callbacks? For now I am getting System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

Unfortunately - no
I have the following error:
08P01: Auth failed

@mifopen, could you share your code?

[mifopen]

@FrameBassman, of course, I could =)
This is how I create dbConnection:
``` c#
private IDbConnection DbConnection()
{
var npgsqlConnection = new NpgsqlConnection(sqlOptions.ConnectionString);
if (!string.IsNullOrEmpty(sqlOptions.ClientCertPath))
npgsqlConnection.ProvideClientCertificatesCallback += ProvideClientCertificates;
return npgsqlConnection;
}

private void ProvideClientCertificates(X509CertificateCollection clientCerts)
{
var clientCertPath = sqlOptions.ClientCertPath;
var cert = new X509Certificate2(clientCertPath);
clientCerts.Add(cert);
}
```

Also, I have found out that that client certificate from Yandex is self-signed, so according to npgsql doc there is no way to make server certificate validation work and you have to add Trust Server Certificate=true to the connection string that you have already done as I see.

[rusrc]

So I use the yandex cloud too. And was getting error too. Here you are my test connection looks like this.

First install the certificate on my VM
mkdir -p ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/root.crt && \
chmod 0600 ~/.postgresql/root.crt

Then the code example:

```C#
public async Task> CanConnect()
{
const string host = "rc4c-blablablablabla.mdb.yandexcloud.net";
var connection =
$"Host={host};Port=6432;SSLMode=Require;TrustServerCertificate=true;Database=YOUR_DB_NAME;Username=YOUR_DB_USERNAME;Password=*";

        var optionsBuilder = new DbContextOptionsBuilder<DbAppContext>();
        optionsBuilder.UseNpgsql(connection, builder =>
        {
            builder.RemoteCertificateValidationCallback((s, c, ch, sslPolicyErrors) =>
            {
                if (sslPolicyErrors == SslPolicyErrors.None)
                {
                    return true;
                }
                _logService.Error($@"Certificate error: {sslPolicyErrors}, 
                Do not allow this client to communicate with unauthenticated servers");
                return false;
            });

            builder.ProvideClientCertificatesCallback(clientCerts =>
            {
                var clientCertPath = "/home/username/.postgresql/root.crt";
                // To avoid permission ex run: "sudo chmod -R 777 /home/username/.postgresql/root.crt"
                var cert = new X509Certificate2(clientCertPath);
                clientCerts.Add(cert);
            });
        });

        using (var ctx = new DbAppContext(optionsBuilder.Options))
        {
            return await ctx.Database.CanConnectAsync();
        }
    }