pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_hba changes not honored
11+ messages / 4 participants
[nested] [flat]

* pg_hba changes not honored
@ 2006-08-23 21:43  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-08-23 21:43 UTC (permalink / raw)
  To: pgsql-admin

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. Why does this happen. (I noticed this behavior with jdbc
connections in jboss with connection pooling)

 

Once pg_hba is reloaded, shouldn't postmaster refuse to accept
connections from the clients not in pg_hba.conf list.

 

Regards

 

Sriram

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2006-08-23 22:10 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:16  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-08-23 22:16 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-08-23 22:29 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:43  Joshua D. Drake <jd@commandprompt.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Joshua D. Drake @ 2006-08-23 22:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-admin

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/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:50  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-08-23 22:50 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 22:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-08-23 22:59 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 23:08  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-08-23 23:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-23 23:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2006-08-23 23:39 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-27 18:58  Aaron Bono <postgresql@aranya.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Aaron Bono @ 2006-08-27 18:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

On 8/23/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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.



Could it be that the JDBC client creates a connection and keeps that
connection open (common with connection poolilng)?  If you SIGHUP the
postmaster, will it disconnect already connected clients or does it only
reject new connections?

I say this since you said doing a restart fixes the problem.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: pg_hba changes not honored
@ 2006-08-27 19:38  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-08-27 19:38 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin

I suspect the same behavior. Existing jdbc connections are not
disconnected..only new ones

 

________________________________

From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of
Aaron Bono
Sent: Sunday, August 27, 2006 11:58 AM
To: Tom Lane
Cc: Sriram Dandapani; Joshua D. Drake; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_hba changes not honored

 

On 8/23/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

	"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.


 

Could it be that the JDBC client creates a connection and keeps that
connection open (common with connection poolilng)?  If you SIGHUP the
postmaster, will it disconnect already connected clients or does it only
reject new connections? 

I say this since you said doing a restart fixes the problem.


==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== 

^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2006-08-27 19:38 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-23 21:43 pg_hba changes not honored Sriram Dandapani <sdandapani@counterpane.com>
2006-08-23 22:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-23 22:43   ` Joshua D. Drake <jd@commandprompt.com>
2006-08-23 22:16 Re: pg_hba changes not honored Sriram Dandapani <sdandapani@counterpane.com>
2006-08-23 22:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-23 22:50 Re: pg_hba changes not honored Sriram Dandapani <sdandapani@counterpane.com>
2006-08-23 22:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-23 23:08 Re: pg_hba changes not honored Sriram Dandapani <sdandapani@counterpane.com>
2006-08-23 23:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-27 18:58   ` Aaron Bono <postgresql@aranya.com>
2006-08-27 19:38 Re: pg_hba changes not honored Sriram Dandapani <sdandapani@counterpane.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox