Understanding the behaviour of hostname in psql

From: Marco Craveiro <marco(dot)craveiro(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Understanding the behaviour of hostname in psql
Date: 2010-12-04 20:57:19
Message-ID: AANLkTi=4TJrV=h70gJXi9F_+dGJbnjEvqJoffFnvym7R@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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
<snip>

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-12-04 21:52:49 Re: Understanding the behaviour of hostname in psql
Previous Message Jasen Betts 2010-12-02 11:58:03 Re: Get server's time in UTC time zone, in ISO 8601 format