Category: postgres

Three years (!) has passed since I first wrote about setting up users for your Postgres development database. This does not come up often for me, but every time I see my own list of instructions I shudder and think there must be a better way. This morning I figured it out.

Here is what I have in my config/database.yml:

development:
adapter: postgresql
encoding: unicode
database: myapp_development
pool: 5


Omitting the username and password from the database.yml means that postgres will try to log using your operating system username using the peer method. Since I am logged in as “mike” that is the username that will be used to authenticate. With this in mind I am just going to create a database user with that name.

mike@sleepycat:~/projects/myapp$ sudo -u postgres createuser --interactive mike
Shall the new role be a superuser? (y/n) y

Notice that used sudo -u to switch to the existing “postgres” user (created by default) and created a superuser that matched my operating system account username.
After that, everything works. Even rake commands.

mike@sleepycat:~/projects/myapp$ rake db:create
mike@sleepycat:~/projects/myapp$ rake db:migrate
==  CreateWidgets: migrating ============================================
...
mike@sleepycat:~/projects/myapp$ rails dbconsole
psql (9.1.9)
Type "help" for help.

myapp_development=# 

Now that is far more civilized.

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.