Organizing Users in Groups
Author: Ilja Booij
Introduction
Currently, DBMail has no real knowledge of groups. To give our IMAP ACL1) code a good finish, we need to implement something, which will have the following properties:
* Users can be added to and deleted from a group by an administrator
* Users can add and delete ACLs for a certain group to any of their mailboxes:
tag1 setacl mysharedbox mygroup lr
which would give lookup and read permissions on mysharedbox to everybody who's a member of 'mygroup'.
Changes to table structure
Two tables need be added:
1. group, which holds groupnames.
2. groupmembership, which holds group memberships.
| column | foreign key | default | constraints | info |
|---|---|---|---|---|
| group_id | group(id) | not null | group id | |
| user_id | users(user_idnr) | not null | user |
The ACL table is currently structured like this:
| column | foreign key | default | constraints | extra |
|---|---|---|---|---|
| user_id | users(user_idnr) | not null | user for which the ACL sets permissions | |
| mailbox_id | mailboxes(mailbox_idnr) | not null | mailbox on which ACL works | |
| lookup_flag | 0 | user has right to lookup mailbox | ||
| … (other flags) | 0 | other flags in ACL |
We can make a simple change to the ACL table, which would make it suitable for working with groups. We only have to add a column group_id in the acl table. The NOT NULL on user_id has to be dropped. We can add the following CHECK constraint to the table:
CHECK ( (user_id IS NULL OR group_id IS NULL) AND NOT (user_id IS NULL AND group_id IS NULL) )
Basically the above says user_id IS NULL XOR group_id IS NULL, but there's no XOR in SQL (AFAIK).
XOR in boolean is equivalent to “is not equal to”, i.e., (element1 is null) != (element2 is null).
Code Changes
Setting ACLs
The function imap_acl_pre_administer() should be adapted to also handle groups. The function should probably move to to acl.c and .h.
Getting ACLs
The acl_has_right() shouldn't have to do anything extra. The checks for group membership can move to db_acl_has_right function, directly into the query.
Group Quotas
With groups set up, we should then be able to implement a group quota system, which would be useful for ISPs.
We should be able to set a limit for the entire group, and reject mail if the total usage for the group would exceed the limit. Individual quotas would still apply: a customer may have a 100M storage, and allow 5M for each staff member, but give the CEO an unlimited individual quota; he will continue to receive mail until the entire group exceeds the quota.
This would probably involve the addition of another column to the group table used to store the maxmail size, and corresponding changes to db_check_sizelimit() (or whatever does that these days). I hacked it up in the past, but there wasn't a consensus of how to approach it in the database.