Grouper Users - Open Discussion List

[Chris Hyzer <>]

Also, there the subject api (part 4) video might help as well:

https://spaces.internet2.edu/display/groupertrain/Grouper+Training

Thanks,
Chris

-----Original Message-----
From: 

 
[mailto:]
 On Behalf Of Shilen Patel
Sent: Friday, January 11, 2013 8:57 AM
To: Keith Hazelton; 

Subject: Re: [grouper-users] Help debugging new jdbc subject adapter

Did you look at this?
https://spaces.internet2.edu/display/Grouper/Member+search+and+sort+columns

Thanks!

-- Shilen

On 1/11/13 8:51 AM, "Keith Hazelton" 
<>
 wrote:

>also looking at: 
>https://spaces.internet2.edu/display/Grouper/subject-0.3.1-doc    --k
>______________
>On Jan 11, 2013, at 07:30:46, Keith Hazelton wrote:
>
>> 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.proper
>>>ties
>>> 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(J
>>>DBCSourceAdapter.java:193)
>>> at 
>>>edu.internet2.middleware.subject.SubjectCheckConfig.checkConfig(SubjectC
>>>heckConfig.java:111)
>>> at 
>>>edu.internet2.middleware.grouper.misc.GrouperCheckConfig$1.callback(Grou
>>>perCheckConfig.java:443)
>>> at 
>>>edu.internet2.middleware.grouper.GrouperSession.callbackGrouperSession(G
>>>rouperSession.java:801)
>>> at 
>>>edu.internet2.middleware.grouper.misc.GrouperCheckConfig.checkConfig(Gro
>>>uperCheckConfig.java:439)
>>> at 
>>>edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStar
>>>tup.java:132)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.
>>>java:151)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(Groupe
>>>rShellWrapper.java:31)
>>> Caused by: org.postgresql.util.PSQLException: ERROR: permission denied
>>>for relation bbsubject
>>> at 
>>>org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
>>>torImpl.java:1592)
>>> at 
>>>org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
>>>l.java:1327)
>>> at 
>>>org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
>>>192)
>>> at 
>>>org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
>>>nt.java:451)
>>> at 
>>>org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
>>>c2Statement.java:350)
>>> at 
>>>org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2St
>>>atement.java:254)
>>> at 
>>>com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxy
>>>PreparedStatement.java:76)
>>> at 
>>>edu.internet2.middleware.subject.provider.JDBCSourceAdapter.getSqlResult
>>>s(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.verifyMemberSortAnd
>>>SearchConfig(GrouperStartup.java:177)
>>> at 
>>>edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStar
>>>tup.java:153)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.
>>>java:151)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(Groupe
>>>rShellWrapper.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.verifyMemberSortAnd
>>>SearchConfig(GrouperStartup.java:177)
>>> at 
>>>edu.internet2.middleware.grouper.misc.GrouperStartup.startup(GrouperStar
>>>tup.java:153)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShell.main(GrouperShell.
>>>java:151)
>>> at 
>>>edu.internet2.middleware.grouper.app.gsh.GrouperShellWrapper.main(Groupe
>>>rShellWrapper.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.GrouperJdbcSourceAd
>>>>apter">
>>>> <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.C3p0JdbcConnecti
>>>>onProvider</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>
>>>> 
>>>> 
>>>> ----------
>>> 
>> 
>