[GENERAL] SSL confirmation
Hi, I am running postgreSQL and just wanted to know how I confirm that SSL is fully functional? I have placed server.key, server.crt and root.crt in the data folder and am able to launch postgreSQL with no problems. I m launching postgreSQl with the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data Is that sufficient to start SSL, how can I check? regards Andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SSL confirmation
To answer my own question I included the -l flag: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data No errors were reported, which I guess there would be if: 1. postgreSQL had not ben built with SSL support? or 2. the certificate has not been properly setup? regards Andrew On 5 Dec 2004, at 16:12, Andrew M wrote: Hi, I am running postgreSQL and just wanted to know how I confirm that SSL is fully functional? I have placed server.key, server.crt and root.crt in the data folder and am able to launch postgreSQL with no problems. I m launching postgreSQl with the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data Is that sufficient to start SSL, how can I check? regards Andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SSL confirmation
Andreas, this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? When I do: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data I get: LOG: checkpoint record is at 0/DAB280 LOG: redo record is at 0/DAB280; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1216; next OID: 17668 LOG: database system is ready LOG: invalid length of startup packet There is no mention of SSL at all regards Andrew On 5 Dec 2004, at 17:03, Andreas Seltenreich wrote: Andrew M. writes: To answer my own question I included the -l flag: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data No errors were reported, which I guess there would be if: 1. postgreSQL had not ben built with SSL support? or 2. the certificate has not been properly setup? You could also use openssl's utilities to diagnose the SSL part of the connection. For example: $ openssl s_client -host localhost -port will show you details about the authentication and encryption in use. HTH Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
Ok, is it possible to trace/monitor processes as they come into postgresql tables like you can in windows SQLServer? regards Andrew On 5 Dec 2004, at 18:27, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 06:59:41PM +0100, Andreas Seltenreich wrote: Andrew M. writes: this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? I'm afraid, I think my suggestion to use openssl's s_client with the postmaster's builtin SSL support was bogus, since Magnus Hagander writes in an older message: "SSL is not enabled at connection time in pgsql - it is negotiatied with the postmaster, and enabled later." http://groups.google.de/groups? as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentun a.se> So using the openssl tools won't help here. Right -- see the "Frontend/Backend Protocol" chapter in the documentation, in particular the "SSL Session Encryption" section: http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782 You can use psql to check if SSL is working. Psql prints a message like the following if SSL was successfully negotiated: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] SSL confirmation
Michael, are you saying I need to do: 'select * from myTable where x=y sslmode=require' to make a request to the database a secure one? If so, as I am using Hibernate, do you know how I would alter my maps to reflect this? regards Andrew On 5 Dec 2004, at 19:41, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 12:27:33PM -0700, Michael Fuhr wrote: On the client side you could set the PGSSLMODE environment variable to "require" (or the older PGREQUIRESSL to "1"), which should tell libpq to attempt only SSL connections. I forgot to mention that you could also use "sslmode=require" or "requiressl=1" (deprecated in 7.4 and later) in your connect string if you're using libpq directly or your interface to libpq allows it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SSL confirmation
Ah... ok I need something like: PostgresDS jdbc:postgresql://localhost:5432/beyarecords org.postgresql.Driver myName myPass require would that do the trick? Sorry for so many questions.. i have a lot to learn about postgreSQL ;-) regards Andrew On 5 Dec 2004, at 20:39, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 08:07:26PM +, Andrew M wrote: are you saying I need to do: 'select * from myTable where x=y sslmode=require' to make a request to the database a secure one? If so, as I am using Hibernate, do you know how I would alter my maps to reflect this? No, "sslmode=require" would be part of the string that gets passed to libpq's PQconnectdb() or its ilk, i.e., the functions that make the initial connection to the database. If you're using an abstraction layer that sits above libpq or an interface that implements the communications protocol without using libpq, then you may or may not have a way to specify such connection options. Check your interface's documentation. If your interface sits above libpq but doesn't allow you to specify connection options like "sslmode=require", then setting environment variables might still work. See the "Environment Variables" section of the "libpq - C Library" chapter in the PostgreSQL documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL confirmation
The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? regards Andrew On 5 Dec 2004, at 21:00, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 08:50:15PM +, Andrew M wrote: PostgresDS jdbc:postgresql://localhost:5432/beyarecords connection-url> org.postgresql.Driver myName myPass require would that do the trick? Sorry for so many questions.. i have a lot to learn about postgreSQL ;-) I don't know if that would work or not -- I'm not familiar with the interface you're using. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SSL confirmation
Hi, seems like I may have located the solution to my earlier problem: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php many thanks Andrew On 5 Dec 2004, at 23:51, Doug McNaught wrote: Michael Fuhr <[EMAIL PROTECTED]> writes: On Sun, Dec 05, 2004 at 09:10:42PM +, Andrew M wrote: The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. The latter. AFAIK it doesn't use environment variables. See the JDBC driver docs for how to set options when connecting. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL confirmation - No trusted certificate found
Ok, spoke to quickly! I am following the example here: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php for ssl support in postgreSQL jdbc (DriverVersion:PostgreSQL 8.0devel JDBC3 with SSL (build 308)). When I get to the final stage, copying the certificate into the java keystore: keytool -keystore [your java home here]/lib/security/cacerts -alias [any name for the cert you like (i.e. postgres)] -import -file server.crt.der I get the following message: Trust this certificate? [no]: I enter yes and get: Certificate was added to keystore I add ssl to my connection string: jdbc:postgresql://localhost:5432/mydatabase?ssl When I launch Jboss, which handles the connection to postgresql, I get the following error: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found What am I doing wrong here many thanks Andrew On 6 Dec 2004, at 01:48, Andrew M wrote: Hi, seems like I may have located the solution to my earlier problem: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php many thanks Andrew On 5 Dec 2004, at 23:51, Doug McNaught wrote: Michael Fuhr <[EMAIL PROTECTED]> writes: On Sun, Dec 05, 2004 at 09:10:42PM +, Andrew M wrote: The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. The latter. AFAIK it doesn't use environment variables. See the JDBC driver docs for how to set options when connecting. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL confirmation - No trusted certificate found
Kriss, I have implemented your earlier suggestion: sslfactory=org.postgresql.ssl.NonValidatingFactory and no error are generated, so I presume that the connection to the database is now ssl'd. So why bother going through the headache of creating a certificate if I can do it like mentioned above? regards Andrew On 6 Dec 2004, at 12:28, Kris Jurka wrote: On Mon, 6 Dec 2004, Andrew M wrote: jdbc:postgresql://localhost:5432/mydatabase?ssl When I launch Jboss, which handles the connection to postgresql, I get the following error: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found Difficult to say. Perhaps JBoss is deciding to use an alternate truststore? Perhaps it doesn't like your cert setup. I would suggest first running a simple client program to test that it's working first. Also adding -Djavax.net.debug=ssl to the java command will help debugging ssl problems. Further the 8.0 JDBC driver can create SSL connnections without doing authentication by adding using an additional url parameter: sslfactory=org.postgresql.ssl.NonValidatingFactory You should try to get it to authenticate correctly, but this is another useful test point. Kris Jurka ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL confirmation - No trusted certificate found
Ok, I have just looked at my postgreSQL terminal window and seen the following message: sslv3 alert certificate unknown Could this mean that the certificate is of the wrong type?? regards Andrew On 6 Dec 2004, at 12:50, Andrew M wrote: Kriss, I have implemented your earlier suggestion: sslfactory=org.postgresql.ssl.NonValidatingFactory and no error are generated, so I presume that the connection to the database is now ssl'd. So why bother going through the headache of creating a certificate if I can do it like mentioned above? regards Andrew On 6 Dec 2004, at 12:28, Kris Jurka wrote: On Mon, 6 Dec 2004, Andrew M wrote: jdbc:postgresql://localhost:5432/mydatabase?ssl When I launch Jboss, which handles the connection to postgresql, I get the following error: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found Difficult to say. Perhaps JBoss is deciding to use an alternate truststore? Perhaps it doesn't like your cert setup. I would suggest first running a simple client program to test that it's working first. Also adding -Djavax.net.debug=ssl to the java command will help debugging ssl problems. Further the 8.0 JDBC driver can create SSL connnections without doing authentication by adding using an additional url parameter: sslfactory=org.postgresql.ssl.NonValidatingFactory You should try to get it to authenticate correctly, but this is another useful test point. Kris Jurka ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation - (could not accept SSL connection: sslv3 alert certificate unknown )
Hi, after much research to various mail-lists, it seems that there is a bug in the beta versions of postgreSQL 8 which is causing the problem. Is there anyway to create a SSLv3 specific certificate? regards Andrew On 6 Dec 2004, at 12:28, Kris Jurka wrote: On Mon, 6 Dec 2004, Andrew M wrote: jdbc:postgresql://localhost:5432/mydatabase?ssl When I launch Jboss, which handles the connection to postgresql, I get the following error: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found Difficult to say. Perhaps JBoss is deciding to use an alternate truststore? Perhaps it doesn't like your cert setup. I would suggest first running a simple client program to test that it's working first. Also adding -Djavax.net.debug=ssl to the java command will help debugging ssl problems. Further the 8.0 JDBC driver can create SSL connnections without doing authentication by adding using an additional url parameter: sslfactory=org.postgresql.ssl.NonValidatingFactory You should try to get it to authenticate correctly, but this is another useful test point. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SSL confirmation - (could not accept SSL connection:
Kris, the information I base my statements on is from the mail-list: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg10008.html I can confirm that Jboss does not requested a keystore, and that it is the driver which is having a problem with the format of the certificate. I can confirm that server.key, server.crt, and root.crt need to be placed in the root of data: /usr/local/pgsql/data If you remove any of those files from the directory , postgreSQl does not startup. This is based on requesting ssl with -l. The options left are to create an SSLv3 specific certificate, if that can be done?? or build version 7.4.* of postgreSQL Any ideas regards Andrew On 6 Dec 2004, at 19:53, Kris Jurka wrote: On Mon, 6 Dec 2004, Andrew M wrote: after much research to various mail-lists, it seems that there is a bug in the beta versions of postgreSQL 8 which is causing the problem. Is there anyway to create a SSLv3 specific certificate? How about sharing a little of that research with us. The whole JDBC and SSL setup worked fine for me last time I tested it (mid-October) and I have little reason to believe it is broken now. What "bug" are you seeing? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SSL confirmation - (could not accept SSL connection:
a:838) at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperD ataSource.java:10 2) ... 98 more Caused by: org.postgresql.util.PSQLException: The connection attempt failed. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(Connecti onFactoryImpl.jav a:136) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.j ava:63) at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connect ion.java:117) at org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connect ion.java:30) at org.postgresql.jdbc3.Jdbc3Connection.(Jdbc3Connection.java:24) at org.postgresql.Driver.connect(Driver.java:183) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.crea teManagedConnecti on(LocalManagedConnectionFactory.java:151) ... 106 more Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No truste d certificate found at com.sun.net.ssl.internal.ssl.BaseSSLSocketImpl.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SunJSSE_az.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SunJSSE_az.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SunJSSE_ax.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) at com.sun.net.ssl.internal.ssl.SSLSocketImpl.j(DashoA12275) at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) at com.sun.net.ssl.internal.ssl.AppOutputStream.write(DashoA12275) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:66) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:124) at org.postgresql.core.PGStream.flush(PGStream.java:486) at org.postgresql.core.v3.ConnectionFactoryImpl.sendStartupPacket(Connectio nFactoryImpl.java :243) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(Connecti onFactoryImpl.jav a:91) ... 112 more Caused by: sun.security.validator.ValidatorException: No trusted certificate found at sun.security.validator.SimpleValidator.buildTrustedChain(SimpleValidator .java:304) at sun.security.validator.SimpleValidator.engineValidate(SimpleValidator.ja va:107) at sun.security.validator.Validator.validate(Validator.java:202) at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(Das hoA12275) at com.sun.net.ssl.internal.ssl.JsseX509TrustManager.checkServerTrusted(Das hoA12275) ... 124 more I sent this message to the jboss forum and they confirmed that Jboss had nothing to do with it: http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3857569#3857569 Just to add, I re-built postgreSQl 7.4.6 and installed the appropriate driver and also experienced the same thing with that build, So, either 1. ph_hba or postgresql.conf is incorrect or 2. the certificate has not been specified correctly... regards Andrew On 6 Dec 2004, at 22:31, Tom Lane wrote: Andrew M <[EMAIL PROTECTED]> writes: the information I base my statements on is from the mail-list: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg10008.html AFAICT that thread describes pilot error compounded by poor error messages from our SSL code. The error message problem, at least, should be fixed in 8.0RC1. If you can demonstrate a problem in RC1, let's see specifics. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
