Thread: psql connection via localhost or 127.0.0.1
I am using postgres 9.4beta3 (Debian jessie) this is my pg_hba.conf ---------------------- local all postgres peer local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 ---------------------- but if I use psql to connect via tcp to the server (on the same machine) I can see two differente behaviours if I use "localhost" or "127.0.0.1" If I understand correctly the first two lines are useful only for Unix sockets and not for tcp-ip connection. ------ with localhost------------ edoardo@host:~$ psql -h localhost -U postgres psql (9.4beta3) Connessione SSL (protocollo: TLSv1.2, cifrario: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compressione: disattivato) Digita "help" per avere un aiuto. postgres=# ------------------------------ no password request ------ with localhost------------ edoardo@happy:~$ psql -h 127.0.0.1 -U postgres Inserisci la password per l'utente postgres: ------------------------------ with password request. Why localhost is not an alias for 127.0.0.1 ? I did some search on Google but with no useful results thank you Edoardo
On 11/06/2014 06:34 AM, Edoardo Panfili wrote: > I am using postgres 9.4beta3 (Debian jessie) > > this is my pg_hba.conf > ---------------------- > local all postgres peer > local all all md5 > host all all 127.0.0.1/32 md5 > host all all ::1/128 md5 > ---------------------- > > but if I use psql to connect via tcp to the server (on the same machine) > I can see two differente behaviours if I use "localhost" or "127.0.0.1" > If I understand correctly the first two lines are useful only for Unix > sockets and not for tcp-ip connection. > > ------ with localhost------------ > edoardo@host:~$ psql -h localhost -U postgres > psql (9.4beta3) > Connessione SSL (protocollo: TLSv1.2, cifrario: > ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compressione: disattivato) > Digita "help" per avere un aiuto. > > postgres=# > ------------------------------ > no password request > > ------ with localhost------------ > edoardo@happy:~$ psql -h 127.0.0.1 -U postgres > Inserisci la password per l'utente postgres: > ------------------------------ > with password request. > > Why localhost is not an alias for 127.0.0.1 ? See here: http://www.postgresql.org/docs/9.3/interactive/libpq-connect.html hostaddr > > I did some search on Google but with no useful results > > thank you > Edoardo > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/6/2014 6:34 AM, Edoardo Panfili wrote: > > Why localhost is not an alias for 127.0.0.1 ? grep localhost /etc/hosts I've seen some weird stuff on some systems. -- john r pierce 37N 122W somewhere on the middle of the left coast
Edoardo Panfili <edoardo@aspix.it> writes: > I am using postgres 9.4beta3 (Debian jessie) > this is my pg_hba.conf > ---------------------- > local all postgres peer > local all all md5 > host all all 127.0.0.1/32 md5 > host all all ::1/128 md5 > ---------------------- > but if I use psql to connect via tcp to the server (on the same machine) > I can see two differente behaviours if I use "localhost" or "127.0.0.1" > If I understand correctly the first two lines are useful only for Unix > sockets and not for tcp-ip connection. Yeah. On modern Linux distros it's quite likely that "localhost" is resolving as IPv6 ::1, not 127.0.0.1, but in neither case would it match your "local" lines. In any case, your first connection attempt is definitely not connecting over a Unix socket because we never use SSL with Unix sockets. I can think of two plausible theories: 1. The postmaster isn't actually using the pg_hba.conf you think it is. (Maybe you modified the file and forgot to do a "pg_ctl reload"?) 2. You have a "service file" active that is capturing the server name "localhost" and redefining it to mean something other than the obvious meaning. http://www.postgresql.org/docs/9.3/interactive/libpq-pgservice.html A useful test would be to try "psql -h ::1" and see which way that behaves. If it does the same thing as "-h localhost" then we can eliminate the service-file theory. regards, tom lane
On 11/06/2014 07:00 AM, John R Pierce wrote: > On 11/6/2014 6:34 AM, Edoardo Panfili wrote: >> >> Why localhost is not an alias for 127.0.0.1 ? > > grep localhost /etc/hosts > > I've seen some weird stuff on some systems. I have localhost/127.0.0.1 set up correctly on my machine and I see the same behavior as the OP. I'm pretty sure it is an interaction between the libpq host/hostaddr code and a .pgpass file. I suspect the OP has a .pgpass entry for localhost but not 127.0.0.1. > -- Adrian Klaver adrian.klaver@aklaver.com
Il 06/11/14 16:00, John R Pierce ha scritto: > On 11/6/2014 6:34 AM, Edoardo Panfili wrote: >> >> Why localhost is not an alias for 127.0.0.1 ? > > grep localhost /etc/hosts 127.0.0.1 localhost 127.0.1.1 host.host host # The following lines are desirable for IPv6 capable hosts ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters > > I've seen some weird stuff on some systems. > 127.0.1.1 sounds a little bit strange form me but the file seems ok. and $ host localhost localhost has address 127.0.0.1 localhost has IPv6 address ::1 Edoardo
On 11/6/2014 7:36 AM, Edoardo Panfili wrote:
>> grep localhost /etc/hosts
> 127.0.0.1 localhost
> 127.0.1.1 host.host host
wahaaaa?
whats the output of ...
# ifconfig lo
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0
TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 GiB)
note that net mask? the loopback interface is the entire 127.0.0.0/8
network you can't put a host at 127.0.1.x and expect it to work right.
you should instead use one of the RFC1918 reserved subnets for a private
network, within 10.0.0.0/8 or 172.16.0.0/12, or 192.168.0.0/16 (you can
use these with any mask size you want, for instance, /24 is usually used
with 192.168.x.y)
--
john r pierce 37N 122W
somewhere on the middle of the left coast
Il 06/11/14 16:08, Tom Lane ha scritto: > Edoardo Panfili <edoardo@aspix.it> writes: >> I am using postgres 9.4beta3 (Debian jessie) >> this is my pg_hba.conf >> ---------------------- >> local all postgres peer >> local all all md5 >> host all all 127.0.0.1/32 md5 >> host all all ::1/128 md5 >> ---------------------- >> but if I use psql to connect via tcp to the server (on the same machine) >> I can see two differente behaviours if I use "localhost" or "127.0.0.1" >> If I understand correctly the first two lines are useful only for Unix >> sockets and not for tcp-ip connection. > Yeah. On modern Linux distros it's quite likely that "localhost" is > resolving as IPv6 ::1, not 127.0.0.1, but in neither case would it match > your "local" lines. In any case, your first connection attempt is > definitely not connecting over a Unix socket because we never use SSL > with Unix sockets. ok, thank you. > > I can think of two plausible theories: > > 1. The postmaster isn't actually using the pg_hba.conf you think it is. > (Maybe you modified the file and forgot to do a "pg_ctl reload"?) another way on my test machine: /etc/init.d/postgresql stop /etc/init.d/postgresql start > > 2. You have a "service file" active that is capturing the server name > "localhost" and redefining it to mean something other than the obvious > meaning. > http://www.postgresql.org/docs/9.3/interactive/libpq-pgservice.html > > A useful test would be to try "psql -h ::1" and see which way that > behaves. If it does the same thing as "-h localhost" then we can > eliminate the service-file theory. -------- connection via ::1---------------- $ psql -h ::1 -U postgres Inserisci la password per l'utente postgres: ---------------------------------------- same as for 127.0.0.1 thank you Edoardo
Il 06/11/14 16:18, Adrian Klaver ha scritto: > On 11/06/2014 07:00 AM, John R Pierce wrote: >> On 11/6/2014 6:34 AM, Edoardo Panfili wrote: >>> >>> Why localhost is not an alias for 127.0.0.1 ? >> >> grep localhost /etc/hosts >> >> I've seen some weird stuff on some systems. > > I have localhost/127.0.0.1 set up correctly on my machine and I see > the same behavior as the OP. I'm pretty sure it is an interaction > between the libpq host/hostaddr code and a .pgpass file. I suspect the > OP has a .pgpass entry for localhost but not 127.0.0.1. ------ .pgpass -------------- localhost:5432:*:postgres:postgres_pwd ---------------------------- removing this file localhost behaves like 127.0.0.1 maybe that the debian installer creates this file during installation (is useful but I didn't know this file). this explains the situation, thank you again to all Edoardo
On 11/06/2014 07:47 AM, John R Pierce wrote: > On 11/6/2014 7:36 AM, Edoardo Panfili wrote: >>> grep localhost /etc/hosts >> 127.0.0.1 localhost >> 127.0.1.1 host.host host > > wahaaaa? https://lists.debian.org/debian-devel/2013/07/msg00809.html -- Adrian Klaver adrian.klaver@aklaver.com
Il 06/11/14 16:47, John R Pierce ha scritto:
> On 11/6/2014 7:36 AM, Edoardo Panfili wrote:
>>> grep localhost /etc/hosts
>> 127.0.0.1 localhost
>> 127.0.1.1 host.host host
>
> wahaaaa?
>
> whats the output of ...
>
> # ifconfig lo
> lo Link encap:Local Loopback
> inet addr:127.0.0.1 Mask:255.0.0.0
> inet6 addr: ::1/128 Scope:Host
> UP LOOPBACK RUNNING MTU:65536 Metric:1
> RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0
> TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0
> collisions:0 txqueuelen:0
> RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6
> GiB)
>
# ifconfig lo
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:3306 errors:0 dropped:0 overruns:0 frame:0
TX packets:3306 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:2544659 (2.4 MiB) TX bytes:2544659 (2.4 MiB)
> note that net mask? the loopback interface is the entire
> 127.0.0.0/8 network you can't put a host at 127.0.1.x and expect it
> to work right.
>
> you should instead use one of the RFC1918 reserved subnets for a
> private network, within 10.0.0.0/8 or 172.16.0.0/12, or
> 192.168.0.0/16 (you can use these with any mask size you want, for
> instance, /24 is usually used with 192.168.x.y)
I can't figure why 127.0.1.1 is there (I will remove it) is an (almost)
new installation on a virtual machine,
Edoardo
On 11/06/2014 07:57 AM, Edoardo Panfili wrote: > Il 06/11/14 16:47, John R Pierce ha scritto: >> On 11/6/2014 7:36 AM, Edoardo Panfili wrote: >>>> grep localhost /etc/hosts >>> 127.0.0.1 localhost >>> 127.0.1.1 host.host host >> >> wahaaaa? >> >> whats the output of ... >> >> # ifconfig lo >> lo Link encap:Local Loopback >> inet addr:127.0.0.1 Mask:255.0.0.0 >> inet6 addr: ::1/128 Scope:Host >> UP LOOPBACK RUNNING MTU:65536 Metric:1 >> RX packets:18367154 errors:0 dropped:0 overruns:0 frame:0 >> TX packets:18367154 errors:0 dropped:0 overruns:0 carrier:0 >> collisions:0 txqueuelen:0 >> RX bytes:23279985092 (21.6 GiB) TX bytes:23279985092 (21.6 >> GiB) >> > # ifconfig lo > lo Link encap:Local Loopback > inet addr:127.0.0.1 Mask:255.0.0.0 > inet6 addr: ::1/128 Scope:Host > UP LOOPBACK RUNNING MTU:65536 Metric:1 > RX packets:3306 errors:0 dropped:0 overruns:0 frame:0 > TX packets:3306 errors:0 dropped:0 overruns:0 carrier:0 > collisions:0 txqueuelen:0 > RX bytes:2544659 (2.4 MiB) TX bytes:2544659 (2.4 MiB) > >> note that net mask? the loopback interface is the entire >> 127.0.0.0/8 network you can't put a host at 127.0.1.x and expect it >> to work right. >> >> you should instead use one of the RFC1918 reserved subnets for a >> private network, within 10.0.0.0/8 or 172.16.0.0/12, or >> 192.168.0.0/16 (you can use these with any mask size you want, for >> instance, /24 is usually used with 192.168.x.y) > I can't figure why 127.0.1.1 is there (I will remove it) is an (almost) > new installation on a virtual machine, See the link I sent to John. > > > Edoardo > > > -- Adrian Klaver adrian.klaver@aklaver.com
Il 06/11/14 16:54, Adrian Klaver ha scritto: > On 11/06/2014 07:47 AM, John R Pierce wrote: >> On 11/6/2014 7:36 AM, Edoardo Panfili wrote: >>>> grep localhost /etc/hosts >>> 127.0.0.1 localhost >>> 127.0.1.1 host.host host >> >> wahaaaa? > > https://lists.debian.org/debian-devel/2013/07/msg00809.html > > > > > No dubt regarding the good reasons of the debian (and postgres) developers. All works fine, the problem is that I search the wrong thing in the documentation. "Connecting to a Database" section of http://www.postgresql.org/docs/9.4/static/app-psql.html contains the explanation for my situation. again, thank you Edoardo
Hi John.
On Thu, Nov 6, 2014 at 4:47 PM, John R Pierce <pierce@hogranch.com> wrote:
note that net mask? the loopback interface is the entire 127.0.0.0/8 network you can't put a host at 127.0.1.x and expect it to work right.
You can, and it works, it just connects to yourself. Even without names, I have used connections from 127.100.100.100:10000 to 127.111.111.111:111111 and 127.222.222.222:22222 to 127.233.233.233:23333 when testing some network code, they all bind to loopback and a single packet trace is easier to follow than just changing the ports.
Francisco Olarte.