Thread: pg_hba changes not honored
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
"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
"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.
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
==================================================================
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
==================================================================