Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group