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

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