Enabling SSL for MariaDB

Discussion in 'HOWTO-Related Questions' started by JustMeh, Aug 1, 2024.

  1. JustMeh

    JustMeh New Member

    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 :).
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
     
    ahrasis likes this.
  3. JustMeh

    JustMeh New Member

    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.
     
  4. JustMeh

    JustMeh New Member

    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.
     
  5. JustMeh

    JustMeh New Member

    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?
     
  6. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    ahrasis likes this.
  7. JustMeh

    JustMeh New Member

    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.
     
  8. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    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.
     
  9. JustMeh

    JustMeh New Member

    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.
    upload_2024-8-22_16-1-3.png

    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'
     
    Last edited: Aug 26, 2024
  10. ahrasis

    ahrasis Well-Known Member

    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.
     
  11. JustMeh

    JustMeh New Member

    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
     
  12. ahrasis

    ahrasis Well-Known Member

    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
     
  13. JustMeh

    JustMeh New Member

    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.
    [​IMG]

    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>
     
    Last edited: Aug 27, 2024
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    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.
     
    ahrasis likes this.
  15. JustMeh

    JustMeh New Member

    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?
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, you should try if that works.
     

Share This Page