Cannot connect to postgres database even with 'trust' settings

Question

I'm currently really struggling with a postgres 9.1 database which i need for testing my software. I did not create this database but i used to log in before with postgres as a user and password. However, right now it is impossible for me to create a connection even with pgadmin3 from the same machine the database is running on. I tried a million combinations in pg-hba.conf like:

host    all             all             192.168.10.178/32         trust

or

host    all             all             0.0.0.0/0                 trust

or

host    example         postgres        192.168.10.178/32       trust

or

host    all             all             ::1/128                 md5

Ip4 adress of server is 192.168.10.178, subnet mask is 255.255.245.0. The machine is running windows server 2008 and i'm logged in with admin rights (if that matters). In postgresql.conf the following lines are set:

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all
                    # (change requires restart)
port = 5432             # (change requires restart)

I'm not familiar at all with postgres and what i read until now about pg_hba.conf did not get me any further. I would have expected that at least with the second setting from above it would let me log in without password not caring about user or host ip. But somehow pgadmin 3 still asks for a password for user 'postgres' and if i use 'postgres' it just tells me:

password-authentication for user 'postgres' failed

Please, can anyone tell me what the hell i'm supposed to do?

Answer 1

This is what my settings look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             nnn.nn.nnn.nnn/32       trust
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

(Obviously the n's are numbers I've hidden from my actual IP address.)

As @VaoTsun said, make sure the lines are in the order you wish them applied, because the first one that matches will be what is used. So if for example you have samenet set to md5, then next line you have samehost set to trust, you will get md5 because samenet is valid for your connection.

However I strongly suspect the issue is that the IPv6 connection entry is either commented out, or set to md5 (or both). I don't know exactly why, but I know on a lot of our 2008 servers, it will use the IPv6 setting rather than the IPv4. Maybe it depends if IPv6 is enabled on the machine. Set that entry as per my settings above, and I bet that will do it.

Also, unless you've configured PGAdmin to save your password, it will still ask you for a password even if trust is properly set. Just hit enter without typing in a password, and it should allow you in. (Assuming your settings have been set correctly.)