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;"
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.

Advertisements

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 http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html, 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:

    http://www.mcbsys.com/techblog/2011/10/set-up-postgresql-for-rails-3-1/

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s