Обсуждение: 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
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 majordomo@postgresql.org) >
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
On Sun, Dec 05, 2004 at 04:12:38PM +0000, 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/
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 >
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
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/
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/ >
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
On Sun, Dec 05, 2004 at 06:37:57PM +0000, 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/
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/
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/
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 >
On Sun, Dec 05, 2004 at 08:07:26PM +0000, 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/
Ah... ok
I need something like:
<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 ;-)
regards
Andrew
On 5 Dec 2004, at 20:39, Michael Fuhr wrote:
> On Sun, Dec 05, 2004 at 08:07:26PM +0000, 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
>
On Sun, Dec 05, 2004 at 08:50:15PM +0000, 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/
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<fontfamily><param>Courier</param><x-tad-bigger>
</x-tad-bigger></fontfamily>
On 5 Dec 2004, at 21:00, Michael Fuhr wrote:
<excerpt>On Sun, Dec 05, 2004 at 08:50:15PM +0000, Andrew M wrote:
<excerpt><<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 ;-)
</excerpt>
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
</excerpt>
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 +0000, 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
>
On Sun, Dec 05, 2004 at 09:10:42PM +0000, 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/
Michael Fuhr <mike@fuhr.org> writes: > On Sun, Dec 05, 2004 at 09:10:42PM +0000, 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
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 <mike@fuhr.org> writes: > >> On Sun, Dec 05, 2004 at 09:10:42PM +0000, 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 > majordomo@postgresql.org) >
Ok, spoke to quickly! I am following the example here: <bold>http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php</bold> for ssl support in postgreSQL jdbc (<bold>DriverVersion:PostgreSQL 8.0devel JDBC3 with SSL (build 308)</bold>). When I get to the final stage, copying the certificate into the java keystore: <bold>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</bold> I get the following message: <bold>Trust this certificate? [no]:</bold> I enter <bold>yes</bold> and get: <bold>Certificate was added to keystore </bold>I add ssl to my connection string: <bold>jdbc:postgresql://localhost:5432/mydatabase?ssl </bold>When I launch Jboss, which handles the connection to postgresql, I get the following error: <bold>javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found </bold>What am I doing wrong here???? many thanks Andrew<bold> </bold> On 6 Dec 2004, at 01:48, Andrew M wrote: <excerpt>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: <excerpt>Michael Fuhr <<mike@fuhr.org> writes: <excerpt>On Sun, Dec 05, 2004 at 09:10:42PM +0000, Andrew M wrote: <excerpt>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? </excerpt> I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. </excerpt> 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 majordomo@postgresql.org) </excerpt> ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings </excerpt> 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 <mike@fuhr.org> writes: >> >>> On Sun, Dec 05, 2004 at 09:10:42PM +0000, 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 >> majordomo@postgresql.org) >> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
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
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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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
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 majordomo@postgresql.org 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 >
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 >
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
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 > majordomo@postgresql.org >
Andrew M <andrew@jibeya.com> 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
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
org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(Abst
ractDeploymentSca
nner.java:277)
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.deploy(MainDeployer.java:746)
at
org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:709)
at
org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:693)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
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 $Proxy5.deploy(Unknown Source)
at
org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:396)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:293)
at org.jboss.Main.boot(Main.java:151)
at org.jboss.Main$1.run(Main.java:405)
at java.lang.Thread.run(Thread.java:552)
Caused by: org.jboss.resource.JBossResourceException: Could not create
connection; - nested throwabl
e: (org.postgresql.util.PSQLException: The connection attempt failed.)
at
org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.crea
teManagedConnecti
on(LocalManagedConnectionFactory.java:161)
at
org.jboss.resource.connectionmanager.InternalManagedConnectionPool.creat
eConnectionEventL
istener(InternalManagedConnectionPool.java:508)
at
org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getCo
nnection(Internal
ManagedConnectionPool.java:207)
at
org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool
.getConnection(JB
ossManagedConnectionPool.java:534)
at
org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedCo
nnection(BaseConn
ectionManager2.java:396)
at
org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConne
ction(TxConnectio
nManager.java:299)
at
org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConn
ection(BaseConnec
tionManager2.java:448)
at
org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionMa
nagerProxy.alloca
teConnection(BaseConnectionManager2.java: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.<init>(AbstractJdbc2Connect
ion.java:117)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connect
ion.java:30)
at
org.postgresql.jdbc3.Jdbc3Connection.<init>(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 <andrew@jibeya.com> 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
>
Andrew M <andrew@jibeya.com> 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