If you?re trying to create or setup your rails postgreSQL daatabase and getting this error, there may 3 possible reasons for this error. Let?s go through each of them.
1-Make sure you?re using correct credentials
Your username and password for your postgreSQL is provided in ?database.yml? file. Move there and make sure you?ve provided your correct username and password fields. If you?ve forgotten your password or even don?t know the password, go for solution below to change password of default user.
2- Change postgres user?s password
PostgreSQL is provided with a default user named ?postgres?. For this, we will use this role and change its password. Run following command to jump into postgreSQL cmd.
sudo -u postgres psql postgres
Enter your root/sudo password. This will take you to the psql cmd which will be prompted like
postgres=#
Here ?postgres? is the username you?re currently logged in with. Now run following command to change password.
password postgres
This will prompt you to enter your new password for user ?postgres? and then will ask you to confirm it by reentering it. You?re done with possible second solution. Go to database.yml file and update password for ?postgres? username according to your new password. Now run the command to create DB. If still it gives you the same error, jump to the solution below.
3- Update ?pg_hba.conf? file
If both of the above solutions fail, the problem is in your ?pg_hba.conf? file. You?ve to edit this file. First we should locate this file so we can edit it. Run following command to locate it.
locate pg_hba.conf
This will show the path where its residing. Copy this path and run the command like this (with complete path, as returned by the above command):
sudo nano /etc/postgresql/…/pg_hba.conf
This will open ?pg_hba.conf? in nano editor. Now locate this line in the file and scrolling down.
local all postgres peer
And replace it with:
local all postgres md5
Hit ctrl+o to save the change and ctrl+x to exit the editor program. I?ll suggest you to reopen the file in nano editor to make sure if the change was saved or not (peer replaced with md5 or not).Now run the following command to reload the posgres service.
sudo service postgresql restart
Give your sudo password and hit enter. Now try creating the DB. And its DONE.
