Standardized Administration Tools (SAT) Accounts API

This is a description of the Accounts API.


All data is housed within the Odyssey database. See web diagram with architecture overview. An API will provide a programmatic mechanism for database access and updates.


As discussed with Dave, the requirements are to be able to access and update accounts data programmatically. The current understanding is that it will be via an API "wrapper" around postgres functions which perform the actions.

  • Updates from Quest happen once a day, overnight.
  • Updates from watiam every 4 hours on the :30; take approximately 7 minutes to complete.
  • Updates from user interfaces are recorded immediately in the database.

The API-calling code will query for updated lists of users and resources. Meeting notes from 2020-01-23 reflect that Infrastructure will do these queries every 4 hours. (The logical time is right before the watiam updates, to provide the largest window of time for manual updates via user interfaces)

(An enhancement for later is making these happen on-demand via a database-driven trigger; see above notes on 2020-01-23 for details.)

Implementation Discussion

Let's start with creation and removal of: user accounts, user access via AD, user access to a course via unix group, and user quota.

User Account Creation

This process can run in each region we are managing, to return userids who should have accounts created.

  • This can be done by a query to group_member_current joined with sponsor_login (selecting for a particular region in sponsor_login); this will return user ids who should have access to that region.
    • This list of user IDs can be used with a diff of the results from the last run.
    • Account additions can be batch run for each region. The exact data required is TBD.

User Account Removal

  • A list for removal can be done by a query to group_member for accounts whose member_active date expired n days/weeks ago.
  • Remark: we propose that (for all region accounts we know of) it is sufficient to check the group for "is a student" - we don't need to know whether they are here or away. If they are a student, their account should exist.

User access via AD

This process can be run for each region we are managing, to return userids who should have active access. This depends on current membership in groups, and being removed from a group should immediately remove access, unlike account deletion which has a grace period.

From /RT#1015583 and RT#910469... Clayton's AD code is found in ubuntu1804-104:/u4/ctucker/Netgroups/cscf_domain_update_psql.v45 There are three queries against the _math_computing schema.


1) The call to sponsor_computing is replaced by a query on group_member_current joined with sponsor_login which returns:

  • group_id (a foreign reference to group_group which includes group name)
  • person_id (a foreign reference to _identity.person_person)
  • instead of computing_starts and computing_expires, there is an SQL date range named member_active

2) sponsor_computing_group is replaced by sponsor_unixgroup.

3) sponsor_userinfo is a map of userid to long name; the purpose is simply to do name lookups, which is simply a query from _watiam.watiam_entry.

User access to a Unix Group

If you wanted to find what users should be added to a particular group:
  • Unix group access can be reported by a query to group_member_current joined with sponsor_unixgroup (selecting for a particular region in sponsor_unixgroup); this will return user ids who should have that group access in that region.

If you wanted to find what groups should be available to a particular user, this is item 2) in the AD item above.


  • All courses that are sponsored under an "admin" (currently CSCF or MFCF) will automatically have unix groups created for them.
  • Other automated unix groups can be added; and we hope to automatically import all existing unix group data as well.
  • Individuals' accounts can automatically get a unix group name in the system, or perhaps that isn't necessary because the Active Directory system can create them automatically. TO BE DETERMINED.
  • New groups will require specifying the unix group name, since the SAT "group" name doeesn't automatically match the desired unix group name.

User quota:

Essentially the same question as unix groups, but with sponsor_diskquota instead of sponsor_unixgroup


-- DanielAllen - 2019-10-30
Edit | Attach | Watch | Print version | History: r5 < r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r5 - 2020-11-30 - DanielAllen
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2021 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback