Will it be possible to tell a PostgreSQL back end to use *only* the new
version of the FE/BE protocol?

That is, will it be possible to set up a database that will reject
connection attempts that do not use the new protocol version?

Thanks,
	Bruce





29 Aug 2003 10:40:24 +1000
Bruce Badger <bruce_badger@badgerse.com>
FE/BE Protocol - Specific version
Bruce Badger <bruce_badger@badgerse.com> writes:
> Will it be possible to tell a PostgreSQL back end to use *only* the new
> version of the FE/BE protocol?
> That is, will it be possible to set up a database that will reject
> connection attempts that do not use the new protocol version?

I cannot imagine a reason that that would be a good idea ... but
convince me if you can ...

			regards, tom lane

Thu, 28 Aug 2003 23:23:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FE/BE Protocol - Specific version 
On Fri, 2003-08-29 at 13:23, Tom Lane wrote:
> Bruce Badger <bruce_badger@badgerse.com> writes:
> > Will it be possible to tell a PostgreSQL back end to use *only* the new
> > version of the FE/BE protocol?
> > That is, will it be possible to set up a database that will reject
> > connection attempts that do not use the new protocol version?
> 
> I cannot imagine a reason that that would be a good idea ... but
> convince me if you can ...

Well, I'm asking because of a specific application.

StORE is a version control system for VisualWorks Smalltalk.  PostgreSQL
is often the chosen back end for StORE.  There are many publicly
accessible StORE repositories running on PostgreSQL.

The StORE to PostgreSQL mapping code currently encodes Byte arrays using
Base64, and stores them in bytea fields.  It should not have happened
like this, but it did - my fault, a stop-gap got used before the gap was
properly filled.

I would like to migrate to using escaped byte arrays in a new version of
the StORE->PostgreSQL mapping software, but we have to be able to
accommodate the mixture of old and new databases, and old and new client
libraries.

I would prefer to have each StORE database have only one encoding used
throughout.  So "old" databases would still use Base64, and "new"
databases would use escaped strings.  The new databases will contain an
additional table indicating the version of the StORE mapping library
they were created by, and this will enable newer implementations to use
the correct encoding in any given situation.  The old mapping library,
of course, will just keep on trying to use the old Base64 encoding.

As the new FE/BE protocol is not supported by the "old" mapping library,
we could stop old versions of the library getting confused (and perhaps
corrupting) "new" databases by setting new databases to refuse
connections using old FE/BE protocol version.

So, being able to stop connections trying to use old protocol versions
would be very helpful in this case.


29 Aug 2003 14:18:40 +1000
Bruce Badger <bruce_badger@badgerse.com>
Re: FE/BE Protocol - Specific version
> So, being able to stop connections trying to use old protocol versions
> would be very helpful in this case.

Wouldn't it be better to have StORE run a select version() after
connecting?  Even better would be to call PQprotocolVersion() as it is a
protocol issue.
Fri, 29 Aug 2003 09:23:46 -0400
Rod Taylor <rbt@rbt.ca>
Re: FE/BE Protocol - Specific version
Rod Taylor <rbt@rbt.ca> writes:
>> So, being able to stop connections trying to use old protocol versions
>> would be very helpful in this case.

> Wouldn't it be better to have StORE run a select version() after
> connecting?

Well, his point is that old versions of his client code wouldn't know to
do that.  However, I don't think that what he's suggesting is a suitable
answer either --- he wants to rely on a chance coincidence, namely that
we're upgrading the FE/BE protocol at the same time that he wants to
make an incompatible application-level change.

What I'd do, if I wanted to lock out old clients from accessing
particular tables, is just rename the tables to something else.
(Or keep using the same names, but put the tables in a schema or
database that old clients won't look in.)  The clients wouldn't fail
very gracefully, perhaps, but the protocol-level hack doesn't qualify
as graceful in my book either ...

			regards, tom lane

Fri, 29 Aug 2003 09:35:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FE/BE Protocol - Specific version 
On Fri, 2003-08-29 at 23:23, Rod Taylor wrote:
> > So, being able to stop connections trying to use old protocol versions
> > would be very helpful in this case.
> 
> Wouldn't it be better to have StORE run a select version() after
> connecting?  Even better would be to call PQprotocolVersion() as it is a
> protocol issue.

Well, if we could compel every existing user of StORE + PostrgeSQL to
upgrade to a new version of the mapping software, yes.

I think that we must accept that one or more people may hang onto old
versions of the mapping software which, of course, would not contain the
checks you suggest :-(



30 Aug 2003 00:26:58 +1000
Bruce Badger <bruce_badger@badgerse.com>
Re: FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 23:35, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> >> So, being able to stop connections trying to use old protocol versions
> >> would be very helpful in this case.
> 
> > Wouldn't it be better to have StORE run a select version() after
> > connecting?
> 
> Well, his point is that old versions of his client code wouldn't know to
> do that.  However, I don't think that what he's suggesting is a suitable
> answer either --- he wants to rely on a chance coincidence, namely that
> we're upgrading the FE/BE protocol at the same time that he wants to
> make an incompatible application-level change.

Exactly right.  I made a mistake and this is a chance to fix it. 
Really, just that simple.
 
> What I'd do, if I wanted to lock out old clients from accessing
> particular tables, is just rename the tables to something else.
> (Or keep using the same names, but put the tables in a schema or
> database that old clients won't look in.)  The clients wouldn't fail
> very gracefully, perhaps, but the protocol-level hack doesn't qualify
> as graceful in my book either ...

I don't think that there is a graceful way to fix this.  Unless I can
pull off the "blocking old protocol versions" trick, it seems that the
most likely option will come from another chance coincidence, i.e. that
the StORE schema changes.

Unfortunately, I am not in a position to initiate a change in the StORE
schema.  StORE is also used with other RDBMSs, and the users of those
have no incentive to bless a change in the schema just to help out the
PostgreSQL users.

So, if it did come to pass that rejecting connections on the basis of
protocol version was possible, then I could fix the broken encoding
implementation.  Otherwise, I think I'll have to wait for the next
chance coincidence.

... unless anyone has any better ideas? :-/

All the best,
	Bruce


30 Aug 2003 00:37:46 +1000
Bruce Badger <bruce_badger@badgerse.com>
Re: FE/BE Protocol - Specific version
Bruce Badger <bruce_badger@badgerse.com> writes:
> Unfortunately, I am not in a position to initiate a change in the StORE
> schema.  StORE is also used with other RDBMSs, and the users of those
> have no incentive to bless a change in the schema just to help out the
> PostgreSQL users.

Hm, I must have misunderstood what you wanted to do, because it sure
sounded like a schema change to me.  But anyway --- although I do not
want to offer this as a supported option, there is nothing to stop you
from altering PG_PROTOCOL_EARLIEST in your build.  See
src/include/libpq/pqcomm.h.

			regards, tom lane

Fri, 29 Aug 2003 12:09:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FE/BE Protocol - Specific version 
On Friday 29 August 2003 15:37, Bruce Badger wrote:
> On Fri, 2003-08-29 at 23:35, Tom Lane wrote:
> > Rod Taylor <rbt@rbt.ca> writes:
> > >> So, being able to stop connections trying to use old protocol versions
> > >> would be very helpful in this case.
> > >
> > > Wouldn't it be better to have StORE run a select version() after
> > > connecting?
> >
> > Well, his point is that old versions of his client code wouldn't know to
> > do that.  However, I don't think that what he's suggesting is a suitable
> > answer either --- he wants to rely on a chance coincidence, namely that
> > we're upgrading the FE/BE protocol at the same time that he wants to
> > make an incompatible application-level change.

> So, if it did come to pass that rejecting connections on the basis of
> protocol version was possible, then I could fix the broken encoding
> implementation.  Otherwise, I think I'll have to wait for the next
> chance coincidence.
>
> ... unless anyone has any better ideas? :-/

1. Run "new" versions of the database on a different port - only the new 
client will know to look there.
2. Write a small proxy to simulate #1 (in case PG is serving other clients) - 
only allow access to the StORE db from localhost
3. Write a small proxy to determine which version of the protocol is in use, 
and allow/deny access as required.

-- 
  Richard Huxton
  Archonet Ltd

Fri, 29 Aug 2003 19:54:32 +0100
Richard Huxton <dev@archonet.com>
Re: FE/BE Protocol - Specific version
On Fri, 2003-08-29 at 12:09, Tom Lane wrote:
> Bruce Badger <bruce_badger@badgerse.com> writes:
> > Unfortunately, I am not in a position to initiate a change in the StORE
> > schema.  StORE is also used with other RDBMSs, and the users of those
> > have no incentive to bless a change in the schema just to help out the
> > PostgreSQL users.
> 
> Hm, I must have misunderstood what you wanted to do, because it sure
> sounded like a schema change to me.  But anyway --- although I do not
> want to offer this as a supported option, there is nothing to stop you
> from altering PG_PROTOCOL_EARLIEST in your build.  See
> src/include/libpq/pqcomm.h.
> 

Tom, just curious as to what your resistance is to this feature? ISTM
that making this admin modifiable doesn't hurt anyone and could be
helpful to some people. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


29 Aug 2003 16:39:39 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: FE/BE Protocol - Specific version
Robert Treat <xzilla@users.sourceforge.net> writes:
> Tom, just curious as to what your resistance is to this feature? ISTM
> that making this admin modifiable doesn't hurt anyone and could be
> helpful to some people. 

Admin modifiable at what level?  I don't believe that the "feature" is
valuable enough to warrant defining, implementing, and documenting a GUC
variable, let alone adding a pg_hba.conf column which one could imagine
someone wanting instead.  I suggested a simple source code change, and
I think that's quite sufficient when we have only one request for it
with a not-very-compelling rationale.  There are much more useful tweaks
(eg, altering BLCKSZ) that we have equivalent levels of support for.

			regards, tom lane

Fri, 29 Aug 2003 16:54:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FE/BE Protocol - Specific version 
Bruce Badger wrote:
> > What I'd do, if I wanted to lock out old clients from accessing
> > particular tables, is just rename the tables to something else.
> > (Or keep using the same names, but put the tables in a schema or
> > database that old clients won't look in.)  The clients wouldn't fail
> > very gracefully, perhaps, but the protocol-level hack doesn't qualify
> > as graceful in my book either ...
> 
> I don't think that there is a graceful way to fix this.  Unless I can
> pull off the "blocking old protocol versions" trick, it seems that the
> most likely option will come from another chance coincidence, i.e. that
> the StORE schema changes.

It would be nice if we could configure a function to run on connection
start.  We have the ability to SET values per db or user, but not to
execute a function.  The function could test SELECT version() and send a
message to the user.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 30 Aug 2003 12:33:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FE/BE Protocol - Specific version
> It would be nice if we could configure a function to run on connection
> start.  We have the ability to SET values per db or user, but not to

If my memory serves me well, Oracle has a number of system triggers. On 
database startup and shutdown and perhaps also on connection start and stop.

Sometimes they're very handy.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               ï¿½ben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lï¿½rdag 12.00-16.00       Web:      www.suse.dk

Sun, 31 Aug 2003 15:22:08 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: FE/BE Protocol - Specific version
Kaare Rasmussen wrote:
> > It would be nice if we could configure a function to run on connection
> > start.  We have the ability to SET values per db or user, but not to
> 
> If my memory serves me well, Oracle has a number of system triggers. On 
> database startup and shutdown and perhaps also on connection start and stop.
> 
> Sometimes they're very handy.

Is this a TODO?  Is there an API that would make sense for us?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 31 Aug 2003 21:29:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: FE/BE Protocol - Specific version
> > If my memory serves me well, Oracle has a number of system triggers. On
> > database startup and shutdown and perhaps also on connection start and
> > stop.
> >
> > Sometimes they're very handy.
>
> Is this a TODO?  Is there an API that would make sense for us?

I believe it would make sense. But I'm not up to the task to implement it. 
Maybe if someone is looking into triggers anyway in another context...

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               ï¿½ben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lï¿½rdag 12.00-16.00       Web:      www.suse.dk

Thu, 4 Sep 2003 18:11:21 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: FE/BE Protocol - Specific version