Another look at creating Rails users in 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.