Re: [JDBC] JDBC connection test with SSL on PG 9.2.1 server

From: Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
To: Hari Babu <haribabu(dot)kommi(at)huawei(dot)com>
Cc: 'dmp' <danap(at)ttc-cmc(dot)net>, pgsql-jdbc(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [JDBC] JDBC connection test with SSL on PG 9.2.1 server
Date: 2013-02-04 14:39:24
Message-ID: 510FC81C.9020302@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-jdbc

On 02/04/2013 04:46 AM, Hari Babu wrote:
> On Friday, February 01, 2013 7:53 PM Adrian Klaver wrote:
>> On 02/01/2013 06:06 AM, Hari Babu wrote:
>>>
>>> We tried the approach as suggested by you but still it is not working as shown in the below log (I had enabled logLevel as 1)
>>> keystore passowrd is qwerty
>>> 19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
>>> 19:26:23.451 (1) Receive Buffer Size is 43808
>>> 19:26:23.452 (1) Send Buffer Size is 25386
>>> getConnection returning driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)3f7fa65e]
>>> Connection1 successful! Conn1:org(dot)postgresql(dot)jdbc4(dot)Jdbc4Connection(at)6baa9f99
>>> null
>>> wrongqwerty
>>> DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres")
>>> trying driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun(dot)jdbc(dot)odbc(dot)JdbcOdbcDriver(at)3597a37c]
>>> *Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
>>> trying driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)3f7fa65e]
>>> 19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
>>> 19:26:23.847 (2) Receive Buffer Size is 43808
>>> 19:26:23.848 (2) Send Buffer Size is 25386
>>> getConnection returning driver[className=org.postgresql.Driver,org(dot)postgresql(dot)Driver(at)3f7fa65e]
>>> Connection2 successful! Conn2:org(dot)postgresql(dot)jdbc4(dot)Jdbc4Connection(at)2e958bb8
>>>
>>> Connect OK
>>>
>>> There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of SSL.
>>> Can you please let us know if there is some similar function in JSSE also.
>
>> Per a previous post, have you verified that pg_hba.conf is set up to
>> properly handle SSL password connections?
>
> Yes, I have modified the pg_hba.conf as follows to handle the SSL connections.
>
> # IPv4 local connections:
> hostssl all all 10.18.0.0/16 cert

This is your entire pg_hba,conf?

If so, note the part about no password prompt below:

http://www.postgresql.org/docs/9.2/interactive/auth-methods.html#AUTH-CERT
"
19.3.10. Certificate Authentication

This authentication method uses SSL client certificates to perform
authentication. It is therefore only available for SSL connections. When
using this authentication method, the server will require that the
client provide a valid certificate. No password prompt will be sent to
the client. The cn (Common Name) attribute of the certificate will be
compared to the requested database user name, and if they match the
login will be allowed. User name mapping can be used to allow cn to be
different from the database user name.

The following configuration options are supported for SSL certificate
authentication:

map
Allows for mapping between system and database user names. See Section
19.2 for details."

I am guessing what you want is:

hostssl all all 10.18.0.0/16 md5

See here for more detail:

http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html

In particular:

"The first record with a matching connection type, client address,
requested database, and user name is used to perform authentication.
There is no "fall-through" or "backup": if one record is chosen and the
authentication fails, subsequent records are not considered. If no
record matches, access is denied."

>
> please provide your suggestions.
>
> Regards,
> Hari babu.
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Kirk Wythers 2013-02-04 14:45:33 partial time stamp query
Previous Message Alexander Farber 2013-02-04 14:36:17 Re: Adding PRIMARY KEY: Table contains duplicated values

Browse pgsql-jdbc by date

  From Date Subject
Next Message Ian Pilcher 2013-02-04 16:36:57 Re: setTimestamp(int, Timestamp, Calendar) ignoring time zone?
Previous Message Hari Babu 2013-02-04 12:46:07 Re: [GENERAL] JDBC connection test with SSL on PG 9.2.1 server