MySQL solves a bunch of problems for us. I have noticed the following problems with our authentication system:

1. We cannot tell when users were added easily
2. 300+ users is very hard to manage with /etc/passwd and /etc/shadow
3. There is no reasonable way to keep track of user bans, a point system makes this automatic
4. We can easily attach more data to a user than is possible with the standard unix authentication (notes, etc)
5. This is much cooler!

Here is some documentation we may need:

• http://www.ma.utexas.edu/users/stirling/computergeek/mysqluser.html
• http://docs.jeagle.org/libpam-mysql/README.gz
• http://www.yolinux.com/TUTORIALS/LinuxTutorialManagingGroups.html

The database layout will be similar in strategy of how the /etc/passwd and /etc/shadow system already works. There will be two accounts able to access the database, with two seperate sets of permissions. There will be an ‘nss_passwd’ user and an ‘nss_shadow’ user. The ‘nss_passwd’ user will be accessible by all users and will have the same access priviledges as a standard user would have to /etc/passwd and /etc/shadow, etc.

database: nsswitch

The passwd table is where the main user record is stored. This is similar to /etc/passwd but only stores vital information. This table also prevents a group from being deleted if it is the user’s default group.

• uid - user’s id
• name - user’s name
• gid - user’s default group id
• created - the date and time when the account was created

The group table store the main group record. This is similar to /etc/group but does not store group memebers.

• gid - the group’s unique id
• name - the name of the group

This tables store pairings of user ids to group ids. This represents what groups a user belongs to. The user id and group id columns will automatically be modified or deleted if a user or group is modified or deleted.

• uid - the user id which this mapping belongs to
• gid - the group that this user should be associated with

The shadow table is where users passwords are stored. This table is only readable/writable by the nss_shadow user. If a user is deleted or their uid is modified their record in this table will be deleted or modified accordingly.

• uid - the user id that this shadow entry belongs to
• password - the password (hashed) for this uid
• points - the number of points this user has (note: this value is recalculated by nss_update_user_points)

This table stored the password for a group. If a group is deleted or its group id changed, this table will be updated automatically.

• gid - the group id that this shadow entry belongs to
• password - the password (hashed) for this gid

This table is a log of every time a user attempts to login to the system. It does reference any other table to ensure that records are never deleted.

• id - the id of this message
• time - the time the user logged in
• user - the name of the user who logged in
• pid - the process id the user logged in from
• host - the host where the user logged in from
• message - the description of the authentication attempt

This table is used to store public meta data for each user. This includes things such as name and email. If a user is removed or a user’s uid changes, this table will be updated automatically.

• uid - the user id whom this meta entry belongs to
• key - the key of the meta data
• value - the value of the meta data

This table is used to store private meta data for each user. This includes things such as authorization information for other programs, and information required of all users but wished to be kept private. If a user is removed or a user’s uid changes, this table will be updated automatically.

• uid - the user id whom this meta entry belongs to
• key - the key of the meta data
• value - the value of the meta data

This table is used to store notes about users. This is used to log user information such as activities admins performed for the user. It is also used to report malicous activities. Each not has a point value, the more points a user has, the closer they are to being banned. Notes that should not impact a users ability to log into the system should be assigned a point value of zero, notes that should result in the user being closer to a ban should be assigned a positive point value, revocation of points should be done by adding a note of negative point value, not by deleting notes. If a user is deleted then all of their notes will also be deleted. Users who are authors of notes (admins) cannot be deleted without modifying the user’s notes.

• id - The note’s unique id.
• created - the date and time the note was created.
• uid - the user id whom this note belongs
• authorid - the user id of the author of this note
• amount - the amount of points this note is worth (can be positive or negative)
• text - the message of the note, reason for giving points

database: nsswitch

This method creates a new user with the given parameters above. Each new user starts with 0 points. If the new user should not be able to login with a password, a NULL password can be used. The user’s first name, last name, email address, home, and shell will be set in user_meta.

Removes a user, and all attached data, including, group associations, notes, and meta data from the database. A user who is an author of a note cannot be deleted.

Gets the user(s) matching the username parameter. The name parameter may contain the MySQL LIKE wildcards. This returns entries from the passwd table.

Returns a user’s id, or zero if they are not in the passwd table.

Determines whether the username and password given is valid. If the username and password combination is not valid, this procedure will return 0.

Sets the password for this user. If no entry exists in the shadow table for this user, a new entry is created.

This method sets the default group for the user.

This method returns an unused user id (highest uid in passwd table + 1). System user ids are less than 1000, regular ones are 1000 and higher.

Creates a new group in the database. A password can be used so users not in the group may gain group-only privileges if they know the password.

Removes a group from the database, removing all users associated with the group from the given group. This procedure will not succeed if a user’s default group is set to the group being removed.

Gets the group(s) matching the groupname parameter. The name parameter may contain the MySQL LIKE wild cards. This returns matching entries from the group table.

Determines whether the user belongs to the group, or if the groupname and password combination is valid.

Sets the password for this group. If no entry exists in the gshadow table for this group, a new entry is created.

Adds the given user as a member of the given group.

This removes the given user from the given group. This procedure will fail if the user’s default group is group given.

This method returns an unused group id (highest gid in passwd table + 1). System group ids are less than 1000, regular ones are 1000 and higher.

This sets metadata for a user. If there is not a username/key entry for the given user then a new entry is created. This will overwrite old metadata entries. If the value given is NULL this procedure will delete the metadata, if it exists. The private variable determines what table to use, either user_meta or user_meta_shadow (if private = 1).

This fetches metadata for a user. Both the name and meta can contain wild cards found in MySQL LIKE clauses and will operate as expected. The private variable determines what what table to use, either user_meta or user_meta_shadow (if private = 1). Note: You cannot get metadata from both tables at once.

Adds a note to a user and then updates their points in the passwd table. Each note is assigned to a specific user and each note must have an author. Notes can have a negative, postive or neutral point value, if a user has too many points, they will automatically be banned. Notes should never be removed.

Recalculates a user’s points based on off the point values of their notes in the user_notes table. This value is reflected in the shadow table.

• shadowarts thinks not very pretty having the ‘type’ variable ~ felix
  • i dont think very pretty having the ‘type’ variable ;-D…. in all seriousness, i dont see why we need a type, points are points, they suck and the description should be more than enough to determine why they got points (side-note… this is the one time i wish there was a discussion page) ~ shadowarts
• All of these tables should be in the same database, no shadow database, we will manage per table permissions ~ shadowarts
• Should notes have a timestamp? Should groups have a time stamp of when they were created (like users do)? ~ shadowarts
• There’s probably a lot more interesting metadata we can have. Ideas? ~ illicium
  • I’m not sure we should be able to have multiple meta data entries with the same name anymore honestly. How would we handle deleting them? Would you have to know their value? ~ shadowarts
• feesh thinks that there should be a mysql.nonlogic.org subdomain, so if the mysql gets handled by another server (or if other servers need to connect to mysql), it won’t be a hassle.