The Hungry Hacker's Explanation of Everything

If you’re a PostgreSQL user running FreeBSD, you probably notice once a day (assuming you read your root mail) that the psql daily job complains loudly about not being able to auth to the database… that is of course unless you’re still using the “trust” auth mechanism, in which case your nightly maintenance should work according to plan, but unless the machine is locked away where no one can get to it (even via the internet), you’re probably a dumbass.

So how do we fix this? The libpq client is unfortunately very temperamental about it’s access, and it doesn’t complain at all when something’s wrong, it just won’t work. It’s actually rather trivial to fix.

The .pgpass file

There’s already plenty of documentation about the .pgpass file so we won’t go over it too much here. Suffice to say you want it to look like this:

localhost:5432:*:pgsql:muhpassword

Change “muhpassword” to whatever your pgsql user’s (which should have superuser privs inside the database server) password is and you should be set.

This file should live at ~pgsql/.pgpass, which on my FreeBSD with PostgreSQL 8.1 installed from ports is /usr/local/pgsql/.pgpass.

Now, and this is the critical part that kept me scratching my head for quite a little while… this file must be owned by pgsql user and it’s permissions must be 0600. If it’s anything else, the psql program will ignore the pgpass file, and prompt for a password on a terminal that doesn’t exist (because all the input/output from the daily scripts is /dev/null). Once the auth timeout hits, the script stops running, and the result is your databases don’t get the maintenance you probably want them to have.

This information may have been presented someplace else, but I’m posting it here for my benefit and I hope it helps someone else.