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?
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).
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.
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.
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?
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
I think this thread is really important, not outdated, and it's directly related to what I'm looking at now. When we install a secondary system we identify the primary/master with a FQDN. The Perfect guides don't differentiate which FQDN should be used in specific conditions. The notes here imply that the FQDN for the connection from secondary to primary can be different than that for other purposes : Assume for this discussion that all servers are in a local subnet, not separated across an extranet. I would prefer to use a local server reference for local MySQL traffic, rather than a public server reference. For example while configuring server2 to reference server1, just using the host "server1" or use a FQDN that is not published "server1.example.com". Then in /etc/hosts: "10.0.0.1 server1.example.com server1" I believe this should keep the traffic on the subnet. With a public-exposed FQDN for server1, a DNS query is required at some point, the traffic leaves server2 on the internet, and the query is made back through the DMZ firewall to server1:3306. Unless there is another need to expose 3306 to the internet, it may not be necessary for this "loopback" to occur with plaintext query exposed to the world. This is a configuration detail that's not addressed in the multiserver tutorials. When we grant permissions for a secondary to query the primary, we use 'root'@'ip.address'. The address depends on how the connection is made. With an external loopback, server2 has an external IP address. With a direct/internal connection server2 has a subnet IP address. I trust that is accurate, but if so it opens some obvious questions: - Which non-root user does server2 use to query server1? - How are permissions defined? - Can random queries be made on server1 from the internet? - What data is exposed to non-root queries? - There are notes in this thread about how some data is protected - aside from passwords, how can/does MySQL decide what data to protect on the wire? OR ... Is all non-syntactical text somehow protected? I wouldn't even want table names exposed because "security through obscurity" is a naive but simple first line of defense. I won't go into it here but this thread discusses MySQL over SSL, and I think that leads to questions about which SSL cert is used for such connections, the host specified, certs for internal vs external usage, self-signed vs a requirement for verified, etc. These questions can be pursued elsewhere. In summary : I don't think the existing documentation explains multiserver FQDN/IP relationships well. This results in hundreds of posts to these forums about which names and addresses to use, mistakes, and lost time for developers and users. I'm looking for answers to these questions in the forum for the new documentation, and if I can't find existing answers I'm going to come back to follow-up or recreate threads like this.
in order: 1. 'ispcsrv#'@'hostname.domain.tld' 'ispcsrv#'@'@@@.@@@.@@@.@@@' where # is the server_id number for the corresponding server in 'select server_id,server_name from dbispconfig.server' on the master and where '@@@.@@@.@@@.@@@' is the ip address of that same server. 2. Code: +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for ispcsrv#@host.domain.tld | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'ispcsrv#'@'host.domain.tld' IDENTIFIED BY PASSWORD '*F0755CB07DA2BA17868260607B6FD46353EBB1F9' | | GRANT SELECT, INSERT, DELETE ON `dbispconfig`.`web_backup` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT, DELETE ON `dbispconfig`.`monitor_data` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT, UPDATE ON `dbispconfig`.`web_traffic` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT, UPDATE ON `dbispconfig`.`ftp_traffic` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT ON `dbispconfig`.`sys_log` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE, DELETE ON `dbispconfig`.`aps_instances` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, DELETE ON `dbispconfig`.`aps_instances_settings` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (dnssec_last_signed, dnssec_info, dnssec_initialized) ON `dbispconfig`.`dns_soa` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT, DELETE ON `dbispconfig`.`mail_backup` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, INSERT, UPDATE ON `dbispconfig`.`mail_traffic` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT ON `dbispconfig`.`sys_group` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (updated) ON `dbispconfig`.`server` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (status) ON `dbispconfig`.`software_update_inst` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (status, error) ON `dbispconfig`.`sys_datalog` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (response, action_state) ON `dbispconfig`.`sys_remoteaction` TO 'ispcsrv#'@'host.domain.tld' | | GRANT SELECT, UPDATE (ssl_key, ssl, ssl_request, ssl_action, ssl_letsencrypt, ssl_cert) ON `dbispconfig`.`web_domain` TO 'ispcsrv#'@'host.domain.tld' | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ root users for adding servers to a multi-server system are created as: Code: CREATE USER 'root'@'host.domain.tld' IDENTIFIED BY 'myrootpassword'; GRANT ALL PRIVILEGES ON * . * TO 'root'@'host.domain.tld' IDENTIFIED BY 'myrootpassword' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; * root user and privileges created using the ip of the server should be added as well. 3. only if the port is open to the internet and you allow users to connect via the internet. so creating users with '%', or a public ip / hostname as the host is a no-no, unless you specifically restrict internet access to port 3306 to specified ip's/hosts. and only create mysql users with those restricted ip's/hostnames. the only reason i can see for doing this is to allow a remote ispconfig server to exist on an external network, eg to split ispconfig dns servers across hosting providers, or provide hosting webservers in specific countries, but maintain a single master control interface. 4. whatever data is in those tables listed above, for ispcsrv# users. or whatever tables you give access rights to for any other users you create on that mysql server instance. i'm not sure there is any documentation for the creation of ispcsrv users. they're created by the install process, not by manual intervention. i believe the only available information is by looking through the install scripts. the root user logins for client servers is in the manual, and in the multi-server install docs.
That sounds right. You of course meant a database query there (clarify for other readers...) All sounds correct. You would/should probably prefer an internal ip address for these connections. On your slave server, run 'grep dbmaster_ /usr/local/ispconfig/server/lib/config.inc.php' to see what it's using. Each slave has a dedicated user for it. They are set by the ISPConfig installer. Eg. when you update versions and answer Yes to "Reconfigure Permissions in master database?" Not in a sane setup, but check your firewall(s) to ensure the port is blocked, and check your mysql.user table to see where queries would be allowed from. And test connections/login attempts from external sources. You can check the User and db tables, and "show grants;", but I don't know if there's anything more than that of the top of my head. To my knowledge (which is just based on casual observations from watching traffic in tcpdump), nothing is protected on the mysql connection if you do not use ssl. Most passwords are themselves stored as hashes, so all you see on the wire is the hashes. Yeah, of necessity, the documentation focuses more on setting up or using ISPConfig, not teaching even basic, and especially not advanced networking or security topics. Those things are too broad to even consider addressing comprehensively in a single document. What might work well is having multiple tutorials based on specific, common scenarios; eg. in our environment we mostly have public ip addrs on all the servers, with no need to consider what address is being connected to, and having that information in a tutorial would make it needlessly long and confusing if you didn't already understand the networking pieces, so a simpler tutorial for that use case would be nice, and a second tutorial describing your setup, with public and private addrs (and possibly nat, or perhaps just a backend, private-addr network in use) might be a solution/improvement? (I suppose that is how it is currently done now, but only the simpler tutorial exists, and you are needing the more complex one.)
Friends - your responses are Golden. Thank you Very much. The information you've provided here will find its way into new docs being discussed. I have no desire for general-purpose network administration to be included in ISPConfig documentation. I'm purely focused on what's in this software. Whenever I expose my vast ineptitude with the nuances of Linux networking, please feel free to tell me to RTFM and I'll happily comply. I actually did mean a DNS query. In the multiserver tutorials we see /etc/hosts with "local.ip.0.0 f.q.d.n host" and in this forum we see directives to use "external.ip.0.0 f.q.d.n host". It seems to me that either one of these could be right or wrong depending on the site requirements. While using the external IP has been stated here as somewhat of a directive, that doesn't seem to be necessary when all servers are on the local subnet. I said a DNS query is required. More accurately I think, a "resolve request" needs to be performed, which can be resolved from /etc/hosts or a local DNS or a remote DNS. If the primary system is a DNS and the secondary is configured to use that DNS, do we even need to reference server1 in the server2 /etc/hosts? No, but we need to grant MySQL privs for server2's external IP. If the primary system is not a DNS then yes, server1 does need to be in the server2 /etc/hosts - and db privs would use server2's internal IP. I don't know if this would work but if we create a new internal zone in the server1 DNS, we can resolve from server2 just with a request for a hostname, and we'll get back a local IP for server1. So server2 can connect using just the hostname, on the local subnet, no external access to 3306 required, and on server1 we would grant privs to server2 using its internal name and IP address. My overall point is that in the broad collection of documentation we have available, there is conflicting information, all presented as authoritative, and with no notes about how the details depend on site-specific requirements. From my noobish perspective, I have been confused about why specific settings are required, I have seen that they are not actually required, and I've been hunting in this forum to find out how it really works so that I can make better decisions - rather than just pressing the buttons as directed in the tutorials. That's where all of this is coming from. I hope someone will acknowledge that my thinking on this stuff is correct, and that there are no absolutes - and I will gladly put these understandings into new documentation. Thanks again.
Then this is not accurate, as a DNS query will use port 53, not port 3306. (Unless possibly "a DNS query" refers to dns, but "the traffic" and "the query" later in that sentence refer to a database query.) And the overall concern/exposure is probably less than you have in mind in this scenario, as there is no exposing of your traffic to the world when your packets (dns, db, or whatever) are looped back through a local firewall. Depending on your firewall capabilities/configuration you probably don't need to have server1 ports open to the world in order for server2 to connect to it via the public ip "loopback", it's likely just less efficient than connecting directly to the private address. I would not do that with any common size installation (ie. not hundreds/thousands of servers), as it is both simpler to use the hosts file and more reliable, as DNS servers do crash on occasion (out of memory, bad config, etc.) - in your scenario here, your entire ISPConfig master/slave communication comes to a halt when the DNS server on server1 goes down. Host files will keep name resolution functional for your ISPConfig server names, and ensure they use the private ip address (and is faster and uses less resources); the downside to them is that you have to keep them in sync between your servers, but that's generally not going to be a huge issue as they rarely need changed/updated. Yes, there's that side, which could surely use some improvement. Then there's the other side, that many users who have little experience don't want to have to learn and understand every specific setting in order to try ISPConfig, they just want to get a working system setup reasonably quickly. Likely developing the autoinstaller can help this latter bunch, and in depth documentation the former. Of course you are going to have problems eventually when trying to administrate a server when you don't understand much of what's going on... but then there's support forums and paid support to help with that, too.
We're on the same page. Note that I later used the phrase DNS resolution rather than DNS query, to clarify port 53 and not 3306. I understand what you're saying and will probably make a couple related changes. This topic for multiserver interactions deserves a thread of its own and related docs with much of the info here. I'll kick that off later. Thanks again.
