The Hungry Hacker's Explanation of Everything

Home » Operating Systems

Howto: Enable FreeBSD’s Postgresql daily periodic job

12 March 2009 No Comment

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.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar. Note: By filling out this comment form or emailing us you are signifying that you have read and agree to the terms laid out on the Contact Us page.