How to Provide a Password to
Recently, I was writing a small Bash program with a GUI (using zenity) that would prompt the user for their MariaDB credentials and then retrieve some information. Passing username and password to the
mysql program looks like:
$ mysql -u user -h host -ppassword mydatabase -e 'select 1;'
(Note that there cannot be a space between
-p and the beginning of the password.)
Now, this obviously works fine, except for two problems:
- We cannot reuse the connection to the database, so we have to make a new connection every time we want to execute a query.
- The password of user
useris given in the command itself. This means that everyone on the same machine can list the processes and see the command-line arguments passed to
In this post, I want to talk about the second problem.
$ pgrep -af mysql 8046 mysql -u user -h 192.168.123.5 -px xxxxxxxxxxxxxxxxxxxxxxx mydatabase $ ls -l /proc/8046/cmdline -r--r--r-- 1 neftas neftas 0 16 feb 10:33 /proc/8046/cmdline $ cat -et /proc/8046/cmdline mysql^@-u^@stefan^@-email@example.com^@-px^@xxxxxxxxxxxxxxxxxxxxxxx^@mydatabase^@
As we can see,
mysql replaces the provided password with
xes, but at the same time warns in
man mysql that
Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.
The option file
man mysql is talking about is either one of the configuration files located at
/etc/my.cnf, or a custom one specified on the command line. So, one option would be to put the credentials in
$HOME/.my.cnf before executing the program, stopping this issue cold in its tracks.
But let’s say that we want to dynamically connect to a database. Asking the user to provide credentials and then write them to a file would provide the same issues as passing it directly to the
mysql command. The only difference would be that we now pass the sensitive information to different command:
printf '[client]\nhost=%s\nusername=%s\npassword=%s\n' \ host username password > creds.tmp mysql --defaults-extra-file=creds.tmp mydatabase
The Environment Solution
Although not mentioned in my version of
man mysql (10.4 from 29 March 2019), the safest way, it turns out, is to set an environment variable named
MYSQL_PWD. Why is this safer?
$ ls -l /proc/8046/environ -r-------- 1 neftas neftas 0 16 feb 11:00 /proc/8046/environ
At least on Linux, environment variables are only readable by the user that started the process. This means other users (beware of
root) cannot look at them.
declare -x MYSQL_PWD IFS='|' read -r username MYSQL_PWD < <(zenity --password --username) mysql -u "$username" -h host mydatabase
declare -x declares a variable that is going to be exported on assignment. This eliminates the need to
export MYSQL_PWD explicitly somewhere down the road: when
MYSQL_PWD gets a value assigned, it will export that value.
In the second line, I use process substitution (see
Process Substitution in
man bash) to provide a pipe-separated string to
read that will split the string and assign the values to
MYSQL_PWD is set, we no longer have to worry about providing the password directly to the
Versions Used In This Post
This post was written using the following versions:
$ uname -a Linux cirrus7 5.5.3-arch1-1 #1 SMP PREEMPT Tue, 11 Feb 2020 15:35:41 +0000 x86_64 GNU/Linux $ mysql --version mysql Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1 $ zenity --version 3.32.0