Standardized Administration Tools (SAT) Accounts Management Requirements

Proposed Requirements

Immediate Implementation


  • The database relies on three sources housed within the Odyssey postgres database. See web diagram with architecture overview, and SATAccountsDatabase for database details.
    • data from Quest and watiam is campus-wide; our design can accomodate the rest of campus if they want to use this in the future.
      • includes data for Grad Students, Faculty, Researchers, Visitors, Staff and Undergraduates - anyone with a watiam account.
  • updates triggered by timed events following the watiam updates (every 4 hours on the :30; take approximately 7 minutes to complete)
  • new schemas cover sponsorship, groups, and other SAT data.
    • sponsorship covers accounts, mail aliases, and print quotas currently.
  • SAT Accounts API - "wrapper" postgres functions to perform updates.
    • Used by CSCF for populating Active Directory. Will eventually be used by MFCF for populating their accounts.

Web UI

  • UI will currently offer one administrative access level- eg., for technical staff in CSCF and MFCF.
  • use-cases:
    • add users to "groups" corresponding to course sessions and/or other purposes such as Research Groups. Group membership grants access.
    • maintain "sponsors" who are authoritative for budgeting related to groups. Sponsors have optional "billcodes" for each of their sponsored groups.
      • UI must intuitively allow management of sponsors without repetitive updates.

Command Line

  • replacement for userinfo command

Future Work


  • using postgres triggers to kick off LDAP updates would be a useful enhancement so that updates that aren't from watiam will propagate more quickly than up-to-4-hours.
  • Grouper integration.
    • Could we use the Grouper API to pull Grouper-defined data into our database?
  • New types of sponsored resources other than accounts/email aliases/print quota.
    • What might we want to automatically set up for different groups of users?...
    • Daniel imagines: authorizing physical lab access; students in a course receiving shared git repo access per assigned group; shared LXC container under student.cs; access to any cloud system we can programmatically authorize
  • Out of scope to plan for accounts that do not have watiam correspondence. odyssey is driven by the campus user database (with unique internal IDs corresponding to them), and this would introduce another authoritative source (potentially conflicting) that hasn't been implemented or worked through.

Web UI

  • user preferences- such as an end-user's preferred/most-used bill codes
    • user preferences will allow data filtering, separate from access permissions, so that a user will see the most useful data first
    • will be necessary if MFCF is going to see different groups and sponsors than CSCF.
  • an eventual goal is self-service: people have access to the right level of permissions for them to grant resources.

Requirements Gathering notes

2019-01-31 - meeting with Adrian Pepper

Met with Adrian about his perspectives on automated and manual accounts management

  1. Isaac and clayton's tools handle automated cases but do not handle either of the following situations, which cause more work than the automated cases:
    • adding people who are auditing a course; (2-5 per term)
    • adding people who are not yet in registrar data but intend to take the course (~2 dozen per term)
    • So we need to consider undergrads as well as grads in this system.
  2. A current complexity is people not in quest (but are in watiam) who need manual recording in "Research Regions" (currently groups in AD)
    • visible from linux.cs > getent group | grep users_
    • 14 groups. list of users come from analysis of accounts_master/data/sponsors/research ...
  3. The importance of recording "sponsorship" - eg., MFCF identified tasks that turn out much easier if there are sponsors
    • (I didn't write down any examples)
    • sponsors data is organized around classes- including "fake" classes like research groups.
    • Q: are sponsors manually set? A: in the current system, sponsor of course accounts is "dean of math"...
    • (what kinds of sponsors are manually set?)
  4. many accounts are by year and we don't actually know if they left before the end... old system threw away what their last situation was?...
    • People change their year mid-term sometimes; they might be listed under CS1 and CS2 at the same time.
    • what happens on this transition from CS1 to CS2? should they have both resources, or just most recent? Overlap?
    • Could we record resources by effective-date/end-date? probably.
      • if there are triggers to automatically change the end-dates
  5. sponsored email aliases
    • do we keep doing this?
    • put this all into salt?
    • some kind of merge; don't want all aliases everywhere;
    • who is able to edit? salt is a shift from "administrative data" to "system administrative data" - possibly less easily editable by an end-user than an .aliases file on an archmater.
    • noting we run 2 mail servers; probably will keep doing so; raised question of internal-only email.
    • email .forward file created by accounts packages - how do we handle this?
  6. Adrian has written sponsors-range to turn resources files -> start/end dates
  7. potential ways forward given that we're shutting down 14.04 arch-master.
    • no 16.04 arch master. shutting down 14.04 by end of term.
    • Adrian says he could set up 18.04 arch master?...
    • accounts relies on libraries that would need to be compiled, but they can be on 18.04
    • sponsor_resources could be kludged to work on 18.04
    • Currently three xhier machines: cs-xh-admin cs-general cs-teaching

2019-03-22 - meeting with Dave Gawley

Met with Dave [update on 31 July 2019]. His summary of related data he wants to access:

Course accounts / CS-TEACHING

Changing each term, we track:

  • for each cs course
    • co-ordinator IDs
    • lecturer IDs
    • TA IDs
    • BIU (Business Income Units) - (CS Students account-names from odyssey db) - under the new budget model- funding units from the province
    • BTU (BIU Teaching Unit) - (non-CS Students account-names from odyssey db) - under the new budget model- funding units defined internal to UW, capacity of a faculty to generate operating grants, according to IAP. data might be from Quest or other UW database
    • ta-sponsored student IDs - (add period per calendar +1 or 2 days; auto-expires @ term drop deadline [???])
    • lecturer-sponsored student IDs - (add any time, end-of-term expires)
    • group (should receive extra resources, such as for project groups)
    • resources
      • disk quota
      • lab room (NOT currently tracked)
      • host list (NOT currently tracked)

  ${Course-ID}_account (account-name exists in both CS-GENERAL and CS-TEACHING)
  ${CS-course-resources}_host (list)
  ${CS-course-resources}_lab_room (list)
  ${CS-course-resources}_extra_quota (list)

CS_course_group (list)
  ${CS-course_group}_resources (CS_course_resources)
The CS-Research domain needs this data for each term:

CS_research_group (list)
  ${CS-research_group}_resources (CS_research_resources)

  ${CS-research-resources}_host (list)
  ${CS-research-resources}_lab_room (list)
  ${CS-research-resources}_extra_quota (list)

Additional to above, add concept of "group" within a course, that gets resources.

  • Using sub-groups, could give particular people higher disk quota, access to particular machines / labs.

Peoples' username unique across (uwdir + UID/GID)

  • Q: do we have accounts that don't have uwdir? A: yes:
    • course accounts don't;
    • role-based accounts such as programming contests don't

Non-course accounts / CS-GENERAL

  • for each "group" (generically defined), we track:
    • faculty co-ordinator
    • group members
    • resources (same as above).

Dave's notes:

Followup by Daniel

  • "group" is currently included in SAT design, with a "coordinator" or responsible person to reference; we need to add:
    • resources
    • other roles corresponding to TAs and lecturers? Or make these as sub-groups which inherit?

2019-03-22 - meeting with Adrian Pepper

  • concerns about composing a person from a set of groups... (I'm not sure why.)
  • we get the data from quest; says Isaac says we should be getting the data from OAT. concerned about making that transition smoothly. (Good to know; we can manage that risk).
  • Work flow question: "how do we make user account go away?"-
    • What do we do about transitional time periods. when someone transfers from CS1 to CS2- we don't want to reap their accounts/files and then create them again.
      • The current account system handles "what we want status to be right now" not "do this to this person"
      • perhaps handle transitions by ensuring system adds then removes?

2019-04-02 - meeting with Dave, Adrian, Clayton, Lawrence, and Isaac.


  1. Better understanding of current system for accounts management
  2. what we need going forward: requirements; nice-to-haves..
  3. timing for requirements

1. Better understanding of current system for accounts management

  • involves manually-maintained text state files - with expiry dates
  • automatic additions/removals from certain groups (classes)
  • which indicate sponsorship of resources for individuals and groups.
  • creates and removes unix accounts directly on linux machines (with a quota)
    • user directory created on regional masters - how? to be discovered.
  • creates and removes AD-controlled accounts
  • handles mailing lists
    • sponsors for mailing lists in CS-GENERAL
    • separate: aliases in cscf-specific
  • See: flow chart Nov 2018

2. what we need going forward: requirements; nice-to-haves..

  • post-processing software is not going away. The new system needs to kick off updates within 5 minutes.
    • see photo; tools being developed by Clayton to post-process.
  • as of a given day, what are the resources being sponsored?
  • as a minimum, we want to update for arbitrary prof's groups.
    • nice to have: prof/ta can update their own
  • security: using campus authentication as sufficient to create/remove accounts?
  • Isaac: exam management will have (modifiable) list of who is in course- we should drive this list of accounts from exam management.

3. timing for requirements

  • go-live summer 2019

2019-04-03 - Lawrence Folland / Adrian Pepper

  • related to Daniel by Lawrence.
Lawrence met with Adrian about accounts to discuss comparing the old and potential systems. The old and new systems both have RO and watiam inputs which are read-only; and the new can have groups-containing-groups which satisfy the same needs as the old.

registrar data:

  • including "Members" to source sub-files: /software/accounts-master/data/sponsors/REGISTRAR/cs includes:
Class: cs116
Description: CS 116
Load: high

Members: <.DATA/cs116

Computing: cs-teaching.cs.private
AssignTo: *MEMBERS*
Computing: canadenis.student.cs
Groups: student_cs  cs116_student
AssignTo: *MEMBERS*


  • And xhier.cs:/software/accounts-master/data/sponsors/REGISTRAR/.DATA/cs116 contains 706 lines each in the format of:

  • A question of understanding the old system: could postgres inputs generate "resources" file ouput? A: We don't know yet. Daniel to follow up with Adrian/Clayton.

2019-04-16 - Adrian Pepper

  • "could postgres inputs generate "resources" file ouput?" A: In theory, I believe yes it could, but with indeterminate, amounts of work. Non-trivial.
  • Adrian and I reviewed the white-board data which I transferred to a web diagram
  • Adrian says diagram of new system's "sponsor_computing" needs to also consider:
    1. ) resource allocations eg quota
    2. ) account deletion/creation: when student drops one CS course, and adds another, does data from registrar's office accurately reflect this?- Adrian says can't guarantee it'll be within the granularity of the system - eg., might be dropped, then added an hour later - we don't want to have their student accounts go away for an hour.
      • existing system will keep their resource allocations
        • possibly expire after a few days?
      • Retention of away students? co-op; medical leave; ...
        • old system: co-ops came back and asked for their accounts to be restored. This involves manual restore from backups.
    3. ) three xh-master machines: xh-admin , general, teaching - do these need recording in the diagram? I don't think so. Just to know these exist.

2019-04-17 - Sean Mason, Lawrence Folland, Isaac Morland, Daniel Allen on Grouper

Lawrence asked Sean to come answer our questions about the status of Grouper ( ) Summary: we might be able to leverage their "groups" rather than storing them ourselves, however we would have to write database or API hooks to automatically populate them with course data. Worth looking further, next term.
  • Grouper offers many interactions with other campus systems. Might be useful for us to use; or to supply data.
    • They have Campus Data -> Quest including:
Academic Level Affiliation -> [4A] -> [ names ]
Class Enrollment -> [1191] -> [ARTS] -> [1191.ARTS.130.005.SEM]
Faculty Affiliation -> [MAT] -> [names]
Plan Affiliation -> [Accounting and Financial Management, Honours, Co-operative Program] [plus 700 more]
Program Affiliation -> [Acc & Financial Mgt,H][plus 146 more]
    • They have Campus Data -> Workday including:
Employee Affiliations -> (Casual Employee, Employee, Faculty, Retiree, Staff, Temporary Employee)
  • Includes everyone as recorded in Workday.
  • Supervisor data is weird, because workday data has weird workarounds.
  • Grouper does NOT have course instructors from Quest, or "course coordinators". odyssey has both- Isaac would be happy to get them this data.
    • Similarly, grouper has section membership but not Isaac's concept of "who's in the combined class" - only defined by faculty members in odyssey.
  • Is it supported?
    • considered production by IST; being used by Science to maintain SPSS license access for students, as well as other units. They will work with us on our needs. Code is freely available and documented
  • How would it be used?
    • populates campus AD directly
    • can consume data from a database view (such as odyssey).
    • can read and write grouper data via API
  • What time-lags are there for updates?
    • typical 5-minute synch to AD groups (eg., confluence).
    • Might be 1-2 hours at high load (once a term they have 100,000 changes at once)
    • sailpoint consumes its data and also sees 5-minute lag (with similar 1-2 hour during high load).
Discussion of our requirements. We don't want to give instructors direct grouper access; Isaac suggests instead instructors would update their course data including course coordinators within the Exam Management System. A Grouper or CS-maintaained database would then interact with that odyssey data.

Sean has given Daniel and Isaac full access to workday and quest data, for examination.

2019-04-21 - Clayton Tucker

  • Reviewed the web diagram and made clarifications about AD.

2019-04-22 - Adrian Pepper

  • Reviewed the web diagram and made further clarifications: no direct path from Quest/watiam sources to the resource files- always via sponsors files.

2019-05-14 - Lawrence Folland

  • Proposed development process:
    1. Clarify the requirements for the new system, including all of the pieces we want to preserve from old system.
      • Q: Are we expected to continue sponsored mail aliases and lists? Are we re-implementing "sponsor_resources" code to output these (along with UID/GID registry,
      • What is the automated process for students who go on leave or co-op? Adrian says currently students leave, return, find their home-dir files are gone, and ask for them to be restored (from backups- manually).
      • Q from Lawrence: will we/can we have a replacement for the "userinfo" command? command line or gui?
        • would Adrian require "userinfo" for his tasks?
        • are there other such command line tools we need to make work?
    2. Determine data models that will make this possible; and how existing systems will interact with the data models to: a) automate what we need and b) provide manual access to the parts that are not automated.
    3. Implement web interfaces which provide all necessary manual access.

2019-07-30 - Meeting with Dave, Adrian, Robyn, Lawrence, Isaac, Daniel. Regrets from Clayton.


  1. Clarify our understanding of requirements for the new system, including all of the pieces we want to preserve from old system.
  2. Current status
  3. Steps moving forward

1. Clarify our understanding of requirements

  • Daniel shared the web diagram which reflects his current understanding of data flow and pieces to be implemented. Key starting points for discussion:
    • a database, sponsor_computing, is being populated with all data currently produced by sponsor_resources - Isaac added hooks into sponsor_resources (C code) to output existing data structures to SQL in addition to the flat text files.
    • this database is used by psql_update to generate LDIF files and populate the new AD, in production since early this year.
    • this process relies on sponsor_resources to generate the data, which will be replaced. (With what? DA)
    • this database might have all the elements sufficient to kick off the additional tasks Dave says are under development- eg., quota allocation, account creation and removal, UID/GID registry.
  • Dave reiterated his request for a database with schema access to data described on 2019-03-22 - updated in
    • And a request for comments on these requirements.
    • He would like an API for accessing the database, and methods for users to update the database.
      • minimally, administrative users in CSCF need to be able to update course and resource memberships.
      • ideally, course managers (faculty members? TA's?) can update their own courses to add special cases). Brief discussion about who should be; this is an administrative/Registrar kind of question for followup discussion.
  • Isaac proposes that course managers update special cases within the Exam Management System, which is already used by nearly all Math faculty courses. This would be a smallish addition.
  • Isaac says that producing the UID/GID registry falls under things he's already thought about and would be very simple to do in SQL.
  • Dave says we can stop being responsible for sponsored mail aliases - handing this task over to IST mail services. For followup discussion.
  • We might be able to fulfil the term-goal requirements without significantly revamping the database schema done by Isaac and used by Clayton. For followup discussion.
    • Adrian raises a concern about data representation of empty classes. Details to follow.
  • Some followup discussion I didn't capture, sorry... flying fast and furious.
  • Robyn shared these MFCF pages of documentation: and
    • and also he shared: On an xhiered accounts master machine - mfcf.math or xhier.cs (?) see these man pages:

      How things work:


      How the data are organized:

2. Current status

  • Infrastructure has a data-path that populates the new AD, as described in early April onward
  • Isaac and Clayton have done schema/database work to output all the (uncommented) detail in the sponsors files.
  • Today we found that we might not need much schema change to meet the term goal accounts-management requirements, which would be helpful, though we will still discuss deeper changes.

3. Steps moving forward

  • This meeting pointed to a number of threads to be discussed; some were opened in email threads, details to follow below.
  • Another meeting to be held ASAP.

Followup emails

  • Summary: Isaac notes the database in question is transitional; minimally it will need additions when we determine what should be added via web UI.
  • Robyn notes Adrian's question about data in context- what if a course has no members for a month? We will probably need to test this.

2019-07-31 - Dave

  • Dave clarified that the existing data in the sponsor_computing schema appears to be sufficient to produce the new AD data (not needing additional contextual data)
  • The "schema" he described yesterday is the desired end-goal of data he would like to access, perhaps from a database view. This goes further than requirements for deployment for this Fall.
  • Most of the additions are already available in OAT/ASUS.
    • BIU/BTU (reporting CS vs non-CS students) is required by the School Director to report under the new budget model. This data would be available in ASIS for each student.
  • The most time-critical piece as far as he is concerned is the UID/GID registry revamps. He asked if Isaac might be able to have this ready for production within two weeks? (The alternative is deploying new UID/GID for end-of-December).
    • I asked Isaac; Isaac thinks this is doable in under two weeks. He will follow up with Dave. A consideration is whether MFCF can also switch-over UIDs at the same time.

2019-08-02 - Dave

  • Dave views the replacement for the sponsor_resources process, is a database transformation of the data sources, into the sponsor_computing schema. And that schema is used to run all of the infrastructure processes such as new quota allocation, account generation/removal, etc.
  • To be determined by talking with Clayton/Isaac: does Clayton's psql_update rely directly on OAT data, or is it only relying on the sponsor_computing data which was pulled by the old sponsors files? That tells us more about how far along we are, since Dave thinks we're not relying on much of the old sponsor files. From what I've heard, we are.
    • 2019-08-06: Clayton says psql_update does read directly from OAT, not just the sponsor data pulled from files. Good!
  • In either case, to eliminate sponsor_resources we'd need to identify what are the pieces of sponsorship data (from new sources, such as the Exam Management UI, and a resources-management UI) that can produce a reasonable output.
  • Next week I will replace the web diagram with updates.

2019-08-08 - Meeting with Dave, Adrian, Clayton, Robyn, Lawrence, Isaac, Daniel.


  1. Reviewing diagram
  2. Steps moving forward
  3. what will we have working by end-of-term?


  • Daniel shared the web diagram and made updates to make it accurate.
  • For future integration: Grouper.
    • Could we use the Grouper API to pull Grouper-defined data into an existing schema? (math_computing? _people?)

Steps moving forward

  • Replacing 14.04 (regional masters; collecting home directory paths)
    • Robyn will look into replacing /u[0-9] with /u
  • Replacing long userids with short userids- likely could be done for beginning of Fall
    • old long userids die by attrition
  • UID/GID registry replacement - NOT for beginning of Fall
    • timing isn't for beginning of Fall, because most new accounts were already registered near beginning of term.
    • since overall process isn't changing this term, we need to use the new registry with xhier - replace uid registry program
  • new account creation: create course resources for previous/current/next term
    • instead of running commands at end-of-term, can have updates at any time
    • Clayton, Isaac to decide between eg., ( netgroup = CS100, with netgroup_1199 vs. netgroup_YYYYMMDD )

what will we deliver by end-of-term?

  • /u[0-9] -> /u
  • short userids
  • existence of new UID/GID registry - but not being used for new F2019 accounts
  • a roadmap for Fall work

2019-08-15 - Meeting with Lawrence, Isaac, Adrian, Clayton, Robyn, Daniel.


  1. Reviewing progress: a) short userids; b) UID/GID registry.
  2. Work remaining for Spring
  3. Roadmap for Fall

Reviewing progress: a) short userids; b) UID/GID registry.

a. short userids
  • accounts creation always will need to consider (old) long userids.
  • q: can we mv all long homedirs too short?
    • replacing old long homedir to symlink - one-time
  • q: have we decided where homedirs go? /u - assuming Dave agrees.
    • in 2015 a suggestion was implemented in code for mkhomes to allow /nethome/ [ 2019-10-16 : rblanders clarifies that nothing else generates these. mkhomes merely recognizes and accepts it the same as it does /u and /uN ]
  • q: can we replace long group names?
    • can we store the long data in the schema? don't have to; it's in _watiam schema.
    • we're OK with a one-time change of group-names
  • we think we have the policy figured out; and TBD who will do it. Isaac is happy to write script to do the symlink creation.
b. Isaac has started schema for UID/GID and has docs from Robyn. Feels confident about design. 1st, implement db; 2nd, will put interface onto it for access.
  • q: is there any chance a userid ever is reused?
    • possibly staff userids get reused
    • need to account for merged userids where old userid gets reused
  • looking at what tools directly call the uid/gid code, versus uidregistry program. may be simple.

Work remaining for Spring

  • Adrian putting expiry dates into registrar data- to have a better picture for data we're seeing
  • UID/GID registry: Isaac will be ready to demo UID/GID database, possibly not uidregister code.
    • Isaac and Adrian will look at existing code on idregistry.math
  • Daniel, Isaac, and Clayton will have another discussion of new schema.

  • Answering q of courses with no people in them?...
    • if a course (requesting extra resources/quota) has no members, do those extra resources persist?
      • existing tables record what are in the resource files - not the final tables.
      • we need new tables to record per-course information. Then revise tools to use these.
    • q: does sponsor_resources properly handle user-specified odd directories? Isaac can look at code to answer this.

Roadmap for Fall

    • deferred two weeks when all are back.

2019-09-26 Database Schema: to be discussed between Daniel and Isaac.

  • Draft description found in SATAccountsDatabase. Isaac has not had time to discuss; he and Daniel agree that Daniel will proceed with his draft, to be updated as necessary.

2019-10-11 and 2019-10-15 Database Schema: discussion between Daniel and Robyn

  • Robyn has helped clear up Daniel's understanding of the existing schema, both for: how some of the fields are used for both CS and MFCF, and what fields are used in MFCF but not CSCF. Details recorded in SATAccountsDatabase (particularly this diff )

2019-10-17 - Meeting with Dave, Lawrence, Lori, Robyn, Isaac, Adrian, Clayton, Daniel.


  1. Reviewing progress: a) short userids (Isaac/Adrian); b) UID/GID registry (Isaac) c) database (Daniel/Isaac)
  2. Roadmap for Fall

Reviewing progress

a. short userids (Isaac/Adrian);
  • we handled most of setup of short userids.
  • short group names to match - and home directory name matches the short name.
  • will be handled by UID/GID registry.

b. UID/GID registry (Isaac)

  • started schema;
  • has conceptualized a new implementation that closely matches what we currently do, with an expectation of adding new stuff later.
  • RFC 2307 and extensions- covers AD extensions that Dave says we should include initially.
  • Isaac will review these and propose a solution. Daniel to talk to Isaac next week to see if he's started.

c. database (Daniel/Isaac)

Roadmap for Fall

  • we should have a web demo within a few weeks - to cover the user interface, and database as it stands.
  • we set up accounts in November; not going to have new system for November.
  • Can development proceed before we have the UID/GID registry finished?
    • Dave needs new accounts API, plus data (not just API).
    • at a cutover-point ldif code needs to switch over to new code.
    • Clarification to the accounts process document: UID/GID tables will be part of the accounts schema; they will be generated when data is available, rather than when it's requested for the user to first log in.
  • Dave wants this complete by end of February - by 15th for testing.
    • would like API and test data by Jan 2.
  • Next meeting in two weeks: Daniel to demo UI; and have details for Dave about API (likely JSON).
    • will also invite Lori Suess.

2019-10-21 Meeting with Caroline Kierstead

While doing an off-campus project, Daniel was sitting next to Caroline Kierstead and he asked her about about accounts management from her perspective.

These kinds of users need to be manually added to the system:

  • sessional instructors
  • grads (TAs or Research Assistants)
  • ISA's (co-op students working on the course).

Who should be making these decisions?

  • depends on the course; primarily depends on the instructor.
  • ISC's or instructors might be making the additions

Who should we talk with next, from an administrative end-user's perspective? Byron Weber Becker; and Gang Lu (Carolyn's replacement; ISG Undergraduate Operations Coordinator).

2019-11-07 - Meeting with Dave, Lori P, Robyn, Lori S, Isaac, Adrian, Anoushka, Daniel.


  1. Reviewing UI
  2. Recording future directions

UI Questions and Feedback

  • Q: What happens at group end date, to members of the group?
    • They are still in the database, and listed in the UI; the API will report to Dave's code on active groups, not including inactive, so it can remove access when the group becomes inactive.

  • Q: Can we delegate authority to change particular data, to a group of admins? Follow up with Dave.
    • Yes, the database can support this, though it's not certain the business-logic and UI will have this complete for January.
    • And yes we will have a path for upgrading the code that doesn't take down the production system...
  • Q: Dave wants multiple sponsors- eg., two advisors, and the second one will remain if the first one is removed.
    • A group can only have one sponsor. However, this can be done in effect by having a group for Prof A, and a group for Prof B, both which contain the student; and they have a parent group, which is the one that we depend on for denoting resources.
  • Q: by Daniel: Are sponsorships always explicit, or do we sometimes want them inherited from groups?
    • Probably sometimes inherited. Will discuss further.
  • UI improvement: Groups: instead of +, "add new member"
  • UI concern by Lori S: not very straightforward workflow to her.
    • How can we support regular workflows? We walked through adding an arriving Faculty Member to a Group; but it's not clear to her where she'd start. We didn't try and walk through a more complicated example, such as giving the arriving Faculty Member proper sponsorships.
    • A basic answer to workflows would be a help page with instructions and links to the appropriate page(s). There aren't hints to the order of pages. (There's more than one way to do it).
    • A more complete answer would be application "wizards" to walk through common processes. A list of these starting-points could be put on the front page.
    • currently Lori trains co-op students to operate the existing system; spends three weeks teaching them, and then they are able to do it in the proper ways. There isn't a written training document.
  • Basic improvements to the UI:
    • hover text;
    • pulldowns and/or validation, not free-field text for text that should be constrained.

Future features

  • user preferences- preferred/most-used bill codes, etc.
    • to offer a user filter for data, so that user won't see more than they should.
    • will be necessary if MFCF is going to see different groups and sponsors than CSCF.
      • I've written up how to do this for groups - displaying based on a person's Primary Appointment and that Appointment's default Group.
      • For resources and sponsors, do we need to add a field to constrain what group "owns" the records?
  • a goal is self-service: people have access to the right level of permissions for them to grant resources.
    • see Dave's first question about delegating authority.

Next steps

  • Continuing work from last group meeting (see 2019-10-17) by Adrian, Isaac, Daniel
  • UI work by Anoushka
  • API work by Daniel and Isaac
  • testing a more complete UI with a wider range of end-users - Anoushka and Daniel
  • interviews to determine who can make the decisions (see 2019-10-21)

2019-11-29 - Meeting with Dave, Lori, Robyn, Isaac, Adrian, Clayton, Daniel.


  1. Reviewing progress: a) UID/GID registry (Isaac) b) short userids (Isaac/Adrian); c) database (Daniel/Isaac) d) API (Daniel/Dave)
  2. Plan for Fall
  3. Plan for Spring

Reviewing progress

a. UID/GID registry (Isaac)
  • Isaac has schema that exactly reproduces UID registry for general.math - normalized
    • analysis shows no data has weird incompatibilities
    • next step: interface, for lookups; looking at programs that refer to registry, for exactly what needs changing.
    • then, can change to use our new numbering scheme under the hood.
  • Isaac can concentrate on this after next Friday.

b. short userids (Isaac/Adrian)

  • new UID registry has long. Isaac is looking into the impact of short, when looking at code.
  • possibly: when requesting ID by number, return short/long? Nope, we don't do that currently.
  • other direction, by name, can accept short/long...
  • Adrian notes registry currently returns long. (yes)
  • group search for short returns unknown; long returns correct.
  • currently there are existing short groups that are ambiguous truncations

c. database (Daniel/Isaac)

  • Lori question: do we need separate AD from these databases?... yes. it's not separate; it's subordinate.
    • all one data-pool
  • Dave question: where are friendly-name, shell, homedir kept? homedir is important to be included in the database
    • shell and homedir not centralized, dependent on "realm" or "region"
    • at any time do we want LDAP rebuilt from data-pool?
    • Clayton and Isaac to discuss.
  • where do we store the list of realms/domains?
    • our "forest" is in AD, can we put it in our data-pool.

d. API (Daniel/Dave)

  • Anthony Brennan arriving Monday for a 5 month contract; will be available 1 week later, Dave hopes (one higher priority). Max. 2 projects.
  • Dave wants to defer API discussion until Anthony's here, so he's on-board.
  • API and test data: deadline: by Jan 2.

Plan for Fall

  • UID/GID registry completion
  • short userids
  • database completion (including stored procedures and triggers)
  • API complete (including stored procedures)
  • UI: hopefully with end-user privileges; possibly with only global administrator privileges
    • Interviews with end-users (Anoushka/Daniel)

Plan for Spring

  • Dave would like roadmap for how we get from Fall to done.
    • end-to-end how create users? - on whiteboard/sticky notes
    • written statement already for what data do we need to authorize an account?
      • What can we delegate?
    • Math: changing plan. needs design; they are not running AD, will be running salt or grouper.
      • Math has an accounts request webform.
    • with the end-to-end design, get admin check-off in followup.
  • [from previous meeting] Dave wants this complete by end of February - by 15th for testing.
    • this requires: API and test data by Jan 2.
    • needs to build: new quota allocation/account creation and removal; sponsored mail aliases; command-line tools (?); new version of AD tools that uses new database
  • Daniel asked Isaac: how can we satisfy Dave's request for "as-needed" updates, rather than on a timed schedule?
    • this would require completely revamping the data process from IST.
    • they currently provide _quest updates once a day. Isaac is in conversation with them about increasing to 3x/day, which would make a big difference.
    • _watiam updates are ?x/day but we don't know a new user is in courses until we get the next morning's _quest update.
    • we could know about name changes more quickly.
    • Isaac's proposal: our "trigger" is running after the data gets updated each morning. If that frequency updates, we automatically run more frequently.

2019-12-06 - Adrian

Question: is next week's meeting to discuss end-to-end how we currently create users, or how we hope to create users in the new system?
  • Our motivation is providing Dave with what he needs to produce the latter. We need to have the former, first, and that's not fully documented. So we are starting with discussing the current system end-to-end.

There are portions of the old system that we haven't discussed much (or at all).

  1. Adrian says he currently turns Undergrad Operations data into sponsor text files for sessionals (teaching).
  2. Grad data is turned into sponsor text files for TAs
    • via a script developed by Isaac and Adrian which queries grad office tables (psql -h postgres.odyssey -d odyssey ; set search_path to _instruct, _identity, _quest, public; [...]
    • produces sponsors file: cs-xh-admin:/software/accounts-master/data/sponsors/CLASSES/automatic/TA-cs2001
  3. Some sponsorship data is NOT in watIAM, such as role accounts (cs-pwset).
    • those are maintained in the "userids" database: cs-xh-admin:/software/accounts-userids/data
            cs-xh-admin:/software/accounts-userids/data# grep cs-pwset Userids
            x:cs-pwset:For Web password:2004/Oct/21 - UWdir
  • all entries where the second character is ":" are maintained outside of watiam, such as "x:" (the nomenclature is documented in a README; there are many formats).

We haven't discussed these non-watiam accounts. MFCF has intentionally stopped using them.

  • If we are going to decide to put them into watiam, we need to implement a process for that.
  • Adrian notes: "Compared to a few years ago, the creation in WatIAM of accounts with a uwuserid corresponding to a userid we are currently using (known to be using) has apparently become troublesome and somewhat time-consuming (for IST). E.g. a recent example of userid "isg"... /RT#1019570 /RT#1020383

2019-12-06 - Gang Lu

I called Gang to ask about her interactions with accounts creation.
  • she emails accounts@cs concerning sessionals and new courses. She updates SessionalHiringArchive to include a list of names and the courses they are teaching. Both (email and twiki) updates are incremental as she receives information.
    • Adrian waits until the list is complete to do them as a batch.
    • where does her list of sessionals come from? Her group goes through the hiring process, and for the successful hires, that information is passed along to HR. Tracy D is in contact with HR, but often HR doesn't tell us when they have created WatIAM accounts. With the new WatIAM creation process, HR sends the new hire a registration-link, and they complete their information, and then it falls into a black hole as far as she's concerned.

In general does Gang know WatIAM IDs for these hires?

  1. if they are recent graduates, yes.
  2. if they are brand new, she knows that they need them (via HR).
  3. others are grey areas; some have them already because they are alums.

Are there other accounts setup we should know about? I saw in RT that she asked Nick for course accounts creation?

  • She first emails accounts@cs, and eventually if needed she reaches out to other CSCFers such as Nick.

Are there other things we should know?

  • She wishes we could find out when someone's WatIAM accounts are created by HR; rather than Tracy having to bug them for it. She says recently a new hire emailed her a screen-shot as proof that they did have an account (that she didn't know about).

2019-12-12 - Meeting with Dave, Robyn, Lori S., Isaac, Adrian, Clayton, Daniel.


  1. End-to-end walk through: how do we create users?
    • MFCF has an accounts-request webform. Is there a written statement for what data does CS need to authorize an account? No.
  2. Reviewing progress: a) UID/GID registry (Isaac) b) short userids (Isaac/Adrian); c) database (Daniel/Isaac) d) API (Daniel/Dave)

End-to-end walkthrough

Whiteboards filled, with existing system. Daniel to transfer to something we can potentially edit and markup collaboratively. Identified a number of questions to answer.
  • Can we eliminate reliance on non-watiam "userids" accounts, moving them into watiam?
    • We are in agreement we would like to eliminate that database. MFCF doesn't use them any more. Dave suggests we consider designing workarounds (eg., for visiting faculty who want a userid which cannot be a watiam account. He has seen three, over the years.) Daniel says watiam existence is fairly fundamental to our design- this would be a big deal.
    • Can we address this ASAP? (Dave?)
  • What validation happens from end-to-end; identifying errors that need to be manually fixed? MFCF relies on regular reports of errors. I believe CSCF debugs errors upon running sponsorships. (Adrian?)
  • What auditing/fixing do course-accounts need? Adrian cautions that we do a lot of auditing; we don't know if the course-account owners rely on this, or wouldn't care. These are questions for Omar. (Adrian/..Daniel?)
  • How do we use "regions"? There are various definitions in use. (discuss as a group)
  • Does CSCF need to consider machines that don't use A/D? Dave notes that Lori does not use A/D for accounts on all cluster machines; typically just for the head nodes (due to network fragility). (Dave?)
  • in MFCF, what precisely do "Jim's scripts" do? (Robyn to investigate.)

Reviewing Progress

a. UID/GID registry (Isaac)
  • Isaac demo'd a schema with custom functions; these replicate the current ID registry functions
  • next step: looking at programs that refer to registry, including reviewing source-code on capo/(etc?) and talking to Robyn and Adrian. (soon).
  • following: depending on the programs that need to continue, either tweaking their source-code and deploying; or replacing.
  • then: can change to use our new numbering scheme under the hood.

We ran out of time in this meeting for discussing other items. Daniel to schedule targeted follow-up meetings soon.

2020-01-23 - Meeting with Dave, Isaac, Daniel

  • To discuss: API and inputs for Dave's tools; timeline.


  • Dave's initial request for "views" on data
  • Isaac proposes postgres access for queries, and stored procedures, which make queries and updates.
  • We've established that Clayton's code is essentially doing all of this, already. Isaac thinks we only have minor work to go?
  • What does Dave need?
    • White board photo
    • SAT API will be read by scripts on linux.cscf, kicking off scripts which send data to the new 2019 CS Windows server, which will send data to LXC client-machines on a new Ubuntu 20.04 server (yet to be setup, likely Feb 1-Feb 15). That server will emulate both general.cs and student.cs for testing.
      • the LDIFs for the new Windows server will also be compared to the LDIFs of the current LDAP forest, to validate that new = old.
      • This part of the diagram is only an overview; further detail/correction is a good idea, but we didn't go into those details in that 1-hour meeting.
    • What does CSCF need, other than the AD-generation?
      • setting account quotas: will be supplied by SAT/accounts/"groups" - also read via database queries.
      • setting email aliases in general.cs and student.cs - under discussion by managers; might not be needed. We will assume we will implement it.
      • on the photo the dotted lines are the data path for setting account quotas and email aliases: data from odyssey -> linux.cscf -> lxc containers for testing general.cs and student.cs
      • We are NOT setting print quotas, despite that being in the spec. Dave says these are per-machine only.
      • Future potential for adding new types of sponsored resources other than disk quota/email aliases/print quota. What do we want to automatically set up for different groups of users?... (Daniel imagines: automatic grouper updates which authorize physical lab access; students in a course receiving shared git repo access per assigned group; shared LXC container under student.cs; access to any cloud system we can programmatically authorize)
      • Re-iterated in the meeting: it's currently out of scope to plan for accounts that do not have watiam correspondence. odyssey is driven by the campus user database (with unique internal IDs corresponding to them), and this would introduce another authoritative source (potentially conflicting) that hasn't been implemented or worked through.
        • existing process: picking uwdir IDs is handled by watiam onboarding. Dave's example of "CS Director asks for a visiting researcher's account at 7pm": answer is: self-service watiam for the researcher, which gets them as far as an auto-created account, we think. They won't get a corresponding CS account until an authorized SAT user adds them to an appropriate group.
        • creating campus-wide email aliases is handled by watiam.
        • Dave asked about Lori P's cluster accounts and off-campus users. Not clear how those accounts relate to campus accounts- not clear how odyssey can see.


  • Two kinds: the timed events for Isaac's watiam updates, and via Postgres.
    • timed events for watiam updates: kick off every 4 hours, on the :30. (and take approx. 7 minutes to complete). Isaac can add a shell stanza that kicks off whatever Dave needs.
      • We agree we will use this for now. It covers watiam updates, as well as for UI changes which lead to group and quota updates (up to 4 hour lag)
    • vs. postgres triggers can kick off stored procedures whenever any data updates.
      • they can also send a "notify" event, which is used by an external listener to execute code -
      • we agree that using postgres triggers to kick off LDAP updates is out of scope for now. But they would be a useful enhancement so updates that aren't watiam updates will propagate more quickly than up-to-4-hours.


  • last month we said the following:
    • Dave wants this complete by end of February - Feb 15th for testing.
    • this requires: API and test data by Jan 2.
    • needs to build: new quota allocation/account creation and removal; sponsored mail aliases; command-line tools (?); new version of AD tools that uses new database
  • We're behind on having a fully vetted API and test data.
  • Isaac and I will work toward having the API and test data in the next few weeks AKA approx Feb 7.
    • Dave wants to have his test environment operational for Feb 15.

Followup with Lori P.

  • How are we involving MFCF in these discussions? Robyn L. should be kept up-to-date.
    • This time, Daniel to do followup with Robyn independently.
    • In the future Daniel to inform Robyn about the meeting in advance, as optional-invite.


Meeting with Isaac, Daniel. Following a lot of work done in ticket /RT#1015583.

Agenda: review and revise draft database schema.

Sponsor Computing related questions:

  • If Clayton determines the "region" from sponsor_computing.computing_name it should be a lookup table, not fill-in text.
  • What part defines whether a unix group should be created?
    • It's definitely not "all SAT Groups become unix groups"
    • It's possibly not "all sponsored SAT Groups become unix groups."
    • So there might need to be a field in the group definition table

[ jumping forward to related topic:]

  • Isaac points out that group_group is best limited to general group definition, and another table is best used for the specifics of sponsorship (including the above, group sponsorship unix group)
    • Which answers an open question, "what should be the default sponsorship for new courses imported by quest?" - they shouldn't have a sponsorship yet; sponsorship details are separate and subsequent.
    • Should group sponsorship have a settable expiration extension time? Eg., person should lose access immediately, but files are not deleted for n months. We know this delay is a spec requirement, but where is the time set?
      • All else being equal, account expiration and removal parameters belong in the accounts expiration software (Dave's group), not the database.
      • Are unix groups addition/removal a different case? I suggest that no, removal can be set by the active-as-of end date, rather than a separate customizable parameter in the database for amount of time.

Group characterization.

  • Change the group_group table to add a "source" column, possible values:
    • Odyssey (automatically imported by script)
    • Manual (updated in UI or CLI)
    • Computed (this is new; discussed in depth):

Computed groups could be unions and intersections of other groups.

  • This may come up in some more complex situations, such as "this research group includes that research group minus this subset"
  • I can see the usefulness, but I'd be concerned about complexity of managing these. Noting end-users who I heard say Grouper's interface was too cluttered/complicated for our account-management, and I think they might have been confused with defining groups. (Which is a fair enough complaint; they are complicated).
  • My schema's proposed hierarchical trees are essentially "parent is union of its children"; but not allowing cyclic graph.
  • I suggested we don't add the complexity of computed at this stage, and could be added later when we had a use-case. Hierarchical meets all the identified use-cases.

Adding a "source" column neatly solves the question of "how do we manage/merge SAT groups and odyssey-supplied groups"?

  • the import process will update/add/delete those labeled "Odyssey"
  • the UI will show both; but only allow edits on "Manual"

Isaac will proceed with writing up another schema version.


Meeting with Isaac, Daniel.

Following Isaac's work on the schema mostly recorded in /RT#910469

Prep for tomorrow's project meeting. Agenda: finalizing schema; planning for data loading.

  • implementing hierarchical groups. Agreed: implement "union" now, for hierarchical groups; other kinds, later.
  • he will share v1.0 schema later today; including sponsor_automatic but leaving off the sponsor booleans that were still uncertain (nocharge and nosubsidy).
    • we can later re-add those fields if needed
    • nocharge might be redunant
    • if in the future, MFCF needs to record subsidies, we can make specifications that meet their current needs, rather than whatever the needs were when the sponsorship database was originally defined.

  • planning for data loading
    • Dave will have a VM "" ready later today /RT#1080931.
    • Isaac will do some work today on 1st-draft queries to load data
    • if we don't have 100% coverage of old groups/classes data, we can work with that in upcoming weeks while Isaac is away.

Other items:

  • future work: making account number into a validated field.
  • current work: includes idregistry schema. So Clayton can request new idregistry values without an external tool.


Meeting with Dave, Robyn, Lori P., Isaac, Adrian, Clayton, Daniel.


  • SAT Accounts Project overall progress?
  • Status report and next steps
  • Timeline

SAT Accounts Project overall progress?

University funding freeze --> no co-op for Daniel this summer --> web UI won't be delivered until Fall.
  • Summer: will deliver command-line tools for querying and manipulating database
  • Summer: will deliver necessary stored procedures and views for technical client needs.
  • Fall: will deliver web UI to meet broader technical needs

Database is proceeding, though not as fast as we'd expected at start of year.

Status report and next steps

  • schema: will be complete as of today.
  • data import: a draft will be complete as of today.
  • stored procedures: idregistry = done. Other stored procedures for data updates are not done yet, but will follow.
  • Isaac is returning on the 28th but will hand over database details to Daniel today.
  • Isaac will coordinate administrative database permissions with Daniel; Daniel to set up access for others while Isaac is away.
    • Dave requests a read-only account

  • Q: will this include MFCF data? Not before end-of-term.
    • Robyn notes that Jim has written code that consumes the flat-files that will need re-implementing.
    • Daniel and Robyn will discuss and document what MFCF needs. (Adrian says there are differences within the accounts code; Robyn suspects it is a loosening of requirements so that's not an issue for the new database).

  • since we have stored procedures for idregistry, any idregistry replacement scripts can be fully implemented now
    • Daniel to set a meeting with ? from Dave's group to discuss implementation.
  • other front-end (admin user) scripts can be spec'd and v1 written while Isaac is away, for testing and finding missing pieces; and Isaac will provide stored procedures after he's back.
  • Qs about queries
    • process for updating resources for TAs, tutors, lecturers? will be command-line. Daniel to discuss the current needs, with Adrian.
      • Isaac notes that lots of updates are automatic via odyssey; ISAs and tutors aren't automatic yet.
    • can we query who has taught a course for the last 5 years? Yes. What courses a student has taken? Yes. Anything in odyssey could be constructed as a query...
    • do we handle ambiguous/conflcting information such as:
      • "student added as an exception; then Quest updates automatically." - yes.
      • "faculty member A requests privileges for a grad student; faculty member B requests other (or overlapping) privileges for the same student" - yes.
      • "faculty member A grants machine privileges for all grads in their research group; faculty member B restricts privileges for their personal workstation" - will need looking into; not yet.
        • a) could be done with separate regions, though it's not entirely clear how we will support overlapping regions.
        • b) current deliverables do not offer subtraction-from-groups, though that is an eventual feature that's within the realm of automatic update.

  • deployment timeline? Dave suggests betwen 18th and 31st, since courses are done on 17th. Let's say Tuesday Aug 25th. OK!


  • database: Done July 17 (Isaac/Daniel)
  • dev AD forest server: Done < July 17 (Clayton)
  • dev AD client server : July 17ish (Dave/Anthony/Clayton)
  • data import, enough for Dave's group to proceed with developing tools: ASAP / either today or week of July 20-24 (Daniel)
  • queries for Clayton to programatically query database: July 20-25 (Daniel, with Clayton, possibly Anthony)
  • stored procedures to update database: start week of July 27 (Isaac)
  • maintenance tools to update database: start week of July 20; may depend on stored procedures. Through mid-August. (? Dave's group / Daniel)
  • discussion of MFCF database needs: July 20-25 (Robyn/Daniel)
  • test that the database is complete: start week of July 20, through mid-August (Clayton / Daniel / Isaac)
  • production-ready / flip the switch on the new AD: August 25th at the latest


Meeting with Isaac, Daniel.

Agenda: What is top criticality? What are we blockers on?

  • process for auto-populating groups from class data
  • data import on S2020 accounts so Clayton can do ldif comparisons
  • rest of existing data import, for the rest of our requirements

Today Isaac is working on:

  • process for auto-populating groups from class data
  • re-deploying test database with more data (currently MIA due to his testing work)
  • being clear on the three queries "what users need to exist in the region; what groups need to exist; which users are in which groups"
  • possibly followup with Clayton about writing queries.

Today Daniel will work on:

  • process for converting data to populate sponsor_unixgroup and sponsor_login tables
  • schedule a group meeting- ideally for Wednesday afternoon, assuming people are available.

Next week we need to:

  • supply stored procedures, including to manually add a person to the database
  • confirm with Dave's group that we are supplying the necessary data/stored procedures/views for maintenance tools
    • including or excluding imported data for quotas? Depending on import, that may add complexity.
    • look at 'userinfo' command and figure out what pieces of data are missing -
      • (noting we will NOT be providing a precise replacement; hopefully there aren't maintenance scripts that try and parse userinfo output).
  • document what is not auto-importing from old data; as a precursor to discussing fallback procedures:
    • can we use the old system to do mail aliases? (this is mostly static data, since we think changes are largely made from include-files)
    • more generally if our best efforts at auto-importing data aren't good enough or we haven't given INF enough time to code and debug, is the old system an option for Fall?

-- DanielAllen - 2019-03-25

Topic attachments
I Attachment History Action Size Date Who Comment
JPEGjpg 2020-01-23-SAT-API-consumers-for-accounts.jpg r1 manage 1543.2 K 2020-01-28 - 12:08 DanielAllen white-board: how API will be used for CS accounts generation
JPEGjpg IMG_7091_IMG_7092.jpg r1 manage 2123.1 K 2019-12-12 - 17:09 DanielAllen white-board: current accounts creation
JPEGjpg IMG_7093.JPG r1 manage 1574.9 K 2019-12-12 - 17:09 DanielAllen white-board: current accounts creation
JPEGjpg IMG_7094_IMG_7095.jpg r1 manage 2467.9 K 2019-12-12 - 17:09 DanielAllen white-board: current accounts creation
JPEGjpg IMG_7096.JPG r1 manage 1320.3 K 2019-12-12 - 17:09 DanielAllen white-board: current accounts creation
Edit | Attach | Watch | Print version | History: r43 < r42 < r41 < r40 < r39 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r43 - 2020-07-31 - DanielAllen
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback