Re: host and hostssl equivalence in pg_hba.conf

I realise large objects are liked anymore and that the wire protocol is
changing in 7.4 but I've just posted this message into the PHP documentation:

-- begin
Using php 4.3.0 and PostgreSQL 7.3.1

I can write a simple script in which pg_lo_write seems to always return 1 and not the number of bytes written, as evidenced by extracting the data through another means.

Further more, I can make this pg_lo_write fail, or at least fail to write all the data it's pretty difficult to tell without the number of bytes written being returned, and not return the false value. In addition to this, the lo resource has been adjusted so that the oid it contains is 0.

Unfortunately, I do not know what exactly the failure mode is, it does seem to be in the ip network communication side of PostgreSQL, which is odd since the unix domain comms works fine for this. However, it would have been useful to have the pg_lo_write() function return as advertised, it would have saved some of the 2 man hours me and the dev. team put into diagnosing this problem.
-- end

Now, I did a little bit of testing and when doing a \lo_export <oid> <filename>
in psql connected via localhost a SIGPIPE is generated in write() in libc and
psql quit, without printing any message to the terminal. Perhaps interestingly
the file that gets written is always 65536 bytes long. The server log shows:

2003-06-05 14:24:02 LOG: query: select proname, oid from pg_proc where proname = 'lo_open' or proname = 'lo_close' or proname =
'lo_creat' or proname = 'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_tell' or proname = 'loread'
or proname = 'lowrite'
2003-06-05 14:24:02 LOG: duration: 0.002924 sec
2003-06-05 14:24:03 LOG: pq_recvbuf: recv() failed: Success

fwiw.

The last 4 bytes saved and next 16 bytes that should follow are:

00fffc 74 f3 5f ff d0 d1 c6 b3 eb bb 01 26 d6 b3 51 a9
01000c 68 e5 70 54

Of course it could be way past there the failure point but I thought it worth
including on the off chance.

When I do the same except allowing psql to connect through the unix domain
socket it works. The right number of bytes are returned and cmp shows no
differences to the original.

So, a) is this known? b) what is it? c) is it not going to happen in the new
protocol? and d) does anyone care?

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> Now, I did a little bit of testing and when doing a \lo_export <oid>
> <filename> in psql connected via localhost a SIGPIPE is generated in
> write() in libc and psql quit, without printing any message to the
> terminal. Perhaps interestingly
> the file that gets written is always 65536 bytes long.

Hm. Are you using an SSL connection? There are some known bugs in the
SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've
not tried it myself.

regards, tom lane

On Fri, 6 Jun 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > Now, I did a little bit of testing and when doing a \lo_export <oid>
> > <filename> in psql connected via localhost a SIGPIPE is generated in
> > write() in libc and psql quit, without printing any message to the
> > terminal. Perhaps interestingly
> > the file that gets written is always 65536 bytes long.
>
> Hm. Are you using an SSL connection? There are some known bugs in the
> SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've
> not tried it myself.

Damn, yes I am, I noticed the notice when connecting but then didn't think
anything of it. Thanks Tom, I'll check that later when I do have time
(shouldn't have wasted the precious minutes joining the NULL != "" war).

--
Nigel Andrews

Note, primary list address changed to -general, I'd suggest any followups
remove the -hackers, which I've left in just for 'closure'.

On Fri, 6 Jun 2003, Nigel J. Andrews wrote:

> On Fri, 6 Jun 2003, Tom Lane wrote:
>
> > "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > > Now, I did a little bit of testing and when doing a \lo_export <oid>
> > > <filename> in psql connected via localhost a SIGPIPE is generated in
> > > write() in libc and psql quit, without printing any message to the
> > > terminal. Perhaps interestingly
> > > the file that gets written is always 65536 bytes long.
> >
> > Hm. Are you using an SSL connection? There are some known bugs in the
> > SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've
> > not tried it myself.
>
> Damn, yes I am, I noticed the notice when connecting but then didn't think
> anything of it. Thanks Tom, I'll check that later when I do have time
> (shouldn't have wasted the precious minutes joining the NULL != "" war).

Ok, I tried to try this but I can not get SSL to _not_ be used when connecting
via any tcp connection, unless the client hasn't been built with ssl support of
course. The pg_hba.conf has:

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

local all all md5
host all all 127.0.0.1 255.255.255.255 md5

psql -U me -h localhost db

prints:

SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168)

psql -U me db

doesn't.

Am I losing my mind? Should I need hostssl on that second line of the config
before ssl is allowed? I did look at that code once a few weeks ago and vaguely
remember something about host and hostssl handling but can't remember the
details. Is this really a bug, even if only in the documentation, or have I got
completely the wrong end of the stick?

--
Nigel J. Andrews

Repost just to add the information that this is [now] on 7.3.3, previously on
7.3.2.

Sorry for the noise of the incomplete previous message, although the email
lists seem to be very light this last week. Obviously the mail server is still
feeling under the weather, I presume many posts have been lost in a hole
somewhere much like another of my posts.

On Sun, 8 Jun 2003, Nigel J. Andrews wrote:

>
> Note, primary list address changed to -general, I'd suggest any followups
> remove the -hackers, which I've left in just for 'closure'.
>
>
> On Fri, 6 Jun 2003, Nigel J. Andrews wrote:
>
> > On Fri, 6 Jun 2003, Tom Lane wrote:
> >
> > > "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > > > Now, I did a little bit of testing and when doing a \lo_export <oid>
> > > > <filename> in psql connected via localhost a SIGPIPE is generated in
> > > > write() in libc and psql quit, without printing any message to the
> > > > terminal. Perhaps interestingly
> > > > the file that gets written is always 65536 bytes long.
> > >
> > > Hm. Are you using an SSL connection? There are some known bugs in the
> > > SSL support in 7.3.1. It's supposed to be fixed in 7.3.3, though I've
> > > not tried it myself.
> >
> > Damn, yes I am, I noticed the notice when connecting but then didn't think
> > anything of it. Thanks Tom, I'll check that later when I do have time
> > (shouldn't have wasted the precious minutes joining the NULL != "" war).
>
> Ok, I tried to try this but I can not get SSL to _not_ be used when connecting
> via any tcp connection, unless the client hasn't been built with ssl support of
> course. The pg_hba.conf has:
>
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
>
> local all all md5
> host all all 127.0.0.1 255.255.255.255 md5
>
> psql -U me -h localhost db
>
> prints:
>
> SSL connection (cipher: EDH-RSA-DES-CBC3-SHA, bits: 168)
>
> psql -U me db
>
> doesn't.
>
> Am I losing my mind? Should I need hostssl on that second line of the config
> before ssl is allowed? I did look at that code once a few weeks ago and vaguely
> remember something about host and hostssl handling but can't remember the
> details. Is this really a bug, even if only in the documentation, or have I got
> completely the wrong end of the stick?
>

--
Nigel J. Andrews

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> Ok, I tried to try this but I can not get SSL to _not_ be used when
> connecting via any tcp connection, unless the client hasn't been built
> with ssl support of course.

Yeah, I believe that's the way it works. It seems overly fascist of the
SSL code to not have the option to turn it off ... but that's how libpq
is set up at the moment.

regards, tom lane

How do people feel about changing matching for host and hostssl to be such that
a plain host line in pg_hba.conf does not allow a SSL connection but requires
the hostssl specifier?

I had been going to submit a very small patch to do this but then it occurred
to me this was a good candidate for a GUC along the lines of
allow_host_hostssl_equivalence (just a name picked out of the air for this
post). As this is a little bit more work and I can't get to anoncvs to refresh
my tree I thought I'd check if it was something to persue or forget.

To recap another thread I started, I had problems with large objects, Tom
suggested it might be SSL related as unix domain connections were fine, I
confirmed I still had the problem in 7.3.3 but then was unable to switch off
SSL for any IP connections without a rebuild as the host line in pg_hba.conf
permits SSL connections.

What I haven't done is confirm 7.4 has the problem (see the anoncvs comment
above).

I suggest this as GUC controlled feature since it seems from first impressions
that it is a lot more work to fall back to without SSL if there is a matching
host line but not a hostssl one. That is, connections from SSL enabled clients
would be rejected if there no hostssl entry for them, even if there was a
matching host entry, thus locking that client out of the server (unless there
was some way to tell the client to not attempt SSL).

Hmm...hope that's understandable, I seem to have rabbited on making this a lot
longer than I was expecting to.

--
Nigel Andrews

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> How do people feel about changing matching for host and hostssl to be such that
> a plain host line in pg_hba.conf does not allow a SSL connection but requires
> the hostssl specifier?

Then there would be no way to have a host entry that allowed both ---
which, aside from being a loss of functionality, would doubtless break
existing setups.

I'd hold still for a "hostnossl" keyword, I guess, but I don't entirely
see the use for it.

If your real gripe is that libpq insists on trying SSL connections
first, the server is the wrong end to be patching that problem at.
There should be a way to control libpq's allow_ssl_try state variable
from the outside.

regards, tom lane

On Tue, 10 Jun 2003, Nigel J. Andrews wrote:

> How do people feel about changing matching for host and hostssl to be such that
> a plain host line in pg_hba.conf does not allow a SSL connection but requires
> the hostssl specifier?

Nigel,

We had discussed overhauling the connection settings on both client and
server to cover all needs, along these lines:

> Date: Tue, 7 Jan 2003 16:07:58 -0500 (EST)
> From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
> To: Peter Eisentraut <peter_e(at)gmx(dot)net>
> Cc: Jon Jensen <jon(at)endpoint(dot)com>, pgsql-patches(at)postgresql(dot)org
> Subject: Re: [PATCHES] Refuse SSL patchf
>
> Peter Eisentraut wrote:
> > Bruce Momjian writes:
> >
> > > > Tom thought that having conflicting REFUSESSL and REQUIRESSL directives
> > > > would be confusing, and since I dug up someone's old discussion in the
> > > > list archives of the four possible modes, we could move to that.
> > >
> > > Oh. I find two params clearer than one with meaningless numbers. :-)
> >
> > But the numeric model provides four modes (refuse ssl, prefer no ssl,
> > prefer ssl, require ssl) whereas the refuse/require combination only
> > provides three modes (refuse ssl, require ssl, and one other depending on
> > how you define it when neither is set). If you don't like numbers, make
> > them words.
>
> OK, that works:
>
> require
> prevent
> prefer
> noprefer
>
> This allows us to subsume PGREQUIRE_SSL into the new variable. Do we
> still need additional functionality in pg_hba.conf? I am only asking if
> pushing these decisions out to the client makes sense?
>
> For performance reasons, it is good to push this information out to the
> clients so the proper connection method is used the first time.
>
> However, for easier maintenance, we could have all of this in
> pg_hba.conf only, and have clients try SSL first, and fall back to
> non-SSL if the server doesn't want SSL. It would require two new
> pg_hba.conf line types. We have prefer-SSL (host) and SSL-only (ssl)
> now.
>
> require (ssl)
> prevent (nossl)
> prefer (hostpreferssl)
> noprefer(host)
>
> This would change 'host' to not prefer SSL.

Unfortunately, I lived with my own local patch and forgot about making the
more general one these past five months.

This proposal would meet your needs, wouldn't it?

Jon

On Tue, 10 Jun 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > How do people feel about changing matching for host and hostssl to be such that
> > a plain host line in pg_hba.conf does not allow a SSL connection but requires
> > the hostssl specifier?
>
> Then there would be no way to have a host entry that allowed both ---
> which, aside from being a loss of functionality, would doubtless break
> existing setups.

Well, what I was thinking of would have allowed it, just using two entries, a
host one and a hostssl one.

> I'd hold still for a "hostnossl" keyword, I guess, but I don't entirely
> see the use for it.

Well Jon Jenson's posted something else on this which I should read when I've
got my mind more in tune with it.

> If your real gripe is that libpq insists on trying SSL connections
> first, the server is the wrong end to be patching that problem at.
> There should be a way to control libpq's allow_ssl_try state variable
> from the outside.

A quick read makes me think that's what Jon's post is on about.

--
Nigel Andrews

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> On Tue, 10 Jun 2003, Tom Lane wrote:
>> If your real gripe is that libpq insists on trying SSL connections
>> first, the server is the wrong end to be patching that problem at.
>> There should be a way to control libpq's allow_ssl_try state variable
>> from the outside.

> A quick read makes me think that's what Jon's post is on about.

Right. I had forgotten that thread, but indeed we had agreed to a
definition that would allow flexible control of libpq's SSL behavior.
Looks like no one got round to actually implementing what was hammered
out though.

Note: if you want to take a swipe at implementing that proposal, please
be sure to start from CVS tip. I mangled all that code just a couple
days ago to allow both old and new protocols to be supported ... so any
patch based on 7.3 is not going to apply ...

regards, tom lane

Nigel J. Andrews wrote:
>
> How do people feel about changing matching for host and hostssl to be such that
> a plain host line in pg_hba.conf does not allow a SSL connection but requires
> the hostssl specifier?
>
> I had been going to submit a very small patch to do this but then it occurred
> to me this was a good candidate for a GUC along the lines of
> allow_host_hostssl_equivalence (just a name picked out of the air for this
> post). As this is a little bit more work and I can't get to anoncvs to refresh
> my tree I thought I'd check if it was something to persue or forget.

The other problem with using GUC here is that is adds even more
complexity to pg_bha.conf, where the meaning of 'host' changes depending
on postgresql.conf, and as Tom pointed out, it doesn't give per-host
control. I do think we need an additional host* line in pg_hba.conf for
this.

The real killer is that folks are getting SSL when they don't even know
it just because their client binaries/server are ssl.

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

Add to TODO:

* Allow SSL-enabled clients to turn off SSL transfers

---------------------------------------------------------------------------

Tom Lane wrote:
> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> writes:
> > Ok, I tried to try this but I can not get SSL to _not_ be used when
> > connecting via any tcp connection, unless the client hasn't been built
> > with ssl support of course.
>
> Yeah, I believe that's the way it works. It seems overly fascist of the
> SSL code to not have the option to turn it off ... but that's how libpq
> is set up at the moment.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

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