Обсуждение: stunnel with just postgresql client part
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? Thanks mr.wu
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