Understanding the behaviour of hostname in psql

Understanding the behaviour of hostname in psql

hello pgsql-novice,

i'm looking for some help in understanding the behaviour of hostname
in postgres 8.4. apologies if this has been asked before; i googled
but to no avail.
basically: do i need to supply both the 127.0.1.1 ip address in
pg_hba.conf as well as the actual ip address (say 192.168.0.5) in
order to be able
to always have trusted local connections? and if yes, whats the best
way of dealing with DHCP?

finally, if i understood correctly that this is a shortcoming of 8.4,
do i also understand correctly that this is fixed in 9.1 by the
parameter samehost?

http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html

investigation details
------------------------------
i've setup my local postgres to trust local connections (pg_hba.conf):

    # IPv4 local connections:
    host    all         all         127.0.1.1/32          trust

i've also made sure listen was set (postgresql.conf):

    listen_addresses = '*'  		# what IP address(es) to listen on;

connections via 127 work:

    $ psql -h 127.0.1.1 -U marco -w --dbname sanzala
    psql (8.4.5)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.

connections via hostname or real ip address fail:

    $ psql -h bohr -U marco -w --dbname sanzala
    psql: FATAL:  password authentication failed for user "marco"
    FATAL:  password authentication failed for user "marco"

    $ psql -h 192.168.0.5 -U marco -w --dbname sanzala
    psql: FATAL:  no pg_hba.conf entry for host "192.168.0.5", user
"marco", database "sanzala", SSL on
    FATAL:  no pg_hba.conf entry for host "192.168.0.5", user "marco",
database "sanzala", SSL off

the problem appears to stem from the way network manager has setup the
hosts file:

    $ cat /etc/hosts
    192.168.0.5	bohr	# Added by NetworkManager
    127.0.0.1	localhost.localdomain	localhost
    ::1	bohr	localhost6.localdomain6	localhost6
    127.0.1.1	bohr
    

many thanks for your time

marco
-- 
The key to Understanding complicated things is to know what not to
look at, and what not to compute, and what not to think. -- Abelson &
Sussman, SICP

blog: http://mcraveiro.blogspot.com

Re: Understanding the behaviour of hostname in psql

Marco Craveiro  writes:
> i'm looking for some help in understanding the behaviour of hostname
> in postgres 8.4. apologies if this has been asked before; i googled
> but to no avail.
> basically: do i need to supply both the 127.0.1.1 ip address in
> pg_hba.conf as well as the actual ip address (say 192.168.0.5) in
> order to be able
> to always have trusted local connections? and if yes, whats the best
> way of dealing with DHCP?

Well, a connection to "localhost" will generally go to 127.0.0.1
(*not* 127.0.1.1 --- that's just a typo from some hand hacking
of your hosts file, I bet).  A connection to your host name will
go to whatever the assigned "real" IP is (192.168.0.5 in your
example).  If you don't have a stable assigned IP because you're
using DHCP, the best advice would be to always write localhost
and never bohr in your psql -h switch.

This has nothing much to do with Postgres specifically --- it's a
generic property of hostname lookup.

			regards, tom lane

Re: Understanding the behaviour of hostname in psql

thanks for your prompt response Tom.

> Well, a connection to "localhost" will generally go to 127.0.0.1
> (*not* 127.0.1.1 --- that's just a typo from some hand hacking
> of your hosts file, I bet).  A connection to your host name will
> go to whatever the assigned "real" IP is (192.168.0.5 in your
> example).  If you don't have a stable assigned IP because you're
> using DHCP, the best advice would be to always write localhost
> and never bohr in your psql -h switch.
>
> This has nothing much to do with Postgres specifically --- it's a
> generic property of hostname lookup.

nice one, this makes perfect sense. so i cleaned up my hosts file and
my pg_hba.conf
file; also, i've restarted the machine just in case some caching is
happening somewhere.
unfortunately, the result is still not quite right:

    $ psql -h 127.0.0.1 -U marco -w --dbname sanzala
    psql (8.4.5)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.

    [marco@127.0.0.1:5432 (12:04:40) sanzala ]$ \q

    $ psql -h localhost -U marco -w --dbname sanzala
    psql: fe_sendauth: no password supplied

    $ ping -c 1 localhost
    PING localhost.localdomain (127.0.0.1) 56(84) bytes of data.
    64 bytes from localhost.localdomain (127.0.0.1): icmp_req=1 ttl=64
time=0.020 ms
    

    $ head -n3 /etc/hosts
    192.168.0.5	bohr	# Added by NetworkManager
    127.0.0.1	localhost.localdomain	localhost
    ::1	bohr	localhost6.localdomain6	localhost6

    $ grep /etc/postgresql/8.4/main/pg_hba.conf -B1 -e^host
    # IPv4 local connections:
    host    all         all         127.0.0.1/32          trust
    # IPv6 local connections:
    host    all         all         ::1/128               md5

not quite sure why i still get asked for a password when i login via
localhost...

cheers
-- 
The key to Understanding complicated things is to know what not to
look at, and what not to compute, and what not to think. -- Abelson &
Sussman, SICP

blog: http://mcraveiro.blogspot.com

Re: Understanding the behaviour of hostname in psql

Marco Craveiro  writes:
> unfortunately, the result is still not quite right:

>     $ psql -h 127.0.0.1 -U marco -w --dbname sanzala
>     [ works as expected ]

>     $ psql -h localhost -U marco -w --dbname sanzala
>     psql: fe_sendauth: no password supplied

It looks to me like "localhost" is getting resolved as the IPv6
loopback address (::1), which for some reason you've configured
differently than the IPv4 loopback address in your pg_hba.conf:

>     host    all         all         127.0.0.1/32          trust
>     host    all         all         ::1/128               md5

I'm not sure why your "ping" example doesn't reflect that --- maybe you
have an IPv4-only version of ping?  But if you're unconvinced you could
turn on log_connections and see where the server sees the connection as
coming from.

			regards, tom lane

Re: Understanding the behaviour of hostname in psql

> It looks to me like "localhost" is getting resolved as the IPv6
> loopback address (::1), which for some reason you've configured
> differently than the IPv4 loopback address in your pg_hba.conf:
>
>>     host    all         all         127.0.0.1/32          trust
>>     host    all         all         ::1/128               md5

ah yes, good point - i was just ignoring IPv6 altogether. school boy error!

> I'm not sure why your "ping" example doesn't reflect that --- maybe you
> have an IPv4-only version of ping?  But if you're unconvinced you could
> turn on log_connections and see where the server sees the connection as
> coming from.

well, setting IPv6 to trust too fixed my problem. i'll keep
log_connections in mind for future problems though.

thanks a lot for your time.

cheers
-- 
The key to Understanding complicated things is to know what not to
look at, and what not to compute, and what not to think. -- Abelson &
Sussman, SICP

blog: http://mcraveiro.blogspot.com

Re: Understanding the behaviour of hostname in psql

Hi

On 4 December 2010 23:52, Tom Lane  wrote:
> Marco Craveiro  writes:
>> i'm looking for some help in understanding the behaviour of hostname
>> in postgres 8.4. apologies if this has been asked before; i googled
>> but to no avail.
>> basically: do i need to supply both the 127.0.1.1 ip address in
>> pg_hba.conf as well as the actual ip address (say 192.168.0.5) in
>> order to be able
>> to always have trusted local connections? and if yes, whats the best
>> way of dealing with DHCP?
>
> Well, a connection to "localhost" will generally go to 127.0.0.1
> (*not* 127.0.1.1 --- that's just a typo from some hand hacking
> of your hosts file, I bet).  [...]

Actually, no.  Some Linux distributions add a line like this to the
hosts file on install:

127.0.1.1 yourhost

I'm not entirely sure what the reason is, but it might involve
allowing for machines with no ethernet etc. interfaces.

-- 
Michael Wood 

Infinite/Huge loop in query