BUG #10140: Configured for 127.0.0.1 but binds to all IP

The following bug has been logged on the website:

Bug reference: 10140
Logged by: Dmitro
Email address: sofiamay(at)mail(dot)ru
PostgreSQL version: 9.3.4
Operating system: Windows 7 Pro x64
Description:

All versions 9.x.x Postgresql have a BUG. Its configured for 127.0.0.1 but
binds to all IP (0.0.0.0)!

In postgresql.conf:

listen_addresses = '127.0.0.1'

but in reality:

postgres.exe TCP 0.0.0.0 5432 and
postgres.exe TCP :: 5432

What's the trouble? Please fix this bug.
Sorry for my english.

Greetings,

* sofiamay(at)mail(dot)ru (sofiamay(at)mail(dot)ru) wrote:
> All versions 9.x.x Postgresql have a BUG. Its configured for 127.0.0.1 but
> binds to all IP (0.0.0.0)!
>
> In postgresql.conf:
>
> listen_addresses = '127.0.0.1'
>
> but in reality:
>
> postgres.exe TCP 0.0.0.0 5432 and
> postgres.exe TCP :: 5432
>
> What's the trouble? Please fix this bug.
> Sorry for my english.

Are you sure that you completely restarted PostgreSQL after making the
change to postgresql.conf? What happens if you, in psql, run:

show listen_addresses;

?

Thanks,

Stephen

sofiamay(at)mail(dot)ru writes:
> All versions 9.x.x Postgresql have a BUG. Its configured for 127.0.0.1 but
> binds to all IP (0.0.0.0)!

What's your evidence for this statement?

> but in reality:
> postgres.exe TCP 0.0.0.0 5432 and
> postgres.exe TCP :: 5432

I don't know what tool you're using here, but I wonder if you aren't
misinterpreting its output. For comparison's sake, when using Linux's
"netstat -l -n", I see this when PG's listen_addresses is '*':

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
...
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
...

while when listen_addresses is 'localhost' the line is:

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN

The all-zeroes foreign address does *not* mean that the socket is
accessible from everywhere, it just reflects an absence of data
for that column. The local address column is what's important
for a listening socket --- at least with netstat.

regards, tom lane

My system is WINDOWS 7 ! Win7 pro x64. NOT LINUX.
All versions 9.x.x Postgresql have a BUG. Its configured for 127.0.0.1 but binds to all IP (0.0.0.0)!

In Console:
**********************************
psql -U postgres
show listen_addresses;

output:
listen_addresses
------------------
*
(1 User)
**********************************

But show in config file: C:\Postgre\postgresql.conf

In Console:
**********************************
sort<C:\Postgre\postgresql.conf
lalala....
listen_addresses = '127.0.0.1' # what IP address(es) to listen on;
lalala....
**********************************

then

In Console:
**********************************
netstat -a

TCP 0.0.0.0:135 UserPC:0 LISTENING
TCP 0.0.0.0:445 UserPC:0 LISTENING
TCP 0.0.0.0:1025 UserPC:0 LISTENING
TCP 0.0.0.0:1026 UserPC:0 LISTENING
TCP 0.0.0.0:1027 UserPC:0 LISTENING
TCP 0.0.0.0:1028 UserPC:0 LISTENING
TCP 0.0.0.0:1029 UserPC:0 LISTENING
TCP 0.0.0.0:1030 UserPC:0 LISTENING
TCP 0.0.0.0:5432 UserPC:0 LISTENING
TCP [::]:135 UserPC:0 LISTENING
TCP [::]:445 UserPC:0 LISTENING
TCP [::]:1025 UserPC:0 LISTENING
TCP [::]:1026 UserPC:0 LISTENING
TCP [::]:1027 UserPC:0 LISTENING
TCP [::]:1028 UserPC:0 LISTENING
TCP [::]:1029 UserPC:0 LISTENING
TCP [::]:1030 UserPC:0 LISTENING
TCP [::]:5432 UserPC:0 LISTENING
**********************************

Process start command:

C:\database\PostgreSQL-9.3.4\bin\pg_ctl.exe start -w -D "C:\Postgre" -s -o "-i"

(server in C:\database\PostgreSQL-9.3.4, data in C:\Postgre)

Config file is work, other parametres work fine, but not work this parameter: listen_addresses = '127.0.0.1'

What's the trouble? Please fix this bug.
Sorry for my english.

=?UTF-8?B?c2RmYXNkZiBhc2RmYXNkZg==?= <sofiamay(at)mail(dot)ru> writes:
> In Console:
> **********************************
> psql -U postgres
> show listen_addresses;

> output:
> listen_addresses
> ------------------
> *
> (1 User)
> **********************************

Well, if it's saying that, then it's not a bug that it is in fact
binding to all addresses.

I suspect Stephen was right that you forgot to restart the postmaster
after changing the configuration file. (pg_ctl reload is *not* enough for
changing this parameter.)

Another theory is that the file you edited isn't the one the postmaster
is looking at. ("show config_file" would help diagnose this.)

Another theory is that the config file setting is being overridden by
some higher-priority setting, such as "-i" on the postmaster command
line. (Looking at the source for listen_addresses in
"select * from pg_settings" would help diagnose this case.)

regards, tom lane

I am not stupid. I start and stop postresql manually, not as sevice.

In Console:
**********************************
psql -U postgres
show config_file;

output:
C:/Postgre/postgresql.conf
**********************************
in file: listen_addresses = '127.0.0.1'

File works fine, but PostreSQL binds to all IP.
Parameter [i] in pg_ctl cmd line is i[mmediate] and does not affect for IP adress.

It is a bug.

Fri, 25 Apr 2014 16:10:10 -0400 от Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> =?UTF-8?B?c2RmYXNkZiBhc2RmYXNkZg==?= <sofiamay(at)mail(dot)ru> writes:
> > In Console:
> > **********************************
> > psql -U postgres
> > show listen_addresses;
>
> > output:
> > listen_addresses
> > ------------------
> > *
> > (1 User)
> > **********************************
>
> Well, if it's saying that, then it's not a bug that it is in fact
> binding to all addresses.
>
> I suspect Stephen was right that you forgot to restart the postmaster
> after changing the configuration file. (pg_ctl reload is *not* enough for
> changing this parameter.)
>
> Another theory is that the file you edited isn't the one the postmaster
> is looking at. ("show config_file" would help diagnose this.)
>
> Another theory is that the config file setting is being overridden by
> some higher-priority setting, such as "-i" on the postmaster command
> line. (Looking at the source for listen_addresses in
> "select * from pg_settings" would help diagnose this case.)
>
> regards, tom lane
>

sofiamay wrote
> File works fine, but PostreSQL binds to all IP.
> Parameter [i] in pg_ctl cmd line is i[mmediate] and does not affect for IP
> adress.
>
> It is a bug.

Cannot confirm or deny but have you actually tested this on 9.0; 9.1; 9.2
and 9.3 to confirm its a bug on all of them? Let's ignore whether it is a
possible bug on 8.4

Please forgive the somewhat credulous response; the fact that this has taken
4+ years to apparently surface and the fact that Windows is a less used
platform (compared to Linux) for the people who monitor the -bugs list means
that the natural response is to confirm that user's setup and not assume the
code is wrong.

Also, did you build from source or did you use an installer?

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-10140-Configured-for-127-0-0-1-but-binds-to-all-IP-tp5801540p5801584.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

IT IS A SECURITY (NETWORK) BUG (bind to all ip instead of 127.0.0.1).

1) I download binare form http://www.enterprisedb.com/products-services-training/pgbindownload

Versions wich i download: 9.3.4 and 9.2.8 (Win x86-32)

2) In config file i wrote:

listen_addresses = '127.0.0.1'

3) Then i do init db and run this command:

C:\database\PostgreSQL-9.3.4\bin\pg_ctl.exe start -w -D "C:\Postgre" -s -o "-i"

PostgreSQL start and work fine, but binds to all ips.

My system is WINDOWS 7. Win7 pro x64. NOT LINUX.

In Console:
**********************************
psql -U postgres
show listen_addresses;

output:
listen_addresses
------------------
*
(1 User)
**********************************

But show in config file: C:\Postgre\postgresql.conf

In Console:
**********************************
sort<C:\Postgre\postgresql.conf
lalala....
listen_addresses = '127.0.0.1' # what IP address(es) to listen on;
lalala....
**********************************

then

In Console:
**********************************
netstat -a

TCP 0.0.0.0:5432 UserPC:0 LISTENING
TCP [::]:5432 UserPC:0 LISTENING
**********************************

I test this bug with 9.3 and 9.2 (Win x86-32) versions of PostgreSQL. Another version not tested (i have not time).
You can check it yourself.

Sorry for my english.

On Sat, Apr 26, 2014 at 8:50 AM, sdfasdf asdfasdf <sofiamay(at)mail(dot)ru> wrote:
> IT IS A SECURITY (NETWORK) BUG (bind to all ip instead of 127.0.0.1).
>
> 1) I download binare form http://www.enterprisedb.com/products-services-training/pgbindownload
>
> Versions wich i download: 9.3.4 and 9.2.8 (Win x86-32)
>
> 2) In config file i wrote:
>
> listen_addresses = '127.0.0.1'
>
> 3) Then i do init db and run this command:
>
> C:\database\PostgreSQL-9.3.4\bin\pg_ctl.exe start -w -D "C:\Postgre" -s -o "-i"
>
> PostgreSQL start and work fine, but binds to all ips.
>
> My system is WINDOWS 7. Win7 pro x64. NOT LINUX.
>
> In Console:
> **********************************
> psql -U postgres
> show listen_addresses;
>
> output:
> listen_addresses
> ------------------
> *
> (1 User)
> **********************************
>
> But show in config file: C:\Postgre\postgresql.conf
>
> In Console:
> **********************************
> sort<C:\Postgre\postgresql.conf
> lalala....
> listen_addresses = '127.0.0.1' # what IP address(es) to listen on;
> lalala....
> **********************************
>
> then
>
> In Console:
> **********************************
> netstat -a
>
> TCP 0.0.0.0:5432 UserPC:0 LISTENING
> TCP [::]:5432 UserPC:0 LISTENING
> **********************************
>
> I test this bug with 9.3 and 9.2 (Win x86-32) versions of PostgreSQL. Another version not tested (i have not time).
> You can check it yourself.

I checked it myself, on Windows 7 x86_64, with 9.3 (64 bit) using
127.0.0.1 and 9.2 (32 bit) using localhost. In both cases:

- Show listen_addresses returns the expected value.

- If I try to connect using my non-loopback addresses, the connections fail.

- Netstat shows that the servers are only listening on 127.0.0.1
(ports 5433 and 5436 in this case):

C:\Users\dpage\Documents>netstat -an

Active Connections

Proto Local Address Foreign Address State
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:2103 0.0.0.0:0 LISTENING
TCP 0.0.0.0:2105 0.0.0.0:0 LISTENING
TCP 0.0.0.0:2107 0.0.0.0:0 LISTENING
TCP 0.0.0.0:5357 0.0.0.0:0 LISTENING
TCP 0.0.0.0:8192 0.0.0.0:0 LISTENING
TCP 0.0.0.0:8193 0.0.0.0:0 LISTENING
TCP 0.0.0.0:8194 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49152 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49153 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49154 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49155 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49156 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49185 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49241 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49242 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49252 0.0.0.0:0 LISTENING
TCP 0.0.0.0:49253 0.0.0.0:0 LISTENING
TCP 0.0.0.0:51234 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5433 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5436 0.0.0.0:0 LISTENING
...
...

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

=?UTF-8?B?c2RmYXNkZiBhc2RmYXNkZg==?= <sofiamay(at)mail(dot)ru> writes:
> IT IS A SECURITY (NETWORK) BUG (bind to all ip instead of 127.0.0.1).

No, it's pilot error.

> 2) In config file i wrote:
> listen_addresses = '127.0.0.1'
> 3) Then i do init db and run this command:

If you did things in that order, it would not have worked anyway, because
the config file that the postmaster will use doesn't exist until initdb
creates it. However, the real problem appears to be here:

> C:\database\PostgreSQL-9.3.4\bin\pg_ctl.exe start -w -D "C:\Postgre" -s -o "-i"

The -o "-i" part is passing a -i command line switch to the postmaster,
and that sets listen_addresses to "*", overriding whatever might be in
the config file. If you'd looked at pg_settings as I advised yesterday,
you'd have seen the evidence of this immediately.

regards, tom lane

Thanks for the answer. I understand you. It's my fault. The problem with option -o "-i", which switches PostgreSQL to all IP addresses "*".
I'm very sorry. Thanks again for your patience and answers.