In both MySQL and Postgres there are times when you will want to login to the server in an automated non-interactive fashion. There are ways to pass the password on the command line, but this can be insecure. Both database systems support a file in your home directory that can authenticate you. The permissions on this file should be rw——-, or 600 so that only your user can read the file.
In MySQL you can specify a username and password in a user specific options file in your home directory. If you create ~/.my.cnf you can give it the following contents:
[client] user=USERNAME password=PASSWORD
This would allow you to enter MySQL by calling the “mysql” binary without any username or password on the cli and it will retrieve them from the ~/.my.cnf file.
Postgres has a similar file called ~/.pgpass. The format of this file is a little different from MySQL, and actually allows you to connect to not only the local database, but remote databases as well. Its contents should look like this (the comment line is optional but can be a nice reference):
It is important that whatever you type for your psql connection match what is in the .pgpass file. For instance, if you try to connect to mydbserver.mydomain.com for the host in your psql command, but your .pgpass file contains the entry as mydbserver, then it will not match the record and will continue as though the record wasn’t found. The same applies to if you try to connect to the servers ip address instead of by name and you have the name in the file or vice-versa. You are able to substitute the hostname, port, and database fields with an asterisk as a wildcard to more broadly match things that are referenced by the psql command.