How to Persist Data from Openhab2.5 on RPi to a MariaDB MYSQL Server on a Synology Using SSL

Tags: #<Tag:0x00007f616f51b738> #<Tag:0x00007f616f51b5d0> #<Tag:0x00007f616f51b4e0>

Would really appreciate guidance to get this working. The docs in Paper UI Add Ons relating to Persistence appear to still be under development.

Key Questions:

  1. For the configuration in the title to this question, do I need to use addon:
    a) “JDBC Persistence MariaDB”, or
    b) JDBC Persistence MySQL or
    c) MySQL Persistence?

  2. What should I name the “.persist” file? jdbc.persist, mariadb.persist, mysql.persist - or doesn’t it matter?
    In the RPi folder /etc/openhab2/services a file is automatically created upon restart (jdbc.cfg, or mysql.cfg). This is fine - I seem to be able to fill it in appropriately.

Many thanks!

  1. Any of those three should work I think. Since you are using MariaDB I’d probably choose the first one.

  2. From the docs:

This service can be configured in the file services/jdbc.cfg .

when using the JDBC add-on.

The name of the .persist file very much does matter.

Thank you!
Working now.
If my Synology/mariadb persistence database were remote from the RPi, with a fixed IP address, and SSL encryption, how would I modify the approach?

Presumably I would need to do the following:

  1. Modify the mariadb database user table to accept a remote host (say fred@no-ip.org:3306)
  2. Arrange for port forwarding
  3. Change the jdbc.cfg file. Would the following make sense?
    url=jdbc:mariadb://fred@no-ip.org:3306/databasename
    Would i need to specify https somewhere?
  4. Are there any other config file adjustments to be made - for example: where would I store the client certificate (provided by the Synology), and how would I point openhab to that location on the RPi?

Much appreciated!

I don’t use MariaDB so can’t comment on the questions.

Thanks Rich - though let me clarify - my question is more about where to store the certificate on the RPi with Openhab installed.

The following would be the entry in the jdbc.cfg file:
url=jdbc:mariadb://fred@no-ip.org:3306/databasename?useSSL=true

In which folder would I store the certificate, and in what form, such that Openhab can find it? This would be a self signed certificate issued by the Synology.
Cheers!

If you don’t see anything about a certificate parameter in the JDBC binding, I would guess either:

  • OH doesn’t verify whether or not the certificate is issued by a trusted CA
  • OH doesn’t support TLS connections to MariaDB.

Any certificate verification will be implemented by the JDBC add-on, not by OH in general. So if the docs don’t mention anything about it, it’s not supported.

And in general, you always have to provide a path to a certificate. There usually isn’t just some folder you can drop it in.

Really appreciate the help.

Posting below steps to get MariaDB Database on a Synology working with Openhab 2.5 on an RPin on same LAN. Hope this can be of some use to others. It was not straightforward for a new user. Please critique constructively.

Later will follow up with steps to get SSL working with the same config and also remotely.

  1. On Synology, using phpMyadmin, create a new user, with host specified as LAN addresss of RPi. Check box to grant all privileges and create database with same name as user.
    You may find that RPi has 2 IP addresses, take the LAN not the Wifi address.

  2. On RPi, from Paper UI, select Add-Ons, Persistence, and then install JDBC Persistence MariaDB

  3. May need to now stop and restart openhab on RPi. Command line:
    sudo systemct1 stop openhab2.service
    followed by
    sudo systemct1 start openhab2.service

  4. RPi folder /etc/openhab2/services will now have a new file named jdcb.cfg. Edit this file (remove the # signs as appropriate). You want the url as follows:
    url=jdbc:mariadb://xxx.xxx.x.xxx:3306/databasenamethatyouchose

    Also need to add in the username (same as database if you followed suggestion above) and password.
    No quotes needed.
    Remove the #.
    3306 is the port used by MariaDB on Synology.
    Also made the following change for readability:
    tableUseRealItemNames=true

  5. You may not already have a new file in /etc/openhab2/persistence; you need to add one called jdbc.persist. In order to get going with something functional, use the following text in the file (copied largely from https://www.openhab.org/docs/configuration/persistence.html though modifying the Items to include everychange.

     Strategies 
     {
     everyHour : "0 0 * * * ?"
     everyDay  : "0 0 0 * * ?"
     default = everyChange
     }
    
     Items 
     {
             * : strategy = everyChange, restoreOnStartup
     }
    
  6. If you access your Synology you should now find that your database has had a table created which is being populated with items from openhab. You are done for now. Your fantail logs should also be without errors.

  7. However if like me you made mistakes along the way, you may find a series of error messages in the logs, for example:
    [ERROR] [org.openhab.persistence.jdbc ] - bundle org.openhab.persistence.jdbc:1.14.0 (266)[org.openhab.persistence.jdbc(247)] : The activate method has thrown an exception
    com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool:…

    or other access issues then it may be necessary to delete incorrect database configurations from openhab on the RPi. To do this open RPi console and type
    openhab-cli console
    followed by the password (default is habopen)

    then type
    config:list
    then review the long list that appears - should be straightforward to identify the pid sections which have been erroneously stored.

  8. Delete unnecessary configurations. For example:

    config:delete org.openhab.jdbc
    or
    config:delete org.openhab.mysql

  9. Then logout:
    logout

    sudo systemct1 stop openhab2.service
    sudo systemct1 start openhab2.service

2 Likes

Enabling encryption between openhab and local or remote Synology MariaDB/Mysql database

Follows on from instructions above to achieve (unsecured) storage on a database on a Synology on the same LAN.

This may incorporate mistakes which I trust others will helpfully point out.

  1. Modifying the jdbc.cfg file with
    url=jdbc:mariadb://youripaddress:3306/yourdatabasename?useSSL=true&serverSslCert=/home/pi/certs_from_mariadb/ca.pem

    Results in the the following error message, among others, since the cert is not installed and database is not properly configured:

    2020-03-04 21:52:08.915 [ERROR] [org.openhab.persistence.jdbc ] - bundle org.openhab.persistence.jdbc:1.14.0 (268)[org.openhab.persistence.jdbc(247)] : The activate method has thrown an exception
    com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Trying to connect with ssl, but ssl not enabled in the server

  2. Check if SSL is enabled by following the instructions in the link below.
    https://mariadb.com/kb/en/secure-connections-overview/
    Entering the following into the SQL query box on phpMyAdmin
    SHOW GLOBAL VARIABLES LIKE 'have_ssl'
    returned
    DISABLED

  3. To address this need to log into the command line of your Diskstation. First need to ensure that admin is enabled on Diskstation (may be disabled for security reasons).
    Also need to enable SSH on Diskstation, and download PuTTY

    Log in using SSH with root permission.

    https://www.synology.com/en-global/knowledgebase/DSM/tutorial/General_Setup/How_to_login_to_DSM_with_root_permission_via_SSH_Telnet

    ssh root@youripaddress

  4. The following page then becomes relevant:

    https://mariadb.com/kb/en/configuring-mariadb-with-option-files/

    and typing in the following confirms it:
    mysqld --help --verbose
    results in (in my case) blank entries for the following :
    ssl
    ssl-ca
    ssl-capath
    ssl-cert
    ssl-cipher
    ssl-key

  5. Navigate to:
    cd /etc
    Execute command
    ls
    Some basic linux commands for file manipulation are needed; the following may be helpful:

    https://ubuntu.com/tutorials/command-line-for-beginners#1-overview

    Create new folder e.g:
    mkdir newcerts && cd newcerts

  6. Create certificates

    https://mariadb.com/kb/en/certificate-creation-with-openssl/

    or

    https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html#creating-ssl-files-using-openssl-unix-command-line

  7. Then to edit cnf file, find:
    DiskStation> cd ./mysql

    DiskStation> ls
    my.cnf

  8. Then figure out how to edit this file using Vi
    https://forum.synology.com/enu/viewtopic.php?t=77677
    Type:
    `DiskStation> vi my.cnf’
    Hit ‘I’ to make the following edits (asuming these are the names of the files just created):

    ssl-ca = /etc/newcerts/ca.pem
    ssl-cert = /etc/newcerts/server-cert.pem
    ssl-key = /etc/newcerts/server-key.pem

    Then save and exit. This will still throw an error however since no client key yet.

  9. Create Client key
    The following two websites may be helpful:
    https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/
    https://mariadb.com/kb/en/certificate-creation-with-openssl/

    Basically going through the steps “Creating a Private Key and Self-signed Certificate” (in the second link) but changing the name of the output file to “client”

    Step 1 (using file names provided above)
    openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
    …writing new private key to ‘client-key.pem’

    Hit enter through the questions except perhaps the one for Commonname

    Step 2
    openssl rsa -in client-key.pem -out client-key.pem

    Step 3
    openssl x509 -req -in client-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

    Step 4 verify
    openssl verify -CAfile ca.pem client-cert.pem

    client-cert.pem: OK

  10. Now need to copy files to Pi (or wherever openhab is being hosted)
    Forums indicate should copy all 3 files /etc/newcerts/ca.pem, /etc/newcerts/client-cert.pem, and /etc/newcerts/client-key.pem to the RPi client. However only the first seems critical. For example using scp:

    scp /etc/newcerts/ca.pem /etc/newcerts/client-cert.pem /etc/newcerts/client-key.pem pi@pi_ipaddress:/home/pi

    Then in jdbc.cfg file:
    url=jdbc:mariadb://youripaddress:3306/yourdatabasename?useSSL=true&serverSslCert=/home/pi/certs_from_mariadb/ca.pem

    Although there is discussion in some online forums about the need for a certificate to be proactively addded to a Java keystore by the user, this does not appear to be the case according to the following link:

    https://mariadb.com/kb/en/about-mariadb-connector-j/

  11. If you are still getting errors, go back to mariadb / phpmyadmin and go to host table / Users Overview (on home screen in my version). Your raspberry pi should be a User with a host IP clearly shown. Click Edit Privileges. Check that Require SSL is selected, and if there is a further radio button, select “Require SSL”.

    Should work locally…

  12. To enable remote connection as well, go back to mariadb / phpmyadmin and add another user, this time with the host IP address being the same as the router (for which a static IP address has already been set - showing a dummy static address).

    then change the jdbc.cfg file as follows:
    url=jdbc:mariadb://staticipaddress:3306/yourdatabasename?useSSL=true&serverSslCert=/home/pi/certs_from_mariadb/ca.pem

2 Likes