Capistrano and database.yml

Last week, an user asked the Capistrano mailing list about database password best practices. This reminded me that I never posted here a Capistrano recipe I created almost one year ago to solve exactly this problem.

Which problem?

Imagine you need to deploy a new Rails application. As you probably know, Rails stores all the database configurations in a single file called config/database.yml, including database authentication credentials.

This file usually lives in your repository along with all your application code base. However, exposing real world passwords to all developers with read access to the repository can lead to major security problems. It’s likely you don’t want to store sensitive data in your repository, thus you need to automatically generate the config.yml file somehow on deploy or on setup.

If you are using Capistrano to deploy your Rails application, you can ask Capistrano to generate and upload the file for you. Let me show you how.

One Problem, Many Solutions

As usual, one problem comes with many different solution. That’s good because this is definitely better than a problem without a reasonable solution… do you agree?

As Robert James pointed out in its email, there are at least 3 different approaches to solve this issue.

• You can store sensitive data in your Capistrano deploy script. The downside of this solutions is that every developer with read access to the Capistrano script have access to the data as well.
• You can store sensitive data on your server, but it requires some kind of manual setup.
• You can use a password-less system, but this is probably the worst idea ever. Don’t misunderstand me, shared keys are a wonderful authentication system and I widely use them, but I didn’t find an effective alternative for database authentication.

My recipes combines the second choice with some additional features, taking advantage of Capistrano ability to execute commands simultaneously on multiple servers.

Capistrano database.yml task

The recipe is available as a gist. I think it is fairly self-explanatory and the documentation section at the beginning should give you a good overview of how it works.

#
# = Capistrano database.yml task
#
# Provides a couple of tasks for creating the database.yml
# configuration file dynamically when deploy:setup is run.
#
# Category::    Capistrano
# Package::     Database
# Author::      Simone Carletti
# Copyright::   2007-2009 The Authors
# License::     MIT License
# Link::        http://www.simonecarletti.com/
# Source::      http://gist.github.com/2769
#
#

unless Capistrano::Configuration.respond_to?(:instance)
  abort "This extension requires Capistrano 2"
end

Capistrano::Configuration.instance.load do

  namespace :db do

    desc <<-DESC
      Creates the database.yml configuration file in shared path.

      By default, this task uses a template unless a template 
      called database.yml.erb is found either is :template_dir 
      or /config/deploy folders. The default template matches 
      the template for config/database.yml file shipped with Rails.

      When this recipe is loaded, db:setup is automatically configured 
      to be invoked after deploy:setup. You can skip this task setting 
      the variable :skip_db_setup to true. This is especially useful 
      if you are using this recipe in combination with 
      capistrano-ext/multistaging to avoid multiple db:setup calls 
      when running deploy:setup for all stages one by one.
    DESC
    task :setup, :except => { :no_release => true } do

      default_template = <<-EOF
      base: &base
        adapter: sqlite3
        timeout: 5000
      development:
        database: #{shared_path}/db/development.sqlite3
        <<: *base
      test:
        database: #{shared_path}/db/test.sqlite3
        <<: *base
      production:
        database: #{shared_path}/db/production.sqlite3
        <<: *base
      EOF

      location = fetch(:template_dir, "config/deploy") + '/database.yml.erb'
      template = File.file?(location) ? File.read(location) : default_template

      config = ERB.new(template)

      run "mkdir -p #{shared_path}/db"
      run "mkdir -p #{shared_path}/config"
      put config.result(binding), "#{shared_path}/config/database.yml"
    end

    desc <<-DESC
      [internal] Updates the symlink for database.yml file to the just deployed release.
    DESC
    task :symlink, :except => { :no_release => true } do
      run "ln -nfs #{shared_path}/config/database.yml #{release_path}/config/database.yml"
    end

  end

  after "deploy:setup",           "db:setup"   unless fetch(:skip_db_setup, false)
  after "deploy:finalize_update", "db:symlink"

end

The following instructions basically represents the documentation you can find at the top of the original recipe.

Requirements

This extension requires the original config/database.yml to be excluded from version control. You can easily accomplish this by renaming the file (for example to database.example.yml) and configuring your SCM in order to ignore the database.yml file.

The following example demonstrate how to rename the file and ignore the original one with Subversion.

$ svn mv config/database.yml config/database.example.yml
$ svn propset svn:ignore 'database.yml' config

If your repository is powered by Git, type the following commands.

$ git mv config/database.yml config/database.example.yml
$ echo 'config/database.yml' >> .gitignore

If you don’t want to rename your file, there’s an other alternative. You can customize the recipe in order to force Capistrano to delete database.yml file after a successful deploy:code_update and before running the database:symlink task.

Usage

Include this file in your deploy.rb configuration file. Assuming you saved this recipe as capistrano_database.rb:

require "capistrano_database"

Now, when deploy:setup is called, this script will automatically create the database.yml file in the shared folder.
Each time you run a new deploy, this script will also create a symlink from your application config/database.yml pointing to the shared configuration file.

In case you need to run deploy:setup again and you don’t want Capistrano to ask for a database password, set the skip_db_setup option to true. This is especially useful in combination with capistrano multi-stage recipe when you already setup your server and you share the same environment across all the stages.

$ cap deploy:setup -s "skip_db_setup=true"

Custom template

By default, this script creates an exact copy of the default database.yml file shipped with a new Rails 2.x application.
If you want to overwrite the default template, simply create a custom Erb template called database.yml.erb and save it into config/deploy folder.

Although the name of the file can’t be changed, you can customize the directory where it is stored defining a variable called :template_dir.

# store your custom template at foo/bar/database.yml.erb
set :template_dir, "foo/bar"
#
# example of database template

base: &base
  adapter: sqlite3
  timeout: 5000
development:
  database: #{shared_path}/db/development.sqlite3
  <<: *base
test:
  database: #{shared_path}/db/test.sqlite3
  <<: *base
production:
  adapter: mysql
  database: #{application}_production
  username: #{user}
  password: #{Capistrano::CLI.ui.ask("Enter MySQL database password: ")}
  encoding: utf8
  timeout: 5000

Because this is an Erb template, you can place variables and Ruby scripts within the file.

For instance, the template above takes advantage of Capistrano CLI to ask for a MySQL database password instead of hard coding it into the template. This solves the original problem of storing sensitive data in your repository or deploy script.