« Scroll through record… | Home | MBS FileMaker Plugin,… »

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.
16 02 19 - 20:01