Grouper Users - Open Discussion List

[Keith Hazelton <>]

On Jan 11, 2013, at 07:20:34, 

 wrote:

> Well, it says you need to specify a sort column in the sources.xml, ans 
> maybe a search column too.  

I can't find documentation that spells out the sort/search column whys and 
hows.  I'm reading the following:

https://spaces.internet2.edu/display/Grouper/Grouper+book+-+Connecting+to+a+subject+source
https://spaces.internet2.edu/display/Grouper/subject-0.2.1-doc

> Also, the is a postgres error in there about permissions, does the 
> connecting user have permissions in the db? 

Yes, the bbsubjectdb uses the same user ("grouper") & privileges (GRANT ALL 
PRIVILEGES ON DATABASE bbsubjectdb TO grouper;) defined for the main Grouper 
database.

       --Keith

> 
> Thanks
> Chris
> 
> 
> ----- Reply message -----
> From: "Keith Hazelton" 
> <>
> To: 
> <>
> Subject: [grouper-users] Help debugging new jdbc subject adapter
> Date: Fri, Jan 11, 2013 7:38 am
> 
> 
> Thanks.  I get an error on startup with gsh and it indicates something 
> wrong with the subject db setup.  I'm sure it's simple...     --Keith
> 
> this:
> 
> sources.xml jdbc source id:   postgresSubjectSource: 
> grouper@jdbc:postgresql://localhost:5432/bbsubjectdb
> Subject API error: error with subject source id: postgresSubjectSource, 
> name: Subject JDBC Source Adapter, problem with getSubject by id, in 
> sources.xml: search searchSubject: , 
> edu.internet2.middleware.subject.SourceUnavailableException: problem in 
> sources.xml source: postgresSubjectSource, sql: select name, description 
> FROM bbsubject WHERE bpid = ?, grouperTestSubjectByIdOnStartupASDFGHJ, 
> searchSubject
> 
> and then this:
> 
> Couldnt startup grouper: At least one sort column should be specified for 
> source postgresSubjectSource
> java.lang.RuntimeException: At least one sort column should be specified 
> for source postgresSubjectSource
> 
> full console log below.
> 
> (Note: I changed the searchSubject query in sources.xml so the WHERE clause 
> uses bpid (primary key) rather than name, but I get the same result in 
> either case)
> 
> ----------
> [root@arche
>  bin]# ./gsh.sh
> Using GROUPER_HOME: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/..
> Using GROUPER_CONF: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/../conf
> Using JAVA: /usr/lib/jvm/java-1.6.0-openjdk/bin/java
> using MEMORY: 64m-750m
> Grouper starting up: version: 2.1.2, build date: 2012/08/14 15:02:01, env: 
> <no label configured>
> grouper.properties read from: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/grouper.properties
> Grouper current directory is: /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin
> log4j.properties read from:   
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/log4j.properties
> Grouper is logging to file:   
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/bin/../logs/grouper_error.log, 
> at min level WARN for package: edu.internet2.middleware.grouper, based on 
> log4j.properties
> grouper.hibernate.properties: 
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/grouper.hibernate.properties
> grouper.hibernate.properties: 
> grouper@jdbc:postgresql://localhost:5432/grouperdb
> sources.xml read from:        
> /opt/grouper-2.1.2/grouper.apiBinary-2.1.2/conf/sources.xml
> sources.xml groupersource id: g:gsa
> sources.xml groupersource id: grouperEntities
> sources.xml jdbc source id:   postgresSubjectSource: 
> grouper@jdbc:postgresql://localhost:5432/bbsubjectdb
> Subject API error: error with subject source id: postgresSubjectSource, 
> name: Subject JDBC Source Adapter, problem with getSubject by id, in 
> sources.xml: search searchSubject: , 
> edu.internet2.middleware.subject.SourceUnavailableException: problem in 
> sources.xml source: postgresSubjectSource, sql: select name, description 
> FROM bbsubject WHERE bpid = ?, grouperTestSubjectByIdOnStartupASDFGHJ, 
> searchSubject
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.uniqueSearch(JDBCSourceAdapter.java:279)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSubject(JDBCSourceAdapter.java:193)
> at 
> edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectCheckConfig.java:111)
> at 
> edu.internet2.middleware.grouper.misc.GrouperCheckConfig$1.callback(GrouperCheckConfig.java:443)
> at 
> edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(GrouperSession.java:801)
> at 
> edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(GrouperCheckConfig.java:439)
> at 
> edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:132)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:151)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
> Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for 
> relation bbsubject
> at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
> at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
> at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
> at 
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
> at 
> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResults(JDBCSourceAdapter.java:625)
> at 
> edu.internet2.middleware.subject.provider.JDBCSourceAdapter.uniqueSearch(JDBCSourceAdapter.java:268)
> ... 8 more
> 
> Couldnt startup grouper: At least one sort column should be specified for 
> source postgresSubjectSource
> java.lang.RuntimeException: At least one sort column should be specified 
> for source postgresSubjectSource
> at 
> edu.internet2.middleware.grouper.misc.GrouperStartup.verifyMemberSortAndSearchConfig(GrouperStartup.java:177)
> at 
> edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:153)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:151)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
> Exception in thread "main" java.lang.RuntimeException: At least one sort 
> column should be specified for source postgresSubjectSource
> at 
> edu.internet2.middleware.grouper.misc.GrouperStartup.verifyMemberSortAndSearchConfig(GrouperStartup.java:177)
> at 
> edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStartup.java:153)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.java:151)
> at 
> edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(GrouperShellWrapper.java:31)
> [root@arche
>  bin]# 
> ----------
> 
> 
> On Jan 10, 2013, at 22:12:36, Chris Hyzer wrote:
> 
> > I would try GSH and do the various subject searches, by id, identifier, 
> > and search.  When it fails, look in logs and see if the error helps.  If 
> > not, forward that error / stack / log messages to the list.
> > 
> > Thanks,
> > Chris
> > 
> > -----Original Message-----
> > From: 
> > 
> >  
> > [mailto:]
> >  
> > Sent: Thursday, January 10, 2013 3:05 PM
> > To: 
> > 
> > Subject: [grouper-users] Help debugging new jdbc subject adapter
> > 
> > I'm creating an alternative subject source for the Project Bamboo 
> > instance of Grouper. I've gotten the database and a subject table created 
> > and populated (see first snippet below) and I modified the sources.xml 
> > file to point to that database and table and do the proper searches (see 
> > second snippet). I bounced the Tomcat instance hosting Grouper on 
> > arche.metazome.info and tried to add a subject to a group. I typed in a 
> > name, hit search and got "Error: Unable to connect to to an entity 
> > database".
> > 
> > 
> > Looking for advice on how to troubleshoot this. I didn't see anything in 
> > the tomcat or grouper logs.
> > 
> > 
> > ----------
> > postgres=# create database bbsubjectdb;
> > CREATE DATABASE
> > postgres=# GRANT ALL PRIVILEGES ON DATABASE bbsubjectdb TO grouper;
> > GRANT
> > postgres=# \c bbsubjectdb
> > psql (8.4.13, server 8.4.12)
> > You are now connected to database "bbsubjectdb".
> > 
> > 
> > bbsubjectdb=# CREATE TABLE bbsubject (
> > bbsubjectdb(# BPId character varying (40) PRIMARY KEY,
> > bbsubjectdb(# name character varying (255) NOT NULL,
> > bbsubjectdb(# description character varying (255) );
> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 
> > "bbsubject_pkey" for table "bbsubject"
> > CREATE TABLE
> > 
> > 
> > bbsubjectdb=# insert into bbsubject(BPId, name, description)
> > bbsubjectdb-# values ('04574969-e851-4fbf-8535-0b81b9b3f2f3', 'earnest 
> > adams', 'Ernie from the Voronoi Project');
> > INSERT 0 1
> > bbsubjectdb=# insert into bbsubject(BPId, name, description)
> > bbsubjectdb-# values ('c00f294d-651b-495b-9633-a900dd1a7418', 'fred 
> > farkle', 'Freddie from the Voronoi Project');
> > INSERT 0 1
> > bbsubjectdb=# select * from bbsubject;
> > bpid | name | description 
> > --------------------------------------+---------------+----------------------------------
> > 04574969-e851-4fbf-8535-0b81b9b3f2f3 | earnest adams | Ernie from the 
> > Voronoi Project
> > c00f294d-651b-495b-9633-a900dd1a7418 | fred farkle | Freddie from the 
> > Voronoi Project
> > (2 rows)
> > 
> > 
> > bbsubjectdb=# select name, description FROM bbsubject WHERE name ILIKE 
> > '%earnest%';
> > name | description 
> > ---------------+--------------------------------
> > earnest adams | Ernie from the Voronoi Project
> > (1 row)
> > 
> > 
> > ----------
> > <?xml version="1.0" encoding="utf-8"?>
> > 
> > 
> > <!--
> > Grouper's subject resolver configuration
> > $Id: sources.example.xml,v 1.8 2009-08-11 20:18:09 mchyzer Exp $
> > -->
> > 
> > 
> > <sources>
> > 
> > 
> > <!-- Group Subject Resolver -->
> > 
> > ...<snip>...
> > 
> > 
> > <!-- khazelton 2013-01-10 Alternative Entity Subject Resolver for Bamboo 
> > -->
> > 
> > 
> > <source 
> > adapterClass="edu.internet2.middleware.grouper.subj.GrouperJdbcSourceAdapter">
> > <id>postgresSubjectSource</id>
> > <name>Subject JDBC Source Adapter</name>
> > <type>person</type>
> > <init-param>
> > <param-name>jdbcConnectionProvider</param-name>
> > <param-value>edu.internet2.middleware.subject.provider.C3p0JdbcConnectionProvider</param-value>
> > </init-param>
> > <init-param>
> > <param-name>maxActive</param-name>
> > <param-value>16</param-value>
> > </init-param>
> > <init-param>
> > <param-name>maxIdle</param-name>
> > <param-value>16</param-value>
> > </init-param>
> > <init-param>
> > <param-name>maxWait</param-name>
> > <param-value>-1</param-value>
> > </init-param>
> > 
> > <init-param>
> > <param-name>dbDriver</param-name>
> > <param-value>org.postgresql.Driver</param-value>
> > </init-param> 
> > <init-param>
> > <param-name>dbUrl</param-name>
> > <param-value>jdbc:postgresql://localhost:5432/bbsubjectdb</param-value>
> > </init-param>
> > <init-param>
> > <param-name>dbUser</param-name>
> > <param-value>xxxxxx</param-value>
> > </init-param>
> > <init-param>
> > <param-name>dbPwd</param-name>
> > <param-value>yyyyyyyy</param-value>
> > </init-param>
> > 
> > <init-param>
> > <param-name>SubjectID_AttributeType</param-name>
> > <param-value>bpid</param-value>
> > </init-param>
> > <init-param>
> > <param-name>Name_AttributeType</param-name>
> > <param-value>name</param-value>
> > </init-param>
> > <init-param>
> > <param-name>Description_AttributeType</param-name>
> > <param-value>description</param-value>
> > </init-param>
> > 
> > <search>
> > <searchType>searchSubject</searchType>
> > <param>
> > <param-name>sql</param-name>
> > <param-value>
> > select name, description FROM bbsubject WHERE name = ?
> > </param-value>
> > </param>
> > </search>
> > <search>
> > <searchType>searchSubjectByIdentifier</searchType>
> > <param>
> > <param-name>sql</param-name>
> > <param-value>
> > select name, description FROM bbsubject WHERE bpid = ?
> > </param-value>
> > </param>
> > </search>
> > <search>
> > <searchType>search</searchType>
> > <param>
> > <param-name>sql</param-name>
> > <param-value>
> > select name, description FROM bbsubject WHERE name ILIKE '%' || ? || '%'
> > </param-value>
> > </param>
> > </search>
> > </source>
> > <!-- end khazelton insert 10-Jan-2013 -->
> > 
> > 
> > </sources>
> > 
> > 
> > ----------
>