Undocumented Matlab
  • SERVICES
    • Consulting
    • Development
    • Training
    • Gallery
    • Testimonials
  • PRODUCTS
    • IQML: IQFeed-Matlab connector
    • IB-Matlab: InteractiveBrokers-Matlab connector
    • EODML: EODHistoricalData-Matlab connector
    • Webinars
  • BOOKS
    • Secrets of MATLAB-Java Programming
    • Accelerating MATLAB Performance
    • MATLAB Succinctly
  • ARTICLES
  • ABOUT
    • Policies
  • CONTACT
  • SERVICES
    • Consulting
    • Development
    • Training
    • Gallery
    • Testimonials
  • PRODUCTS
    • IQML: IQFeed-Matlab connector
    • IB-Matlab: InteractiveBrokers-Matlab connector
    • EODML: EODHistoricalData-Matlab connector
    • Webinars
  • BOOKS
    • Secrets of MATLAB-Java Programming
    • Accelerating MATLAB Performance
    • MATLAB Succinctly
  • ARTICLES
  • ABOUT
    • Policies
  • CONTACT

Secure SSL connection between Matlab and PostgreSQL

March 18, 2016 No Comments

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

    $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)

    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

    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

    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

    $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"

    $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)

    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

    $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"

    $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

    >> 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

    ~/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&

    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');

    >> 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

    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

>> 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!

$./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 🙂

Related posts:

  1. Speeding up Matlab-JDBC SQL queries – Fetching SQL ResultSet data from JDBC into Matlab can be made significantly faster. ...
  2. Using SQLite in Matlab – SQLite databases can be accessed in a variety of different ways in Matlab. ...
  3. Fixing Matlab's actxserver – Matlab's COM (ActiveX) server behavior can be fixed in a couple of useful manners. ...
  4. Matlab compilation quirks – take 2 – A few hard-to-trace quirks with Matlab compiler outputs are explained. ...
  5. Matlab's internal memory representation – Matlab's internal memory structure is explored and discussed. ...
  6. Matlab's HG2 mechanism – HG2 is presumably the next generation of Matlab graphics. This article tries to explore its features....
Database Jeff Mandel Pure Matlab Undocumented feature
Print Print
« Previous
Next »
Leave a Reply
HTML tags such as <b> or <i> are accepted.
Wrap code fragments inside <pre lang="matlab"> tags, like this:
<pre lang="matlab">
a = magic(3);
disp(sum(a))
</pre>
I reserve the right to edit/delete comments (read the site policies).
Not all comments will be answered. You can always email me (altmany at gmail) for private consulting.

Click here to cancel reply.

Useful links
  •  Email Yair Altman
  •  Subscribe to new posts (email)
  •  Subscribe to new posts (feed)
  •  Subscribe to new posts (reader)
  •  Subscribe to comments (feed)
 
Accelerating MATLAB Performance book
Recent Posts

Speeding-up builtin Matlab functions – part 3

Improving graphics interactivity

Interesting Matlab puzzle – analysis

Interesting Matlab puzzle

Undocumented plot marker types

Matlab toolstrip – part 9 (popup figures)

Matlab toolstrip – part 8 (galleries)

Matlab toolstrip – part 7 (selection controls)

Matlab toolstrip – part 6 (complex controls)

Matlab toolstrip – part 5 (icons)

Matlab toolstrip – part 4 (control customization)

Reverting axes controls in figure toolbar

Matlab toolstrip – part 3 (basic customization)

Matlab toolstrip – part 2 (ToolGroup App)

Matlab toolstrip – part 1

Categories
  • Desktop (45)
  • Figure window (59)
  • Guest bloggers (65)
  • GUI (165)
  • Handle graphics (84)
  • Hidden property (42)
  • Icons (15)
  • Java (174)
  • Listeners (22)
  • Memory (16)
  • Mex (13)
  • Presumed future risk (394)
    • High risk of breaking in future versions (100)
    • Low risk of breaking in future versions (160)
    • Medium risk of breaking in future versions (136)
  • Public presentation (6)
  • Semi-documented feature (10)
  • Semi-documented function (35)
  • Stock Matlab function (140)
  • Toolbox (10)
  • UI controls (52)
  • Uncategorized (13)
  • Undocumented feature (217)
  • Undocumented function (37)
Tags
ActiveX (6) AppDesigner (9) Callbacks (31) Compiler (10) Desktop (38) Donn Shull (10) Editor (8) Figure (19) FindJObj (27) GUI (141) GUIDE (8) Handle graphics (78) HG2 (34) Hidden property (51) HTML (26) Icons (9) Internal component (39) Java (178) JavaFrame (20) JIDE (19) JMI (8) Listener (17) Malcolm Lidierth (8) MCOS (11) Memory (13) Menubar (9) Mex (14) Optical illusion (11) Performance (78) Profiler (9) Pure Matlab (187) schema (7) schema.class (8) schema.prop (18) Semi-documented feature (6) Semi-documented function (33) Toolbar (14) Toolstrip (13) uicontrol (37) uifigure (8) UIInspect (12) uitools (20) Undocumented feature (187) Undocumented function (37) Undocumented property (20)
Recent Comments
  • Marcel (9 days 12 hours ago): Hi, I am trying to set the legend to Static, but this command seems not to work in R2022a anymore: set(gca,’LegendColorbarL isteners’,[]); Any ideas? THANKS / marcel
  • Gres (9 days 16 hours ago): In 2018b, you can get the icons by calling [hh,icons,plots,txt] = legend({‘Line 1’});
  • Yair Altman (11 days 11 hours ago): @Mitchell – in most cases the user wants a single string identifier for the computer, that uniquely identifies it with a distinct fingerprint that is different from any...
  • Mitchell (11 days 20 hours ago): Great post! I’m not very familiar with the network interfaces being referenced here, but it seems like the java-based cross-platform method concatenates all network...
  • Yair Altman (14 days 13 hours ago): Dani – You can use jViewport.setViewPosition(java .awt.Point(0,0)) as I showed in earlier comments here
  • dani (15 days 9 hours ago): hi!! how i can set the horizontal scrollbar to the leftside when appearing! now it set to right side of text
  • Yair Altman (24 days 5 hours ago): Dom – call drawnow *just before* you set hLine.MarkerHandle.FaceColorTy pe to 'truecoloralpha'. Also, you made a typo in your code: it’s truecoloralpha, not...
  • Dom (25 days 4 hours ago): Yair I have tried your code with trucoloralpha and the markers do not appear transparent in R2021b, same as for Oliver.
  • Yair Altman (28 days 11 hours ago): Ren – This is usually the expected behavior, which avoids unnecessary duplications of the Excel process in CPU/memory. If you want to kill the process you can always run...
  • Yair Altman (29 days 1 hour ago): When you use plot() without hold(‘on’), each new plot() clears the axes and draws a new line, so your second plot() of p2 caused the first plot() line (p1) to be...
  • Cesim Dumlu (35 days 8 hours ago): Hello. I am trying to do a gradient plot for multiple functions to be displayed on the same axes and each one is colorcoded by respective colordata, using the same scaling. The...
  • Yair Altman (43 days 12 hours ago): @Veronica – you are using the new version of uitree, which uses HTML-based uifigures, and my post was about the Java-based uitree which uses legacy Matlab figures. For...
  • Veronica Taurino (43 days 12 hours ago): >> [txt1,txt2] ans = ‘abrakadabra’
  • Veronica Taurino (43 days 12 hours ago): Hello, I am just trying to change the uitree node name as you suggested: txt1 = 'abra'; txt2 = 'kadabra'; node.setName([txt1,txt2]); >> "Unrecognized method, property, or...
  • Yair Altman (46 days 12 hours ago): The version of JGraph that you downloaded uses a newer version of Java (11) than the one that Matlab supports (8). You need to either (1) find an earlier version of JGraph that...
Contact us
Undocumented Matlab © 2009 - Yair Altman
This website and Octahedron Ltd. are not affiliated with The MathWorks Inc.; MATLAB® is a registered trademark of The MathWorks Inc.
Scroll to top