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”.