Grouper Users - Open Discussion List

[Keith Hazelton <>]

One step closer to getting a new jdbc subject source defined & working with 
my Project Bamboo Grouper 2.1.2 instance.

GSH starts successfully now, but on startup I still get the postgres 
permission error on the bbsubject table in my new subject database, 
bbsubjectdb.
----------
...
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
...
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for 
relation bbsubject
...
----------

What I have in my sources.xml is
----------       
     <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>grouper</param-value>
     </init-param>
     <init-param>
       <param-name>dbPwd</param-name>
       <param-value>yyySanitizedyyyy</param-value>
     </init-param>
----------

and in postgres 
postgres=# GRANT ALL PRIVILEGES ON DATABASE bbsubjectdb TO grouper;

Do I need to grant privileges on bbsubjectdb to an additional user? or??

        Thanks in advance,  --Keith
______________________________
On Jan 11, 2013, at 07:56:43, Shilen Patel wrote:

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