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?
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.)