in Databases

Logging in to MySQL and Postgres without a password

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.

MySQL

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

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):

#hostname:port:database:username:password
HOSTNAME:PORT:DATABASE:USERNAME:PASSWORD

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.

Write a Comment

Comment

  1. Hi CJ, can you put other settings in that Postgres or MySQL config files to request integrated account authentication (using PAM, LDAP, PKI client certificate, or Windows Authentication?) I believe that the sooner we can completely remove passwords from our architectures, the better. Other database servers, like Oracle and SQL Server support authentication that uses the account credentials of the running application to open the connection to the database. This is reflected in the database logs as well which gives the DBA visibility into actual users (or service accounts) that are hitting their system. This takes a little work to initially set up, but worth it when weighed against the open vector that a hacker could use to get into data by just knowing the database account credentials. Of course, the service account that is used in this manner should also be blocked from running a web browser to prevent it from also being a used to provide a back door to the database. Does the config file also support an encryption setting for the connection itself? This prevents snooping on the network traffic, which if done during a session start would reveal the username/password as easily as it would be seen as a command line argument. For some servers, like Oracle and SQL Server, this can be mandated at the server side so it might be possible to mandate this also for MySql or Postgres. Thanks!

    • Robert, thanks for your comment! This has always been a point of contention for me with MySQL. In one of the newest versions of the enterprise MySQL distribution they finally have a PAM and a key plugin that you can use for integration. I don’t have any experience with this module personally as it is relatively new. You can read more about it here: https://dev.mysql.com/doc/refman/5.5/en/pam-authentication-plugin.html
      http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/sha256-authentication-plugin.html

      I do not believe the features are available in the community edition. There are other things you can do to attempt to support alternative authentication methods such as use a proxy software in front of MySQL that support them but this feels kind of hacky to me and I’ve never done this.

      Postgres has support for LDAP authentication in its core feature set. We are in the process of building this out and testing it. Keep an eye on our blog and as soon as I’ve worked through our kinks, I’ll do a write up of our experience working with it.

      To directly answer your question, the settings to enable features for both Postgres and MySQL would be managed in the global config files. The files I mentioned in this article are just for the connection parameters. I would imagine that in MySQL you would just need the username (and potentially a path to the key) in the user config file. I’m not yet sure what you would put for the password field in the Postgres user file.

      There is the ability to set both MySQL and Postgres up to use SSL which should prevent network snooping. Both system encrypt their connection strings even if you are not running SSL.