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

mu is too short · Accepted Answer · 2011-12-27 01:39:35Z

138

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.

edited Dec 27, 2011 at 1:39

answered Dec 26, 2011 at 22:44

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

LearningRoR Over a year ago

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?

mu is too short Over a year ago

@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 Over a year ago

@wrbg: Can you psql -d postgres -U postgres?

mu is too short Over a year ago

@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 Over a year ago

@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.

|

Ishan Kanade · Accepted Answer · 2017-06-07 05:37:47Z

11

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!

answered Jun 7, 2017 at 5:37

Ishan Kanade

3071 gold badge3 silver badges10 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:02:26Z

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.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Feb 10, 2016 at 12:31

Dennis

60.1k28 gold badges148 silver badges143 bronze badges

1 Comment

Meekohi Over a year ago

Does it really need --superuser or is -d enough?

Collectives™ on Stack Overflow

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

3 Answers 3

12 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related