Grouper Users - Open Discussion List

[<>]

Well, it says you need to specify a sort column in the sources.xml, ans maybe a search column too.  Also, the is a postgres error in there about permissions, does the connecting user have permissions in the db? 

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>
> 
> 
> ----------