I know how to run pt-kill as systemd service:
[Unit]
Description = pt-kill myUser
After=syslog.target mysql.service
Requires=mysql.service
[Service]
Type = simple
PIDFile = /var/run/ptkill.pid
ExecStart = /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log/ptkill.log --match-user myUser --match-state "Sending data" --busy-time 640 --kill-query --print
Restart=on-abort
[Install]
WantedBy=multi-user.target
However, this way another system user can see the mysql root password via ps -e -o pid,unit,cmd:
7786 ptkill.service perl /usr/bin/pt-kill -uxxx -pxxx --daemonize --pid=/var/run/ptkill.pid --log=/var/log
I tried adding User=root to the [Service]-section, because root user has protected /root/.my.cnf where passwordless access to mysql is configured. Sadly this doesn't work (I got no permission to access mysql without specifying user… when starting ptkill.service, I think the protected .my.cnf does not get loaded when starting the ptkill.service).
I also tried adding parameters "-umyUser -p`cat /path/to/my/password`" which does not work either.
How to securely run pt-kill as a system service pt-kill and how to securely let it access the mysql server?
Best Answer
I hope you are not one of my workmates :-) He had to setup a very similar configuration than yours a few weeks ago- you can check our packages if that helps you: Source deb, puppetization.
I told him to use socket-based authentication, it is the same method we use for non-human accounts that only need local-only permissions (icinga, prometheus). You only need to enable the plugin and tell the account with the same name as the account the service is going to run as to use it, so no password is required. Because there is no password, it cannot be stolen, so a compromise of the host would not escalate to a worse issue. Also less things that are private to handle. Unless there is a MySQL bug, the account is as secure as your OS account. You have to create a non-root account for both MySQL (auth_socket will provent password-based authentication and will lock it to localhost only) and the OS (system user with locked login), or otherwise it would defeat the point of it.
If for some reason you cannot use the above, you can still use password-based authentication, but set it on its own configuration file only readable by the user (non-root) that you start the service as.
The point is always to provide the least amount of permissions you can to a service. Root usage not only give the maximum amount, it also overlaps some OS (logging, limits, etc.) and mysql limits (max_connections) you don't want to, even if it effectively has a lots of permissions to kill other processes.