psql connection via localhost or 127.0.0.1

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.