Re: stunnel with just postgresql client part

On Mon, May 9, 2011 at 7:17 PM, zhong ming wu <mr(dot)z(dot)m(dot)wu(at)gmail(dot)com> wrote:
> On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>> Thanks.  Yes, when I installed the latest stunnel-4.36 it works.
>>>
>>> One strange thing I notice.  When I do ssl connect with psql I am
>>> supposed to get a message like
>>>
>>> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
>>>
>>> With client side stunnel and (nonssl capable) psql I am not getting
>>> this message.  But still the connection seems to be ssl..
>>
>> it is? try setting up your connection string to require ssl.
>>
>
>
> I assume it is because in pg_hba.conf "hostssl" is specified for this
> client ip/user/database.  Plus I check ps output on the server during
> the connection and postgres server reports that connection is from the
> ip address specified in pg_hba.conf
>
> Here is what I tried
> ---------------
> PGSSLMODE=require bin/psql -h 127.0.0.1 -U xmpp xmpp
> psql: server does not support SSL, but SSL was required
> --------------
>
> Just so I don't get confused between multiple lines in pg_hba.conf I
> also deleted all other lines in it and retested.  Assuming postgres
> server is correctly applying the restrictions in pg_hba.conf, and
> assuming the out put of "ps" is reliable then I am doing an ssl
> connection but somehow psql does not think so and does not work unless
> I drop PGSSLMODE=require

Now manybe *I'm* a little confused. Are you connecting to the write
port (stunnel's secure port)? As I understand it, the stunnel pgsql
protocol is such that the client side libpq application can connect to
stunnel which unwraps the encrypted data and connects w/o ssl to
postgres. From the server's point of view, the connection should be
unencrypted and from the client's it should remain encrypted.

I can think of two reasons why you would want to do this:
*) pgbouncer, or a some other connection pooler type piece of software
that does not support ssl
*) for loading purposes you are trying to keep all
encryption/decryption off the main server.

merlin