Thread: Trouble setting up ssl cert authentication from java/hibernate

Trouble setting up ssl cert authentication from java/hibernate

From
Magosányi Árpád
Date:
Hi!

I have a server which authenticates with ssl certificates. I have no
trouble using it with psql.

However I cannot figure out how to do the same with java. I have added
my private key and cert along with the CA cert to my keystore.
I set the javax.net.ssl.trustStore and javax.net.ssl.trustStorePassword
properties. But it seems that the underlying ssl does not use my
certificate/key.
Both the server and client reports "FATAL:  connection requires a valid
client certificate"
What am I doing wrong?

The juice of my hibernate config is:
<property
name="hibernate.connection.url">jdbc:postgresql://localhost:5433/archi?sslmode=required&ssl=true&</property>
<property name="hibernate.connection.username">mag</property>
<property
name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>

The juice of my test case:
         String password = new PasswordDialog(new Shell()).ask();
         System.out.println("keystore
path="+System.getProperty("javax.net.ssl.trustStore"));
         File keystorepath = new
File(System.getProperty("user.home"),".keystore");

System.setProperty("javax.net.ssl.trustStore",keystorepath.getAbsolutePath());
         System.setProperty("javax.net.ssl.trustStorePassword", password);
         System.out.println("keystore
path="+System.getProperty("javax.net.ssl.trustStore"));
         System.out.println("keystore
pwd="+System.getProperty("javax.net.ssl.trustStorePassword"));

         Session session = getSessionFactory().getCurrentSession();
         System.out.println("session="+session);
         session.beginTransaction(); // dies here

You can find the full code at commit
8c35c887d973fed1ba6eccdcc7726a11ebfe0612 of
git@github.com:magwas/org.rulez.magwas.styledhtml.git
org.rulez.magwas.enterprise/src/org/rulez/magwas/enterprise/repository/RepoFactoryTest.java

And the stack trace:

org.hibernate.exception.GenericJDBCException: Cannot open connection
     at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
     at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
     at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
     at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
     at
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
     at
org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
     at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
     at
org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
     at
org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
     at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:616)
     at
org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:344)
     at $Proxy5.beginTransaction(Unknown Source)
     at
org.rulez.magwas.enterprise.repository.RepoFactoryTest.test(RepoFactoryTest.java:28)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
     at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:616)
     at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
     at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
     at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
     at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
     at
org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
     at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
     at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
     at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
     at
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
     at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
     at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
     at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
     at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
     at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.postgresql.util.PSQLException: FATAL: connection requires
a valid client certificate
     at
org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
     at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
     at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
     at
org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
     at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
     at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
     at org.postgresql.Driver.makeConnection(Driver.java:393)
     at org.postgresql.Driver.connect(Driver.java:267)
     at java.sql.DriverManager.getConnection(DriverManager.java:620)
     at java.sql.DriverManager.getConnection(DriverManager.java:169)
     at
org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
     at
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
     ... 34 more




Re: Trouble setting up ssl cert authentication from java/hibernate

From
Magosányi Árpád
Date:
Hi!

Well, I was able to connect using a CertAuthfactory method copied from
http://postgresql.1045698.n5.nabble.com/attachment/4405851/0/CertAuthFactory.java
Should I consider it a workaround or the canonical solution?
It WORKSFORME, but I have seen mentioned that the driver supports
certificate authentication out of the box by just configuring the
underlying ssl.
Solution is in commit 21a2edb4e43be142a70493bd4041eb64678faa32.

On 2011-11-02 14:45, Magosányi Árpád wrote:
> Hi!
>
> I have a server which authenticates with ssl certificates. I have no
> trouble using it with psql.
>
> However I cannot figure out how to do the same with java. I have added
> my private key and cert along with the CA cert to my keystore.
> I set the javax.net.ssl.trustStore and
> javax.net.ssl.trustStorePassword properties. But it seems that the
> underlying ssl does not use my certificate/key.
> Both the server and client reports "FATAL:  connection requires a
> valid client certificate"
> What am I doing wrong?
>
> The juice of my hibernate config is:
> <property
> name="hibernate.connection.url">jdbc:postgresql://localhost:5433/archi?sslmode=required&ssl=true&</property>
> <property name="hibernate.connection.username">mag</property>
> <property
> name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
>
> The juice of my test case:
>         String password = new PasswordDialog(new Shell()).ask();
>         System.out.println("keystore
> path="+System.getProperty("javax.net.ssl.trustStore"));
>         File keystorepath = new
> File(System.getProperty("user.home"),".keystore");
>
> System.setProperty("javax.net.ssl.trustStore",keystorepath.getAbsolutePath());
>         System.setProperty("javax.net.ssl.trustStorePassword", password);
>         System.out.println("keystore
> path="+System.getProperty("javax.net.ssl.trustStore"));
>         System.out.println("keystore
> pwd="+System.getProperty("javax.net.ssl.trustStorePassword"));
>
>         Session session = getSessionFactory().getCurrentSession();
>         System.out.println("session="+session);
>         session.beginTransaction(); // dies here
>
> You can find the full code at commit
> 8c35c887d973fed1ba6eccdcc7726a11ebfe0612 of
> git@github.com:magwas/org.rulez.magwas.styledhtml.git
> org.rulez.magwas.enterprise/src/org/rulez/magwas/enterprise/repository/RepoFactoryTest.java
>
>
> And the stack trace:
>
> org.hibernate.exception.GenericJDBCException: Cannot open connection
>     at
> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
>     at
> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
>     at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
>     at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
>     at
> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
>     at
> org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
>     at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
>     at
> org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
>     at
> org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463)
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>     at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>     at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>     at java.lang.reflect.Method.invoke(Method.java:616)
>     at
>
org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:344)
>     at $Proxy5.beginTransaction(Unknown Source)
>     at
> org.rulez.magwas.enterprise.repository.RepoFactoryTest.test(RepoFactoryTest.java:28)
>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>     at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>     at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>     at java.lang.reflect.Method.invoke(Method.java:616)
>     at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
>     at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
>     at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
>     at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
>     at
> org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
>     at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
>
>     at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
>     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
>     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
>     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
>     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
>     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
>     at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
>     at
> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
>     at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
>     at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
>     at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
>     at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
>     at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
> Caused by: org.postgresql.util.PSQLException: FATAL: connection
> requires a valid client certificate
>     at
> org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
>     at
> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
>     at
> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>     at
> org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
>     at
> org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
>     at
> org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
>     at org.postgresql.Driver.makeConnection(Driver.java:393)
>     at org.postgresql.Driver.connect(Driver.java:267)
>     at java.sql.DriverManager.getConnection(DriverManager.java:620)
>     at java.sql.DriverManager.getConnection(DriverManager.java:169)
>     at
> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
>     at
> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
>     ... 34 more
>
>
>
>



Re: Trouble setting up ssl cert authentication from java/hibernate

From
Marc-André Laverdière
Date:
Hello,

I am one of the guys who worked on the cert auth. I wasn't integrated in
the canonical driver because of lack of testing. Thanks for giving your
experience report.

Also, +1 for merging with canonical driver :)

> Hi!
>
> Well, I was able to connect using a CertAuthfactory method copied from
> http://postgresql.1045698.n5.nabble.com/attachment/4405851/0/CertAuthFactory.java
> Should I consider it a workaround or the canonical solution?
> It WORKSFORME, but I have seen mentioned that the driver supports
> certificate authentication out of the box by just configuring the
> underlying ssl.
> Solution is in commit 21a2edb4e43be142a70493bd4041eb64678faa32.
>
> On 2011-11-02 14:45, Magosányi Árpád wrote:
>> Hi!
>>
>> I have a server which authenticates with ssl certificates. I have no
>> trouble using it with psql.
>>
>> However I cannot figure out how to do the same with java. I have added
>> my private key and cert along with the CA cert to my keystore.
>> I set the javax.net.ssl.trustStore and
>> javax.net.ssl.trustStorePassword properties. But it seems that the
>> underlying ssl does not use my certificate/key.
>> Both the server and client reports "FATAL:  connection requires a
>> valid client certificate"
>> What am I doing wrong?
>>
>> The juice of my hibernate config is:
>> <property
>> name="hibernate.connection.url">jdbc:postgresql://localhost:5433/archi?sslmode=required&ssl=true&</property>
>> <property name="hibernate.connection.username">mag</property>
>> <property
>> name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
>>
>> The juice of my test case:
>>         String password = new PasswordDialog(new Shell()).ask();
>>         System.out.println("keystore
>> path="+System.getProperty("javax.net.ssl.trustStore"));
>>         File keystorepath = new
>> File(System.getProperty("user.home"),".keystore");
>>
>> System.setProperty("javax.net.ssl.trustStore",keystorepath.getAbsolutePath());
>>         System.setProperty("javax.net.ssl.trustStorePassword",
>> password);
>>         System.out.println("keystore
>> path="+System.getProperty("javax.net.ssl.trustStore"));
>>         System.out.println("keystore
>> pwd="+System.getProperty("javax.net.ssl.trustStorePassword"));
>>
>>         Session session = getSessionFactory().getCurrentSession();
>>         System.out.println("session="+session);
>>         session.beginTransaction(); // dies here
>>
>> You can find the full code at commit
>> 8c35c887d973fed1ba6eccdcc7726a11ebfe0612 of
>> git@github.com:magwas/org.rulez.magwas.styledhtml.git
>> org.rulez.magwas.enterprise/src/org/rulez/magwas/enterprise/repository/RepoFactoryTest.java
>>
>>
>> And the stack trace:
>>
>> org.hibernate.exception.GenericJDBCException: Cannot open connection
>>     at
>> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
>>     at
>> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
>>     at
>> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
>>     at
>> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
>>     at
>> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
>>     at
>> org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
>>     at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
>>     at
>> org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
>>     at
>> org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463)
>>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>     at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>     at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>     at java.lang.reflect.Method.invoke(Method.java:616)
>>     at
>>
org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:344)
>>     at $Proxy5.beginTransaction(Unknown Source)
>>     at
>> org.rulez.magwas.enterprise.repository.RepoFactoryTest.test(RepoFactoryTest.java:28)
>>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>     at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>     at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>     at java.lang.reflect.Method.invoke(Method.java:616)
>>     at
>> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
>>     at
>> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
>>     at
>> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
>>     at
>> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
>>     at
>> org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
>>     at
>> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
>>
>>     at
>> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
>>     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
>>     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
>>     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
>>     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
>>     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
>>     at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
>>     at
>> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
>>     at
>> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
>>     at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
>>     at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
>>     at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
>>     at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
>> Caused by: org.postgresql.util.PSQLException: FATAL: connection
>> requires a valid client certificate
>>     at
>> org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
>>     at
>> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
>>     at
>> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>>     at
>> org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
>>     at
>> org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
>>     at
>> org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
>>     at org.postgresql.Driver.makeConnection(Driver.java:393)
>>     at org.postgresql.Driver.connect(Driver.java:267)
>>     at java.sql.DriverManager.getConnection(DriverManager.java:620)
>>     at java.sql.DriverManager.getConnection(DriverManager.java:169)
>>     at
>> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
>>     at
>> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
>>     ... 34 more
>>
>>
>>
>>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>


--
Marc-André Laverdière
Software Security Researcher
Innovation Labs, Tata Consultancy Services
Montréal, Québec, Canada

Re: Trouble setting up ssl cert authentication from java/hibernate

From
"magwas@rabic.org"
Date:
Ok, I take it as a "yes, this is the canonical way".

And I would like to second the suggestion to include the code in the
canonical driver.
I was thinking about it, and it can actually be needed to be able to
define which keystore one wants to use for a particular task.
An example: I need to create the database schema with a high
privileged user, but want to use it with a low priority one.
I have just created a second keystore for that reason.
Moreover I want to test access control functionality, and I need
different users for that.

BTW is there a way to tell the jdbc driver to issue a "set role xy"
right after connection?
Most of the above use cases could be handled by that feature as well,
and more cleanly.
But separation of identites of different level of privilege is still a
valid use case (e.g. you are running code you cannot entirely trust.)

On nov. 2, 23:23, marc-an...@atc.tcs.com (Marc-André Laverdière)
wrote:
> Hello,
>
> I am one of the guys who worked on the cert auth. I wasn't integrated in
> the canonical driver because of lack of testing. Thanks for giving your
> experience report.
>
> Also, +1 for merging with canonical driver :)
>
>
>
>
>
>
>
>
>
> > Hi!
>
> > Well, I was able to connect using a CertAuthfactory method copied from
> >http://postgresql.1045698.n5.nabble.com/attachment/4405851/0/CertAuth...
> > Should I consider it a workaround or the canonical solution?
> > It WORKSFORME, but I have seen mentioned that the driver supports
> > certificate authentication out of the box by just configuring the
> > underlying ssl.
> > Solution is in commit 21a2edb4e43be142a70493bd4041eb64678faa32.
>
> > On 2011-11-02 14:45, Magosányi à rpád wrote:
> >> Hi!
>
> >> I have a server which authenticates with ssl certificates. I have no
> >> trouble using it with psql.
>
> >> However I cannot figure out how to do the same with java. I have added
> >> my private key and cert along with the CA cert to my keystore.
> >> I set the javax.net.ssl.trustStore and
> >> javax.net.ssl.trustStorePassword properties. But it seems that the
> >> underlying ssl does not use my certificate/key.
> >> Both the server and client reports "FATAL:  connection requires a
> >> valid client certificate"
> >> What am I doing wrong?
>
> >> The juice of my hibernate config is:
> >> <property
> >>
name="hibernate.connection.url">jdbc:postgresql://localhost:5433/archi?sslmode=required&ssl=true&</property>
> >> <property name="hibernate.connection.username">mag</property>
> >> <property
> >> name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
>
> >> The juice of my test case:
> >>         String password = new PasswordDialog(new Shell()).ask();
> >>         System.out.println("keystore
> >> path="+System.getProperty("javax.net.ssl.trustStore"));
> >>         File keystorepath = new
> >> File(System.getProperty("user.home"),".keystore");
>
> >> System.setProperty("javax.net.ssl.trustStore",keystorepath.getAbsolutePath());
> >>         System.setProperty("javax.net.ssl.trustStorePassword",
> >> password);
> >>         System.out.println("keystore
> >> path="+System.getProperty("javax.net.ssl.trustStore"));
> >>         System.out.println("keystore
> >> pwd="+System.getProperty("javax.net.ssl.trustStorePassword"));
>
> >>         Session session = getSessionFactory().getCurrentSession();
> >>         System.out.println("session="+session);
> >>         session.beginTransaction(); // dies here
>
> >> You can find the full code at commit
> >> 8c35c887d973fed1ba6eccdcc7726a11ebfe0612 of
> >> g...@github.com:magwas/org.rulez.magwas.styledhtml.git
> >> org.rulez.magwas.enterprise/src/org/rulez/magwas/enterprise/repository/RepoFactoryTest.java
>
> >> And the stack trace:
>
> >> org.hibernate.exception.GenericJDBCException: Cannot open connection
> >>     at
> >> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
> >>     at
> >> org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
> >>     at
> >> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
> >>     at
> >> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
> >>     at
> >> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
> >>     at
> >> org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
> >>     at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
> >>     at
> >> org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
> >>     at
> >> org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1463)
> >>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >>     at
> >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> >>     at
> >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> >>     at java.lang.reflect.Method.invoke(Method.java:616)
> >>     at
> >>
org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:344)
> >>     at $Proxy5.beginTransaction(Unknown Source)
> >>     at
> >> org.rulez.magwas.enterprise.repository.RepoFactoryTest.test(RepoFactoryTest.java:28)
> >>     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >>     at
> >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> >>     at
> >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> >>     at java.lang.reflect.Method.invoke(Method.java:616)
> >>     at
> >> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
> >>     at
> >> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
> >>     at
> >> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
> >>     at
> >> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
> >>     at
> >> org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
> >>     at
> >> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
>
> >>     at
> >> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
> >>     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
> >>     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
> >>     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
> >>     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
> >>     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
> >>     at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
> >>     at
> >> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
> >>     at
> >> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
> >>     at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
> >>     at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
> >>     at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
> >>     at
> >> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
> >> Caused by: org.postgresql.util.PSQLException: FATAL: connection
> >> requires a valid client certificate
> >>     at
> >> org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
> >>     at
> >> org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
> >>     at
> >> org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
> >>     at
> >> org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
> >>     at
> >> org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
> >>     at
> >> org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
> >>     at org.postgresql.Driver.makeConnection(Driver.java:393)
> >>     at org.postgresql.Driver.connect(Driver.java:267)
> >>     at java.sql.DriverManager.getConnection(DriverManager.java:620)
> >>     at java.sql.DriverManager.getConnection(DriverManager.java:169)
> >>     at
> >> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
> >>     at
> >> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
> >>     ... 34 more
>
> > --
> > Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org)
> > To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-jdbc
>
> --
> Marc-André Laverdière
> Software Security Researcher
> Innovation Labs, Tata Consultancy Services
> Montréal, Québec, Canada
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-j...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-jdbc


Re: Trouble setting up ssl cert authentication from java/hibernate

From
Craig Ringer
Date:
On 11/02/2011 09:45 PM, Magosányi Árpád wrote:


> The juice of my test case:
> String password = new PasswordDialog(new Shell()).ask();
> System.out.println("keystore
> path="+System.getProperty("javax.net.ssl.trustStore"));
> File keystorepath = new File(System.getProperty("user.home"),".keystore");
> System.setProperty("javax.net.ssl.trustStore",keystorepath.getAbsolutePath());

You must set the javax.net.ssl system properties before *any* SSL code
is run. They're read once and after that changes to them are ignored.

If you set them on the java command line instead of in code, does it use
the certificate and key you provide then?

BTW, I'd also like to see the cert auth factory integrated into PgJDBC.
It's seen some testing, and the best way to get that kind of code tested
more broadly is get it into use.

--
Craig Ringer