[tech] UCC database

[David Adam]

On Tue, 17 May 2016, Jonathan Van buren wrote:
> Got a question. What type of database (if any) does ucc use to store all
> the members user account information? How accessible is this information (I
> know you can finger system accounts can you also query the database for
> information or do you need elevated privileges to do so)?

As far as I know, there's two different sources of information.

The authoritative database for account information - that is, user names, 
passwords, Unix and Windows user IDs, home directories and so on - is 
stored in our OpenLDAP server and accessed via LDAP. You do need elevated 
privileges to view some information such as password hashes, but you can 
have a look at what's stored as an anonymous user:

 $ ldapvi '(uid=*)'

This will show you the entries for all users on the system (use plain 
`ldapvi` to look through the entire database, but there is lots of other 
uninteresting system information inside).

However, the membership register - which contains full names, addresses, 
student numbers and so on - is maintained separately. At the moment I 
think we're still using MemberDB, which [PXY] started and I continued 
with, renamed as the Gumby Management System (after the Fish Management 
System, an old machine database at UCC - after all G follows F :-).

This is accessible at:
  https://secure.ucc.asn.au/members/

You do need to be on committee (or have committee add you) as obviously 
this information is private.

GMS is written in a Python framework called Django, and the thing about it 
that made it so suitable is that the entire thing is under 100 lines of 
code and configuration. You can have a look at the source, I think - it's 
in /services/gms with the bulk of the code in 
/services/gms/memberdb/models.py.

This sits on top of a PostgreSQL database, although that's entirely an 
implementation detail - in the past it was a SQLite database in the 
secretary's home directory.

There have been plans for years to merge these two databases, or to 
rewrite MemberDB/GMS into something bigger that would allow online signups 
or even payments. The former is a bad idea - we need to keep historical 
records and carefully protect the privacy of the information, and OpenLDAP 
makes this easy to mess up. The latter is a great idea, but it's quite 
hard and involves words like "workflow" which are no fun.

David Adam
zanchey@