Обсуждение: SSL Connection Problems
A good day, I am trying to connect to my postgresql database using SSL (I am using the Win32 development build from postgresql). I have followed the steps in: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php and all seems to go fine. I edited the postgresql.conf and pg_hba.conf and added ?ssl to the connection url but it doesn't work. I can't even connect using pgAdmin. This is what I get from the java debug: --------------------------------------------------------------------------------------------------------- PostgreSQL 7.5devel JDBC3 with SSL (build 304) Trying to establish a protocol version 3 connection to localhost:5432 FE=> SSLRequest <=BE SSLOk converting regular socket connection to ssl FE=> StartupPacket(user=useronly, database=GIDS, client_encoding=UNICODE, DateStyle=ISO) SQLException: SQLState(08001) vendor code(451181827) org.postgresql.util.PSQLException: The connection attempt failed at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:120) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:106) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:19) at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:15) at org.postgresql.Driver.connect(Driver.java:138) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) at GIDS.DB.DBPool.newConnection(DBPool.java:133) at GIDS.DB.DBPool.getConnection(DBPool.java:87) at GIDS.DB.DBaseManager.getConnection(DBaseManager.java:170) at GIDS.DB.Querier.<init>(Querier.java:57) at GIDS.LoginScreen.checkAdmin(LoginScreen.java:65) at GIDS.LoginScreen.<init>(LoginScreen.java:46) at GIDS.LoginScreen.main(LoginScreen.java:217) Exception: org.postgresql.util.PSQLException: The connection attempt failed trying driver[className=org.postgresql.Driver,org.postgresql.Driver@1b3f8f6] connect jdbc:postgresql://localhost:5432/GIDS?ssl&loglevel=2 PostgreSQL 7.5devel JDBC3 with SSL (build 304) Trying to establish a protocol version 3 connection to localhost:5432 FE=> SSLRequest <=BE SSLOk converting regular socket connection to ssl FE=> StartupPacket(user=useronly, database=GIDS, client_encoding=UNICODE, DateStyle=ISO) SQLException: SQLState(08001) vendor code(451181827) org.postgresql.util.PSQLException: The connection attempt failed at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:120) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:106) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:19) at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:15) at org.postgresql.Driver.connect(Driver.java:138) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:171) at GIDS.DB.DBPool.newConnection(DBPool.java:133) at GIDS.DB.DBPool.getConnection(DBPool.java:87) at GIDS.DB.DBaseManager.getConnection(DBaseManager.java:170) at GIDS.DB.Querier.<init>(Querier.java:57) at GIDS.LoginScreen.checkAdmin(LoginScreen.java:65) at GIDS.LoginScreen.<init>(LoginScreen.java:46) at GIDS.LoginScreen.main(LoginScreen.java:217) Exception: org.postgresql.util.PSQLException: The connection attempt failed getConnection failed: org.postgresql.util.PSQLException: The connection attempt failed ----------------------------------------------------------------------------------------------------- I am connecting to the server from the same computer as where the server is running. Just local... The log of the server gives this: LOG: could not initialize SSL connection: SSL error code 336306371 Any ideas on what I'm doing wrong? Or guides that can explain me what exactly I have to do in order to get things working? Thanks in advance...
On Mon, 26 Jul 2004, dgr wrote: > A good day, > > I am trying to connect to my postgresql database using SSL (I am using > the Win32 development build from postgresql). If you can't connect from pgadmin either it certainly looks like a server problem, not a client one. With the win32 build the best place to ask this might be pgsql-hackers-win32@postgresql.org. > LOG: could not initialize SSL connection: SSL error code 336306371 > This error message isn't very useful and a google search on the number comes up with nothing. Does this have a LOCATION: or DETAIL: line? Can you get the server to provide them with log_error_verbosity=verbose? Kris Jurka
Thanks for the reply... About the LOG, this is the complete log (with log_error_verbosity=verbose): ------------------------------------------------------ C:\Documents and Settings\postgresql>postmaster LOG: could not recognize system timezone, defaulting to "Etc/GMT-1" HINT: You can specify the correct timezone in postgresql.conf. LOG: could not load root certificate file "C:/postgres/data/root.crt": No such file or directory DETAIL: Will not verify client certificates. LOG: database system was shut down at 2004-07-28 12:21:47 W. Europe Standard Ti me LOG: checkpoint record is at 0/C853918 LOG: redo record is at 0/C853918; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1233553; next OID: 423208 LOG: database system is ready LOG: could not initialize SSL connection: SSL error code 336306371 ------------------------------------------------------ I have sought on google and everything for the error code as well or any of the other errors... but with no success. I'll try the pgsql-hackers-win32@postgresql.org though. But doesn't pgadmin need valid/correct certificates and all? I thought there might be a problem in there since pgadmin doesn't work either... or perhaps the win32 build is not complete yet :) As I said, I'll try there and again... tnx for the effort. On Mon, 26 Jul 2004 12:35:17 -0500 (EST), books@ejurka.com (Kris Jurka) wrote: > > >On Mon, 26 Jul 2004, dgr wrote: > >> A good day, >> >> I am trying to connect to my postgresql database using SSL (I am using >> the Win32 development build from postgresql). > >If you can't connect from pgadmin either it certainly looks like a server >problem, not a client one. With the win32 build the best place to ask >this might be pgsql-hackers-win32@postgresql.org. > >> LOG: could not initialize SSL connection: SSL error code 336306371 >> > >This error message isn't very useful and a google search on the >number comes up with nothing. Does this have a LOCATION: or >DETAIL: line? Can you get the server to provide them with >log_error_verbosity=verbose? > >Kris Jurka > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend
On Wed, 28 Jul 2004, dgr wrote: > Thanks for the reply... > > About the LOG, this is the complete log (with > log_error_verbosity=verbose): > > ------------------------------------------------------ > C:\Documents and Settings\postgresql>postmaster > LOG: could not recognize system timezone, defaulting to "Etc/GMT-1" > HINT: You can specify the correct timezone in postgresql.conf. > LOG: could not load root certificate file > "C:/postgres/data/root.crt": No such > file or directory > DETAIL: Will not verify client certificates. This error message isn't really an error. It is an additional option that may be used to verify that the connecting client is who they say they are. This is not needed and is not the cause of your problem. Kris Jurka