The Database Structure

Overview / Introduction

This database ("projectdb") is for basically everything that‘s handled by dsadmin. This includes mirrors, projects and their services. Most of the stuff is in a mysql database, but users, groups, mailing lists and mail accounts are in LDAP, as that data is also used by pam-ldap and qmail-ldap.

Schemas and setup scripts:

• ../../db/schema.sql
• ../../db/gensql.rb
• ../../db/ldap/setup.ldif
• ../../db/ldap/dotsrc.schema and :../../db/ldap/qmail.schema

Some general notes:

• Both the web frontend and the backend (admind) have the same access rights to both the mysql and the ldap part. Most of the database content manipulation is in fact done by the frontend.
• The mysql part uses rails / ActiveRecord naming conventions
• MySQL 4 with InnoDB support (for foreign keys and transactions) is assumed, but it should also work without InnoDB. Theoretically.

Users and Groups

See ../../db/ldap/setup.ldif for details and examples.

The user/group setup is designed to be used as following:

• dsadmin access control: Users and groups from LDAP are used, with one group (by default "staff") granted full ("superuser") access
• user ssh logins: Users and groups from LDAP (via pam-ldap), except for anything/anyone associated with the "superuser" group. In other words: A dedicated sshd (typically in a linux vserver or bsd jail) for non-privileged users
• administrator shell login: classical /etc/passwd or different LDAP server / LDAP domain, to minimize the effects of an eventual unauthorized LDAP write access (e.g. via some vulnerability in dsadmin).

Mirrors

The main table here is "mirrors", containing one record per mirror. The data in there is (1) stuff needed for the mirror updates (connections spec etc), (2) general info (name, contact email, …) and some status data (size, time of last successful update).

"mirror_updates" contains one record for each attempted update of each mirror, with semi-detailed status data for later statistics generation.

Projects

This part is still very much in flux, so I won‘t describe it here yet.

Misc. Stuff