New UDB

Like the Old UDB, the New UDB is a user database providing Linux account information to hosts and workstations. It also implements a hierarchical class-membership structure which both allows users' roles in the school to be established, and also controls user access to certain resources. Additionally, the same database hosting its tables also contains tables supporting the operation of CSE's mlalias mail-forwarding and mailing-lists system. Both systems' database tables are loosely coupled.

The New UDB:

1. Runs primarily on the host bandleader,
2. Uses PostgreSQL for its database
3. Uses an OpenLDAP server on the same server to "feed" hosts and workstations with account information (UID, GID, etc.). The contents of the OpenLDAP server's database are synch'd with the contents of the PostgreSQL database multiple times per hour

Operations

To access the PostgreSQL database:

1. Log on to bandleader as root,
2. su - ldapudb (ignore error/warning about findmnt)
3. psql xcse (xcse is the database containing both the UDB and the mlalias tables)

Database tables

The listing below shows the tables in the xcse database, namely the database containing the tables supporting the UDB and mlalias.

• Red is UDB
• Green is mlalias
• Gray tables are unused. These are left over from an attempt to use an LDAP translator to respond to client LDAP requests by directly querying the database, instead of using OpenLDAP (as we finally did due to querying the database being way too slow, most notably due to the recursion required for class membership lookups).

bandleader % psql xcse
psql (9.4.6)
Type "help" for help.

xcse=# \dt
                 List of relations
 Schema |          Name          | Type  |  Owner   
--------+------------------------+-------+----------
 public | group_member_table     | table | postgres
 public | group_membership_list  | table | postgres
 public | group_table            | table | postgres
 public | ldap_all_names_by_user | table | ldapif
 public | ldap_classes_by_user   | table | ldapif
 public | ldap_groups_by_user    | table | ldapif
 public | ldap_user_table        | table | ldapif
 public | ldap_users_by_class    | table | ldapif
 public | ldap_users_by_group    | table | ldapif
 public | mlalias_address        | table | postgres
 public | mlalias_control        | table | postgres
 public | mlalias_owner          | table | postgres
 public | mlalias_sender         | table | postgres
 public | mlalias_user_external  | table | ldapudb
 public | nisnetgroup_table      | table | postgres
 public | user_alias_table       | table | postgres
 public | user_group_table       | table | postgres
 public | user_table             | table | postgres
(18 rows)

user_table

This table loosely corresponds to /etc/passwd. userid is the user's UID and username is typically their zID in the case of students and recent hires.

Gray indicates a column is no longer in use and is no longer updated.

xcse=# \d user_table
                            Table "public.user_table"
    Column     |          Type           |               Modifiers                
---------------+-------------------------+----------------------------------------
 userid        | integer                 | not null
 username      | character varying(32)   | not null
 password      | character varying(64)   | 
 groupid       | integer                 | not null
 homedirectory | character varying(128)  | not null
 loginshell    | character varying(32)   | not null
 registration  | character varying(8)    | 
 dob           | date                    | 
 emailforward  | character varying(254)  | 
 yellowform    | date                    | 
 otherhomes    | character varying(8192) | not null default ::character varying

group_table

This table, along with user_group_table (below), loosely corresponds to /etc/group AND is extended to also identify classes.

xcse=# \d group_table
          Table "public.group_table"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 groupid   | integer               | not null
 groupname | character varying(32) | not null
 grouptype | group_type            | not null

• groupid is unique
• group-type is either class or [Unix] group. A group may appear as a file system object group owner, but a class may not

user_group_table

This table establishes the direct (or primary) membership of users in groups and classes. Note that each individual membership has a date range for which the membership is effective. A user may be a member of a particular class or group over multiple date ranges by having multiple membership records.

xcse=# \d user_group_table
               Table "public.user_group_table"
   Column   |  Type   |              Modifiers               
------------+---------+--------------------------------------
 userid     | integer | not null
 ismemberof | integer | not null
 startdate  | date    | not null default ('now'::text)::date
 enddate    | date    | not null default '9999-12-31'::date

group_member_table

This table establishes membership of classes/groups in other classes/groups, i.e., nesting.

xcse=# \d group_member_table
              Table "public.group_member_table"
   Column   |  Type   |              Modifiers               
------------+---------+--------------------------------------
 groupid    | integer | not null
 ismemberof | integer | not null
 startdate  | date    | not null default ('now'::text)::date
 enddate    | date    | not null default '9999-12-31'::date

user_alias_table

Alternate names for users. The user's primary user name is that in the user_table.

xcse=# \d user_alias_table
        Table "public.user_alias_table"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 userid    | integer               | not null
 aliasname | character varying(32) | not null