Category: ubuntu

Note: Since writing this I have revisited this issue and found a better way. read about it here.

I am getting started with a new greenfield Rails app and had a bit of an adventure setting up a user for my Rails app in Postgres. My attempts to connect or create a database would fail. I thought I had created a user properly but apparently I was wrong.
Now I know for next time.
Start with the create user command:


mike@sleepycat:~$ sudo -u postgres createuser --createdb --pwprompt desired_username


You can see we are using the createdb option in this call, allowing the user we are creating to create new databases. You can take a look at the databases available and the users that can access them with the command:


sudo -u postgres psql -l


So now lets assign the user we created rights on the database we just created with:


mike@sleepycat:~$ sudo -u postgres psql my_project_development -c "grant all privileges on database my_project_development to desired_username;"
GRANT


I had hoped that might be all that was needed but then my rake task crapped out:


mike@sleepycat:~$ rake db:create

Couldn't create database for {"encoding"=>"unicode", "username"=>"desired_username", "adapter"=>"postgresql", "database"=>"my_project_test", "pool"=>5, "password"=>nil}
FATAL:  Ident authentication failed for user "desired_username"


It turns out that Postgres is set to expect the user accessing the database to actually have a system account. That seems like a little much for a development environment where I would end up with tonnes of those accounts. To change that expectation you need to edit the host-based authentication configuration file pg_hba.conf:


sudo vim /etc/postgresql/8.4/main/pg_hba.conf


Press capital G to jump to the bottom of the file and change “local all postgres ident” and “local all all ident” to md5 as shown below:


# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database super user can access the database using some other method.
# Noninteractive access to all databases is required during automatic maintenance
#(custom daily cronjobs, replication, and similar tasks).
# Database administrative login by UNIX sockets
local   all         postgres         md5

# TYPE  DATABASE    USER        CIDR-ADDRESS      METHOD
# "local" is for Unix domain socket connections only

local   all         all         md5
# IPv4 local connections:
host    all         all         127.0.0.1/32    md5
# IPv6 local connections:
host    all         all         ::1/128       md5


Restart your Postgres server and you should be good to go.


mike@sleepycat:~/projects/key_master$ sudo /etc/init.d/postgresql-8.4 restart
* Restarting PostgreSQL 8.4 database server
...done.


Now your Rails users don’t need to have system accounts and your rake tasks should run just fine. Back to work!

Thanks to Mark Berry for his comments below! They have been incorporated into the post now.