8

I need to make backups of the remote machine mariadb database. My plan is to run

ssh user@remoteip 'mariadb-dump -uuser -ppass --all-databases > backup.sql'

from my local machine.

I found that remote machines history is not being populated with the mariadb-dump command I just executed. journalctl also did not contain the command.

Is there any security reason on the remote side I should be worried about when passing mariadb password in the ssh command? Are there better alternatives?

sanjihan
  • 257
  • all commands with password on command line are.... use a file to load user and pwd. You should also see a warning about command line pwd. Mysql does :P If this is a machine used by many: ssh private key. and don't use ssh with mariadb; use ssh to connect creatre the file and rcp it – Rinzwind Sep 14 '24 at 10:35

2 Answers2

11

Yes this is very unsecure.
While running, any user on the system can see the plaintext password with ps aux.

You should rather add a file ~/.my.cnf for the user with permissions 600 and following content:

[mariadb-dump]
user=myuser
password=mySecretPassword
pLumo
  • 28,011
  • 2
  • 65
  • 97
  • But that requires putting the password in plaintext on the server, where it previously wasn't. That might be unacceptable too. – marcelm Sep 14 '24 at 17:27
  • 2
    @marcelm it's also stored in a file that has its mode set to 600, so reasonably nobody other than the owner or root will be able to read its contents. – kos Sep 14 '24 at 20:23
  • 2
    I don't think it's all that useful to say "very unsecure". Storing the password in a file is vulnerable to certain kinds of attacks, while passing it on the CLI is vulnerable to different ones. A proper approach would involve discussing the threat model: who might be trying to steal the password, what resources do they have access to, how much effort are they willing to put in, and that sort of thing; then depending on how likely you judge each threat to be as part of the model, you can then make an informed decision about whether each approach is acceptable. – David Z Sep 15 '24 at 03:22
  • 1
    Well, in general that is totally true and having a password in a file is not always the best idea. But in that case the attack vector for a file with permission 600 is having root (or that user) or physical access to the machine/hard drive. And in this case, your machine must be seen as compromised anyways. The attacker would have access to the database (and more) anyways. And then you could also see the running processes. – pLumo Sep 15 '24 at 06:31
8

Since the MariaDB and system users seem to match, you could also allow the system user user to authenticate as the MariaDB user user through unix_socket:

ALTER USER 'user'@'localhost'
    IDENTIFIED VIA mysql_native_password USING PASSWORD('pass')
    OR unix_socket
;

This will grant passwordless login as the MariaDB user named user to the system user named user (which may be convenient in general), leaving to other system users the option to authenticate as the MariaDB user named user using the password pass (you may run just ALTER USER 'user'@'localhost' IDENTIFIED VIA unix_socket; if the latter is undesirable).

Then you could run just:

ssh user@remoteip 'mariadb-dump --all-databases > backup.sql'
kos
  • 41,378