I am running Ignition v8.1.9 and have a connection to a mysql database using mariadb driver.
I initially got faulty database connection:
I get this to work by adding “allowPublicKeyRetrieval=true” to the extra connection properties
-Is this the correct way of fixing this issue? 
-What security compromises am I introducing? 
Note that
AllowPublicKeyRetrieval=Truecould allow a malicious proxy to perform a MITM attack to get the plaintext password, so it isFalseby default and must be explicitly enabled.
What would you suggest to be the most secure solution to this issue?
I would suggest mutual TLS:
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html
Though, that guide don’t drop directly into Ignition, some of the keystore manipulation steps are somewhat different for Ignition. I don’t know if anyone has a guide written up for TLS auth with MySQL.
I am on deep water here, even if I understand some of the basic principles of key-pairs etc…
-By accident I was using mysql connector on previous project, and it seemed to work out of the box against Ignition.
-Any Idea why?
-Would it be more secure using another driver for my current project as well?
-Or is mysql driver more un-secure by default?
It seems you've got a slightly different configuration going on here. This excerpt from Paul's link explains:
If the user account uses
sha256_passwordauthentication, the password must be protected during transmission; TLS is the preferred mechanism for this, but if it is not available then RSA public key encryption will be used.
So you seem to have set up sha256_password authentication but not SSL or TLS, which means to avoid sending the password presumably plaintext the database is now trying to use asymmetric encryption, but you haven't configured the client with the necessary public key and the database sending it to the client over plaintext is a security problem in its own right.
https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html
You are right. I did not enable SSL/TLS. I will consider getting a SSL certificate
Tnx for pointing me in the right direction
I’m not talking about enabling SSL/TLS for your Ignition Gateway, I’m talking about for the connection from the Gateway to your MySQL server.
