- Undocumented Matlab - https://undocumentedmatlab.com/blog_old -
Secure SSL connection between Matlab and PostgreSQL
Posted By Yair Altman On March 18, 2016 | No Comments
I’d like to introduce guest blogger Jeff Mandel [3] 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.
[4]
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:
Here are the neccesary steps:
$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
.
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)
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
.
$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
.
$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)
$openssl pkcs8 -topk8 -inform PEM -outform DER -in postgresql.key -out postgresql.pk8 -nocrypt
$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 [7]:
~/Matlab/postgresql-9.4.1208.jre7.jar
jdbc:postgresql://diseserver.mydomain.org/mytable?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&
>> username = java.lang.System.getProperty('user.name');
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 [9].
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 [10] (a post that later led to the now-famous cprintf utility [11]). 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
Article printed from Undocumented Matlab: https://undocumentedmatlab.com/blog_old
URL to article: https://undocumentedmatlab.com/blog_old/secure-ssl-connection-between-matlab-and-postgresql
URLs in this post:
[1] Image: https://undocumentedmatlab.com/feed/
[2] email feed: https://undocumentedmatlab.com/subscribe_email.html
[3] Jeff Mandel: http://www.med.upenn.edu/apps/faculty/index.php/g275/p40141
[4] Image: http://postgresql.org
[5] OpenSSL: http://www.openssl.org
[6] CACert.org: http://cacert.org
[7] static java classpath that is used by Matlab: https://undocumentedmatlab.com/blog/static-java-classpath-hacks
[8] Basildon Coder: https://basildoncoder.com/blog/postgresql-jdbc-client-certificates.html
[9] Matlab-Java programming secrets book: https://undocumentedmatlab.com/books/matlab-java
[10] how to change Matlab’s command-window colors: https://undocumentedmatlab.com/blog/changing-matlab-command-window-colors
[11] cprintf utility: https://undocumentedmatlab.com/blog/cprintf
[12] Matlab’s internal memory representation : https://undocumentedmatlab.com/blog_old/matlabs-internal-memory-representation
[13] Matlab compilation quirks – take 2 : https://undocumentedmatlab.com/blog_old/matlab-compilation-quirks-take-2
[14] Customizing uiundo : https://undocumentedmatlab.com/blog_old/customizing-uiundo
[15] JMI wrapper – remote MatlabControl : https://undocumentedmatlab.com/blog_old/jmi-wrapper-remote-matlabcontrol
[16] Creating a simple UDD class : https://undocumentedmatlab.com/blog_old/creating-a-simple-udd-class
[17] General-use object copy : https://undocumentedmatlab.com/blog_old/general-use-object-copy
Click here to print.
Copyright © Yair Altman - Undocumented Matlab. All rights reserved.