Coming up with an interesting issue. I can connect to the database fine using JetBrains Rider, but when trying to connect using LLBLGen I get the following error: The remote certificate is invalid according to the validation procedure.

I don't understand why JetBrains has no issue, but the .NET is rejecting the connection. Turning SSL off gives

Exception type: PostgresException
28000: no pg_hba.conf entry for host "111.222.216.68", user "postgres", database "postgres", no encryption

Here's the stack dump for the SSL=Require error:

LLBLGen Pro version 5.11. Build 5.11.3
-----[Core exception]--------------------
   at System.Net.Security.SslState.StartSendAuthResetSignal(ProtocolToken message, AsyncProtocolRequest asyncRequest, Exception exception)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ProcessReceivedBlob(Byte[] buffer, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest, Boolean renegotiation)
   at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
   at Npgsql.NpgsqlConnector.<RawOpen>d__153.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnector.<Open>d__149.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.ConnectorPool.<AllocateLong>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlConnection.Open()
   at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.TestConnectionData(Boolean showSuccess)

The designer ships with npgsql 4.0.12. This is because newer versions contain a bug that makes it fail on .net framework, and this version works fine for what we're doing.

The connection string that's used in the postgresql driver is build using the following code:

public override string ConstructConnectionString(Dictionary<ConnectionElement, string> connectionElementsToUse)
{
    var connectionString = new StringBuilder();
    DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "Database", connectionElementsToUse.GetValue(ConnectionElement.CatalogName) ?? string.Empty);
    DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "Server", connectionElementsToUse.GetValue(ConnectionElement.ServerName) ?? string.Empty);
    DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "Port", connectionElementsToUse.GetValue(ConnectionElement.PortNumber) ?? string.Empty);
    DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "User Id", connectionElementsToUse.GetValue(ConnectionElement.UserID) ?? string.Empty);
    DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "Password", connectionElementsToUse.GetValue(ConnectionElement.Password) ?? string.Empty);
    if(connectionElementsToUse.ContainsKey(ConnectionElement.SslMode))
    {
        string sslMode = connectionElementsToUse.GetValue(ConnectionElement.SslMode) ?? string.Empty;
        if(sslMode.ToLowerInvariant() != "disable")
        {
            DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "SslMode", sslMode);
        }
    }
    return connectionString.ToString();
}

where the values used are the ones you fill in in the wizard gui.

I honestly don't know what the reason is for this issue. I do think rider uses a different way to connect to the database so that's likely not comparable. Could you use the code above with a console app and npgsql to see if you can reproduce it with that? The connection object is created with the DbProviderFactory, using CreateConnection() after which we set its ConnectionString property with the string created with the method above.

The npgsql people might know?

I searched for the error you got, and google's first reply was a visited link on stackexchange so I think I had the same problem at one point I think this might help: https://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host

It may be that by default AWS' RDS link has an invalid SSL certificate, but adding the following allows a connection: DbConnectionStringBuilder.AppendKeyValuePair(connectionString, "Trust Server Certificate", "true");

I would suggest this either be an option in the dialogue box, or added an another SSL option such as "Unvalidated" as it's pretty common to want the SSL protocol when you don't yet have a valid certificate.

Regards, Jason

Otis wrote:

I searched for the error you got, and google's first reply was a visited link on stackexchange so I think I had the same problem at one point I think this might help: https://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host

The issue here is that these solutions involve editing the config files on the server, and with RDS there is no way to do that. I couldn't find any built-in feature to let you configure default certificate handling to override default connection parameters, so I think they consider it a client responsibility.

Regards, Jason

Looking at the documentation for RDS it appears you have to specify the certificate (if you want it validating at all): https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.SSL.html#PostgreSQL.Concepts.General.SSL.Connecting

I wonder if it would be better to allow manual provision of connectionstring, rather than only building it. There are dozens of optional parameters for certificates, and that would simplify ways of adding weird overrides where necessary, such as ignoring the validation.

OK, I found a workaround. If you download the global-bundle.pem from your AWS RDS instance (it contains all of the regions certificates) you can add them to your local machine's certificates. This makes the SSL validation pass. It's a pain, as you have to split the file into individual certificates and add one at a time, but you can use the command line to do it by using the command: certutil -addstore -enterprise -f "Root" global-bundle-part-x.pem

where global-bundle-part-x.pem is the individual certificate in the bundle.

I do however still think the connectionstring override would be good, as there are many other options available.

Good that you have found this workaround. Thanks for sharing.

We don't have custom connection string parameters as it wasn't necessary to do so (i.e. the arguments to specify were all configurable). Is there documentation about which settings you're referring to regarding certificates that we're missing currently? Thanks!

Otis wrote:

We don't have custom connection string parameters as it wasn't necessary to do so (i.e. the arguments to specify were all configurable). Is there documentation about which settings you're referring to regarding certificates that we're missing currently? Thanks!

The main one I'm guessing would be Trust Server Certificate, but there are several required if you need to specify a local certificate file. Many of them have environment variables, but that get's messy if you have multiple certificates.

For a full (horrendously long) list you can check out the doco here: https://www.npgsql.org/doc/connection-string-parameters.html

Thanks!

yeah postgres is popular, but it sure is a big mess at times