cron jobs for PostgreSQL users without using passwords

Ever needed to run some kind of cron job on a PostgreSQL database, but was stumped for not being able to specify a database user's password with the psql utility?

In using PostgreSQL with Ruby on Rails, I often have to set up regular cron jobs to clear out stale data, such as session data when using ActiveRecord as the session storage mechanism. Instead of coding up some object to react on a timer, you can simply use cron to do your bidding. But your regular cron job to connect to the PostgreSQL database needs a user, and that user needs to provide a password when connecting. Here's how you can set this up by creating a new UNIX/PostgreSQL user and configuring this user's database access in pg_hba.conf.

First, create a new UNIX account for your user. We will use the same username when creating a database user account as well. Why? Well, PostgresSQL allows connections not only over TCP/IP, but also over UNIX domain sockets. And, you can configure pg_hba.conf so that when a certain database user attempts to connect over a UNIX domain socket, PostgreSQL will check for an existing UNIX user account. If this UNIX user is the same as the user account attempting the database connection, then PostgreSQL will treat such access as trusted, hence no explicit password passing will be required.

For example, let's say that I want to clear out stale session data from my Rails sessions table. I first create a UNIX account for my database user.

# useradd victor

Don'f forget to set up a good password to victor. You may also want to revoke permissions from groups and others to access this user's home directory. For production environments, you want to be sure to assign victor to a group with limited UNIX user privileges, since we don't want people to misuse victor and mess with our database.

Next, we create a PostgreSQL database user account for victor, using the same username. We will create this database user with limited privilege on the sessions table, for security's sake. Assuming that the we are talking about the default Rails sessions table, we have:

# psql -U [database admin] [your_database]
psql@your_database> create user victor with password 'somepassword';
psql@your_database> grant select, delete on sessions to victor;

There! Now we have a PostgreSQL database user corresponding to our newly created UNIX user account.

Next, we add this line to our $PGDATA/pg_hba.conf file:

# TYPE  DATABASE            USER     CIDR-ADDRESS    METHOD
local   [your_database]     victor                   ident sameuser

This is how we allow the new user to connect to the PostgreSQL database using a UNIX domain socket connection. Logging into your UNIX machine as victor, you can now set up a simple cron job like so:

$ crontab -e
# crontab for victor
# Let's run a simple psql command as victor to delete stale rows in sessions table
# Execution time is
0 0 * * * /full/path/to/psql -U victor -c "delete from sessions where \
 updated_at < (now() - interval '6 hours')" [your_database] > /dev/null

So now we are good to go, with a UNIX/PostgreSQL user whose cron job will run daily at midnight, clearing out stale rows in the sessions table for our Rails ActiveRecord session management. And all without having to explicitly specify the password!