MySQL root-login with SSH private key?

Question

Suppose Mysql -dbs and an admin with a bad habbit to forget the passwords (or paranoid enough wanting more creative authentication, not just password). Is it possible to access Mysql -root shell for example in a way that the Ssh-agent stores the private keys and then I could just login by "mysql -u root" (without typing the passwords or a combination of private-key and a password)?

Solution

You can certainly use the normal SSH key authentication to log on to your shell account and then use a ~/.my.cnf file with your password inside. This will be used by default by the mysql command line client.

Its content should be:

[client]
user=the_user_name
password=the_password

Beware that whoever can read this file will be able to use those credentials. Protect it so that only the owner can read it (and lock your terminal if you go away from your keyboard).

---

If you want direct key-based authentication, you could also use SSL client-certificate authentication. You could create your own small CA and issue yourself with these client-certificates. A number of tools support this if you require direct remote access.

Comments

[hhh]

..precisely, thanks! Besides them, I have some ideas such as setting up ssh-fs things with gpg, have to think how to protect the file carefully (I am on shared system) -- perhaps using many ways of authentication so not really mattering if losing the password, ideas? Perhaps overengineering now, investigating.

[Bruno]

That's not necessarily going to work with ssh-fs. This file will be used by the mysql client if running on the actual machine (and only in non-encrypted form). (You could of course mount the remote home directory with ssh-fs and then specify this config file with your local mysql client, but this seems convoluted). Keep the mysql root password in this file in the root account if you need. For remote access, client-certs might be a better option (although it's more involved in terms of management).