not logging

Discussion in 'Installation/Configuration' started by chief, Sep 11, 2018.

  1. chief

    chief Member

    Hi,
    ispconfig 3.1.12, debian - multi server setup.
    1.web and control panel server. 1. mail server. 1. db server. 2. dns servers. ns1 and ns2.

    problem.
    1. this shows in the mail server mail.log
    <code>/etc/cron.daily/logrotate:
    ^Gmysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: NO)'
    error: error running shared postrotate script for '/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log '
    run-parts: /etc/cron.daily/logrotate exited with return code 1</code>

    Question

    As far as i see the communication between servers is not encrypted. control panel server mysql updates all other servers in chain, this uses port 3306. i would like to encrypt it, how do i achieve this?
     
  2. Jesse Norell

    Jesse Norell Well-Known Member

    This likely comes from /etc/logrotate.d/mysql-server, which runs 'mysqladmin --defaults-file=/etc/mysql/debian.cnf ....' - that file (/etc/mysql/debian.cnf) normally specifies the password, so it's probably corrupt or empty. You can regenerate it with dpkg-reconfigure .. you don't mention your debian version, but for debian 9 it'd be
    Code:
    dpkg-reconfigure mariadb-server-10.1
    Correct; the mysql user password is encrypted within the mysql connection, as are most user password fields within the database tables, but most everything is sent in the clear.
    Actually it's the other way around, all the slave servers connect to the master (on port 3306) to read what updates should be made, and that uses a non-root mysql user. The slave servers must also connect to the master as the mysql root user during ispconfig updates.
    Update to at least ISPConfig 3.1.13, setup an ssl certificate on your master mysql server, then create /usr/local/ispconfig/server/lib/config.inc.local.php on your slave (not master) servers with:
    Code:
    <?php
    # talk to master via ssl:
    $conf['dbmaster_client_flags'] = MYSQLI_CLIENT_SSL;
    
    If you want to ensure the slave servers can no longer use clear text at all, you can change the mysql users on your master server:
    Code:
    MariaDB [mysql]> update mysql.user set ssl_type = 'ANY' where user like 'ispc%' and host != 'localhost';
    
    MariaDB [mysql]> flush privileges; 
    Note that ISPConfig updates will still connect in cleartext as the mysql root user when you reconfigure permissions in the master server like this. I know the installer's mysql client library doesn't support ssl, but I don't recall having tested updates, I think it uses the same library, and hence does not support ssl, but you could try it (or I'll give it a try some time and post here if I remember :). In any case, if the updater works with ssl you can just run a similar mysql.user update as above, only leave out "user like 'ispc%' and " (ie. so the query updates the 'root' user for all hosts except 'localhost') and it'll be required.

    And note you have a dedicated db server, so you may well want to setup ssl there, so your client websites can use SSL, too. I've not tried that offhand, to see how much work it would be to update various websites. For mysql cli, connection setup is simple, just edit my.cnf and it applies to all connections, but for php you have to modify code so the mysql connection handling enables SSL; I have no idea for other languages offhand, if you need/use those. It might be easier just to setup a tunnel to the db server and encrypt it outside of mysql (at least initially, which would get you a transition period till any code changes could be made).
     
  3. chief

    chief Member

    So, having a dedicated mysql server is not actually that safe as the data traveling between servers is unencrpyted untill you create the ssl certificate and then all database traffic is secured.
     
  4. Jesse Norell

    Jesse Norell Well-Known Member

    Correct, at least in general; it really depends on how secure the network is between the servers, as that is where the unencrypted traffic will be. If there are any other machines on that network, if that network is not physically secure, or especially if that network is the internet, it increases your vulnerability.
     
  5. chief

    chief Member

    I followed this, checked the file /etc/mysql/debian.cnf it does not contain a mysql password, do i need to add mysql password to this file?
     
  6. Jesse Norell

    Jesse Norell Well-Known Member

    Yes, it should look something like:
    Code:
    # Automatically generated for Debian scripts. DO NOT TOUCH!
    [client]
    host     = localhost
    user     = debian-sys-maint
    password = random_string
    socket   = /var/run/mysqld/mysqld.sock
    [mysql_upgrade]
    host     = localhost
    user     = debian-sys-maint
    password = random_string
    socket   = /var/run/mysqld/mysqld.sock
    basedir  = /usr
    
     

Share This Page