SSL connection between geronimo and postgresSQL

[Paul Wong-5]

Hi all,

I am trying to setup a SSL connection between a Geronimo
(geronimo-tomcat6-javaee5-2.1.4) application and PostgresSQL
(postgresql-8.3.3-1) database.  The database has the SSL turn on.  
However, I could not find the ConnectURL property to set.  A sample
connectURL string for PostgresSQL is like
"jdbc:postgresql://MyServer:5432/MyDB?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory".


Here is the code in the db-resource-adapter.xml file

            <connection-definition>
               
<connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
                <connectiondefinition-instance>
                    <name>MyDbPool</name>
                    <config-property-setting
name="DatabaseName">MyDB</config-property-setting>
                    <config-property-setting
name="ServerName">MyServer</config-property-setting>
                    <connectionmanager>
                        <local-transaction/>
                        <single-pool>
                            <max-size>10</max-size>
                            <min-size>0</min-size>
                            <match-one/>
                        </single-pool>
                    </connectionmanager>
                </connectiondefinition-instance>
            </connection-definition>

Any suggestion?

Thanks a lot.

Paul

[Paul Wong-5]

This may be a re-sent.
Just not sure I was on the mailing list or not.

Thanks a lot.

Paul

> Hi all,
>
> I am trying to setup a SSL connection between a Geronimo
> (geronimo-tomcat6-javaee5-2.1.4) application and PostgresSQL
> (postgresql-8.3.3-1) database.  The database has the SSL turn on.  
> However, I could not find the ConnectURL property to set.  A sample
> connectURL string for PostgresSQL is like
> "jdbc:postgresql://MyServer:5432/MyDB?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory".
>
>
> Here is the code in the db-resource-adapter.xml file
>
>            <connection-definition>
>                
> <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
>
>                <connectiondefinition-instance>
>                    <name>MyDbPool</name>
>                    <config-property-setting
> name="DatabaseName">MyDB</config-property-setting>
>                    <config-property-setting
> name="ServerName">MyServer</config-property-setting>
>                    <connectionmanager>
>                        <local-transaction/>
>                        <single-pool>
>                            <max-size>10</max-size>
>                            <min-size>0</min-size>
>                            <match-one/>
>                        </single-pool>
>                    </connectionmanager>
>                </connectiondefinition-instance>
>            </connection-definition>
>
> Any suggestion?
>
> Thanks a lot.
>
> Paul
>
>
>

[djencks]

First time I've seen this message...

IIRC the postgres tranql wrapper uses the postgres  
ConnectionPoolDataSource (at least with tranql trunk).  To configure  
ssl you'd need to be able to configure ssl using this postgres class.  
If you can provide info on how to do that in the next day or two we  
can make sure tranql can do it too in the next tranql wrapper release.

thanks
david jencks

On Sep 30, 2009, at 10:25 AM, Paul Wong wrote:

> This may be a re-sent.
> Just not sure I was on the mailing list or not.
>
> Thanks a lot.
>
> Paul
>
>> Hi all,
>>
>> I am trying to setup a SSL connection between a Geronimo (geronimo-
>> tomcat6-javaee5-2.1.4) application and PostgresSQL  
>> (postgresql-8.3.3-1) database.  The database has the SSL turn on.  
>> However, I could not find the ConnectURL property to set.  A sample  
>> connectURL string for PostgresSQL is like "jdbc:postgresql://
>> MyServer:5432/MyDB?
>> ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory".
>>
>> Here is the code in the db-resource-adapter.xml file
>>
>>           <connection-definition>
>>               <connectionfactory-interface>javax.sql.DataSource</
>> connectionfactory-interface>
>>               <connectiondefinition-instance>
>>                   <name>MyDbPool</name>
>>                   <config-property-setting  
>> name="DatabaseName">MyDB</config-property-setting>
>>                   <config-property-setting  
>> name="ServerName">MyServer</config-property-setting>
>>                   <connectionmanager>
>>                       <local-transaction/>
>>                       <single-pool>
>>                           <max-size>10</max-size>
>>                           <min-size>0</min-size>
>>                           <match-one/>
>>                       </single-pool>
>>                   </connectionmanager>
>>               </connectiondefinition-instance>
>>           </connection-definition>
>>
>> Any suggestion?
>>
>> Thanks a lot.
>>
>> Paul
>>
>>
>>
>

[Paul Wong-5]

David,

Thanks a lot.  Here is what I have.
Please let me know, if you need more information.

Thanks a lot.

Paul

Here is the content in the "geronimo-application.xml" file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<app:application
xmlns:app="http://geronimo.apache.org/xml/ns/j2ee/application-2.0"
xmlns:client="http://geronimo.apache.org/xml/ns/j2ee/application-client-2.0"
xmlns:conn="http://geronimo.apache.org/xml/ns/j2ee/connector-1.2"
xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2"
xmlns:ejb="http://openejb.apache.org/xml/ns/openejb-jar-2.2"
xmlns:name="http://geronimo.apache.org/xml/ns/naming-1.2"
xmlns:pers="http://java.sun.com/xml/ns/persistence"
xmlns:pkgen="http://openejb.apache.org/xml/ns/pkgen-2.1"
xmlns:sec="http://geronimo.apache.org/xml/ns/security-2.0"
xmlns:web="http://geronimo.apache.org/xml/ns/j2ee/web-2.0.1"
application-name="DB Layer Application">
    <dep:environment>
        <dep:moduleId>
            <dep:groupId>mycompany.server</dep:groupId>
            <dep:artifactId>db</dep:artifactId>
            <dep:version>2.0.0</dep:version>
            <dep:type>car</dep:type>
        </dep:moduleId>
    </dep:environment>
    <app:module>
       
<app:connector>tranql-connector-postgresql-local-1.1.rar</app:connector>
        <app:alt-dd>META-INF/db-resource-adapter.xml</app:alt-dd>
    </app:module>
</app:application>

Here is the content in the "db-resource-adapter.xml" file:
<?xml version="1.0" encoding="UTF-8"?>
<connector xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-1.2">
    <dep:environment
xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2">
        <dep:moduleId>
            <dep:groupId>mycomapny.server</dep:groupId>
            <dep:artifactId>MyDbPool</dep:artifactId>
            <dep:version>2.0.0</dep:version>
            <dep:type>rar</dep:type>
        </dep:moduleId>
        <dep:dependencies>
            <dep:dependency>
                <dep:groupId>jdbc</dep:groupId>
                <dep:artifactId>postgresql</dep:artifactId>
                <dep:version>603</dep:version>
                <dep:type>jar</dep:type>
            </dep:dependency>
        </dep:dependencies>
    </dep:environment>
    <resourceadapter>
        <outbound-resourceadapter>
            <connection-definition>
               
<connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
                <connectiondefinition-instance>
                    <name>MyDbPool</name>
                    <config-property-setting
name="DatabaseName">MyDB</config-property-setting>
                    <config-property-setting
name="ServerName">MyServer</config-property-setting>
                    <connectionmanager>
                        <local-transaction/>
                        <single-pool>
                            <max-size>10</max-size>
                            <min-size>0</min-size>
                            <match-one/>
                        </single-pool>
                    </connectionmanager>
                </connectiondefinition-instance>
            </connection-definition>
        </outbound-resourceadapter>
    </resourceadapter>
</connector>




David Jencks wrote:

> First time I've seen this message...
>
> IIRC the postgres tranql wrapper uses the postgres
> ConnectionPoolDataSource (at least with tranql trunk).  To configure
> ssl you'd need to be able to configure ssl using this postgres class.  
> If you can provide info on how to do that in the next day or two we
> can make sure tranql can do it too in the next tranql wrapper release.
>
> thanks
> david jencks
>
> On Sep 30, 2009, at 10:25 AM, Paul Wong wrote:
>
>> This may be a re-sent.
>> Just not sure I was on the mailing list or not.
>>
>> Thanks a lot.
>>
>> Paul
>>
>>> Hi all,
>>>
>>> I am trying to setup a SSL connection between a Geronimo
>>> (geronimo-tomcat6-javaee5-2.1.4) application and PostgresSQL
>>> (postgresql-8.3.3-1) database.  The database has the SSL turn on.  
>>> However, I could not find the ConnectURL property to set.  A sample
>>> connectURL string for PostgresSQL is like
>>> "jdbc:postgresql://MyServer:5432/MyDB?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory".
>>>
>>>
>>> Here is the code in the db-resource-adapter.xml file
>>>
>>>           <connection-definition>
>>>              
>>> <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
>>>
>>>               <connectiondefinition-instance>
>>>                   <name>MyDbPool</name>
>>>                   <config-property-setting
>>> name="DatabaseName">MyDB</config-property-setting>
>>>                   <config-property-setting
>>> name="ServerName">MyServer</config-property-setting>
>>>                   <connectionmanager>
>>>                       <local-transaction/>
>>>                       <single-pool>
>>>                           <max-size>10</max-size>
>>>                           <min-size>0</min-size>
>>>                           <match-one/>
>>>                       </single-pool>
>>>                   </connectionmanager>
>>>               </connectiondefinition-instance>
>>>           </connection-definition>
>>>
>>> Any suggestion?
>>>
>>> Thanks a lot.
>>>
>>> Paul
>>>
>>>
>>>
>>
>
>

[Fredrik Jonson-3]

David Jencks wrote:

>  IIRC the postgres tranql wrapper uses the postgres  
>  ConnectionPoolDataSource (at least with tranql trunk).  To configure  
>  ssl you'd need to be able to configure ssl using this postgres class.  
>  If you can provide info on how to do that in the next day or two we  
>  can make sure tranql can do it too in the next tranql wrapper release.

David,

I'd be happy to supply a patch, if only I knew which svn trunk you're
talking about. The tranql svn repo at codehaus is, well, confusing, and I
don't even have enough svn-fu to know where to point 'svn co'. ;)

Otherwise, for the properties Paul asked about, there is a boolean property
for SSL and a string property sslsocketfactory on the base class of
PGConnectionPoolDataSource[0]. Might be relevant to expose the other
"public" properties in that class too though.

<http://jdbc.postgresql.org/documentation/publicapi/index.html?
org/postgresql/ds/common/BaseDataSource.html>

--
Fredrik Jonson

[djencks]

On Sep 30, 2009, at 1:33 PM, Fredrik Jonson wrote:

> David Jencks wrote:
>
>> IIRC the postgres tranql wrapper uses the postgres
>> ConnectionPoolDataSource (at least with tranql trunk).  To configure
>> ssl you'd need to be able to configure ssl using this postgres class.
>> If you can provide info on how to do that in the next day or two we
>> can make sure tranql can do it too in the next tranql wrapper  
>> release.
>
> David,
>
> I'd be happy to supply a patch, if only I knew which svn trunk you're
> talking about. The tranql svn repo at codehaus is, well, confusing,  
> and I
> don't even have enough svn-fu to know where to point 'svn co'. ;)

I've had that problem too and recently reorganized it.  Try this:

https://svn.codehaus.org/tranql/ra/trunk


>
> Otherwise, for the properties Paul asked about, there is a boolean  
> property
> for SSL and a string property sslsocketfactory on the base class of
> PGConnectionPoolDataSource[0]. Might be relevant to expose the other
> "public" properties in that class too though.
>
> <http://jdbc.postgresql.org/documentation/publicapi/index.html?
> org/postgresql/ds/common/BaseDataSource.html>

A patch would be great, if you don't get to it I'll try to look into it.

thanks
david jencks

>
> --
> Fredrik Jonson
>

[Fredrik Jonson-3]

In <9C99181D-93F4-402A-91E8-723D9D828035@...> David Jencks wrote:
 
>  I've had that problem too and recently reorganized it.  Try this:
>
>  https://svn.codehaus.org/tranql/ra/trunk

Thanks, I'll look into it.

--
Fredrik Jonson

[Paul Wong-5]

David and Fredrik,

Thanks a lot.  Can I specify the implemented class injected is
org.postgresql.ds.PGSimpleDataSource in my ra.xml file?
Can you or anyone give me some tips of doing so?  I tried several
parameters and could not get it to work.

Here is my java code:
    @Resource( name = " MyDbPool ", authenticationType =
Resource.AuthenticationType.APPLICATION  )
    private PGSimpleDataSource jdbcDataSource;

Here is in the db-resource-adapter.xml
        <outbound-resourceadapter>
            <connection-definition>
               
<connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface>
                <connectiondefinition-instance>
                    <name>MyDbPool</name>
                    <config-property-setting
name="DatabaseName">MyDB</config-property-setting>
                    <config-property-setting
name="ServerName">MyServer</config-property-setting>
                    <connectionmanager>
                        <local-transaction/>
                        <single-pool>
                            <max-size>10</max-size>
                            <min-size>0</min-size>
                            <match-one/>
                        </single-pool>
                    </connectionmanager>
                </connectiondefinition-instance>
            </connection-definition>
        </outbound-resourceadapter>

Here are the attributes I can use:
connectionFactory, statisticsProvider, UserName, ServerName, ,
connectionInterface, DatabaseName, Password, objectName,
configProperties, managedConnectionFactoryClass, PortNumber,
eventProvider, kernel, PrepareThreshold, resourceAdapterWrapper,
connectionFactoryInterface, classLoader, abstractName, LoginTimeout,
implementedInterfaces, connectionManagerContainer, connectionImplClass,
connectionFactoryImplClass

Thanks a lot for your help.

Paul

Fredrik Jonson wrote:

> In <9C99181D-93F4-402A-91E8-723D9D828035@...> David Jencks wrote:
>  
>  
>>  I've had that problem too and recently reorganized it.  Try this:
>>
>>  https://svn.codehaus.org/tranql/ra/trunk
>>    
>
> Thanks, I'll look into it.
>
>  

[Fredrik Jonson-3]

David,

I've opened TQL-18 and submitted a patch. It has only received rudimentary
testing, and the changes on the xa variant isn't tested at all. I've attached
a example datasource plan to the issue.

http://jira.codehaus.org/browse/TQL-18

--
Fredrik Jonson

[Fredrik Jonson-3]

Paul Wong wrote:

>  Can you or anyone give me some tips of doing so?  I tried several
>  parameters and could not get it to work.

I'm not aware of any way to inject a PGSimpleDataSource directly. But then
I feel like I've only scratched the surface of what G can do sofar, so I
wouldn't be surprised I'm wrong. ;)

Maybe you can try the patch in TQL-18, if you feel up to it? The patched
snapshots of tranql (1.2/1.5-SNAPSHOT) seems to deploy and run just fine
in G 2.1.4. At least for as far as I took it.

--
Fredrik Jonson

[Paul Wong-5]

Fredrik,

Thanks a lot.

Can you point me to get the 1.2/1.5-SNAPSHOT?

I can found the 1.2 SNAPSHOT, but I could located the patched one?

http://snapshots.repository.codehaus.org/org/tranql/tranql-connector-postgresql-local/1.2-SNAPSHOT/tranql-connector-postgresql-local-1.2-20090925.074240-3.rar

Thanks a lot for your help.

Yours Truly,

Paul

Fredrik Jonson wrote:

> Paul Wong wrote:
>
>  
>>  Can you or anyone give me some tips of doing so?  I tried several
>>  parameters and could not get it to work.
>>    
>
> I'm not aware of any way to inject a PGSimpleDataSource directly. But then
> I feel like I've only scratched the surface of what G can do sofar, so I
> wouldn't be surprised I'm wrong. ;)
>
> Maybe you can try the patch in TQL-18, if you feel up to it? The patched
> snapshots of tranql (1.2/1.5-SNAPSHOT) seems to deploy and run just fine
> in G 2.1.4. At least for as far as I took it.
>
>  

[djencks]

I applied Fredrik's patch and pushed new snapshots which should be  
available now under

http://snapshots.repository.codehaus.org/org/tranql/

thanks for the patch!  Let us know if there are problems.

BTW I would be very careful setting autocommit disabled on a local  
datasource since it may well interfere with geronimo transaction  
management.

thanks
david jencks





On Oct 5, 2009, at 12:04 PM, Paul Wong wrote:

> Fredrik,
>
> Thanks a lot.
>
> Can you point me to get the 1.2/1.5-SNAPSHOT?
>
> I can found the 1.2 SNAPSHOT, but I could located the patched one?
>
> http://snapshots.repository.codehaus.org/org/tranql/tranql-connector-postgresql-local/1.2-SNAPSHOT/tranql-connector-postgresql-local-1.2-20090925.074240-3.rar
>
> Thanks a lot for your help.
>
> Yours Truly,
>
> Paul
>
> Fredrik Jonson wrote:
>> Paul Wong wrote:
>>
>>
>>> Can you or anyone give me some tips of doing so?  I tried several  
>>> parameters and could not get it to work.
>>>
>>
>> I'm not aware of any way to inject a PGSimpleDataSource directly.  
>> But then
>> I feel like I've only scratched the surface of what G can do sofar,  
>> so I
>> wouldn't be surprised I'm wrong. ;)
>>
>> Maybe you can try the patch in TQL-18, if you feel up to it? The  
>> patched
>> snapshots of tranql (1.2/1.5-SNAPSHOT) seems to deploy and run just  
>> fine
>> in G 2.1.4. At least for as far as I took it.
>>
>>
>

[Paul Wong-5]

David and Fredrik,

It works very well.  You guys are SUPER.

Many thanks again.

Cheers.

Paul


David Jencks wrote:

> I applied Fredrik's patch and pushed new snapshots which should be
> available now under
>
> http://snapshots.repository.codehaus.org/org/tranql/
>
> thanks for the patch!  Let us know if there are problems.
>
> BTW I would be very careful setting autocommit disabled on a local
> datasource since it may well interfere with geronimo transaction
> management.
>
> thanks
> david jencks
>
>
>
>
>
> On Oct 5, 2009, at 12:04 PM, Paul Wong wrote:
>
>> Fredrik,
>>
>> Thanks a lot.
>>
>> Can you point me to get the 1.2/1.5-SNAPSHOT?
>>
>> I can found the 1.2 SNAPSHOT, but I could located the patched one?
>>
>> http://snapshots.repository.codehaus.org/org/tranql/tranql-connector-postgresql-local/1.2-SNAPSHOT/tranql-connector-postgresql-local-1.2-20090925.074240-3.rar 
>>
>>
>> Thanks a lot for your help.
>>
>> Yours Truly,
>>
>> Paul
>>
>> Fredrik Jonson wrote:
>>> Paul Wong wrote:
>>>
>>>
>>>> Can you or anyone give me some tips of doing so?  I tried several  
>>>> parameters and could not get it to work.
>>>>
>>>
>>> I'm not aware of any way to inject a PGSimpleDataSource directly.
>>> But then
>>> I feel like I've only scratched the surface of what G can do sofar,
>>> so I
>>> wouldn't be surprised I'm wrong. ;)
>>>
>>> Maybe you can try the patch in TQL-18, if you feel up to it? The
>>> patched
>>> snapshots of tranql (1.2/1.5-SNAPSHOT) seems to deploy and run just
>>> fine
>>> in G 2.1.4. At least for as far as I took it.
>>>
>>>
>>
>
>