Обсуждение: pg_hba changes not honored

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> When I update pg_hba.conf to disallow certain client machines from
> connectiong (update pg_hba.conf, pg_ctl reload), I still see new
> connections appearing in the process list from the clients I want to
> disconnect.

Sounds to me like a mistake in your pg_hba file, ie, there is still
a line that allows them to connect.  We've also heard of people editing
the wrong copy of pg_hba.conf, or sighup'ing the wrong postmaster ...

            regards, tom lane

This is the complete pg_hba contents

local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
#host   all         all         172.31.0.84/24        trust
# IPv6 local connections:
host    all         all         ::1/128               trust

ps ax | grep INSERT  shows a few processes from client machines not in
pg_hba.conf

The processes that do INSERTS keep appearing from other clients not in
pg_hba.conf

There is only 1 postmaster running

The connections that keep appearing are from jdbc clients.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 23, 2006 3:11 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_hba changes not honored

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> When I update pg_hba.conf to disallow certain client machines from
> connectiong (update pg_hba.conf, pg_ctl reload), I still see new
> connections appearing in the process list from the clients I want to
> disconnect.

Sounds to me like a mistake in your pg_hba file, ie, there is still
a line that allows them to connect.  We've also heard of people editing
the wrong copy of pg_hba.conf, or sighup'ing the wrong postmaster ...

            regards, tom lane

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> This is the complete pg_hba contents
> local   all         all                               trust
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          trust
> #host   all         all         172.31.0.84/24        trust
> # IPv6 local connections:
> host    all         all         ::1/128               trust

That file certainly shouldn't let in any nonlocal connections, so
I'm of the opinion you're editing the wrong file :-(.  You might
try strace'ing the postmaster to see what files it re-reads after
being sighup'd.

            regards, tom lane

Tom Lane wrote:
> "Sriram Dandapani" <sdandapani@counterpane.com> writes:
>> When I update pg_hba.conf to disallow certain client machines from
>> connectiong (update pg_hba.conf, pg_ctl reload), I still see new
>> connections appearing in the process list from the clients I want to
>> disconnect.
>
> Sounds to me like a mistake in your pg_hba file, ie, there is still
> a line that allows them to connect.  We've also heard of people editing
> the wrong copy of pg_hba.conf, or sighup'ing the wrong postmaster ...

That is actually quite common, especially on Ubuntu :)

Joshua D. Drake

>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

This problem occurs only when the changes are made while postmaster is
running and pg_ctl is used to reload config files.

When the changes are applied and postmaster is stopped and restarted, it
works fine.

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, August 23, 2006 3:44 PM
To: Tom Lane
Cc: Sriram Dandapani; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_hba changes not honored

Tom Lane wrote:
> "Sriram Dandapani" <sdandapani@counterpane.com> writes:
>> When I update pg_hba.conf to disallow certain client machines from
>> connectiong (update pg_hba.conf, pg_ctl reload), I still see new
>> connections appearing in the process list from the clients I want to
>> disconnect.
>
> Sounds to me like a mistake in your pg_hba file, ie, there is still
> a line that allows them to connect.  We've also heard of people
editing
> the wrong copy of pg_hba.conf, or sighup'ing the wrong postmaster ...

That is actually quite common, especially on Ubuntu :)

Joshua D. Drake

>
>             regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> This problem occurs only when the changes are made while postmaster is
> running and pg_ctl is used to reload config files.
> When the changes are applied and postmaster is stopped and restarted, it
> works fine.

Hm.  OK, that means we need to look closer at how you're using pg_ctl.
Are you sure you're pointing it at the right data directory?  Does it
claim to have successfully signaled the postmaster?  What PG version
is this exactly, and on what platform?

            regards, tom lane

Pg_ctl is pointing to the same directory that postmaster points to on
startup. There is only 1 data directory/postgres installation that I
use.

Pg_ctl informs that postmaster is signaled. When I see the logs for
postmaster, it says "received SIGHUP, reloading configuration files"

Linux 2.4.21-40.ELsmp #1 SMP Thu Feb 2 22:22:39 EST 2006 i686 i686 i386
GNU/Linux

Postgres 8.1.2

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 23, 2006 4:00 PM
To: Sriram Dandapani
Cc: Joshua D. Drake; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_hba changes not honored

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> This problem occurs only when the changes are made while postmaster is
> running and pg_ctl is used to reload config files.
> When the changes are applied and postmaster is stopped and restarted,
it
> works fine.

Hm.  OK, that means we need to look closer at how you're using pg_ctl.
Are you sure you're pointing it at the right data directory?  Does it
claim to have successfully signaled the postmaster?  What PG version
is this exactly, and on what platform?

            regards, tom lane

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> Pg_ctl is pointing to the same directory that postmaster points to on
> startup. There is only 1 data directory/postgres installation that I
> use.

> Pg_ctl informs that postmaster is signaled. When I see the logs for
> postmaster, it says "received SIGHUP, reloading configuration files"

Well, I just re-tested it here, and it works fine for me (using 8.1.4,
but I don't see anything in the CVS logs indicating changes in the
relevant code since 8.1.2).  So I still think there's some sort of
pilot error involved here, but I'm running out of ideas about what.
You might want to try the strace experiment I suggested to confirm
that the postmaster is reading the file you think it is.

            regards, tom lane