I am experiencing issues properly Enabling SSL for MariaDB. I followed the setup for https://www.howtoforge.com/tutorial...l-pureftpd-bind-postfix-doveot-and-ispconfig/ a year or so ago. I have had some success with enabling SSL MariaDB by Editing: /etc/mysql/mariadb.conf.d/50-server.cnf # For generating SSL certificates you can use for example the GUI tool "tinyca". ssl_cert = /etc/mysql/ssl/server-cert.pem ssl_key = /etc/mysql/ssl/server-key.pem ssl_ca = /etc/mysql/ssl/ca-cert.pem # ..when MariaDB is compiled with OpenSSL: ssl-cipher = AES128-SHA:AES256-SHA256:AES128-GCM-SHA256:AES256-GCM-SHA384:TLS_AES_128_GCM_SHA256 However when testing connections to MySQL I encounter the following results: AES128-SHA - Works AES256-SHA256 - Does not work AES128-GCM-SHA256 - Does not work AES256-GCM-SHA384 - Does not work TLS_AES_128_GCM_SHA256 - Does not work Note: I also noticed that any emails hosted on the server fail to connect for sending/receiving emails immediately after enabling SSL to MariaDB. So I reverted the changes made. I am not sure how postfix dovecot and mysql interact with SSL and which settings should be set. I assume the emails fail because its trying to connect on one of the ciphers that fail to connect. Any help would be appreciated .
Why do you want to enable SSL in MariaDB? Enabling SSL in MySQL or MariaDB makes sense only for external connections, e.g., when you work with external MySQL tools that connect to your MySQL server over the internet. Enabling SSL globally for local connections like the one postfix is doing to MariaDB does not make much sense, as the data never leaves your server. In the case of localhost MySQL connections, the data is exchanged through a socket only and will not improve security; it just causes a higher system load.
Good question. Maybe I am digging in the wrong place. It's for a external PowerBI connection to Auto refresh data models for one of our websites every X days. I have managed to successfully connect to the DB and the connection worked when building the data models. But as soon as I enabled a scheduled refresh, it started failing due to: The MySql source doesn't support encrypted connections. I found no errors in the error Logs for mysql at around the time the refresh was running.
To add to this. It seems like the initial connections was made with no encryption, but since we are pulling sensitive data regularly we would like to enable encryption.
I am not sure if I should start a new thread or if I should continue on this thread. Based on the reply from @till, I realized that i do not need to enable encryption for the whole MariaDB service, I only need the encryption for 1 or 2 external DB users. Is it possible to only configure encryption for certain Remote Access DB users?
Yes, at least according to Mariadb documentation. See https://mariadb.com/kb/en/securing-connections-for-client-and-server/ under "Requiring TLS for Specific User Accounts".
Hmmm. It seems like the documentation first enables TLS / SSL globally for MariaDB, such as what I have done in my initial post. The issue with that was postfix failing to connect to the MariaDB when enabling TLS / SSL globally, which stopped all traffic on my mailboxes. I will need to look into Allowing postfix to connect without SSL/TLS when TLS / SSL is enabled globally. Alternatively, not enabling SSL/TLS for MariaDB globally but only for certain DB Users. Alternatively, setting up postfix to connect with SSL/TLS. Will dig around and see what i can manage.
Are you sure SSL must be set globally? Have you tested or otherwise verified it is so? The document states "Note that requirements set for specific user accounts will take precedence over this setting.", where "this setting" is require_secure_transport as I understand it.
So I assumed that the method I was using to enable SSL/TLS was enabling MariaDB SSL/TLS globally, as my email mailboxes immediately stops working after enabling SSL/TLS. (and still does) I see I was mistaken and can confirm that my setup did not enforce SSl/TLS globally. For example, i tested a mysql connections with --ssl-mode=DISABLED for the below users and it connected. I then created a test DB users and set it up to Require SSL (c1tmp | %). Connecting with --ssl-mode=DISABLED failed as expected. But surprisingly connecting with --ssl-mode=REQUIRED unexpectedly failed... [ERROR] MySQL Error 2026 (HY000): SSL connection error: error:0A000102:SSL routines::unsupported protocol It continued to fail even when setting tls-ciphersuites to match TLS_AES_128_GCM_SHA256, which was set in my config: ssl-cipher = AES128-SHA:AES256-SHA256:AES128-GCM-SHA256:AES256-GCM-SHA384:TLS_AES_128_GCM_SHA256 [ERROR] MySQL Error 2026 (HY000): SSL connection error: error:0A000102:SSL routines::unsupported protocol My mailboxes immediately stopped working. which lets me to believe that somehow I have something misconfigured. Mailboxes Error: 1. Not receiving any Mail 2. Sending mail results in Error: The following recipient(s) cannot be reached: '[email protected]' on ~~~~~~ Server error: '451 4.3.0 <[email protected]>: Temporary lookup failure'
I would use default setup for ISPConfig server because so far that I am concerned it is already secured. I am not sure why you have to change mariadb config because a secure connection from outside to the server, if needed, may be done simply by adding the --ssl param for that connection.
If I do not explicitly enable SSL/TLS for MariaDB, and try to connect with --ssl-mode=REQUIRED, then I end up with: MySQL Error 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it
Yes. You need to enable ssl on the MariaDB server but not on MariaDB client (do read at the above given link by @Taleman) because for the later you may simply use --ssl parameters and it will then become a one way TLS. We had attempted to use LE SSL certs long time ago of which you can refer them from about this post: https://forum.howtoforge.com/thread...ts-encrypt-free-ssl.75554/page-13#post-376720
I think there is some other issue at play here. I've carefully reverted all changes made to the below configs: - /etc/mysql/mariadb.conf.d/50-server.cnf - /etc/mysql/mariadb.conf.d/50-client.cnf - /etc/ssl/openssl.cnf And only applied SSL/TLS for the MariaDB server as suggested: 1. Create Self-Signed SSL Certificates - https://mariadb.com/kb/en/certificate-creation-with-openssl/ 2. Edit /etc/mysql/mariadb.conf.d/50-server.cnf - https://mariadb.com/kb/en/securing-connections-for-client-and-server/ ssl_cert = /path to my server-cert.pem/ ssl_key = /path to my server-key.pem/ ssl_ca = /path to my ca.pem/ With just the above configuration I can confirm that Clients that do not Require SSL (such as each one of the below) can locally connect to MariaDB without SSL. But still my mailboxes immediately stop working. I tried looking at postfix's configs, and I can confidently say I have no idea what's going on there. But mysql-virtual_*.cf does seem to indicate that postfix tries to locally connect to dbispconfig with the above mentioned user ispconfig. Mail.log has the following entries while attempting to send emails: Aug 27 12:54:25 artserver postfix/trivial-rewrite[1701060]: warning: virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem Aug 27 12:54:25 artserver postfix/trivial-rewrite[1701060]: warning: virtual_alias_domains lookup failure Aug 27 12:54:25 artserver postfix/smtpd[1701062]: warning: proxy:mysql:/etc/postfix/mysql-virtual_client.cf lookup error for "unknown" Aug 27 12:54:26 artserver postfix/trivial-rewrite[1701060]: warning: virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem Aug 27 12:54:26 artserver postfix/trivial-rewrite[1701060]: warning: virtual_alias_domains lookup failure Aug 27 12:54:26 artserver postfix/trivial-rewrite[1701060]: warning: virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem Edit: And this error when trying to setup my email again on outlook: NOQUEUE: reject: RCPT from *Server*: 451 4.3.0 <[email protected]>: Temporary lookup failure; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<Support>
Postfix connects to IP 127.0.0.1 and not localhost. In MySQL and MariaDB, localhost and 127.0.0.1 are two different things. Localhost is the MySQL socket (not a network connection), while 127.0.0.1 is a network connection to the localhost IP address, which is IP 127.0.0.1. So, it seems as if you block connections to the localhost IP while you can still connect to the socket.
I see, Thanks for the clarification. In that case postfix is configured to use user ispconfig to connect to dbispconfig via (network connection to the localhost IP). When enabling SSL/TLS for MariaDB-Server, should the postfix configuration be changed to use Localhost (MySQL socket) instead of 127.0.0.1?
