Create postgres users and databases from Chef

db_server.rb

#
# Cookbook Name:: atari
# Recipe:: db_server
#
# Copyright 2010, Atari
#
# All rights reserved
#

include_recipe "postgresql::server"

r = package "ruby-pg" do
  package_name "libpgsql-ruby"
  action :nothing
end
r.run_action(:upgrade)

# Snippet from opscode to reload gems
require 'rubygems'
Gem.clear_paths
require "pg"

execute "create-database-user" do
  command "createuser -U postgres -SDRw youruser"
  only_if do
    c = PGconn.connect(:user=>'postgres', :dbname=>'postgres')
    r = c.exec("SELECT COUNT(*) FROM pg_user WHERE usename='youruser'")
    r.entries[0]['count']
  end
end

execute "create-database" do
  command "createdb -U postgres -O youruser-E utf8 -T template0 yourdb"
  only_if do
    c = PGconn.connect(:user=>'postgres', :dbname=>'postgres')
    r = c.exec("SELECT COUNT(*) FROM pg_database WHERE datname='yourdb'")
    r.entries[0]['count']
  end
end

Where should this code live?

This is just a recipe, but a better version of it is in progress in my COOK-634 branch which provides an LWRP structure.