Zaid-Ajaj/Npgsql.FSharp

Question about SSL connection

gusennan opened this issue · 3 comments

gusennan commented

Hi, I'm loving how this library's designed.

I'm following the first example you provide in the README and am having trouble connecting to my database though and was wondering if you might see anything wrong with how I'm doing anything. I do not have the equivalent SSL certificate exception when using pgcli or Datagrip to connect to my database, which is curious. My database is managed by a major cloud provider, so I don't think it's actually a problem with their certificate. Here's the exception that I'm getting:

Npgsql.NpgsqlException (0x80004005): Exception while performing SSL handshake
 ---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
   at System.Net.Security.SslStream.SendAuthResetSignal(ProtocolToken message, ExceptionDispatchInfo exception)
   at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](TIOAdapter adapter, Boolean receiveFirst, Byte[] reAuthenticationData, Boolean isApm)
   at System.Net.Security.SslStream.ProcessAuthentication(Boolean isAsync, Boolean isApm, CancellationToken cancellationToken)
   at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
   at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<<Rent>g__RentAsync|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Npgsql.NpgsqlConnection.<>c__DisplayClass41_0.<<Open>g__OpenAsync|0>d.MoveNext()
--- End of stack trace from previous location ---
   at Npgsql.NpgsqlConnection.Open()
   at Npgsql.FSharp.Tasks.Sql.execute[t](FSharpFunc`2 read, SqlProps props)
   at Program.getAllDesigns(String connStr) in /home/nsg/code/fsharp-experiments/DigitalOcean/Program.fs:line 45

Here is how I am creating the connection string (details omitted, of course):

    let connectionString =
        Sql.host <hostname>
        |> Sql.database "osprey"
        |> Sql.username "osprey"
        |> Sql.password <password>
        |> Sql.port <port>
        |> Sql.requireSslMode
        |> Sql.formatConnectionString

and how I connect equivalently using pgcli:

pgcli 'postgresql://osprey:<password>@<hostname>:<port>/osprey?sslmode=require'

If this looks like it might be caused by something upstream at Npgsql, please let me know and I'll move this question over there.

Zaid-Ajaj commented

When requiring SSL mode, you might need to give trust to the server you are connecting to like this:

let connectionString =
    Sql.host <hostname>
    |> Sql.database "osprey"
    |> Sql.username "osprey"
    |> Sql.password <password>
    |> Sql.port <port>
    |> Sql.requireSslMode
    |> Sql.trustServerCertificate true
    |> Sql.formatConnectionString

Notice the part Sql.trustServerCertificate true, can you give this a try?

gusennan commented

When I add the Sql.trustServerCertificate true, it does indeed work, though I don't want to blindly trust the server. This behavior is same in npgsql C# too. I don't know why .NET seems to be validating SSL certificates differently from other implementations (Datagrip uses JDBC and pgcli uses psycopg2). Will move the question over to the npgsql repo to see if they might have any idea.

Thank you!

  • 👍4
njlr commented

The problem is that the underlying .NET library (Npgsql) does not support Require SSL mode properly. If you ask for Require, what you actually get is verify-full, using the .NET SSL libraries. This checks the entire cert chain of the Postgres server, so you must have appropriate certificates in your trust store. Thus, your connection string could work with psql on the command-line, but not in your F# code.

One solution is to download the certificate from Amazon and load it on application start.

wget https://www.amazontrust.com/repository/AmazonRootCA1.pem -q -O AmazonRootCA1.pem
use cert = new X509Certificate2 ("AmazonRootCA1.pem")

use store = new X509Store (StoreName.TrustedPeople, StoreLocation.CurrentUser)

store.Open OpenFlags.ReadWrite
store.Add cert // Idempotent

store.Close ()

let db =
  Sql.fromUriToConfig (Uri connectionString)
  |> Sql.connectFromConfig
  • 👍1