Search code examples
postgresqlpsqlpgadmin-4

Connection failed with psql and pgadmin4

I use postgres 11, I just try to connect with one of my user to psql and pgadmin4

my pg_hba.conf file

# "local" is for Unix domain socket connections only
local   all             all                                 peer
host    sito        lcm_admin       127.0.0.1/32            md5
host    sito        sito_lcm        127.0.0.1/32            md5

My postgres user have a password.

I tried my posgres user, sito_lcm and lcm_admin to connect to pgAdmin4, alway get

Invalid username or password

I know there are good because It's the one I use to connect to posgres db in a java program

for psql i tried

su - postgres typed my password

authentication fail

tried with one of my db user

psql -U lcm_admin sito
psql: FATAL:  Peer authentication failed for user "lcm_admin"

Edit

With Daniel Vérité tips, I can connect to psql but not to pgAdmin4

enter image description here

First time i see this screen, my last connection was a few month ago, don't know if some updated had created this issue

Solution

  • Because of this rule in pg_hba.conf

    host    sito        lcm_admin       127.0.0.1/32            md5

    You probably want to invoke psql that way:

    psql -U lcm_admin -h 127.0.0.1 sito

    Then it will ask for a password instead of failing with "Peer authentication failed".

    Note that rules in pg_hba.conf are tested in their order in the file (top to bottom), and it stops at the first one that matches.

    About connectiong with pgAdmin4:

    As mentioned in https://www.pgadmin.org/docs/pgadmin4/latest/getting_started.html there is an additional step to access pgAdmin4 itself, as an application.

    In a server deployment, an email address and password are asked at installation time. If you don't remember the password but you gave a valid email address, the link "Forgotten your password" might work for you. Otherwise I guess reinstalling pgAdmin4 from scratch might work too.

    Personally, I launch pgAdmin4 as a docker container without a persistent volume:

    $ docker run -p8080:80  -e PGADMIN_DEFAULT_EMAIL=foo@example.com -e PGADMIN_DEFAULT_PASSWORD=bar dpage/pgadmin4

    Then I connect to http://localhost:8080 and the email/password to enter on the first page are those environment variables passed on the command line.