Thread: SSL Connection Problems

SSL Connection Problems

From
dgr
Date:
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...

Re: SSL Connection Problems

From
Kris Jurka
Date:

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


Re: SSL Connection Problems

From
dgr
Date:
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


Re: SSL Connection Problems

From
Kris Jurka
Date:

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