Connecting to PostgreSQL with SSL and client certificate file in Xojo
You can pass various options when connecting to PostgreSQL server with SQLDatabaseMBS or SQLConnectionMBS classes in our MBS Xojo SQL Plugin. This includes several SSL options. If ssl mode is require, the SSL encryption is required. But only if you provide local certificate chain and the mode verify-full, the chain of certificates is validated. So if someone puts in a proxy with their own certificate, your application would not connect.
So here a sample code about how to connect with a client side certificate, a private key and the root certificates to validate against:
dim r as new SQLDatabaseMBS
// the certificate and key files. PEM files work, too.
Dim certFile As FolderItem = GetFolderItem("postgresql.crt")
Dim authFile As FolderItem = GetFolderItem("root.crt")
Dim keyFile As FolderItem = GetFolderItem("postgresql.key")
// where the library file is located. dylib for Mac.
Dim LibFile As FolderItem = GetFolderItem("libpq.5.11.dylib")
// get native paths:
Dim certPath As String = certFile.NativePath
Dim authPath As String = authFile.NativePath
Dim keyPath As String = keyFile.NativePath
Dim libPath As String = LibFile.NativePath
// options from here:
// https://www.postgresql.org/docs/9.5/libpq-connect.html
// build the option strings
dim options as string = _
"dbname='myDatabase' "+_
"connect_timeout=2 "+_
"application_name='test' " + _
"sslrootcert='"+authPath+"' "+_
"sslcert='"+certPath+"' "+_
"sslkey='"+keyPath+"' "+_
"sslmode=verify-full"
const host = "database.domain.com"
const port = "63996"
r.DatabaseName = "PostgreSQL:"+host+","+port+"@"+options
// credentials
r.UserName = "xxx"
r.Password = "yyy"
// set path to library
r.Option(r.kOptionLibraryPostgreSQL) = libPath
If r.Connect() Then
MsgBox "Connected"
Else
MsgBox r.ErrorMessage
End If
If you have questions, please don't hesitate to contact us.