stunnel with just postgresql client part

On Mon, May 9, 2011 at 9:35 AM, zhong ming wu <mr.z.m.wu@gmail.com> wrote:

Hi

My postgresql client (ejabberd postgresql lib) does not seem to be
capable of ssl connection to postgresql server (with hostssl in
pg_hba)

So I tried to use run stunnel on the client box (ejabberd).  It
appears not to work.

Here is stunnel log on the client end
------------------
2011.05.09 09:04:06 LOG7[7608:3086100176]: postgres accepted FD=7 from
127.0.0.1:41046
2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres started
2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 7 in non-blocking mode
2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode
2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 9 in non-blocking mode
2011.05.09 09:04:06 LOG7[7608:3086097296]: Connection from
127.0.0.1:41046 permitted by libwrap
2011.05.09 09:04:06 LOG5[7608:3086097296]: postgres connected from
127.0.0.1:41046
2011.05.09 09:04:06 LOG7[7608:3086097296]: FD 8 in non-blocking mode
2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres connecting 10.10.10.10:5433
2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: waiting 10 seconds
2011.05.09 09:04:06 LOG7[7608:3086100176]: Cleaning up the signal pipe
2011.05.09 09:04:06 LOG6[7608:3086100176]: Child process 7614 finished
with code 0
2011.05.09 09:04:06 LOG7[7608:3086097296]: connect_wait: connected
2011.05.09 09:04:06 LOG7[7608:3086097296]: Remote FD=8 initialized
2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect):
before/connect initialization
2011.05.09 09:04:06 LOG7[7608:3086097296]: SSL state (connect): SSLv3
write client hello A
2011.05.09 09:04:06 LOG3[7608:3086097296]: SSL_connect: Peer suddenly
disconnected
2011.05.09 09:04:06 LOG5[7608:3086097296]: Connection reset: 0 bytes
sent to SSL, 0 bytes sent to socket
2011.05.09 09:04:06 LOG7[7608:3086097296]: postgres finished (0 left)
----------------------

If required I can post postgresql server log.

 It seems to be shame that I have to run stunnel on the pg box as well.

My question is that client only stunnel to pg server requiring ssl
connection is not expected to work?  Or am I doing something wrong?

what version stunnel? did you set the protocol in stunnel.conf?

merlin

On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
.
.
.

 It seems to be shame that I have to run stunnel on the pg box as well.

My question is that client only stunnel to pg server requiring ssl
connection is not expected to work?  Or am I doing something wrong?

what version stunnel? did you set the protocol in stunnel.conf?

stunnel-4.15-2.el5.1

I was not setting protocol. But since I got your message, I tried
'protocol = pgsql' in stunnel.conf

Still no go..

In stunnel log, there is now new part about 'protocol pgsql not
supported in client mode'

----------------
2011.05.09 16:20:48 LOG7[8758:3086231248]: postgres accepted FD=7 from
127.0.0.1:50693
2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres started
2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 7 in non-blocking mode
2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 8 in non-blocking mode
2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 9 in non-blocking mode
2011.05.09 16:20:48 LOG7[8758:3086231248]: Cleaning up the signal pipe
2011.05.09 16:20:48 LOG6[8758:3086231248]: Child process 8761 finished
with code 0
2011.05.09 16:20:48 LOG7[8758:3086228368]: Connection from
127.0.0.1:50693 permitted by libwrap
2011.05.09 16:20:48 LOG5[8758:3086228368]: postgres connected from
127.0.0.1:50693
2011.05.09 16:20:48 LOG7[8758:3086228368]: FD 8 in non-blocking mode
2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres connecting 10.10.10.10:5433
2011.05.09 16:20:48 LOG7[8758:3086228368]: connect_wait: waiting 10 seconds
2011.05.09 16:20:48 LOG7[8758:3086228368]: connect_wait: connected
2011.05.09 16:20:48 LOG7[8758:3086228368]: Remote FD=8 initialized
2011.05.09 16:20:48 LOG5[8758:3086228368]: Negotiations for pgsql
(client side) started
2011.05.09 16:20:48 LOG3[8758:3086228368]: Protocol pgsql not
supported in client mode
2011.05.09 16:20:48 LOG5[8758:3086228368]: Connection reset: 0 bytes
sent to SSL, 0 bytes sent to socket
2011.05.09 16:20:48 LOG7[8758:3086228368]: postgres finished (0 left)

---
postgres server log
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
-----

output from psql

psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
----

On Mon, May 9, 2011 at 3:24 PM, zhong ming wu <mr.z.m.wu@gmail.com> wrote:

On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
.
.
.

 It seems to be shame that I have to run stunnel on the pg box as well.

My question is that client only stunnel to pg server requiring ssl
connection is not expected to work?  Or am I doing something wrong?

what version stunnel? did you set the protocol in stunnel.conf?

stunnel-4.15-2.el5.1

I was not setting protocol.  But since I got your message, I tried
'protocol = pgsql' in stunnel.conf

see: http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer

"Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for
both client and server side. It is activated by setting
protocol=pgsql.

For older 4.2x versions the support code is available as patch:
stunnel-postgres.diff

Alternative is to use Stunnel on both sides of connection, then the
protocol support is not needed."

merlin

On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

I was not setting protocol.  But since I got your message, I tried
'protocol = pgsql' in stunnel.conf

see: http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer

"Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for
both client and server side. It is activated by setting
protocol=pgsql.

For older 4.2x versions the support code is available as patch:
stunnel-postgres.diff

Alternative is to use Stunnel on both sides of connection, then the
protocol support is not needed."

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

On Mon, May 9, 2011 at 5:03 PM, zhong ming wu <mr.z.m.wu@gmail.com> wrote:

On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

I was not setting protocol.  But since I got your message, I tried
'protocol = pgsql' in stunnel.conf

see: http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_ssl_connections_with_pgbouncer

"Use Stunnel. Since version 4.27 it supports PostgreSQL protocol for
both client and server side. It is activated by setting
protocol=pgsql.

For older 4.2x versions the support code is available as patch:
stunnel-postgres.diff

Alternative is to use Stunnel on both sides of connection, then the
protocol support is not needed."

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.

merlin

On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure <mmoncure@gmail.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

On Mon, May 9, 2011 at 7:17 PM, zhong ming wu <mr.z.m.wu@gmail.com> wrote:

On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure <mmoncure@gmail.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

On Mon, May 9, 2011 at 10:50 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

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

My client connects to the stunnel'l local port. Come to think of it..
assuming that the line

"SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)"

comes from psql I am getting the expected behavior. Because psql
connects to stunnel local port unencrypted. stunnel encrypts the data
and sends it to the postgres server. The server accepts the
connection because it is coming in encrypted.

I would also be nice to find out from the pg server that the
communication is encrypted. I just don't see a way to find it out
except from the following two facts 1) my server is configured to be
just so 2) the output of 'ps' which tells me how the connection is
coming in.

On Tue, May 10, 2011 at 6:09 AM, zhong ming wu <mr.z.m.wu@gmail.com> wrote:

On Mon, May 9, 2011 at 10:50 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

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

My client connects to the stunnel'l local port.  Come to think of it..
assuming that the line

"SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)"

comes from psql I am getting the expected behavior.  Because psql
connects to stunnel local port unencrypted.  stunnel encrypts the data
and sends it to the postgres server.  The server accepts the
connection because it is coming in encrypted.

yup, you're right. I always set it up the other way so I just assumed
that's what you were doing.

I would also be nice to find out from the pg server that the
communication is encrypted.  I just don't see a way to find it out
except from the following two facts 1) my server is configured to be
just so 2) the output of 'ps' which tells me how the connection is
coming in.

100% agree. maybe a column in pg_stat_activity showing the encryption protocol?

merlin