0

PG 9.4 Centos 6.7

I successfully installed Postgres 9.4 on Centos. I am checking my authentication through pgAdmin. I can create new users etc with the Centos terminal.

However, after the user and passwords are created, I can't use them to access localhost on Postgres. I get the error: 'FATAL: Ident authentication failed for user "pguser"'

The weird thing is, I can login using my linux username and NO password. However, as soon as I create a new username and password, it doesn't work.

/var/lib/pgsql/9.4/data/pg_hba.conf file as in password authentication failed for user "postgres":

# Database administrative login by UNIX sockets
local   all         postgres                          ident
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "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

How do I set up a new superuser and password that will properly authenticate? (This user is going to be used to read/write to pg in a ruby on rails app)

Edit: the answer provided below works. You may have to restart your computer or find a good way to kill/restart pg, looks like sometimes the changes to the pg_hba.conf file don't take.

Community
  • 1
  • 1
GavinBelson
  • 2,514
  • 25
  • 36

1 Answers1

1

You need to configure your pg_hba.conf file to accept password as the METHOD to get in. If I am reading this correctly, you are attempting to log in to postgres locally with a user and password you configured. So you should configure pg_hba.conf as such:

# Database administrative login by UNIX sockets
local   all         postgres                          ident
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     password

After this is done, you should be able to log in locally with the username and password.

ryekayo
  • 2,341
  • 3
  • 23
  • 51
  • I commented out what I had in the pg_hba.conf file and inserted the lines you recommended. Created a new user in the terminal, and still getting the same error :( – GavinBelson May 09 '16 at 19:44
  • Hmm ok, let me take a look at their documentation and figure out what to do. Its been a while since i used postgres :) – ryekayo May 09 '16 at 19:45
  • With the proposed settings my Linux username doesn't work either to access localhost – GavinBelson May 09 '16 at 19:46
  • thank you. Yeah, it's probably something with this file, I can't seem to figure it out though. All this worked perfectly on Ubuntu. – GavinBelson May 09 '16 at 19:47
  • Did you try restarting postgres once the change is made? There are only two types of password authentication: md5 and password. Md5 uses MD5 encrypted passwords whereas password uses cleartext.. Cleartext should work in this case... – ryekayo May 09 '16 at 19:48
  • Also, do not comment everything out. Just change local's METHOD from md5 to password – ryekayo May 09 '16 at 19:49
  • Hmm.. yeah I've been restarting postgres and even opening a new terminal window just in case. I'll try changing the local method. – GavinBelson May 09 '16 at 20:14
  • If your trying to access Postgres db locally, you should have changed the local method to password.. – ryekayo May 09 '16 at 20:17
  • Still doesn't run even with the local method as password. I create the user with: "createuser -s pguser" then set a password with "\password pguser" - that sound alright? – GavinBelson May 09 '16 at 20:20
  • OK wow.. your answer is correct.. my issue was closing the terminal windows and restarting pgadmin turned out to not be enough - had to fully reboot for the pg_hba.conf to take. thank you for the help – GavinBelson May 09 '16 at 20:53
  • Sure, if you can checkmark this answer that would be great :) – ryekayo May 09 '16 at 20:57