Authentification failure for PostgreSQL server (9.3)

Question

I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433.

 Ver Cluster Port Status Owner Data directory Log file
 9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
 9.3 main 5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

I can connect to the 9.1 server with pgadmin, but when I try to access the 9.3 server I get the error that the password can't be authenticated:

 An error has occurred:
 Error connecting to the server: FATAL: Passwort-Authentifizierung für Benutzer „postgres" fehlgeschlagen
 FATAL: Passwort-Authentifizierung für Benutzer „postgres" fehlgeschlagen

The 9.1 server has the following lines in pg_hba.conf:

 # Database administrative login by Unix domain socket
 local all postgres peer
 # TYPE DATABASE USER ADDRESS METHOD
 # "local" is for Unix domain socket connections only
 local all all peer
 # IPv4 local connections:
 host all all 127.0.0.1/32 md5
 # IPv6 local connections:
 host all all ::1/128 md5
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local replication postgres peer
 #host replication postgres 127.0.0.1/32 md5
 #host replication postgres ::1/128 md5

and the 9.3 server has the following in the pg_hba.conf:

 # Database administrative login by Unix domain socket
 local all postgres md5
 # 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
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local replication postgres peer
 #host replication postgres 127.0.0.1/32 md5
 #host replication postgres ::1/128 md5

The difference is the "peer" or "md5" in the first two active lines. I am going to try switching those to peer next. Am I on the right track to a solution? (Edit: No, this did not help).

Password

I tried changing the default password of the server using:

 $ sudo -u postgres psql
 psql (9.3.4, Server 9.1.13)
 postgres=# \password

I have no idea why this didn't work. Maybe the command only affects the 9.1 server and not the 9.3 server (Hopefully I can kill the old one now and properly set up the 9.3 server).

Related questions:

-https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

-https://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres?rq=1

• frlan: How do you connect with pgadmin? Are you using the socket or are you using the localhost/127.0.0.1?
• tobias47n9e: I tried both leaving the "Host"-field in pgadmin empty, and typing in "localhost". Both entries don't work.
• user1822: "Passwort-Authentifizierung für Benutzer "postgres" fehlgeschlagen" sounds as if you simply supplied a wrong password
• tobias47n9e: Yes, I should mention that. The default password did not work and setting the password to something else also didn't work (Using sudo -u postgres psql).
• user1822: If you forgot the password you need to switch to trust mode, then login using the postgres super user (no need for a sudo, just use psql -U postgres) change the password, then change trust back to md5

Answer 1

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

 # Database administrative login by Unix domain socket
 local all postgres trust
 # TYPE DATABASE USER ADDRESS METHOD
 # "local" is for Unix domain socket connections only
 local all all trust
 # IPv4 local connections:
 host all all 127.0.0.1/32 md5
 # IPv6 local connections:
 host all all ::1/128 md5
 # Allow replication connections from localhost, by a user with the
 # replication privilege.
 #local replication postgres peer
 #host replication postgres 127.0.0.1/32 md5
 #host replication postgres ::1/128 md5

• frlan: Well, you don't need to open a new question for: it is ;) Every local user can now connect without any password.