64

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

LearningRoR
  • 26,582
  • 22
  • 85
  • 150

3 Answers3

139

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 2
    Its not letting me get inside of `psql.exe`. When I try to open it, the window comes up and then automatically closes so I then tried to go to the cmd prompt and typed in `c:\Program Files..\..\bin > psql.exe > psql: FATAL: role "User" does not exist` getting the `FATAL` once again. Any ideas? – LearningRoR Dec 26 '11 at 23:40
  • 2
    @wrbg: You created a `postgres` user during installation, right? Try `psql -d postgres -U postgres`, if you need a password then `psql -d postgres -U postgres -W`. – mu is too short Dec 26 '11 at 23:49
  • It didn't prompt for a user during installation. Let me see in reinstalling. – LearningRoR Dec 27 '11 at 00:12
  • OK, during installation it says my username is `postgres` if that helps you any. But other then that, yeah I just create a password, or in this case, enter the existing one. – LearningRoR Dec 27 '11 at 00:18
  • 3
    @wrbg: Can you `psql -d postgres -U postgres`? – mu is too short Dec 27 '11 at 00:20
  • Ok so I created the role called `app` and now when I put in `rake db:create` I get `PGError: ERROR: permission denied to create database`. Also your code above did work out. – LearningRoR Dec 27 '11 at 00:28
  • 1
    @wrbg: Try `create role app_name login createdb` or `alter role app_name createdb` and then `rake db:create`. I probably had different defaults set up last time I did this. – mu is too short Dec 27 '11 at 01:38
  • So I ran inside of the `cmd prompt`: psql -d postgres -U postgres and then tried `create role app_name login createdb` and `alter role app_name createdb` but still no luck. How would I know if I even created my database? – LearningRoR Dec 27 '11 at 01:53
  • 2
    @wrbg: You can use `\du` in `psql` to see the users/roles and their permissions or `\l` to list the databases. If your user is set up properly then `rake db:create` should create the database and `psql -U app_name -d app_development` should get into it. – mu is too short Dec 27 '11 at 02:24
  • Thank you for the help Mu, I was able to find out more about using the `pgAdminIII', starting the server by entering the password, seeing my new user and giving it permission to create databases. That solved the issue. – LearningRoR Dec 27 '11 at 14:47
  • @Edward: Which command? [`create role`](http://www.postgresql.org/docs/current/static/sql-createrole.html)? – mu is too short May 14 '13 at 16:18
  • @mu is too short, Can you please help me for this: http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records – MAK Mar 05 '15 at 06:28
10

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.

Community
  • 1
  • 1
Dennis
  • 56,821
  • 26
  • 143
  • 139
10

If you have a specific account/user on your machine for postgres called postgres for example.

Then executing this command will bring a prompt for you to enter a role name. 

sudo -u postgres createuser --interactive

Then doing

rake db:create

Should work!

Ishan Kanade
  • 197
  • 1
  • 2
  • 10