Re: [GENERAL] stunnel with just postgresql client part
On Tue, May 10, 2011 at 6:09 AM, zhong ming wu wrote: > On Mon, May 9, 2011 at 10:50 PM, Merlin Moncure 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 10:50 PM, Merlin Moncure 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 7:17 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 6:42 PM, Merlin Moncure 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 5:03 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 4:37 PM, Merlin Moncure 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.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 3:24 PM, zhong ming wu wrote: > On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 2:01 PM, Merlin Moncure 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] stunnel with just postgresql client part
On Mon, May 9, 2011 at 9:35 AM, zhong ming wu 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general