BUG #16531: listen_addresses wide open?

PG Bug reporting form <noreply@postgresql.org> writes:

I only had this in postgresql.conf:
listen_adresses = '192.168.1.50'

Just to be sure, "show listen_addresses" actually shows that,
and not something else?

The machine was properly functioning on 'localhost' in an application I had
on the same machine.

When I set the server's listen_addresses to just the machine's TCP
address, trying to connect to "localhost" gives me what I'd expect:

$ psql -h localhost -l
psql: error: could not connect to server: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

That's entirely independent of what's in pg_hba.conf: there's simply
no open socket on localhost.

So basically, it works for me, and you haven't shown what sort of
configuration problem is making it not work for you.

It could be that there's something odd about the way "localhost"
resolves on your machine; you could check "dig localhost." to be
sure. But probably a more likely theory is that you didn't make
the listen_addresses setting take effect (you need to restart the
postmaster to change that). Or possibly you've got more than one
postmaster active on the machine, and the other one is answering
localhost?

regards, tom lane

"Bee.Lists" <bee.lists@gmail.com> writes:

BTW I’m not part of the bugs mailing list. Didn’t know it would be posted there.

Please keep the list cc'd, anyway, for the archives' sake.
(For the record, that website form just posts to the bugs list.)

On Jul 8, 2020, at 12:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Just to be sure, "show listen_addresses" actually shows that,
and not something else?

I didn’t check, but then changed it to ‘*’ since troubleshooting these connection issues I’m having.

Well, my point here is that you didn't close the loop to establish that
what you thought you'd set listen_addresses to had actually taken effect.
The default value is 'localhost', and there are several ways that that
might still be the active value even if you'd edited the config file to
say something else.

The issue was that it WAS connecting when set to the LAN IP4 address only.

That's pretty hard to believe; I think a configuration oversight is
a much more likely explanation.

regards, tom lane

On Jul 8, 2020, at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Jul 8, 2020, at 12:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Just to be sure, "show listen_addresses" actually shows that,
and not something else?

I didn’t check, but then changed it to ‘*’ since troubleshooting these connection issues I’m having.

Well, my point here is that you didn't close the loop to establish that
what you thought you'd set listen_addresses to had actually taken effect.
The default value is 'localhost', and there are several ways that that
might still be the active value even if you'd edited the config file to
say something else.

OK, then why even have a config file if it’s ignored, or possibly not ignored? Isn’t that the point of a config file?

The issue was that it WAS connecting when set to the LAN IP4 address only.

That's pretty hard to believe; I think a configuration oversight is
a much more likely explanation.

regards, tom lane

Well, all of a sudden, my app was complaining that Pogtgres wasn’t accepting on that port, when the app had been using just that. Nothing changed and it suddenly went deaf. People suggested looking at the listen_addresses directive.

Cheers, Bee

On Wed, Jul 8, 2020 at 3:03 PM Bee.Lists <bee.lists@gmail.com> wrote:

On Jul 8, 2020, at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

On Jul 8, 2020, at 12:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Just to be sure, "show listen_addresses" actually shows that,
and not something else?

I didn’t check, but then changed it to ‘*’ since troubleshooting these

connection issues I’m having.

Well, my point here is that you didn't close the loop to establish that
what you thought you'd set listen_addresses to had actually taken effect.
The default value is 'localhost', and there are several ways that that
might still be the active value even if you'd edited the config file to
say something else.

OK, then why even have a config file if it’s ignored, or possibly not
ignored? Isn’t that the point of a config file?

Consider that usually software will read its configuration files only when
the software is launched. PostgreSQL, being a long-running service,
provides some facilities to change configuration while it is still
running. However, that capability comes with rules and limitations. The
setting you are talking about it one of those with a limitation.

The issue was that it WAS connecting when set to the LAN IP4 address

only.

That's pretty hard to believe; I think a configuration oversight is
a much more likely explanation.

Well, all of a sudden, my app was complaining that Pogtgres wasn’t
accepting on that port, when the app had been using just that. Nothing
changed and it suddenly went deaf. People suggested looking at the
listen_addresses directive.

Given how vague that all is it's still going to be supposed that there is
operator error involved relative to the listen_address configuration rather
than the server being bugged and ignoring it. Especially since your
observed problem is that the application couldn't connect to the server but
your problem report is that the application could connect even though it
probably shouldn't have.

In short, this doesn't look like a bug and if you are having application
connectivity issues help for those is preferably provided on the -general
list, not -bugs.

David J.

"Bee.Lists" <bee.lists@gmail.com> writes:

On Jul 8, 2020, at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, my point here is that you didn't close the loop to establish that
what you thought you'd set listen_addresses to had actually taken effect.
The default value is 'localhost', and there are several ways that that
might still be the active value even if you'd edited the config file to
say something else.

OK, then why even have a config file if it’s ignored, or possibly not ignored? Isn’t that the point of a config file?

You might have forgotten to restart (not just reload) the service after
editing. There'd have been a postmaster log message telling you that the
new value wasn't applied yet, but you wouldn't know it if you didn't think
to check the log file.

Or you might've forgotten to uncomment that config file line, or edited
the wrong copy of the config file (we've all been there). Or there might
have been another entry in the config file (or the pg.auto.conf file)
overriding the one you changed.

And there's still the possibility of multiple postmasters on the machine.
Or other mistakes I'm not thinking of at the moment.

Checking the "show" result would have been a handy way to start narrowing
down the possibilities.

Anyway, while it remains possible that you saw a Postgres or kernel bug,
I think pilot error is a far more likely explanation --- especially since
I reproduced what you said you did on a similar platform (RHEL8) and did
not see any such problem.

Well, all of a sudden, my app was complaining that Pogtgres wasn’t accepting on that port, when the app had been using just that. Nothing changed and it suddenly went deaf. People suggested looking at the listen_addresses directive.

Well, that does NOT square with how you've been describing the problem.
You've been claiming that applications are successfully connecting when
they shouldn't, which seems the exact opposite of this.

Also, it's difficult to make much headway with a report that "nothing
changed" but things stopped working. Evidently *something* changed.

regards, tom lane

On Jul 8, 2020, at 6:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

You might have forgotten to restart (not just reload) the service after
editing. There'd have been a postmaster log message telling you that the
new value wasn't applied yet, but you wouldn't know it if you didn't think
to check the log file.

Over the last year I’ve restarted Postgres as well as the machine, both several times.

Or you might've forgotten to uncomment that config file line, or edited
the wrong copy of the config file (we've all been there). Or there might
have been another entry in the config file (or the pg.auto.conf file)
overriding the one you changed.

I checked ‘show all;’ in psql after I edited it. So the file was the correct one.

And there's still the possibility of multiple postmasters on the machine.
Or other mistakes I'm not thinking of at the moment.

Checking the "show" result would have been a handy way to start narrowing
down the possibilities.

Anyway, while it remains possible that you saw a Postgres or kernel bug,
I think pilot error is a far more likely explanation --- especially since
I reproduced what you said you did on a similar platform (RHEL8) and did
not see any such problem.

Well, all of a sudden, my app was complaining that Pogtgres wasn’t accepting on that port, when the app had been using just that. Nothing changed and it suddenly went deaf. People suggested looking at the listen_addresses directive.

Well, that does NOT square with how you've been describing the problem.
You've been claiming that applications are successfully connecting when
they shouldn't, which seems the exact opposite of this.

That above, is what got me investigating. The recommendation lead me to that directive where the application should have NEVER had any authentication.

Also, it's difficult to make much headway with a report that "nothing
changed" but things stopped working. Evidently *something* changed.

I didn’t change anything. I didn’t edit anything. Nobody else has access to this server. It stopped working, which appears the way it should have behaved in the first place, according to the pg_hba.conf and postgresql.conf. So I thought I would inform the right people about a possible issue.

Cheers, Bee

On Wednesday, July 8, 2020, Bee.Lists <bee.lists@gmail.com> wrote:

Also, it's difficult to make much headway with a report that "nothing
changed" but things stopped working. Evidently *something* changed.

I didn’t change anything. I didn’t edit anything. Nobody else has access
to this server. It stopped working, which appears the way it should have
behaved in the first place, according to the pg_hba.conf and
postgresql.conf. So I thought I would inform the right people about a
possible issue.

The intent is good, but as Tom’s initial response said the situation
presented could not be duplicated. Lacking a functioning demonstration of
the wrong behavior there isn’t much else to do. If someone wants to read
this and try to replicate the described problem, good for them. That is
beyond what I’d expect here given that this is a first report for the issue
and light on details at that (like how exactly does your application
connect).

David J.

On Jul 8, 2020, at 7:44 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

The intent is good, but as Tom’s initial response said the situation presented could not be duplicated. Lacking a functioning demonstration of the wrong behavior there isn’t much else to do. If someone wants to read this and try to replicate the described problem, good for them. That is beyond what I’d expect here given that this is a first report for the issue and light on details at that (like how exactly does your application connect).

David J.

OK that’s fine. Like I said, just thought I’d let someone know.

Cheers, Bee