Grouper Users - Open Discussion List

[<>]

I think if the database says there's a permissions error then something isn't right... Can you connect as that user outside of java and select from that table?



----- Reply message -----
From: "Keith Hazelton" <>
To: <>
Subject: [grouper-users] Help debugging new jdbc subject adapter
Date: Sun, Jan 13, 2013 11:04 am


So, Chris, you think user "grouper" having all privileges on the subject database, "bbsubjectdb" SHOULD be enough?

        --Keith
____________
On Jan 13, 2013, at 10:01:42,  wrote:

> This seems like a database permissions problem and not a grouper problem... Any postgres people who have advice?  Otherwise google might help?  :)
> 
> 
> 
> ----- Reply message -----
> From: "Keith Hazelton" <>
> To: <>
> Subject: [grouper-users] Help debugging new jdbc subject adapter
> Date: Fri, Jan 11, 2013 7:46 pm
> 
> 
> 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>
> >>>>> 
> >>>>> 
> >>>>> ----------
> >>>> 
> >>> 
> >> 
> > 
>