Re: Trouble setting up ssl cert authentication from java/hibernate - Mailing list pgsql-jdbc

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