Role does not exist and unable to create database when using PostgreSQL

Question

I am using Heroku for my application and it requires PostgreSQL but you can still use SQLite3 for development. Since Heroku strongly advised against having 2 different databases I decided to change to PostgreSQL for development. I installed the gem pg and also went to the official PostgreSQL site to get the Windows installer and also changed my database.yml. During installation it requires a password for PostgreSQL so I made one. I had to change the pg_hba.conf file from using md5 to trust in order get past: fe_sendauth: no password supplied when trying to create the database.

# TYPE  DATABASE        USER            ADDRESS                 METHOD  # IPv4 local connections: host    all             all             127.0.0.1/32            trust # was md5 # IPv6 local connections: host    all             all             ::1/128                 trust # was md5 # Allow replication connections from localhost, by a user with the # replication privilege. #host    replication     postgres        127.0.0.1/32            trust #host    replication     postgres        ::1/128                 trust 

After getting rid of that though, I now get this:

$ rake db:create (in C:/app) FATAL:  role "User" does not exist  Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"utf8",  "database"=>"app_test", "pool"=>5, "username"=>nil, "password"=>nil}  

I do still have my development.sqlite3 and text.sqlite3 present, could that be the issue? What must be done?

Here is my full gist: https://gist.github.com/1522188

Answer 1

Add a username to your database.yml, might as well use your application's name (or some variant of the name) as the username, I'll use app_name as a placeholder:

development:   adapter: postgresql   encoding: utf8   database: app_development   pool: 5   username: app_name   password: 

Then create the user (AKA "role") inside PostgreSQL using psql.exe:

$ psql -d postgres postgres=# create role app_name login createdb; postgres=# \q 

The first line is in your terminal, the next two are inside psql. Then do your rake db:create.

The User user is possibly a default but user is already taken for other purposes in PostgreSQL so you'd have to quote it to preserve the case if you wanted to use User as a username:

postgres=# create role "User" login createdb; 

You're better off creating one user per-application anyway.

You'll want to do similar things for your test entry in database.yml as well.

Answer 2

PostgreSQL will try to create the database with your account (login) name if a username isn't specified in your config/database.yml. On OS X and Linux you can you see who this is with whoami. Looks like you're using Windows.

Solution A: Create a PostgreSQL user that matches the one it's looking for. For example

createuser --superuser some_user 

Solution B: Change the DB user by explicitly setting a username as shown in mu's answer.