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.

1) see RFC 2086, IMAP ACL extension
 
groups.txt · Last modified: 2012/02/27 21:39 by bas
 
DBMail is developed by Paul J Stevens together with developers world-wide