Обсуждение: SSL Connection Issue

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:

---------------------------------------------------------------------------------------------------------
     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 (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
------------------------------------------------------

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...

> 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 (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

^^^
This here is your problem. There is no root certificate for it to load.
You need to either adjust the path to wherever you have your root
certificate, or move the root certificate to the specified location.

(The archives.postgresql.org server appears dead ATM, so I can't see the
instructions you followed. But if they contained a step about the root
certificate, that's where you have to verify that you did the right
thing)

//Magnus

Magnus Hagander wrote:
>
>
>>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 (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
>
>
> ^^^
> This here is your problem. There is no root certificate for it to load.
> You need to either adjust the path to wherever you have your root
> certificate, or move the root certificate to the specified location.
>

No, AFAIR root.crt is necessary for client certificates only. My Linux
installation works perfectly without.
server.req, server.key and server.crt are in my PGDATA dir.

Regards,
Andreas