rake db:create using PostgreSQL ---- fe_sendauth: no password supplied

Question

New to all this so please be patient. Basically I was trying to follow this to get Rails set up on my Linux Lite machine. After installing PostgreSQL I went ahead to create a database role, as per the tutorial instructions, like this

sudo -u postgres createuser tjjjwxzq -s

But I didn't follow up with creating a password with this role, as the tutorial made it seem like it was an optional thing. I specified the development environment username in my database.yml file:

development:
  <<: *default
  database: myapp_development

  # The specified database role being used to connect to postgres.
  # To create additional roles in postgres see `$ createuser --help`.
  # When left blank, postgres will use the default role. This is
  # the same name as the operating system user that initialized the database.
  username: tjjjwxzq

  # The password associated with the postgres role (username).
  #password:

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost

(Edit: I uncommented host:localhost)

I also changed the pg_hba.conf file to authenticate with the role I created:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# settings for tjjjwxzq user
local   all             tjjjwxzq                                     trust

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

As you can see I set the authentication method for the tjjjwxzq user to trust, because I didn't create a password and I don't want to have to supply one. But when I run, in my Rails project directory

rake db:create

I get:

fe_sendauth: no password supplied
...
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "pool"=>5, "database"=>"myapp_test"}

I am completely new to PostgreSQL and Rails, and I have been googling around for a solution for hours now. Changing the pg_hba.conf file seemed promising, but it hasn't worked for me. I suspect I could get pass this by specifying a password for the tjjjwxzq role I created, but I want to understand why my setup now is not working. Any help is much appreciated.

EDIT: Okay, I've realized I've made a dumb mistake. rake db:create does work for creating myapp_development (the specified development database) I've checked the log file, and the latest logs were:

2015-04-01 07:04:28 SGT [2342-1] tjjjwxzq@postgres ERROR:  database "myapp_development" already exists
2015-04-01 07:04:28 SGT [2342-2] tjjjwxzq@postgres STATEMENT:  CREATE DATABASE "myapp_development" ENCODING = 'unicode'

Now I realize that in the output for rake db:create, the last line displays

Couldn't create database for {"adapter"=>"postgresql", encoding"=>"unicode", "pool"=>5, "database"=>"myapp_test"}

So the problem was actually with my test database, and looking at my database.yml file, I see:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: 5

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: myapp_test

So there is no username field. Thanks for your help so far, but now my questions would be: 1) is it okay for myapp_test to not be created successfully, or should I set its user to tjjjwxzq as well? And 2) where can a newb like me read up more about this to get a better understanding (but nothing overwhelmingly technical, for now) of what is going on and the need for the development, test and production configs and environments? Rails docs?

Answer 1

I don't like this.

sudo -u postgres createuser tjjjwxzq -s

There's no reason to make that user a superuser (-s) for the whole cluster. Just allow that user to create databases.

sudo -u postgres createuser tjjjwxzq --createdb

Change your config/database.yml to use sockets. Comment out the host: localhost line. (I'm assuming you're not running under Windows.)

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  # host: localhost

You had set up your pg_hba.conf to trust connections on sockets; uncommenting this line makes Rails try to connect using tcp instead of sockets.

This should work now.

$ bin/rake db:create