Role does not exist error when logging into Postgresql

Question

My Rails app can't connect to Postgresql. I suspect it's because Postgres expects the user logging into it to be the same user logged into Ubuntu (12.10).

deployer@xxx:~$ whoami
deployer
deployer@xxx:~$ psql
psql: FATAL:  role "deployer" does not exist

My setup followed this sequence:

a) As root@server I created a new group admin and a new user deployer

groupadd admin
adduser deployer --ingroup admin
ssh-copy-id root@xxx.xxx.xx.xx

b) I logged in as deployer and installed Postgres

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql libpq-dev

c) On my remote server, I logged into psql and created a new user to match my Rails app:

sudo -u postgres psql
create user 'my_app' with password 'secret';
create database my_app_production owner my_app;

For deployment, I'm using the deployer user. This error keeps coming up during deployment:

FATAL: password authentication failed for user "my_app"

Here's my database.yml

production:
  adapter: postgresql
  encoding: utf8
  database: my_app_production
  pool: 5
  host: localhost
  username: my_app
  password: secret

Running \du in psql produces this:

 Role name  |                   Attributes                   | Member of 
------------+------------------------------------------------+-----------
 postgres   | Superuser, Create role, Create DB, Replication | {}
 my_app     |                                                | {}

Answer 1

deployer@xxx:~$ psql
psql: FATAL:  role "deployer" does not exist

You need to create the user in postgres:

create user deployer;

Alternatively, add an export in your profile, e.g.:

export PGUSER=postgres

Or change calls to psql as needed: psql -Uusername database