CVSROOT:	/cvsroot
Module name:	pgsql
Changes by:	tgl@postgresql.org	01/11/02 13:39:57

Modified files:
	doc/src/sgml   : client-auth.sgml runtime.sgml 
	src/backend/commands: user.c 
	src/backend/libpq: crypt.c 
	src/backend/postmaster: postmaster.c 
	src/include/libpq: crypt.h 

Log message:
	Fix pg_pwd caching mechanism, which was broken by changes to fork
	postmaster children before client auth step.  Postmaster now rereads
	pg_pwd on receipt of SIGHUP, the same way that pg_hba.conf is handled.
	No cycles need be expended to validate password cache validity during
	connection startup.


Fri, 2 Nov 2001 13:39:57 -0500 (EST)
tgl@postgresql.org
pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ...
> CVSROOT:	/cvsroot
> Module name:	pgsql
> Changes by:	tgl@postgresql.org	01/11/02 13:39:57
> 
> Modified files:
> 	doc/src/sgml   : client-auth.sgml runtime.sgml 
> 	src/backend/commands: user.c 
> 	src/backend/libpq: crypt.c 
> 	src/backend/postmaster: postmaster.c 
> 	src/include/libpq: crypt.h 
> 
> Log message:
> 	Fix pg_pwd caching mechanism, which was broken by changes to fork
> 	postmaster children before client auth step.  Postmaster now rereads
> 	pg_pwd on receipt of SIGHUP, the same way that pg_hba.conf is handled.
> 	No cycles need be expended to validate password cache validity during
> 	connection startup.

Tom, does a client do a kill() to its parent on password change?

If this is true, people can't depend on editing pg_hba.conf and having
the change take affect _only_ when they sighup the postmaster.  If
someone changes a password, pg_hba.conf gets reread anyway, right?  Not
a problem but something we should be aware of.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 2 Nov 2001 17:30:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ...
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Fix pg_pwd caching mechanism, which was broken by changes to fork
>> postmaster children before client auth step.  Postmaster now rereads
>> pg_pwd on receipt of SIGHUP, the same way that pg_hba.conf is handled.

> Tom, does a client do a kill() to its parent on password change?

Right, it's basically the same as the way we handle checkpoint and
SI-overrun signaling:

	/*
	 * Signal the postmaster to reload its password-file cache.
	 */
	if (IsUnderPostmaster)
		kill(getppid(), SIGHUP);

> If this is true, people can't depend on editing pg_hba.conf and having
> the change take affect _only_ when they sighup the postmaster.

True.  But recall that in all previous releases it's been completely
unsafe to edit pg_hba.conf in place, so I don't regard this as a big
step backwards.

We could possibly set up the password-file-reload action to occur on
some other, presently unused signal.  But there aren't a lot of spare
signal numbers left, and I'm not eager to use one up for this...

			regards, tom lane

Fri, 02 Nov 2001 17:56:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ... 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Fix pg_pwd caching mechanism, which was broken by changes to fork
> >> postmaster children before client auth step.  Postmaster now rereads
> >> pg_pwd on receipt of SIGHUP, the same way that pg_hba.conf is handled.
> 
> > Tom, does a client do a kill() to its parent on password change?
> 
> Right, it's basically the same as the way we handle checkpoint and
> SI-overrun signaling:
> 
> 	/*
> 	 * Signal the postmaster to reload its password-file cache.
> 	 */
> 	if (IsUnderPostmaster)
> 		kill(getppid(), SIGHUP);
> 
> > If this is true, people can't depend on editing pg_hba.conf and having
> > the change take affect _only_ when they sighup the postmaster.
> 
> True.  But recall that in all previous releases it's been completely
> unsafe to edit pg_hba.conf in place, so I don't regard this as a big
> step backwards.
> 
> We could possibly set up the password-file-reload action to occur on
> some other, presently unused signal.  But there aren't a lot of spare
> signal numbers left, and I'm not eager to use one up for this...

I think your solution is fine.  I just wanted to make it clear so we
don't encourage people to edit those files and wait around thinking they
can control when the reload happens.  I will check the docs to make sure
I didn't add any suggestion of that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 2 Nov 2001 18:16:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ...
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think your solution is fine.  I just wanted to make it clear so we
> don't encourage people to edit those files and wait around thinking they
> can control when the reload happens.  I will check the docs to make sure
> I didn't add any suggestion of that.

I already looked and didn't see anyplace claiming it was safe to do
that.  However, there's also not any explicit statement pointing out
that it's not safe.

			regards, tom lane

Fri, 02 Nov 2001 18:19:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ... 
Hi,

I am looking at changing the jdbc driver to be asynchronous, in other
words retrieve the result set when asked as opposed to retrieving it all
before returning.

This will involve understanding the client/backend protocol completely.
So far I have found one reference to this. Does anyone have a flow chart
or can point me to any other references.

Dave


Sat, 3 Nov 2001 11:22:31 -0500
"Dave Cramer" <dave@fastcrypt.com>
Backend Protocol
"Dave Cramer" <dave@fastcrypt.com> writes:
> This will involve understanding the client/backend protocol completely.
> So far I have found one reference to this. Does anyone have a flow chart
> or can point me to any other references.

The only documentation I know of is the protocol chapter in the PG
Developer's Guide:
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/protocol.html

The development-sources version of this has additional entries for the
new authentication methods added in 7.2.  It's still missing any
discussion of SSL encryption :-(

It would probably be useful to add something flowchart-ish to section
4.2.2 to show the typical sequence of messages for various commands.
However, I'd caution you against wiring in more assumptions than you
absolutely must about message order.  It's best to build the client
library as a state machine that will accept any message type at any
time that the message makes any sense.

			regards, tom lane

Sat, 03 Nov 2001 13:02:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Backend Protocol 
Tom Lane writes:

> > Tom, does a client do a kill() to its parent on password change?
>
> Right, it's basically the same as the way we handle checkpoint and
> SI-overrun signaling:
>
> 	/*
> 	 * Signal the postmaster to reload its password-file cache.
> 	 */
> 	if (IsUnderPostmaster)
> 		kill(getppid(), SIGHUP);

But does this mean that postgresql.conf will be reread globally (i.e., by
the postmaster), when the user signals HUP only to a single backend?  I
guess this is actually a good idea, but it would be a change of
functionality.  Admittedly, the window of utility of signalling only a
single backend is small, but let's make sure the hupping behavior is
correctly documented, because this is possibly not expected.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sun, 4 Nov 2001 14:05:24 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml
Peter Eisentraut <peter_e@gmx.net> writes:
> But does this mean that postgresql.conf will be reread globally (i.e., by
> the postmaster), when the user signals HUP only to a single backend?

No.  What it does mean is that ADD/DROP/ALTER USER will cause a global
reread of the conf files.  That is kinda annoying, I agree.

We could avoid this by using a different signal number, but there's a
shortage of available signals.  I was toying with the notion of unifying
all three of the existing reasons for signalling the postmaster
(SIGUSR1, SIGUSR2, SIGHUP) into a single child-to-parent signal number,
say SIGUSR1.  A flag array in shared memory could be used to indicate
what the reason(s) are for the most recent signal.  This would actually
free up one signal number, which seems like a good idea in the long run.
Comments?

			regards, tom lane

Sun, 04 Nov 2001 11:13:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ... 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > But does this mean that postgresql.conf will be reread globally (i.e., by
> > the postmaster), when the user signals HUP only to a single backend?
> 
> No.  What it does mean is that ADD/DROP/ALTER USER will cause a global
> reread of the conf files.  That is kinda annoying, I agree.
> 
> We could avoid this by using a different signal number, but there's a
> shortage of available signals.  I was toying with the notion of unifying
> all three of the existing reasons for signalling the postmaster
> (SIGUSR1, SIGUSR2, SIGHUP) into a single child-to-parent signal number,
> say SIGUSR1.  A flag array in shared memory could be used to indicate
> what the reason(s) are for the most recent signal.  This would actually
> free up one signal number, which seems like a good idea in the long run.
> Comments?

While is not ideal, I am not too concerned that USER commands will
reread all config files.  Maybe we should wait to see if anyone reports
a problem with this behavior before adding code to correct it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 4 Nov 2001 13:00:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> While is not ideal, I am not too concerned that USER commands will
> reread all config files.  Maybe we should wait to see if anyone reports
> a problem with this behavior before adding code to correct it.

But Peter's already complaining ;-) ... and you were concerned about it
too, yesterday.

I went ahead and made the changes, because I think we'd have been forced
into it sooner or later anyway.  We didn't have any spare signal numbers
in the postmaster, so the next new reason for child processes to signal
the postmaster would've forced creation of this mechanism anyhow.

			regards, tom lane

Sun, 04 Nov 2001 15:15:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm ... 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > While is not ideal, I am not too concerned that USER commands will
> > reread all config files.  Maybe we should wait to see if anyone reports
> > a problem with this behavior before adding code to correct it.
> 
> But Peter's already complaining ;-) ... and you were concerned about it
> too, yesterday.

Oh, I didn't know he was complaining.  I was asking only so I understood
the behavior and could help people if it surprised them.

> I went ahead and made the changes, because I think we'd have been forced
> into it sooner or later anyway.  We didn't have any spare signal numbers
> in the postmaster, so the next new reason for child processes to signal
> the postmaster would've forced creation of this mechanism anyhow.

Very true.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 4 Nov 2001 15:19:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [COMMITTERS] pgsql/ oc/src/sgml/client-auth.sgml oc/src/sgm
Dave,

This is documented in the Developer's Guide (Chapter 4 Frontend/Backend 
Protocol of the 7.1 docs).

A few days ago I added this same item the the jdbc todo list.  I don't 
think the implementation will need to do anything different at the FE/BE 
protocol level.  Instead my thoughts on this are to wrap all jdbc 
Statements/Prepared Statements doing selects in a sql cursor, and then 
issuing fetch statements to set sets of rows instead of the entire 
result set.

Thus 'select foo from bar'

Whould get executed by the driver as:

declare barcursor cursor as select foo from bar;

followed by:

fetch forward 10 from barcursor;

(to select the first 10 rows)

additional fetches as needed...

finally:

close barcursor;

Just when this should be done (i.e. you probably don't want to overhead 
of using a cursor for every select statement), how many rows to fetch at 
a time (perhaps you might want to fetch 100 rows per call to minimize 
network roundtrips) would need to be configurable somehow.

Also not the the jdbc2 spec has methods for setCursorName(), etc. and 
just how they would play into this I don't know.

thanks,
--Barry



Dave Cramer wrote:

> Hi,
> 
> I am looking at changing the jdbc driver to be asynchronous, in other
> words retrieve the result set when asked as opposed to retrieving it all
> before returning.
> 
> This will involve understanding the client/backend protocol completely.
> So far I have found one reference to this. Does anyone have a flow chart
> or can point me to any other references.
> 
> Dave
> 
> 



Mon, 05 Nov 2001 19:11:38 -0800
Barry Lind <barry@xythos.com>
Re: Backend Protocol