Thread: 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 <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
Marco Craveiro <marco.craveiro@gmail.com> 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
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
<snip>
$ 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
Marco Craveiro <marco.craveiro@gmail.com> 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
> 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
Hi On 4 December 2010 23:52, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Marco Craveiro <marco.craveiro@gmail.com> 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 <esiotrot@gmail.com>
| I have a huge query which for some reason, runs without ending(after 1 hour I stopped the process because obviously something was wrong). The query itself is too bulky to paste here so I will try to paste only the part that (i think) causes the problem: select l.id,l.date, (select group_concat(u.id) from Logs l2 join Users u... where u.id not in (select u2.id from Logs l3 join Users u2 where....and l3.id=l.id)) from Logs l The problem lies at the "and l3.id=l.id" part. Without this small comparison, the query runs in 14 seconds. So I'm pretty sure this is the problem. Now the Logs table have about 5 million records, and I'm guessing that somehow he tries to compare each of the 5 mil records with (again) each of the 5 mil recors, causing these huge periods of time. Can anybody actually tell me if the comparison makes any sense, since I find it weird to compare the id's of the same tables between themselves (both l3 and l are infact Logs table). In my oppinion the comparison will always return true so there is no reason for it. Am I right or am I missing some fundamental rules here? Thanks in advance |