7

I'm trying to run rake db:create with postgresql on a DigitalOcean server.

However, it's returning the error Peer authentication failed for user "rails", referring to config/database.yml where the credentials to log in are stored

What's strange is that these are the exact credentials displayed to me in plain text when I log into the server through SSH. I've tried both <%= ENV['APP_DATABASE_PASSWORD'] %> and the password displayed to me in plain text and the same thing happens.

The environment is in production, which I have to enforce manually because the app is in development on startup and forcing it to change in config/environments.rb isn't working.

If I had to guess I might say that something funny is happening with the environment, because DigitalOcean will continue to serve a cached version of the site until the server is restarted and it might still think it's in development as far as it's concerned. But I'm in kind of a catch-22 until I figure out how to force it into production on startup.

This question is what I've arrived at after a lot of tribulation with postgres and trying to set up a database on the backend, so I need to be walked through a couple things.

Many thanks.

JackHasaKeyboard
  • 1,599
  • 1
  • 16
  • 29
  • 2
    In my case, I forgot specify the host in `database.yml` i.e. `localhost`. By default PG is configured to accept all calls from `localhost` or `127.0.0.1` – Shiva Jan 22 '17 at 12:47

1 Answers1

14

The problem is with your pg_hba.conf file. Which you can find in /etc/postgresql/9.3/main/pg_hba.conf. Here 9.3 is postgres version, you can change with your postgres version. More detail about authentication is here.

You have code something like :

# TYPE  DATABASE        USER    ADDRESS             METHOD

# "local" is for Unix domain socket connections only
local    all             all                        peer

You can either change it to md5 or trust.

# TYPE  DATABASE        USER    ADDRESS             METHOD

# "local" is for Unix domain socket connections only
local    all             all                        trust

It will fix your problem.

Note : To edit pg_hba.conf file you should have sudo permission on server.

Dipak Gupta
  • 7,321
  • 1
  • 20
  • 32
  • That fixed the problem. Is that a step backwards in terms of security, though? – JackHasaKeyboard Nov 27 '15 at 23:01
  • Not working for me... is it necessary to restart PosgreSql server? – Albert Català Mar 02 '16 at 23:17
  • Yes, Sometime you need to restart system also. In never version of postgres 9.4, I have experience we don't need to make any such settings – Dipak Gupta Mar 03 '16 at 04:27
  • @DipakG. Can you tell me that after edit this file from terminal how to save changes. i have same issue i am trying it but my file is not save changes frm me – Pooja Mokariya Apr 02 '17 at 09:54
  • To save this file you must have sudo access. You can open file in vim or gedit editor. `sudo vi /etc/postgresql/9.3/main/pg_hba.conf` or `sudo gedit /etc/postgresql/9.3/main/pg_hba.conf`. gedit provide visual interface so use gedit command. Let me know if you need any other help. – Dipak Gupta Apr 03 '17 at 04:56
  • I just tried in PostgreSQL 10.1, it will need to restart psql server after changed. – Zernel Jan 20 '18 at 15:33
  • Yes restart required on every version, I will update answer. Thanks. – Dipak Gupta Jan 24 '18 at 08:06