It probably makes more sense to store member information in our LDAP directory rather than in some crappy SQL ORM thing (see: memberdb).

To do this, we need a structured schema to allow us to store the information we want. The current LDAP schema already uses the inetOrgPerson and posixAccount classes, and these provide a useful base (name, email address, account name). We can use these classes to store some of the data we are interested in (such as homePostalAddress, birthday, and rfc822Mailbox), but some of the other data requires us to define our own classes.

LDAP already contains some custom UCC classes for Dispense - MIFARE attributes are stored in a uccDispenseAccount objectClass (not shown below, see /services/ldap/ucc.schema)

When designing a schema, it is important to consider:

• attribute type
• search indexes
• security/ACLs

Only the attribute type is set in the schema; the others are defined in slapd.conf.

Schema File

# UCC's LDAP OID is 1.3.6.1.4.8324.3.2.1.1
objectIdentifier uccLDAP 1.3.6.1.4.8324.3.2.1.1

# LDAP attributes are in 8324.3.2.1.1.1
objectIdentifier uccAttributeType uccLDAP:1
objectIdentifier uccMemberAttribute uccAttributeType:2

# LDAP objectTypes are in 8324.3.2.1.1.2
objectIdentifier uccObjectType uccLDAP:2

####
# UCC member attributes

# UCC TLA
# SYNTAX is type directoryString
# maximum length of 6
attributetype (uccMemberAttribute:1 NAME 'uccMemberTLA'
    DESC 'UCC member three-letter acronym'
    EQUALITY caseIgnoreMatch
    SUBSTR caseIgnoreSubstringsMatch
    ORDERING caseIgnoreOrderingMatch
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.15{6}
    )
# should probably not be unique, collisions not impossible
# multiple values allowed? not sure
# ACL: writeable by committee, readable by all

# Date account created
# SYNTAX is type generalizedTime
# probably optional because we have a bunch of accounts that don't have this information
attributetype (uccMemberAttribute:2 NAME 'uccAccountCreated'
    DESC 'Date of UCC account creation'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.24
    EQUALITY generalizedTimeMatch
    ORDERING generalizedTimeOrderingMatch
    SINGLE-VALUE
    )
# ACL: writeable by wheel, readable to user and committee

# Date account last renewed
# SYNTAX is type generalizedTime
# probably mandatory, can be set to a generic value
attributetype (uccMemberAttribute:3 NAME 'uccAccountRenewed'
    DESC 'Date UCC account last renewed'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.24
    EQUALITY generalizedTimeMatch
    ORDERING generalizedTimeOrderingMatch
    SINGLE-VALUE
    )
# ACL: writeable by wheel and committee, readable to user

# Life membership status
# SYNTAX is type boolean
# probably mandatory, defaults to False
attributetype (uccMemberAttribute:4 NAME 'uccLifeMember'
    DESC 'UCC life membership status'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.7
    EQUALITY booleanMatch
    SINGLE-VALUE
    )
# ACL: writeable by committee, readable to all? or just wheel/committee/user?
# indexes: exact match

# Student number / UWA Person ID
# SYNTAX is type numericString
attributetype (uccMemberAttribute:5 NAME 'uccUWAPersonID'
    DESC 'UWA Person ID (student/staff number)'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.36{8}
    EQUALITY numericStringMatch
    ORDERING numericStringOrderingMatch
    SUBSTR numericStringSubstringsMatch
    )
# ACL: writeable by user, perhaps with some restrictions, readable to committee
# probably enforce unique values
# probably allow multiple values e.g. staff number and student number
# indexes: exact match

# Guild member
attributetype (uccMemberAttribute:6 NAME 'uccGuildMember'
    DESC 'UWA Guild of Undergraduates Financial Member'
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.7
    EQUALITY booleanMatch
    SINGLE-VALUE
    )
# ACL: writeable by user, perhaps with some restrictions, readable to committee
# indexes: exact match

# UCC member object
# dob is from labextension.schema
objectclass ( uccObjectType:2 NAME 'uccMember'
    DESC 'UCC member'
    STRUCTURAL
    SUP inetOrgPerson
    MUST (uccAccountRenewed $ uccLifeMember $ uid )
    MAY (uccMemberTLA $ uccAccountCreated $ uccUWAPersonID $ uccGuildMember $ dob $ homePostalAddress $ mail $ telephoneNumber)
    )

Comments

Put your comments here. [DAA]

uccUWAPersonID should probably be optional, seeing as we have a few members that don't have either a student number or a staff number. This does however present the problem of being able to confirm the identity of new members in this position (assuming it's an online form). [BOB]

• Yeah, uccUWAPersonID is a MAY rather than a MUST attribute (see the objectClass). I had not intended that this be a way of signing up new members at this stage, not that we do at all a good job of verifying identity anyway. [DAA]

need to clean up the test accounts and things that are in /home/wheel & /home/ucc before using the scripts below [DAA]

not all data from memberdb imports cleanly - the error log should be reviewed and any issues corrected [DAA]

ACLs: there are some things that we don't want people to be able to edit (like their guild status) directly. maybe.

Migration

• Dump the database: slapcat > slapcat.date

• Update /etc/ldap/schema/ucc.schema to the new version: cd /services/ldap && make

• Update the dump to the new schema using the following Python script:

   1 import ldif, sys, csv
   2 
   3 class UCCUpgradeLDIF(ldif.LDIFParser):
   4     def __init__(self, input, output):
   5         ldif.LDIFParser.__init__(self, input)
   6         self.writer = ldif.LDIFWriter(output)
   7     
   8     def handle(self, dn, entry):
   9         if 'inetOrgPerson' in entry['objectClass'] and 'uid' in entry and 'ou=Contacts' not in dn:
  10             uid = entry['uid'][0]
  11             homedir = entry['homeDirectory'][0]
  12             
  13             if '/home/wheel' in homedir or '/home/ucc' in homedir:
  14                 # probably a real person and a UCC member
  15                 entry['objectClass'].remove('inetOrgPerson')
  16                 entry['objectClass'].append('uccMember')
  17                 entry['structuralObjectClass'][0] = 'uccMember'
  18                 entry['uccLifeMember'] = ['FALSE']
  19                 entry['uccAccountRenewed'] = ['197001010000Z']
  20             
  21         self.writer.unparse(dn, entry)
  22 
  23 parser = UCCUpgradeLDIF(sys.stdin, sys.stdout)
  24 parser.parse()

• Stop the master slapd instance on Mussel: /etc/init.d/slapd stop

• Nuke the database: mv /var/lib/ldap /var/lib/ldap-preschema && mkdir /var/lib/ldap && chown openldap:openldap /var/lib/ldap

• Reload the database: slapadd < slapcat.edited.date && chown openldap:openldap /var/lib/ldap/*

• Stop the slave slapd instance on Martello

• Start the master slapd instance on Mussel: /etc/init.d/slapd start

• Update the schema on Martello
• Nuke the database on Martello
• Start the slave slapd instance on Martello (this will pull down the DB and rebuild the local copy as required)

Now we can start adding attributes.

• Add account creation dates - run this script as ssh mussel cat /usr/local/newusers.list | uccCreateTime.py | ldapmodify -c -S errorlog -D cn=admin,dc=ucc,dc=gu,dc=uwa,dc=edu,dc=au -x -y /etc/pam_ldap.secret

   1 import sys
   2 from datetime import datetime
   3 
   4 nl = sys.stdin
   5 out = sys.stdout
   6 
   7 for line in nl.readlines():
   8     if 'failed at' in line:
   9         continue
  10     
  11     uid = line.split(' ')[0]
  12     datestring = line.split('added on')[1].strip()
  13     
  14     createdate = datetime.strptime(datestring, '%a %b %d %H:%M:%S %Z %Y')
  15     # bodgy hack - assume all times are GMT+0800, as the timezone doesn't get detected properly
  16     formatted_date = createdate.strftime('%Y%m%d%H%M%S+0800')
  17     
  18     out.write("""dn: uid=%s,ou=People,dc=ucc,dc=gu,dc=uwa,dc=edu,dc=au
  19 changetype: modify
  20 add: uccAccountCreated
  21 uccAccountCreated: %s
  22 -
  23 
  24 """ % (uid, formatted_date))

• Import data from MemberDB - run this script as uccMemberDBLDIF.py | ldapmodify -c -S errorlog -D cn=admin,dc=ucc,dc=gu,dc=uwa,dc=edu,dc=au -x -y /etc/pam_ldap.secret

   1 import sys, os
   2 
   3 out = sys.stdout
   4 
   5 sys.path += ['/home/wheel/zanchey']
   6 os.environ['DJANGO_SETTINGS_MODULE'] = 'uccmemberdb.settings'
   7 
   8 from uccmemberdb.memberdb.models import Member
   9 
  10 members = Member.objects.exclude(username='')
  11 
  12 for user in members:
  13     ldif = ''
  14     ldif += 'dn: uid=%s,ou=People,dc=ucc,dc=gu,dc=uwa,dc=edu,dc=au\n' % user.username
  15     ldif += 'changetype: modify\n'
  16     
  17     if user.address:
  18         ldif += 'add: homePostalAddress\n'
  19         ldif += 'homePostalAddress: %s\n' % (user.address.strip().replace('\r\n', '$'))
  20         ldif += '-\n'
  21     
  22     if user.date_of_birth:
  23         ldif += 'add: dob\n'
  24         ldif += 'dob: %s\n' % user.date_of_birth.strftime('%Y-%m-%d')
  25         ldif += '-\n'
  26     
  27     if user.email_address:
  28         ldif += 'add: mail\n'
  29         ldif += 'mail: %s\n' % user.email_address
  30         ldif += '-\n'
  31     
  32     ldif += 'add: uccGuildMember\n'
  33     if user.guild_member:
  34         ldif += 'uccGuildMember: TRUE\n'
  35     else:    
  36         ldif += 'uccGuildMember: FALSE\n'
  37     ldif += '-\n'
  38     
  39     if user.phone_number:
  40         ldif += 'add: telephoneNumber\n'
  41         ldif += 'telephoneNumber: %s\n' % user.phone_number
  42         ldif += '-\n'
  43     
  44     if user.student_no:
  45         ldif += 'add: uccUWAPersonID\n'
  46         ldif += 'uccUWAPersonID: %s\n' % user.student_no
  47         ldif += '-\n'
  48         
  49     ldif += 'replace: uccAccountRenewed\n'
  50     ldif += 'uccAccountRenewed: %s\n' % (user.signed_up.strftime('%Y%m%d080000+0800')) 
  51     ldif += '-\n'
  52     
  53     ldif += '\n'
  54     
  55     out.write(ldif)