Connecting Rails and Postgres

I am working on a project for Code Academy with a few other students. We decided to use PostgreSQL, since that is what Heroku uses. One of the other students posted some directions on his blog. I could not follow them since I run Linux.

I had already run apt-get to install some libraries, so I will skip that.

I put this into my Gemfile:

gem 'pg'

I put this in config/database.yml

common: &common
  adapter: postgresql
  encoding: utf8
  pool: 5
  host: localhost
  # username: digital_schoolhouse
  # password: 
  # template: template0
  # port: 5432

development:
  <<: *common
  database: digital_schoolhouse_development

test:
  <<: *common
  database: digital_schoolhouse_test

production:
  <<: *common
  database: digital_schoolhouse_production

But when I ran

rake db:create

I got this error:

PG::Error: ERROR:  permission denied to create database

I had to do a lot of googling. I won’t go over some of the other errors I got, so I will try to list some commands that will help me to get Rails working with Postgres on Linux boxes in the future.

You must create a user that has the same name as the username that will be running the rails app. It must own the database, and have permissions to create and alter databases. For some reason I did not need to enter a password in any Rails file. Even so, things seemed to work.

In order to use Postgres, I have to type this:

sudo -u postgres psql postgres

I would like to log into postgres without sudo-ing. I can log into MySQL as root from my main user ID.

To create the user:

create user ericm with password '$SOME_PASSWORD' createdb;

To see users, run the metacommand \dg

postgres=# \dg
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 ericm     | Create DB                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rails     |                                                | {}

Then create the database:

create database digital_schoolhouse_test with owner ericm;
grant all privileges on database digital_schoolhouse_development to ericm;

To list the databases, run the metacommand \l

postgres=# \l
                                             List of databases
              Name               |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
---------------------------------+----------+----------+-------------+-------------+-----------------------
 digital_schoolhouse_development | ericm    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 digital_schoolhouse_test        | ericm    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres                        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 second_chance_development       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0                       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                 |          |          |             |             | postgres=CTc/postgres
 template1                       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                 |          |          |             |             | postgres=CTc/postgres
(6 rows)

After this, you should be able to run the commands to create, populate and reset the databases:

rake db:create
rake db:reset

Image from Wikimedia, assumed allowed under Fair Use. Image from the Cotton Genesis, a Greek manuscript of the Book of Genesis created in the 4th or 5th Century. It is called the “Cotton Genesis” because at one time it was owned by a man with the surname “Cotton”; it is not made of cotton.