Wiki Home

Grouper Release Announcements

Grouper Guides

Grouper Deployment Guide

Community Contributions

Internal Developer Resources

External System: Database

midPoint provisioner uses a database external system so create one as shown in the screenshots below.



DDL

You can have any type of database between Grouper and MidPoint.  There are a few examples below

DDL Postgres

CREATE TABLE gr_mp_groups (
	group_name varchar(1024) NULL, -- Name of group mapped in some way
	id_index int8 NOT NULL, -- This is the integer identifier for a group and foreign key to group attributes and memberships
	display_name varchar(1024) NULL, -- Display name of group mapped in some way
	description varchar(1024) NULL, -- Description of group mapped in some way
	last_modified int8 NOT NULL, -- Millis since 1970, will be sequential and unique
	deleted varchar(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
	CONSTRAINT gr_mp_groups_pkey PRIMARY KEY (id_index)
);
CREATE INDEX gr_mp_groups_ddx ON gr_mp_groups(display_name);
CREATE INDEX gr_mp_groups_gdx ON gr_mp_groups(group_name);
CREATE UNIQUE INDEX gr_mp_groups_idx ON gr_mp_groups(id_index);
CREATE UNIQUE INDEX gr_mp_groups_ldx ON gr_mp_groups(last_modified);
COMMENT ON TABLE gr_mp_groups IS 'This table holds groups';

COMMENT ON COLUMN gr_mp_groups.group_name IS 'Name of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.id_index IS 'This is the integer identifier for a group and foreign key to group attributes and memberships';
COMMENT ON COLUMN gr_mp_groups.display_name IS 'Display name of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.description IS 'Description of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_groups.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_subjects (
	subject_id_index int8 NOT NULL, -- This is the integer identifier for a subject and foreign key to subject attributes and memberships
	subject_id varchar(1024) NULL, -- Subject ID mapped in some way
	last_modified int8 NOT NULL, -- Millis since 1970, will be sequential and unique
	deleted varchar(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
	CONSTRAINT gr_mp_subjects_pkey PRIMARY KEY (subject_id_index)
);
CREATE UNIQUE INDEX gr_mp_subjects_idx ON gr_mp_subjects(subject_id_index);
CREATE UNIQUE INDEX gr_mp_subjects_ldx ON gr_mp_subjects(last_modified);
CREATE INDEX gr_mp_subjects_sdx ON gr_mp_subjects(subject_id);
COMMENT ON TABLE gr_mp_subjects IS 'This table holds subjects';

COMMENT ON COLUMN gr_mp_subjects.subject_id_index IS 'This is the integer identifier for a subject and foreign key to subject attributes and memberships';
COMMENT ON COLUMN gr_mp_subjects.subject_id IS 'Subject ID mapped in some way';
COMMENT ON COLUMN gr_mp_subjects.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_subjects.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_group_attributes (
	group_id_index int8 NOT NULL, -- This is the integer identifier for a group and foreign key to groups and memberships
	attribute_name varchar(1000) NOT NULL, -- Attribute name for attributes not in the main group table
	attribute_value varchar(4000) NULL, -- Attribute value could be null
	last_modified int8 NOT NULL, -- Millis since 1970, will be sequential and unique
	deleted varchar(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
	CONSTRAINT gr_mp_group_attributes_fk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups(id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_group_attributes_idx ON gr_mp_group_attributes(group_id_index, attribute_name, attribute_value);
CREATE UNIQUE INDEX gr_mp_group_attributes_ldx ON gr_mp_group_attributes(last_modified);
COMMENT ON TABLE gr_mp_group_attributes IS 'This table holds group attributes which are one to one or one to many to the groups table';

COMMENT ON COLUMN gr_mp_group_attributes.group_id_index IS 'This is the integer identifier for a group and foreign key to groups and memberships';
COMMENT ON COLUMN gr_mp_group_attributes.attribute_name IS 'Attribute name for attributes not in the main group table';
COMMENT ON COLUMN gr_mp_group_attributes.attribute_value IS 'Attribute value could be null';
COMMENT ON COLUMN gr_mp_group_attributes.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_group_attributes.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_memberships (
	group_id_index int8 NOT NULL, -- This is the foreign key to groups
	subject_id_index int8 NOT NULL, -- This is the foreign key to subjects
	last_modified int8 NOT NULL, -- Millis since 1970, will be sequential and unique
	deleted varchar(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
	CONSTRAINT gr_mp_memberships_gfk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups(id_index) ON DELETE CASCADE,
	CONSTRAINT gr_mp_memberships_sfk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects(subject_id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_memberships_idx ON gr_mp_memberships(group_id_index, subject_id_index);
CREATE UNIQUE INDEX gr_mp_memberships_ldx ON gr_mp_memberships(last_modified);
COMMENT ON TABLE gr_mp_memberships IS 'This table holds memberships.  The primary key is group_id_index and subject_id_index';

COMMENT ON COLUMN gr_mp_memberships.group_id_index IS 'This is the foreign key to groups';
COMMENT ON COLUMN gr_mp_memberships.subject_id_index IS 'This is the foreign key to subjects';
COMMENT ON COLUMN gr_mp_memberships.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_memberships.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_subject_attributes (
	subject_id_index int8 NOT NULL, -- This is the integer identifier and foreign key to subjects
	attribute_name varchar(1000) NOT NULL, -- Attribute name for attributes not in the main subject table
	attribute_value varchar(4000) NULL, -- Attribute value could be null
	last_modified int8 NOT NULL, -- Millis since 1970, will be sequential and unique
	deleted varchar(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
	CONSTRAINT gr_mp_subject_attributes_fk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects(subject_id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_subject_attributes_idx ON gr_mp_subject_attributes(subject_id_index, attribute_name, attribute_value);
CREATE UNIQUE INDEX gr_mp_subject_attributes_ldx ON gr_mp_subject_attributes(last_modified);
COMMENT ON TABLE gr_mp_subject_attributes IS 'This table holds subject attributes which are one to one or one to many to the subjects table';

COMMENT ON COLUMN gr_mp_subject_attributes.subject_id_index IS 'This is the integer identifier and foreign key to subjects';
COMMENT ON COLUMN gr_mp_subject_attributes.attribute_name IS 'Attribute name for attributes not in the main subject table';
COMMENT ON COLUMN gr_mp_subject_attributes.attribute_value IS 'Attribute value could be null';
COMMENT ON COLUMN gr_mp_subject_attributes.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_subject_attributes.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

DDL MySQL

CREATE TABLE gr_mp_groups (
  group_name varchar(1024) DEFAULT NULL,
  id_index bigint NOT NULL,
  display_name varchar(1024) DEFAULT NULL,
  description varchar(1024) DEFAULT NULL,
  last_modified bigint NOT NULL,
  deleted varchar(1) NOT NULL,
  PRIMARY KEY (id_index),
  UNIQUE KEY gr_mp_groups_ldx (last_modified),
  UNIQUE KEY gr_mp_groups_idx (id_index),
  KEY gr_mp_groups_ddx (display_name(255)),
  KEY gr_mp_groups_gdx (group_name(255))
);

CREATE TABLE gr_mp_group_attributes (
  group_id_index bigint NOT NULL,
  attribute_name varchar(1000) NOT NULL,
  attribute_value varchar(4000) DEFAULT NULL,
  last_modified bigint NOT NULL,
  deleted varchar(1) NOT NULL,
  UNIQUE KEY gr_mp_group_attributes_ldx (last_modified),
  UNIQUE KEY gr_mp_group_attributes_idx (group_id_index,attribute_name(100),attribute_value(155)),
  CONSTRAINT gr_mp_group_attributes_fk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups (id_index) ON DELETE CASCADE
 );

CREATE TABLE gr_mp_subjects (
  subject_id_index bigint NOT NULL,
  subject_id varchar(1024) DEFAULT NULL,
  last_modified bigint NOT NULL,
  deleted varchar(1) NOT NULL,
  PRIMARY KEY (subject_id_index),
  UNIQUE KEY gr_mp_subjects_ldx (last_modified),
  UNIQUE KEY gr_mp_subjects_idx (subject_id_index),
  KEY gr_mp_subjects_sdx (subject_id(255))
);

CREATE TABLE gr_mp_subject_attributes (
  subject_id_index bigint NOT NULL,
  attribute_name varchar(1000) NOT NULL,
  attribute_value varchar(4000) DEFAULT NULL,
  last_modified bigint NOT NULL,
  deleted varchar(1) NOT NULL,
  UNIQUE KEY gr_mp_subject_attributes_ldx (last_modified),
  UNIQUE KEY gr_mp_subject_attributes_idx (subject_id_index,attribute_name(100),attribute_value(155)),
  CONSTRAINT gr_mp_subject_attributes_fk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects (subject_id_index) ON DELETE CASCADE
 );

CREATE TABLE gr_mp_memberships (
  group_id_index bigint NOT NULL,
  subject_id_index bigint NOT NULL,
  last_modified bigint NOT NULL,
  deleted varchar(1) NOT NULL,
  UNIQUE KEY gr_mp_memberships_ldx (last_modified),
  UNIQUE KEY gr_mp_memberships_idx (group_id_index,subject_id_index),
  KEY gr_mp_memberships_sfk (subject_id_index),
  CONSTRAINT gr_mp_memberships_gfk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups (id_index) ON DELETE CASCADE,
  CONSTRAINT gr_mp_memberships_sfk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects (subject_id_index) ON DELETE CASCADE
 );


DDL for Oracle

CREATE TABLE gr_mp_groups (
  group_name varchar2(1024) NULL, -- Name of group mapped in some way
  id_index number(12) NOT NULL, -- This is the integer identifier for a group and foreign key to group attributes and memberships
  display_name varchar2(1024) NULL, -- Display name of group mapped in some way
  description varchar2(1024) NULL, -- Description of group mapped in some way
  last_modified number(12) NOT NULL, -- Millis since 1970, will be sequential and unique
  deleted varchar2(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
  CONSTRAINT gr_mp_groups_pkey PRIMARY KEY (id_index)
);
CREATE INDEX gr_mp_groups_ddx ON gr_mp_groups(display_name);
CREATE INDEX gr_mp_groups_gdx ON gr_mp_groups(group_name);
CREATE UNIQUE INDEX gr_mp_groups_ldx ON gr_mp_groups(last_modified);
COMMENT ON TABLE gr_mp_groups IS 'This table holds groups';

COMMENT ON COLUMN gr_mp_groups.group_name IS 'Name of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.id_index IS 'This is the integer identifier for a group and foreign key to group attributes and memberships';
COMMENT ON COLUMN gr_mp_groups.display_name IS 'Display name of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.description IS 'Description of group mapped in some way';
COMMENT ON COLUMN gr_mp_groups.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_groups.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_subjects (
  subject_id_index number(12) NOT NULL, -- This is the integer identifier for a subject and foreign key to subject attributes and memberships
  subject_id varchar2(1024) NULL, -- Subject ID mapped in some way
  last_modified number(12) NOT NULL, -- Millis since 1970, will be sequential and unique
  deleted varchar2(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
  CONSTRAINT gr_mp_subjects_pkey PRIMARY KEY (subject_id_index)
);
CREATE UNIQUE INDEX gr_mp_subjects_ldx ON gr_mp_subjects(last_modified);
CREATE INDEX gr_mp_subjects_sdx ON gr_mp_subjects(subject_id);
COMMENT ON TABLE gr_mp_subjects IS 'This table holds subjects';

COMMENT ON COLUMN gr_mp_subjects.subject_id_index IS 'This is the integer identifier for a subject and foreign key to subject attributes and memberships';
COMMENT ON COLUMN gr_mp_subjects.subject_id IS 'Subject ID mapped in some way';
COMMENT ON COLUMN gr_mp_subjects.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_subjects.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_group_attributes (
  group_id_index number(12) NOT NULL, -- This is the integer identifier for a group and foreign key to groups and memberships
  attribute_name varchar2(1000) NOT NULL, -- Attribute name for attributes not in the main group table
  attribute_value varchar2(4000) NULL, -- Attribute value could be null
  last_modified number(12) NOT NULL, -- Millis since 1970, will be sequential and unique
  deleted varchar2(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
  CONSTRAINT gr_mp_group_attributes_fk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups(id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_group_attributes_idx ON gr_mp_group_attributes(group_id_index, attribute_name, standard_hash(attribute_value));
CREATE UNIQUE INDEX gr_mp_group_attributes_ldx ON gr_mp_group_attributes(last_modified);
COMMENT ON TABLE gr_mp_group_attributes IS 'This table holds group attributes which are one to one or one to many to the groups table';

COMMENT ON COLUMN gr_mp_group_attributes.group_id_index IS 'This is the integer identifier for a group and foreign key to groups and memberships';
COMMENT ON COLUMN gr_mp_group_attributes.attribute_name IS 'Attribute name for attributes not in the main group table';
COMMENT ON COLUMN gr_mp_group_attributes.attribute_value IS 'Attribute value could be null';
COMMENT ON COLUMN gr_mp_group_attributes.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_group_attributes.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_memberships (
  group_id_index number(12) NOT NULL, -- This is the foreign key to groups
  subject_id_index number(12) NOT NULL, -- This is the foreign key to subjects
  last_modified number(12) NOT NULL, -- Millis since 1970, will be sequential and unique
  deleted varchar2(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
  CONSTRAINT gr_mp_memberships_gfk FOREIGN KEY (group_id_index) REFERENCES gr_mp_groups(id_index) ON DELETE CASCADE,
  CONSTRAINT gr_mp_memberships_sfk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects(subject_id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_memberships_idx ON gr_mp_memberships(group_id_index, subject_id_index);
CREATE UNIQUE INDEX gr_mp_memberships_ldx ON gr_mp_memberships(last_modified);
COMMENT ON TABLE gr_mp_memberships IS 'This table holds memberships.  The primary key is group_id_index and subject_id_index';

COMMENT ON COLUMN gr_mp_memberships.group_id_index IS 'This is the foreign key to groups';
COMMENT ON COLUMN gr_mp_memberships.subject_id_index IS 'This is the foreign key to subjects';
COMMENT ON COLUMN gr_mp_memberships.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_memberships.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';

CREATE TABLE gr_mp_subject_attributes (
  subject_id_index number(12) NOT NULL, -- This is the integer identifier and foreign key to subjects
  attribute_name varchar2(1000) NOT NULL, -- Attribute name for attributes not in the main subject table
  attribute_value varchar2(4000) NULL, -- Attribute value could be null
  last_modified number(12) NOT NULL, -- Millis since 1970, will be sequential and unique
  deleted varchar2(1) NOT NULL, -- T or F.  Deleted rows will be removed after they have had time to be processed
  CONSTRAINT gr_mp_subject_attributes_fk FOREIGN KEY (subject_id_index) REFERENCES gr_mp_subjects(subject_id_index) ON DELETE CASCADE
 );
CREATE UNIQUE INDEX gr_mp_subject_attributes_idx ON gr_mp_subject_attributes(subject_id_index, attribute_name, standard_hash(attribute_value));
CREATE UNIQUE INDEX gr_mp_subject_attributes_ldx ON gr_mp_subject_attributes(last_modified);
COMMENT ON TABLE gr_mp_subject_attributes IS 'This table holds subject attributes which are one to one or one to many to the subjects table';

COMMENT ON COLUMN gr_mp_subject_attributes.subject_id_index IS 'This is the integer identifier and foreign key to subjects';
COMMENT ON COLUMN gr_mp_subject_attributes.attribute_name IS 'Attribute name for attributes not in the main subject table';
COMMENT ON COLUMN gr_mp_subject_attributes.attribute_value IS 'Attribute value could be null';
COMMENT ON COLUMN gr_mp_subject_attributes.last_modified IS 'Millis since 1970, will be sequential and unique';
COMMENT ON COLUMN gr_mp_subject_attributes.deleted IS 'T or F.  Deleted rows will be removed after they have had time to be processed';


Provisioner Configuration

midPoint provisioner is essentially a trimmed-down version of SQL provisioner. You only need to enter the prefix for the tables, and it assumes the rest of the part. For example gr is the prefix configured in the screenshot below and grouper assumes that the table names are going to look like gr_mp_groups, gr_mp_memberships, etc.



Design

  1. Provisioning Target is from a single-assign metadata in Grouper
  2. The provisioner could map whatever it wants to group name, display name, id_index, description
  3. id_index will generally be a numeric bigint that can be used for efficient foreign keys
  4. The group table has some common columns that can be used or not
  5. When Grouper changed a record it will edit the last_modified column which can be used as a change log
  6. Data will not be deleted at first, but rather will be set with a deleted flag
  7. The T and F are strings of size 1 not boolean database types
  8. Group attributes will generally be single valued but could be multi-valued
  9. The User Subject_Id must be something that MidPoint can use to lookup a user, hopefully will be the Grouper Subject_Id
  10. Grouper can delete deleted rows from the table after a week (configurable)
  11. Two types of work will not happen at the same millisecond
  12. Discuss with Midpoint folks the edge case of reading tables and not seeing data in time

Some edits to Chris Hyzer’s original proposal based on Software Integration Working Group Discussions

gr_mp_groups

group_name

id_index

display_name

description

last_modified

deleted

some:group34Some:GroupThis group is here becausexx1T
some:other:group45

xx2F
some:other:group247

xx3T


gr_mp_group_attributes

group_id_index

attribute_name

attribute_value

last_modified

deleted

34somethingsomeValuexx1T
45something1anotherValuexx2F
45something2aValuexx3T

gr_mp_memberships

group_id_index

subject_id_index

last_modified

deleted

4598yy1T
3487yy2F

gr_mp_subjects

subject_id_index

subject_ID

last_modified

deleted

9812345678yy1T
8798764543yy2F
8723456789yy3T

gr_mp_subject_attributes

subject_id_index

attribute_name

attribute_value

last_modified

deleted

9812345678yy1
T
8798764543yy2
F
8923456789yy3
T




Meeting notes

midpoint - Grouper Connector / Integration

Aug 23, 2022 - Preamble - During 2021 and continued into 2022 as folks have begun using the existing midpoint-Grouper connector several issues have arisen:

  • there are multiple instances of the original messaging-based connector available which is confusing
  • none of these work in an optimal fashion and some just break under load - however - some institutions have put a messaging-based connector into production
  • optimizing the existing SQL connector has been discussed as a solution
    • scripted SQL would be simple yet may not be performance
    • performant SQL may be complex
  • a decision has not yet been made as to if both types of connectors need to be supported or we can determine the ONE best pattern and agree on it and optimize issues on Both sides to support the One Connector
    • LDAP may also be a/the long term option


Current Known Stakeholders

Aug 24 - Software Integration WG call on this

  1. midPoint - Grouper End state connector pattern(s)
    1. scripted SQL would be simple yet may not be performan
    2. performant SQL may be somewhat complex
    1. LDAP may also be a/the long term option
    1. works fast and easy but only a small number of groups thus far
    1. has 4 possible solutions - overview of all of these on the call
      1. Limitations could be: running out of “space” in column for data or a user having too many groups
      2. ISU would note they have not yet run into this issue as they have a controlled/limited number of groups being passed to midpoint from Grouper
      1. have top level group and can see the whole membership tree
      2. just finishing off this work
      1. using same as Ill St and works fine with limitations they have
      2. multi-account would be great and then could consume unlimited number
      3. scripted sql - imports memberships and imports the groups (good if the multi-account won’t work)
      4. (might be an option) If we can mark the memberships as Associations then should be able to have multi-associations
    1. PaulS - could have Grouper use midPoint’s APIs
    1. there are multiple instances of the original messaging-based connector available which is confusing
    2. none of the messaging/WS-API patterns work in an optimal fashion and some just break under load - however - some institutions have put a messaging-based connector into production
    3. optimizing the existing SQL connector has been discussed as a possible solution
    4. a decision has not yet been made as to if both types of connectors need to be supported or we can determine the ONE best pattern and agree on it and optimize issues on Both sides to support the One Connector
    5. BenR: not using scripted SQL just straight SQL - Grouper provisions out to SQL and create a view for midPoint to consume - 1 row per group membership, have not been able to get the multiple account/identity feature in midPoint to work so had to use this pattern (Ethan also has this issue)
    6. Drew: number of groups per person being sent to midPoint is limited 
    7. PaulS: have a ticket into midPoint about the multi-account feature not working
    8. BenR: what about having Grouper provision directly to midPoint’s DB tables?  Evolveum likely to not want to go this way.

midPoint Grouper Integration, Previous Unicon Notes 

Problems

  • GrouperWS doesn’t give a list of all subjects and their group assignments
    • The work-around was to query GrouperWS for all groups midPoint should be concerned about and get a list of members and their assignments. This is how the 0.6, 0.7, UW Madison, and 1.x !Labs enhancements currently work
    • The above work-around has a side effect of requiring an user object template shadow query to retrieve and populate group membership (organization assignments) in midPoint. This has caused processing time issues in the past.
  • Questions of how performant the GrouperWS/REST integration is with real Grouper environments that possibly could contain millions of groups with millions of members?
    • UW Madison, Mines and others hit limits with the 0.6/0.7 version. UW Madison funded enhancements that allow filtering by attribute and adding pagination help, but we don’t know about others or if we’ll hit hard limits of REST/GrouperWS in the future. 
  • Documentation and release versions
    • No existing good documentation on connector options and how to implement with required midPoint objects such as functional library, group meta-role and user object template. 
    • Latest version(s) not released in Git
    • !Labs Enhancements and UW Madison performance enhancements need to be merged (which will require a development effort to rebuild the functionality on top of one or the other)


Goals

  • Grouper Connector + midPoint Integration that’s easy to use and understand
  • Grouper Connector + midPoint Integration that’s performant and matches all possible Grouper scenarios (millions of groups and millions of members) using one resource/connector instance if possible
  • Grouper Connector + midPoint Integration that’s testable and reliable and has less of chance to break between midPoint releases




Possible Solutions


midPoint pulling from Grouper

  1. If ConnID allows, query GrouperWS using the list of users in midPoint and get all their group memberships directly using one API call. 
    1. Group attributes
    2. User info?!? Is this doable
    3. Stem
    4. Etc.
    1. This if possible would require a total re-write of the connector
    2. If possible, we’d need to think how to filter/restrict users and groups queried or returned based on parameters we think users might need in the real world.
    3. Problem with admins, are all groups returned? Does that prevent this option from working?
    4. We shouldn’t ask for all subjects without curating first as that may not be possible or what midPoint needs/wants. 
    5. Needs more Evolveum investigation.


  1. If number 1 above doesn’t work, then is it possible to query Grouper’s subject source directly and then use that to somehow get the user’s assignments?
    1. This wasn’t fully discussed on the call, not sure how viable this potential solution is.
    2. Would likely need more Evolveum investigation.


  1. This would abandon the GrouperWS/REST integration entirely and instead focus on an ASYNC/MQ only solution
    1. Benefits: This may be the only way to be fully scalable and get away from Grouper WS/REST limitations
    2. midPoint possibly not consistent after, but eventually would be. Can midPoint handle this volume etc.
    3. Unicon to start investigation, possibly need to involve others at a later time
    1. Virginia Tech has setup a way for Grouper to send the current state to a message queue. This is then used for reconciliation
    2. We could test/do the same with midPoint.


  1. Can we use SQL to get what midPoint needs see number 1 above?
    1. This was not discussed on the call but is there a SQL way to do this? Could a view be setup?
    2. Jj at Unicon mentioned there is a view we could use, but has limitations on filtering/curating returned subjects and memberships. But this has a high potential of working reliably versus REST
    3. Main drawback would be midPoint needs to connect to Grouper DB and possibly adopters would have to customize/create their own view/SQL to curate/filter and return the results needed. More investigation/discussion into this option is needed.


  1. If all else fails and any of the above are not possible, then we’ll need to look at enhancing the user object template query/processing time.
    1. Evolveum thinks midscale improvements in 4.4+ may help
    2. Evolveum could possibly look into performance enhancements in this area if warranted


Grouper Pushing to midPoint

  1. Use MQ to push “current state” of Grouper to midPoint
  2. Use SQL provisioner in Grouper to send required info to midPoint
    1. This could be in midPoint’s db itself or a separate db setup for this purpose
    2. TBD structure schema and if the resulting structure eliminates the drawback of the user object template query we have now


Meeting 10/19/2021 Notes:


Existing REST Connector:

  1. Evolveum see if ConnID can support the option of taking a list of users (say from midPoint) and use that list to query Grouper WS. Caveat: Grouper admins may pass all groups? Is there a way to fix that in Grouper otherwise this won’t work? Alternative would querying Grouper’s subject source help in any way?


  1. Add multiple base stem ability similar to multiple attribute filter from UW Madison


  1. Add point in time functionality to UW Madison paging enhancements


  1. Change regex to use GrouperWS filtering not midPoint processing


  1. Merge !Labs and UW Madison Grouper (filter by attributes and add paging and optional hide Grouper WS request query logs) versions


  1. Evolveum possibly look into Object Template shadow query performance enhancements


  1. Documentation on existing connector, options, when to use various configs. Look at getting releases (Jars) in Git. Define which version(s) are production ready. Add documentation on object template query, group meta role and functional library and other objects and what is needed to get a baseline integration started. 


  1. Look at implementing JSON Library and clean up JSON query logic (eliminates some issues that may even appear in UW Madison/!Labs enhancements).


ASYNC Reconciliation (don’t use GrouperWS/REST - Virginia Tech):  Unicon to see if this possible/works with midPoint.