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
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
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
On Mon, 6 Dec 2004, Andrew M wrote: 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? It leaves you open to man in the middle attacks. You are no longer verifying that the server is who they say they are. Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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:
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]
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:
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
Re: [GENERAL] SSL confirmation - (could not accept SSL connection:
Hi Tom, below is a breakdown of the problems i am experiencing with SSL Connection String: jdbc:postgresql://localhost:5432/mydatabase?ssl my Driver is: DriverVersion PostgreSQL 8.0devel JDBC3 with SSL (build 308) The error message i'm getting is: Code: 14:12:56,779 WARN [SettingsFactory] Could not obtain connection metadata org.jboss.util.NestedSQLException: Could not create connection; - nested throwable: (org.postgresql. util.PSQLException: The connection attempt failed.); - nested throwable: (org.jboss.resource.JBossRe sourceException: Could not create connection; - nested throwable: (org.postgresql.util.PSQLException : The connection attempt failed.)) at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperD ataSource.java:10 6) at net.sf.hibernate.connection.DatasourceConnectionProvider.getConnection(D atasourceConnecti onProvider.java:59) at net.sf.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java: 73) at net.sf.hibernate.cfg.Configuration.buildSettings(Configuration.java: 1132) at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.jav a:766) at org.jboss.hibernate.jmx.Hibernate.buildSessionFactory(Hibernate.java: 476) at org.jboss.hibernate.jmx.Hibernate.startService(Hibernate.java:444) at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupp ort.java:271) at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBean Support.java:221) at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher. java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.jav a:242) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642) at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController .java:891) at $Proxy0.start(Unknown Source) at org.jboss.system.ServiceController.start(ServiceController.java:416) at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher. java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.server.Invocation.invoke(Invocation.java:72) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.jav a:242) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176) at $Proxy4.start(Unknown Source) at org.jboss.deployment.SARDeployer.start(SARDeployer.java:261) at org.jboss.deployment.MainDeployer.start(MainDeployer.java:935) at org.jboss.deployment.MainDeployer.start(MainDeployer.java:927) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:746) at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:709) at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:324) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher. java:141) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80) at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor. java:119) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBea nOperationInterce ptor.java:131) at org.jboss.mx.server.Invocation.invoke(Invocation.java:74) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.jav a:242) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642) at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176) at $Proxy8.deploy(Unknown Source) at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentSc anner.java:305) at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScan ner.java:481) at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doS can(AbstractDeplo ymentScanner.java:204) at
Re: [GENERAL] SSL confirmation - (could not accept SSL connection:
Andrew M [EMAIL PROTECTED] writes: below is a breakdown of the problems i am experiencing with SSL ... Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No truste d certificate found It would appear that either you didn't put a root certificate into the server's $PGDATA/root.crt file, or the server certificate you put into $PGDATA/server.crt isn't signed by any of the CAs that the client code thinks are trusted. I have no idea where the javax.net.ssl library looks for trusted certs; possibly you could find someone more clueful about that on pgsql-jdbc. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[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
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 port will show you details about the authentication and encryption in use. HTH Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 04:12:38PM +, Andrew M wrote: 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 You can omit the -i if you have tcpip_socket = true (or set listen_address if you're using 8.0) in postgresql.conf Is that sufficient to start SSL, how can I check? You should have ssl = true in postgresql.conf (restart the backend after making a change). When you make an SSL connection with psql, psql should print a message like the following: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Make sure you're using a TCP connection instead of a local (Unix-domain) connection. You can use psql's -h option or the PGHOST environment variable to force a TCP connection (e.g., psql -h localhost). See also the hostssl and hostnossl connection types in pg_hba.conf. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
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 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
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. URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.se So using the openssl tools won't help here. Sorry for the inconvenience Andreas ---(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
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. URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.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 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
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. URL: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
On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote: 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) I tend to fire up ethereal and look at the data stream to make absolutely sure that my app is doing SSL to postgresql. I've been burnt once or twice by the libpq my app uses not negotiating SSL correctly while the version of libpq that psql uses being just fine (dumb build problems on my part, but I'd probably have missed them without the sanity check of sniffing the connection). Cheers, Steve ---(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
On Sun, Dec 05, 2004 at 06:37:57PM +, Andrew M wrote: is it possible to trace/monitor processes as they come into postgresql tables like you can in windows SQLServer? See the Run-time Configuration section in the Server Run-time Environment chapter of the PostgreSQL documentation. Look for the variables to configure logging. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 11:02:33AM -0800, Steve Atkins wrote: On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote: 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) I tend to fire up ethereal and look at the data stream to make absolutely sure that my app is doing SSL to postgresql. Doesn't hurt to be sure. I've been burnt once or twice by the libpq my app uses not negotiating SSL correctly while the version of libpq that psql uses being just fine (dumb build problems on my part, but I'd probably have missed them without the sanity check of sniffing the connection). On the backend side you can force SSL by using hostssl in pg_hba.conf; connections that don't use SSL should then fail instead of silently proceeding unencrypted. 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. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SSL confirmation
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
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
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
Re: [GENERAL] SSL confirmation
Ah... ok I need something like: datasources local-tx-datasource jndi-namePostgresDS/jndi-name connection-urljdbc:postgresql://localhost:5432/beyarecords/ connection-url driver-classorg.postgresql.Driver/driver-class user-namemyName/user-name passwordmyPass/password sslmoderequire/sslmode /local-tx-datasource /datasources 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
On Sun, Dec 05, 2004 at 08:50:15PM +, Andrew M wrote: datasources local-tx-datasource jndi-namePostgresDS/jndi-name connection-urljdbc:postgresql://localhost:5432/beyarecords/ connection-url driver-classorg.postgresql.Driver/driver-class user-namemyName/user-name passwordmyPass/password sslmoderequire/sslmode /local-tx-datasource /datasources 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
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 Andrewx-tad-bigger /x-tad-bigger On 5 Dec 2004, at 21:00, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 08:50:15PM +, Andrew M wrote: datasources> local-tx-datasource> jndi-name>PostgresDS/jndi-name> connection-url>jdbc:postgresql://localhost:5432/beyarecords/ connection-url> driver-class>org.postgresql.Driver/driver-class> user-name>myName/user-name> password>myPass/password> sslmode>require/sslmode> /local-tx-datasource> /datasources> 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
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. If it uses libpq then see the libpq - C Library chapter in the PostgreSQL documentation, in particular the Database Connection Control Functions and Environment Variables sections. http://www.postgresql.org/docs/ Even if you're using an interface that abstracts libpq and you're not calling its functions directly, it's useful to know how the underlying library works. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
Re: [GENERAL] SSL confirmation
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])
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
