Skip to Content.
Sympa Menu

grouper-users - Re: [grouper-users] Querying Grouper database to produce attributes for Shibboleth

Subject: Grouper Users - Open Discussion List

List archive

Re: [grouper-users] Querying Grouper database to produce attributes for Shibboleth


Chronological Thread 
  • From: Tom Barton <>
  • To: "" <>
  • Cc: "" <>
  • Subject: Re: [grouper-users] Querying Grouper database to produce attributes for Shibboleth
  • Date: Thu, 13 Aug 2009 16:12:45 -0500

Chris, won't this whole approach cease to return indirect memberships with 1.5?

Richard, I also have concern since we don't commit to supporting a specific SQL form of logical group information access. Any SQL-based approach may break across certain grouper versions.

The new LDAP provisioner TomZ is developing, or one of its components, *might* prove to be pluggable into a shibboleth IdP, providing a grouper data connector to the IdP. You might want to look into that prospect for the longer term as an alternative to using LDAP provisioning to meet the IdP's needs.

Tom

Chris Hyzer wrote:
I agree that LDAP is currently the best way to go. I hope we can provide
another high performance / high availability option in 1.6 or 1.7 for when
LDAP isn't convenient...

Another low cost option you could consider to improve performance is a
materialized view. Mysql doesn't have these like oracle, but this page at
the bottom gives an option that seems like it might work:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Create a table 'shib_lookup' with two cols: login_id, and group_name, and
index the login_id at least. Shib should query that table.
Run these commands periodically (e.g. every 30 minutes? You can tradeoff
up-to-date data vs performance)

CREATE TABLE shib_lookup_new SELECT distinct
concat('${campusid.get(0)}','@ncl.ac.uk'), ga.value FROM ...
RENAME TABLE shib_lookup to shib_lookup_old, shib_lookup_new to shib_lookup;
DROP TABLE IF EXISTS shib_lookup_old;


Regarding your query, if that was the whole thing (was it truncated?), I
don't think it is correct.

SELECT DISTINCT ga.value AS grouper_groups FROM grouper_attributes AS ga, grouper_members gm, grouper_memberships AS gms WHERE ga.value LIKE '%:%' AND ga.value NOT LIKE '% %' AND ga.group_id = gms.owner_id
AND gms.member_id = gm.id
AND gm.subject_id = concat('${campusid.get(0)}','@ncl.ac.uk'

This will select all attribute values for a group (with colon and no space) the person
is in in any list or privilege. You need to change it so that you are only looking at
the "name" attribute (assuming you want that and not display name) [join
attribute to grouper_field], and then you need to only look at the members list I would
think (not other lists, and not privileges) [join membership to grouper_field]. Its
probably also a good idea to put the subject_source in there as well...


However, if you do the "materialized view" approach, you might try just using the
grouper view, since the performance of that is a little less important (only running once every
so often, not with each login). :) Actually, I have a Jira to put the "lightweight"
membership view I use at penn into grouper for 1.5... why don't you just add that now:

CREATE OR REPLACE VIEW GROUPER_MEMBERSHIPS_LW_V
(SUBJECT_ID, SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE, GROUP_ID)
AS select distinct gm.SUBJECT_ID, gm.SUBJECT_SOURCE, ga.VALUE as group_name,
gfl.NAME as list_name, gfl.TYPE as list_type, ga.GROUP_ID
from grouper_memberships gms, grouper_members gm, grouper_attributes ga, grouper_fields gfa, grouper_fields gfl
where gms.OWNER_ID = ga.GROUP_ID and ga.FIELD_ID = gfa.ID
and gfa.NAME = 'name' and gms.FIELD_ID = gfl.ID
and gms.MEMBER_ID = gm.ID


Then select from it like this (where a and b are whatever your subject
sources are, maybe you only have 1 applicable):

select distinct concat(subject_id, '@ncl.ac.uk') as login_id, group_name from grouper_memberships_lw_v gmlv
where list_type = 'list' and list_name = 'members'
and subject_source in ('a','b')

The whole thing would be:

Step 1: Create the table and index:

CREATE TABLE shib_lookup SELECT distinct concat(subject_id, '@ncl.ac.uk') as
login_id, group_name from grouper_memberships_lw_v gmlv where list_type =
'list' and list_name = 'members' and subject_source in ('a','b');

alter table shib_lookup add index login_id_idx (login_id);


Step 2: Put these four statements on a cron:

CREATE TABLE shib_lookup_new SELECT concat(subject_id, '@ncl.ac.uk') as
login_id, group_name from grouper_memberships_lw_v gmlv where list_type =
'list' and list_name = 'members' and subject_source in ('a','b');

alter table shib_lookup_new add index login_id_idx (login_id);

RENAME TABLE shib_lookup to shib_lookup_old, shib_lookup_new to shib_lookup;

DROP TABLE shib_lookup_old;


Good luck!
Regards,
Chris



-----Original Message-----
From: Jim Fox
[mailto:]
Sent: Thursday, August 13, 2009 12:43 PM
To:

Cc:

Subject: Re: [grouper-users] Querying Grouper database to produce
attributes for Shibboleth


So we are wondering if anyone has any advice on the best way to get
data out of the grouper database to be used in situations such as the
above? If this is not the best route to go down we are also considering
looking into using the LDAP connector as an alternative.
LDAP will always be a lot faster for this type of query. In addition
LDAP
servers are very easily replicated.

Jim




Archive powered by MHonArc 2.6.16.

Top of Page