Обсуждение: Postgres 9.6.1 accepts connections from not allowed Ip addresses

Hi, I wanted to report a bug that I found while using postgres 9.6.1.

Summary: The database accepts connections from addresses that were previous=
ly allowed but later on have been excluded in the  postgresql.conf & pg_hba=
.conf files.

Step by step

1.       Set up an environment where the postgresql.conf file is set to "li=
sten_addresses =3D '*'" and pg_hba.conf includes the following line "host  =
  all             all             0.0.0.0/0            md5" (This is just a=
n example, the issue can be reproduced with a range of other ip addresses, =
like your local host).

2.       Connect your application to the database and make sure everything =
is up and running.

3.       Exclude the ip-address that is connected to the database from pg_h=
ba.conf or listen_addresses. Eg if your application users ip address 192.16=
8.1.1, comment (add a # or remove the entire row) the row you added in step=
 1 in the pg_hba.conf file and/or change listen_addresses to 'localhost'. R=
estart the service postgresql-x64-9.6 to be sure that the new settings are =
active.

4.       The application is still connected to the database even though the=
 access should be denied. As a matter of fact you are still able to write d=
ata to the database. Only when the remote application shuts its connection =
down and re-connects it is rejected by postgres.

This issue cannot be reproduced on postgres 9.3
OS: Windows 10

Best Regards Jan Ogrodowczyk, if you have any questions do not hesitate to =
ask.

Jan Ogrodowczyk <Jan.Ogrodowczyk@qlik.com> writes:
> Hi, I wanted to report a bug that I found while using postgres 9.6.1.
> Summary: The database accepts connections from addresses that were previ=
ously allowed but later on have been excluded in the  postgresql.conf & pg=
_hba.conf files.

It seems highly likely to me that this report is pilot error.
The simplest explanation is that you edited the wrong config files.
You could try "show config_file" and "show hba_file" while connected
to the running server to see where it's getting its configuration from.
Also, when changing listen_addresses, you could use "show
listen_addresses" to verify that your changes took effect.  (Unfortunately
there's not currently any direct way to see the active pg_hba entries,
though we're working on that.)

A different line of thought is that maybe the connections are not coming
from where you think they are; for example ipv4 vs ipv6 addresses.  You
could check into that by activating log_connections and seeing what gets
logged about the unwanted connections.

Also, don't forget that pg_hba lines are order-sensitive --- you need to
put "reject" entries before anything else that could possibly match the
incoming connection.

            regards, tom lane

On Thu, Nov 17, 2016 at 10:58 AM, Jan Ogrodowczyk
<Jan.Ogrodowczyk@qlik.com> wrote:
> 4.       The application is still connected to the database even though the
> access should be denied. As a matter of fact you are still able to write
> data to the database. Only when the remote application shuts its connection
> down and re-connects it is rejected by postgres.

This part makes me think the server was not in fact restarted at all.
Perhaps you just reloaded the config files which would not actually
disconnect already connected clients?

--
greg