Thread: using ssl some of the time

using ssl some of the time

From
Charles Hornberger
Date:
I'm having a bit of trouble understanding exactly how SSL-encrypted
connections are supposed to work -- and specifically whether it's
possible to "use SSL some of the time."

Here's what I want to do:

1) I want one of my web servers to be able to connect to the database
without the overhead of SSL (it connects as an unprivileged user who
just does SELECTs).

2) I want to allow connections from another machine -- also a web server
-- ONLY if they're encrypted with SSL (users with the ability to
INSERT/UPDATE will be connecting with passwords via a web/db interface
hosted on this machine).

It seems to me that I can't do both. If I turn on the 'ssl=true' option
in $PGDATA/postgresql.conf, then connections from psql at least are
_always_ encrypted, no matter what's in pg_hba.conf.

For the record, here's what's in pg_hba.conf (notably, there's no
hostssl entry):

local    all    all                                     trust
host     all    all       127.0.0.1   255.255.255.255   trust


And here's what happens when I connect with psql:

[economex] charlie/$ psql -h 127.0.0.1 template1
Welcome to psql 7.3.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help on internal slash commands
        \g or terminate with semicolon to execute query
        \q to quit

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

template1=> \q

... which looks encrypted to me.

Am I right in interpreting this to mean that I either have to use SSL
all the time or none of the time?  I'm especially tempted to believe
this might be the case after seeing this item in the "Clients" section
of http://developer.postgresql.org/todo.php:

   - Allow SSL-enabled clients to turn off SSL transfers

Does that mean that, if SSL is enabled for the postmaster, the client
will always be forced to use SSL? Or is there something I need to do to
force the client to NOT use SSL?

Thanks,
Charlie


Re: using ssl some of the time

From
Bruce Momjian
Date:
Charles Hornberger wrote:
> Am I right in interpreting this to mean that I either have to use SSL
> all the time or none of the time?  I'm especially tempted to believe
> this might be the case after seeing this item in the "Clients" section
> of http://developer.postgresql.org/todo.php:
>
>    - Allow SSL-enabled clients to turn off SSL transfers
>
> Does that mean that, if SSL is enabled for the postmaster, the client
> will always be forced to use SSL? Or is there something I need to do to
> force the client to NOT use SSL?

Right, it will use SSL if possible, so if both client and server are SSL
enabled, SSL will be used. 7.4 will allow you to control that.

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

Re: using ssl some of the time

From
Charles Hornberger
Date:
On Wed, 23 Jul 2003, Bruce Momjian wrote:
> Charles Hornberger wrote:
> > Am I right in interpreting this to mean that I either have to use SSL
> > all the time or none of the time?  I'm especially tempted to believe
> > this might be the case after seeing this item in the "Clients" section
> > of http://developer.postgresql.org/todo.php:
> >
> >    - Allow SSL-enabled clients to turn off SSL transfers
> >
> > Does that mean that, if SSL is enabled for the postmaster, the client
> > will always be forced to use SSL? Or is there something I need to do to
> > force the client to NOT use SSL?
>
> Right, it will use SSL if possible, so if both client and server are SSL
> enabled, SSL will be used. 7.4 will allow you to control that.

Interesting. So, am I right in thinking that in 7.3.x, theoretically it'd
be possible to build the postgres backends with SSL support but the
clients -- and I guess libpq is really what I'm talking about here, since
normally I'm connecting via Python or PHP -- without it? And would an
SSL-enabled backend agree to talk to a SSL-disabled client?

As an aside: The only reason I'm worring about this is that sometimes my
client apps generate rather large query results and as far as I can tell,
the overhead of SSL encryption/decryption is slowing things down quite
noticeably in those cases. But I'm pretty ignorant about these matters,
and maybe SSL's not to blame (although I'd be hard pressed to explain the
difference in query performance between local and SSL-over-TCP connections
otherwise).

-Charlie


Re: using ssl some of the time

From
Bruce Momjian
Date:
Charles Hornberger wrote:
> On Wed, 23 Jul 2003, Bruce Momjian wrote:
> > Charles Hornberger wrote:
> > > Am I right in interpreting this to mean that I either have to use SSL
> > > all the time or none of the time?  I'm especially tempted to believe
> > > this might be the case after seeing this item in the "Clients" section
> > > of http://developer.postgresql.org/todo.php:
> > >
> > >    - Allow SSL-enabled clients to turn off SSL transfers
> > >
> > > Does that mean that, if SSL is enabled for the postmaster, the client
> > > will always be forced to use SSL? Or is there something I need to do to
> > > force the client to NOT use SSL?
> >
> > Right, it will use SSL if possible, so if both client and server are SSL
> > enabled, SSL will be used. 7.4 will allow you to control that.
>
> Interesting. So, am I right in thinking that in 7.3.x, theoretically it'd
> be possible to build the postgres backends with SSL support but the
> clients -- and I guess libpq is really what I'm talking about here, since
> normally I'm connecting via Python or PHP -- without it? And would an
> SSL-enabled backend agree to talk to a SSL-disabled client?

Yes, you could to it, but by default, libpq will have SSL compiled in it
just like the backend, but if you created a non-ssl client, it would
talk to the postmaster just fine, unless you have hostssl in
pg_hba.conf.

> As an aside: The only reason I'm worring about this is that sometimes my
> client apps generate rather large query results and as far as I can tell,
> the overhead of SSL encryption/decryption is slowing things down quite
> noticeably in those cases. But I'm pretty ignorant about these matters,
> and maybe SSL's not to blame (although I'd be hard pressed to explain the
> difference in query performance between local and SSL-over-TCP connections
> otherwise).

Please let us know what you find from testing.

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

Re: using ssl some of the time

From
Charles Hornberger
Date:
Bruce Momjian wrote:
> Charles Hornberger wrote:
>
>>On Wed, 23 Jul 2003, Bruce Momjian wrote:
>>
>>>Charles Hornberger wrote:
>>>
>>>>Am I right in interpreting this to mean that I either have to use SSL
>>>>all the time or none of the time?  I'm especially tempted to believe
>>>>this might be the case after seeing this item in the "Clients" section
>>>>of http://developer.postgresql.org/todo.php:
>>>>
>>>>   - Allow SSL-enabled clients to turn off SSL transfers
>>>>
>>>>Does that mean that, if SSL is enabled for the postmaster, the client
>>>>will always be forced to use SSL? Or is there something I need to do to
>>>>force the client to NOT use SSL?
>>>
>>>Right, it will use SSL if possible, so if both client and server are SSL
>>>enabled, SSL will be used. 7.4 will allow you to control that.


I have one more question about the plans for 7.4. How will users of
clients based on libpq use this? Will there be a new optional connection
parameter ('ssl=true') or something?


>>As an aside: The only reason I'm worring about this is that sometimes my
>>client apps generate rather large query results and as far as I can tell,
>>the overhead of SSL encryption/decryption is slowing things down quite
>>noticeably in those cases. But I'm pretty ignorant about these matters,
>>and maybe SSL's not to blame (although I'd be hard pressed to explain the
>>difference in query performance between local and SSL-over-TCP connections
>>otherwise).
>
>
> Please let us know what you find from testing.
>

Just a quick follow-up to share one (!) data point, which looks to me
like it indicates that SSL encryption/decryption is pretty expensive on
one of our Sun Ultra 5 boxes. The following query ("select * from wp")
generates ~270K of output. When executed via a psql client that's
connected over a non-encrypted link, it takes 0.7 seconds; over an
encrypted link, it takes more than 10 times that long.

# time psql -qAt -c 'select * from wp' eclatch > /dev/null
real    0m0.718s
user    0m0.120s
sys     0m0.080s
# time psql -h localhost -qAt -c 'select * from wp' eclatch > /dev/null
real    0m8.081s
user    0m3.930s
sys     0m0.410s
# psql -qAt -c 'select * from wp' eclatch | wc
     2057   30717  276549
# psql -c "select version()" template1
                              version
------------------------------------------------------------------
  PostgreSQL 7.3.2 on sparc-sun-solaris2.7, compiled by GCC 2.95.2

-Charlie


Re: using ssl some of the time

From
Bruce Momjian
Date:
Charles Hornberger wrote:
> Bruce Momjian wrote:
> > Charles Hornberger wrote:
> >
> >>On Wed, 23 Jul 2003, Bruce Momjian wrote:
> >>
> >>>Charles Hornberger wrote:
> >>>
> >>>>Am I right in interpreting this to mean that I either have to use SSL
> >>>>all the time or none of the time?  I'm especially tempted to believe
> >>>>this might be the case after seeing this item in the "Clients" section
> >>>>of http://developer.postgresql.org/todo.php:
> >>>>
> >>>>   - Allow SSL-enabled clients to turn off SSL transfers
> >>>>
> >>>>Does that mean that, if SSL is enabled for the postmaster, the client
> >>>>will always be forced to use SSL? Or is there something I need to do to
> >>>>force the client to NOT use SSL?
> >>>
> >>>Right, it will use SSL if possible, so if both client and server are SSL
> >>>enabled, SSL will be used. 7.4 will allow you to control that.
>
>
> I have one more question about the plans for 7.4. How will users of
> clients based on libpq use this? Will there be a new optional connection
> parameter ('ssl=true') or something?

Yes, exactly.

> Just a quick follow-up to share one (!) data point, which looks to me
> like it indicates that SSL encryption/decryption is pretty expensive on
> one of our Sun Ultra 5 boxes. The following query ("select * from wp")
> generates ~270K of output. When executed via a psql client that's
> connected over a non-encrypted link, it takes 0.7 seconds; over an
> encrypted link, it takes more than 10 times that long.
>
> # time psql -qAt -c 'select * from wp' eclatch > /dev/null
> real    0m0.718s
> user    0m0.120s
> sys     0m0.080s
> # time psql -h localhost -qAt -c 'select * from wp' eclatch > /dev/null
> real    0m8.081s
> user    0m3.930s
> sys     0m0.410s
> # psql -qAt -c 'select * from wp' eclatch | wc
>      2057   30717  276549
> # psql -c "select version()" template1

Wow.  I wonder if we should be using SSL by default in our connections.

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

Re: using ssl some of the time

From
Tom Lane
Date:
> Charles Hornberger wrote:
>> Just a quick follow-up to share one (!) data point, which looks to me
>> like it indicates that SSL encryption/decryption is pretty expensive on
>> one of our Sun Ultra 5 boxes. The following query ("select * from wp")
>> generates ~270K of output. When executed via a psql client that's
>> connected over a non-encrypted link, it takes 0.7 seconds; over an
>> encrypted link, it takes more than 10 times that long.

I suspect most of this is from the ridiculously small renegotiation
interval we put into 7.3.  Try increasing RENEGOTIATION_LIMIT in
src/backend/libpq/be-secure.c (we've bumped it to 512 meg for 7.4).

            regards, tom lane

Re: using ssl some of the time

From
Bruce Momjian
Date:
Tom Lane wrote:
> > Charles Hornberger wrote:
> >> Just a quick follow-up to share one (!) data point, which looks to me
> >> like it indicates that SSL encryption/decryption is pretty expensive on
> >> one of our Sun Ultra 5 boxes. The following query ("select * from wp")
> >> generates ~270K of output. When executed via a psql client that's
> >> connected over a non-encrypted link, it takes 0.7 seconds; over an
> >> encrypted link, it takes more than 10 times that long.
>
> I suspect most of this is from the ridiculously small renegotiation
> interval we put into 7.3.  Try increasing RENEGOTIATION_LIMIT in
> src/backend/libpq/be-secure.c (we've bumped it to 512 meg for 7.4).

Yes, good point!  Would you try 7.4 or that change and let us know the
results?

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