Secure SSL connection between Matlab and PostgreSQL

I’d like to introduce guest blogger Jeff Mandel of the Perelman School of Medicine at the University of Pennsylvania. Today Jeff will discuss a how-to guide for setting up an SSL connection between Matlab and a PostgreSQL database. While this specific topic may be of interest to only a few readers, it involves hard-to-trace problems that are not well documented anywhere. The techniques discussed below may also be applicable, with necessary modifications, to other SSL targets and may thus be of use to a wider group of Matlab users.

PostgreSQL database
I’m developing software for pharmacokinetic control, and needed secure access to a central database from users at remote sites. The client software is written in Matlab, and while I have targeted MacOS, this could be adapted to Windows fairly easily. Hopefully, this will save someone the week it took me to figure all this out.

My environment:

  • PostgreSQL 9.4 installed on the server (Windows 7 PC, but Linux would be equally good)
  • DynDNS CNAME pointing at the server (diseserver.mydomain.org)
  • CACert.org registration for domain mydomain.org
  • Matlab 2015b running on El Capitan

Here are the neccesary steps:

  1. First, we need a certificate for the server. We can generate this with OpenSSL:
    $openssl req -out diseserver.csr -new -newkey rsa:2048 -nodes -keyout diseserver.key

    Specify any information you want on the key, but ensure CN=diseserver.mydomain.org.

  2. Paste the resulting diseserver.csr file into a new key request at CACert.org. Save the resulting certificate as diseserver.crt on your machine.
  3. While still at CACert.org, grab the Class 1 root certificate and save it as root.crt.
  4. Put the files diseserver.key, diseserver.crt, and root.crt in the PostgreSQL data directory.
  5. Edit your postgresql.conf file:
    ssl = on
    ssl_cert_file = 'diseserver.crt'  # (change requires restart)
    ssl_key_file  = 'diseserver.key'  # (change requires restart)
    ssl_ca_file   = 'root.crt'        # (change requires restart)
  6. Restart the PostgreSQL server. The server will now permit SSL connections, a necessary pre-condition for certificate authentication.
  7. We now add 2 lines to pg_hba.conf:
    hostnossl  all    all   0.0.0.0/0   reject
    hostssl	 mytable  all   0.0.0.0/0   cert map=ssl clientcert=1

    The first line causes all non-SSL connections to be rejected. The second allows certificate logins for mytable using the map ssl that is defined in pg_ident.conf:

    ssl  /^(.*).mydomain\.org$ \1

    this line extracts the username prefix from CN=username.mydomain.org.

  8. Now we need to generate client certificates. PostgreSQL expects these to be in ~/.postgresql (Windows %appdata%\postgresql\):
    $mkdir ~/.postgresql
    $cd ~/.postgresql
    $openssl req -out postgresql.csr -new -newkey rsa:2048 -nodes -keyout postgresql.key

    for this key, make CN=username.mydomain.org.

  9. Again, paste the resulting postgresql.csr file into CACert.org, saving the certificate as postgresql.crt.
  10. Test this:
    $psql "sslmode=verify-full host=diseserver.mydomain.org dbname=effect user=username"

    The server should respond:

    psql (9.4.6, server 9.4.1)
    SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
  11. Next we need to convert our key into pkcs8 format so that Java can read it:
    $openssl pkcs8 -topk8 -inform PEM -outform DER -in postgresql.key -out postgresql.pk8 -nocrypt
  12. Next, ensure that we have the correct version of the JDBC driver (Java-to-database connector). From the Mac command line:
    $java -version
    java version "1.8.0_05"

    and in Matlab:

    >> version -java
    ans =
    Java 1.7.0_75-b13 with Oracle Corporation Java HotSpot(TM) 64-Bit Server VM mixed mode

    This shows that although we have Java 8 installed on El Capitan (at the OS level), Matlab uses a private Java 7 version. So we need the correct version of the jdbc on our static java classpath that is used by Matlab:

    ~/Matlab/postgresql-9.4.1208.jre7.jar
  13. The next part is very poorly documented in both the MathWorks and the PostgreSQL documentation, but I found it in Russel Gray’s Basildon Coder blog: We need to use the jdbc postgresql driver to check the client certificate. To do this, we need a custom SSLSocketFactory – LibPQFactory. This will grab our certificate and key from ~/.postgresql and present them to the server. The url is (note the trailing &):
    jdbc:postgresql://diseserver.mydomain.org/mytable?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&
  14. Next we need the username. Rather than hard-coding this in the source code, we get the system username:
    >> username = java.lang.System.getProperty('user.name');
  15. Bundle this all up in a Matlab function, stripping the trailing CR from the username:
    function dbtest
       driver = 'org.postgresql.Driver';
       [~,username] = system('whoami');
       url = 'jdbc:postgresql://diseserver.mydomain.org/mytable?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&';
       myconn = database('mytable', username, '', driver, url);
       if ~isempty(myconn.Message)
          fprintf(2,'%s\n', myconn.Message);
       else
          fprintf(1, 'Connected!\n');
       end
    end

Now we can connect from the Matlab command line or a Matlab program.

What if we’re deployed? We also need to add the contents of our .postgresql directory, plus the jdbc jar file to our deployed app:

>> mcc -m dbtest.m -a ~/.postgresql -a ~/Matlab/postgresql-9.4.1208.jre7.jar

Let’s test the compiled program from the OS command line:

$./run_dbtest.sh /Applications/Matlab/Matlab_Runtime/v90
Connected!

Note that the key and certificates are part of the encrypted bundle produced by Matlab’s mcc compiler.

I hope this helps someone!

Yair’s note: the Matlab code above uses Matlab’s Database Toolbox (specifically, the database function) to connect to the database. In future posts I plan to show how we can connect Matlab directly to a database via JDBC. This topic is covered in detail in chapter 2 of my Matlab-Java programming secrets book.

p.s. – this blog celebrates a 7-year anniversary tomorrow: I published my very first post here on March 19, 2009, showing how to change Matlab’s command-window colors (a post that later led to the now-famous cprintf utility). It’s been a long and very interesting ride indeed, but I have no plans to retire anytime soon :-)

Categories: Guest bloggers, High risk of breaking in future versions, Undocumented feature

Tags: ,

Bookmark and SharePrint Print

Leave a Reply


Your email address will not be published. Required fields are marked *