pgsql can connect, pgadmin cannot

Question

We want only 1 role owning 1 database, Domain-unix Socket, no password

postgres@luciol-essai:~$ more /etc/postgresql/8.4/main/pg_hba.conf | grep jppstphp
local   essais_php  jppstphp                       trust
postgres@luciol-essai:~$

Success with psql :

envol@luciol-essai:~$ psql -U jppstphp essais_php
psql (8.4.15)
essais_php=>

Failure with pgadmin3 :

This is the error.

Error connecting to the server: FATAL:  authentification Ident ?chou?e pour l'utilisateur << jppstphp >>

What are the connection parameters you're using with pgadmin3? Are you using the socket or the 5432 port to localhost? — Lætitia, May 27 '13 at 16:55
pgadmin 1.10.5, for host, trying empty, or trying path to lock 5432, suppressing tcp port inhibit validate button ! — Jean-Pierre, May 27 '13 at 17:41
obviously pgadmin succed with habilitation host essais_php jppstphp 127.0.0.1/32 trust — Jean-Pierre, May 27 '13 at 17:49
using `more` there is completely unnecessary, it's like using `cat file | command`. Just use `grep term filename`. — Craig Ringer, May 28 '13 at 01:32

score 1 · Answer 1 · answered May 28 '13 at 01:34

It looks like PgAdmin-III is being told to connect to localhost or 127.0.0.1, so it's using TCP/IP, wheras psql isn't being given a host specification so it's using UNIX sockets (local).

Leave the host name field in pgadmin3 blank, or add another pg_hba.conf entry for host essais_php jppstphp 127.0.0.1/32 trust.

score 0 · Answer 2 · answered May 28 '13 at 12:42

You should check your whole pg_hba.conf file, the order of directives is important. I guess you kept the default line in it:

local   all         all                               ident

If this line is present before your specific line about jppstphp, the postgresql server will first try to identify you from your UNIX username, which seems to be envol. This will fail because you try to connect as postgres user/role jppstphp.

If my assumption is correct, removing the above line (and possibly the similar entry with md5 password authentication) or placing your jppstphp line before it should allow you to connect through the local socket.

To connect through the local socket, leaving the host field blank, like you did, is all that is required.

score 0 · Answer 3 · answered Oct 06 '16 at 19:00

Had same problem with psql via command line connecting and pgAdmin not connecting on RDS with AWS. I did have my RDS set to Publicly Accessible. I made sure my ACL and security groups were wide open and still problem so, I did the following: sudo find . -name *.conf then sudo nano ./data/pg_hba.conf then added to top of directives in pg_hba.conf file host all all 0.0.0.0/0 md5 and pgAdmin automatically logged me in.

As a side note, my RDS was in my default VPC. I had an identical RDS instance in my non-default VPC with identical security group, ACL and security group settings to my default VPC and I could not get it to work. Not sure why but, that's for another day.

pgsql can connect, pgadmin cannot

3 Answers3