postgres: setting up permissions for a user

I wanted to give a CGI script access to one specific postgres database, both read and write. This is remarkably complicated. I ended up creating a password protected role named “oarunone” and gave it access to a database named “oarunone” (yes, confusing) and one table named “queue”. Something like:

  • create role oarunone with password ‘secret’;
  • grant all privileges on database oarunone to oarunone
  • grant all privileges on table queue to oarunone

But that’s not good enough. Ubuntu’s default postgres only identifies users via the Unix UID. So you also have to add a line like this to pg_hba.conf. and it has to occur before the catch-all “all” rule.

  • local oarunone oarunone md5

That says allow the role “oarunone” to authenticate with a password (MD5ed), but only from a local Unix domain socket and only to the database “oarunone”.