retrieve attributes from postgresql

[Grunge]

did get the dataconnector running.
i guess it was the missing "jdbc:" in the jdbcURL


    <resolver:DataConnector id="kszUserDb" xsi:type="RelationalDatabase" 
xmlns="urn:mace:shibboleth:2.0:resolver:dc" queryTimeout="PT0.000S">

         <dc:ApplicationManagedConnection jdbcDriver="org.postgresql.Driver"
                                   poolAcquireRetryAttempts="1"
                                   poolAcquireRetryDelay="1"
poolBreakAfterAcquireFailure="false"
jdbcURL="jdbc:postgresql:/secretaddress:someport/userdata"
                                   jdbcUserName="shibreaduser"
                                   jdbcPassword="thepassword"/>

         <dc:QueryTemplate>
                 <![CDATA[
                         SELECT * FROM authtags WHERE 
username='$requestContext.principalName'
                 ]]>
         </dc:QueryTemplate>


         <dc:Column columnName="tag" attributeID="dbtag" />
     </resolver:DataConnector>



On 27.11.2013 11:16, Grunge wrote:
> Hi
>
> I would like to pull attribute(s) from a postgresql db.
> First i would like to get the DataConnector to be fine, i know that 
> the AttributDefinition ist somehow wrong, so i commented it out.
> Can you please help me to solve the errors, i dont understand the 
> messages in idp-process.log.
> Thanks and Greetings
> Philipp
>
>
> -- part from attribute-resolver.xml (changed ip, port and pw) --
>
>    <!-- ****************** -->
>    <!-- *    KSZ Tags    * -->
>    <!-- ****************** -->
> <!-- broken->attributencoder
>         <resolver:AttributeDefinition xsi:type="ad:Simple" 
> id="ksztags" sourceAttributeID="dbtag">
>                 <resolver:AttributeEncoder xsi:type="SAML1String" 
> xmlns="urn:mace:shibboleth:2.0:attribute:encoder" name="ksztags" />
>                 <resolver:Dependency ref="kszUserDb" />
>         </resolver:AttributeDefinition>
> -->
> <!-- broken->unknown -->
>    <resolver:DataConnector xsi:type="RelationalDatabase" 
> xmlns="urn:mace:shibboleth:2.0:resolver:dc"
>                         id="kszUserDb"
>                         queryTimeout="0">
>
>
>         <ApplicationManagedConnection jdbcDriver="org.postgresql.Driver"
>                                   poolAcquireRetryAttempts="1"
>                                   poolAcquireRetryDelay="1"
> poolBreakAfterAcquireFailure="false"
> jdbcURL="postgresql://secretaddress:someport/userdata"
>                                   jdbcUserName="shibreaduser"
>                                   jdbcPassword="passwordhere"/>
>
>         <QueryTemplate>
>                 <![CDATA[
>                         SELECT tag FROM authtags WHERE 
> username='$requestContext.principalName'
>                 ]]>
>         </QueryTemplate>
>
>
>         <Column columnName="tag" attributeID="dbtag" />
>     </resolver:DataConnector>
>
>     <!-- end of KSZ Tags -->
>
>
>
>
>
>
>
> -- parts from idp-process.log --
>
> 10:34:15.132 - INFO 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.AbstractResolutionPlugInBeanDefinitionParser:55] 
> - Parsing configuration for DataConnector plugin with ID: kszUserDb
> 10:34:15.133 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.AbstractResolutionPlugInBeanDefinitionParser:65] 
> - Dependencies for plugin kszUserDb: none
> 10:34:15.322 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:215] 
> - Created application managed data source for data connector kszUserDb
> 10:34:15.326 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:242] 
> - Data connector kszUserDb query template:
>
>                     SELECT tag FROM authtags WHERE 
> username='$requestContext.principalName'
>
>
> 10:34:15.329 - WARN 
> [edu.internet2.middleware.shibboleth.common.config.SpringConfigurationUtils:276] 
> - Numerical duration form is deprecated. The property queryTimeout on 
> relational database connector kszUserDb should use the duration 
> notation: PT0.000S
> 10:34:15.330 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:251] 
> - Data connector kszUserDb SQL query timeout: 0ms
> 10:34:15.331 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:259] 
> - Data connector kszUserDb query uses stored procedures: false
> 10:34:15.331 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:267] 
> - Data connector kszUserDb connections are read only: true
> 10:34:15.335 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:390] 
> - Data connector kszUserDb column descriptors: 
> [RBDMSColumnDescriptor{columnName=tag, attributeId=dbtag, 
> dataType=String}]
> 10:34:15.335 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.config.attribute.resolver.dataConnector.RDBMSDataConnectorBeanDefinitionParser:294] 
> - Data connector kszUserDb no results is error: false
>
> -- later --
>
> 10:34:16.330 - DEBUG 
> [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:215] 
> - RDBMS data connector kszUserDb - Validating configuration.
> 10:34:16.338 - ERROR 
> [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:243] 
> - RDBMS data connector [kszUserDb] - Invalid connector configuration
> java.sql.SQLException: Connections could not be acquired from the 
> underlying database!
>     at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     at 
> com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     at 
> com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
> --snipped--
> Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: 
> A ResourcePool could not acquire a resource from its primary factory 
> or source.
>     at 
> com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     at 
> com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     at 
> com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     at 
> com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) 
> ~[c3p0-0.9.1.2.jar:0.9.1.2]
>     ... 53 common frames omitted