PostgreSQL – Benefits of Using MD5 Over Trust Authentication for Local Unix Sockets

postgresqlpostgresql-9.1

I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user:

alter role postgres password '<<super-secret-password>>';

… and then change the pg_hba.conf file so that the line for local (Unix) sockets reads:

local all all md5

… instead of:

local all all trust

It would seem to me that this only adds an extra layer of defense but for somebody to take advantage of local all all trust he would first have to gain access to the local machine, right?

Question 1: do I understand correctly that if access to the local machine is password protected (UNIX password) then there should be no worries over a local all all trust ?

Also, the same guide additionally recommends that a .pgpass file is located in my home folder (I guess for backup scripts that cannot be prompted for a password). The password is stored clear-text in that ~/.pgpass file:

echo 'localhost:5432:*:postgres:<<super-secret-password>>' > .pgpass
chmod 0600 ~/.pgpass

Question 2: So, what is the benefit of using md5 and not trust for local sockets? If one gains access to local machine, he can also read the .pgpass file. Am I missing anything?

Best Answer

For Question 1: using md5 auth for local connections is particularly helpful when you have a multi-user machine, or if you might add additional user accounts for friends/family/coworkers/whoever someday.

If you are and will remain the only actual user on your system, there is less benefit, but it could still be useful: suppose some bad guy manages to break into some system account like 'www'. He may not be able to do a whole lot from that account without root privileges. But if you've left your pg_hba.conf open to 'trust' all local users, the bad guy can do anything to your database, probably including taking over the 'postgres' user account as well, and moving on to attempt to root your system, steal your data, or whatever from there.

For Question 2: You could use a .pgpass file with, say, only a 'readonly' user's credentials, so if a bad buy took over that account he wouldn't have superuser-level access to the database. If you do store the password for the postgres user in your ~/.pgpass, then yes, it would be "game over" for your postgres instance if a bad guy took over that local user account. Again, that documentation was written with the possible use-case of a multi-user machine in mind, e.g. different local users might have or use various Postgres user accounts, not necessarily superuser accounts.

Related Question