Capistrano postgresql: FATAL: Peer authentication failed for user

Question

I am deploying an app to digital ocean for the first time and ran into two (possible more) issues.

1) I can't bundle install after adding gem 'unicorn' to Gemfile. I found that kgio is not compatible with windows. Do I have to have Gemfile.lock present when I deploy via capistrano? How would I work around this issue?

group :production do
  gem 'pg', '0.14.1'
  gem "nginx"
  gem 'unicorn'
end

2) I am having trouble with authentication on postgresql on the server.

production:
  adapter: postgresql
  encoding: unicode
  database: postgresql
  pool: 5
  username: postgresql
  password: secret

I ran these commands (along with some other variations):

create user postgresql with password 'secret';
create database postgresql with owner postgresql;

Every time I cap deploy, I get this error:

FATAL: Peer authentication failed for user "postgresql"

I tried putting an invalid username that I know doesn't exist, a database that is invalid but the error message is always the same. According to postgresql website, I should be getting different errors for those...

If I can get some help, that'd be amazing. Thank you!

Answer 1

You need to specify the host for the password authentication.

production:
  adapter: postgresql
  encoding: unicode
  database: postgresql
  pool: 5
  host: localhost
  username: postgresql
  password: secret

More details here

Answer 2

You have to set up Postgres for password or md5 (safer: scram-sha-256 since Postgres 11) authentication first - in the pg_hba.conf file.

As long as only ident or peer authentication is allowed, passwords are not prompted. You are only allowed to log in as the db role corresponding to your system user.

BTW, database role and OS user are typically called postgres, not postgresql. That's not a typo there, I assume?

Try in a shell:

sudo -u postgres -i

And then log in as postgres db role with peer authentication.

See:

• PostgreSQL error: Fatal: role "username" does not exist
• Run batch file with psql command without password