Creating Rails users in Postgres on 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;"

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:

# 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

# "local" is for Unix domain socket connections only

local   all         all         md5
# IPv4 local connections:
host    all         all    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

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.


4 thoughts on “Creating Rails users in Postgres on Ubuntu”

  1. Thanks for the helpful article. Sometimes the “simple” stuff like how to restart PostgreSQL is the hardest to find/remember.

    Regarding your pg_hba.conf, according to, using “trust” means “This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.” Is that what you want?

    I followed a somewhat different strategy that creates a DB user that logs in with an MD5 password:

    BTW I also use WordPress and it seems to leave double dashes alone if enclosed in tags.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s