0

I just installed PostgreSQL 9.2 server on an EC2 AMI instance. However I am not able to connect to it from the command prompt.

Moreover I see two directories in /var/lib: pgsql9 and pgsql92. The data directory in pgsql92 is empty and hence it looks like pgsql9 is the one that is getting used.

[root@ip-172-31-56-103 etc]# psql
Password:
psql: FATAL:  password authentication failed for user "root"
[root@ip-172-31-56-103 etc]# sudo su - postgres
-bash-4.2$ psql
Password:
psql: FATAL:  password authentication failed for user "postgres"
-bash-4.2$ psql -U postgres
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
-bash-4.2$

pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host     all            all             127.0.0.1/32            md5
host     all            all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

postgresql.conf

listen_addresses = '*'
Nital
  • 5,784
  • 26
  • 103
  • 195
  • 1
    Possible duplicate of [Postgresql: password authentication failed for user "postgres"](http://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres) – Dmitry S Jan 14 '16 at 07:53
  • Tried the solutions from link but still getting the same error – Nital Jan 14 '16 at 15:10

1 Answers1

1

To work out which PG install you are using: ps -ef | grep pgsql. You will see for sure which binary, data directory and conf file is being used to give you some comfort.

Have you changed the pg_hba.conf from the default? If so, did you reload it? Something like sudo service postgresql reload should do it, depending upon your OS.

You might want to change the IPv6 local connection to use md5 as well.

Try adding -h localhost or -h 127.0.0.1 to your psql command: e.g. psql -h localhost -U postgres.

Check your postgres password to be doubly / triply sure.

Otherwise, check out the specific docs for your OSs installation. Sometimes apt or yum repos do some additional security configuration for you.

Finally, worst case, change all the pg_hba.conf auth methods to trust, then restart the database, logon, change the postgres password, logout, change the auth methods to md5, reload and try to logon again.

smcstewart
  • 2,016
  • 14
  • 16