Обсуждение: using ssl some of the time
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
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
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
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
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
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
> 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
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