1

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

  • How do you connect with pgadmin? Are you using the socket or are you using the localhost/127.0.0.1? – frlan May 8 '14 at 21:59
  • I tried both leaving the "Host"-field in pgadmin empty, and typing in "localhost". Both entries don't work. – tobias47n9e May 9 '14 at 6:41
  • "Passwort-Authentifizierung für Benutzer "postgres" fehlgeschlagen" sounds as if you simply supplied a wrong password – a_horse_with_no_name May 9 '14 at 8:16
  • 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). – tobias47n9e May 9 '14 at 8:21
  • 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 – a_horse_with_no_name May 9 '14 at 8:59
2

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
  • 3
    Well, you don't need to open a new question for: it is ;) Every local user can now connect without any password. – frlan May 9 '14 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.