I think I've said this around 2006 already, but maybe time has come to
suggest it again. As far as I read this mailing list, most people use
dbmail to run several (if not lots of) domains. We are in the same
situation, a small ISP running some hundred domains.
I've extended dbmail since before we started, in order to have an extra
domains and customers table. This has big advantages. But first,
I'll list our tables (named zmi_* to distinct from dbmail builtin
tables):
# \d dbmail_users
Spalte | Typ | Attribute
-+-+--
user_idnr | bigint | not null default
userid | character varying(100) | not null
passwd | character varying(34) | not null
client_idnr | bigint | not null default 0
maxmail_size| bigint | not null default 0
curmail_size| bigint | not null default 0
maxsieve_size | bigint | not null default 0
cursieve_size | bigint | not null default 0
encryption_type | character varying(20) | not null default ''
last_login | timestamp without time zone | not null
user_realname | character varying(100) |
domain_idnr | bigint | not null
visible | boolean | not null default true
active | boolean | not null default true
Indexe:
»dbmail_users_pkey« PRIMARY KEY, btree (user_idnr)
»dbmail_users_name_idx« UNIQUE, btree (userid)
»dbmail_users_2« btree (lower(userid::text))
»dbmail_users_domain« btree (domain_idnr, userid) CLUSTER
(not used by us; could be removed)
client_idnr is from dbmail, but nowhere used; could be deleted
user_realname is to store the name of a person. We use that in order for
support to be easier (Mr. Ahmed Turfan has a problem)
domain_idnr is a link to the zmi_domains table
visible defines if that user is visible in the web interface (used to
suppress display of spamtraps)
active can be set false to disable a user (e.g. did not pay, etc.)
# \d zmi_domains
Spalte | Typ | Attribute
-++
domain_idnr | bigint | not null default
nextval('zmi_domains_domain_idnr_seq'::regclass)
domain | character varying(200) | not null
maxaccounts | bigint | not null default 0
userquota | bigint | not null default 100
domainquotasoft | bigint | not null default 100
domainquotahard | bigint | not null default 100
comment | text |
pridomain_idnr | bigint |
cust_idnr | bigint | not null default 0
active | boolean| not null default true
Indexe:
»zmidom_pk« PRIMARY KEY, btree (domain)
»zmi_domains_domain_idnr_key« UNIQUE, btree (domain_idnr) CLUSTER
Fremdschlüssel-Constraints:
»zmi_domains_cust_idnr_fkey« FOREIGN KEY (cust_idnr) REFERENCES
zmi_customers(cust_idnr)
»zmi_domains_pridomain_idnr_fkey« FOREIGN KEY (pridomain_idnr)
REFERENCES zmi_domains(domain_idnr)
domain_idnr is simply a SERIAL
domain is the name of the domain
maxaccounts sets a limit on how many addresses a customer can define via
the web interface
userquota sets the default quota a user in that domain receives
domainquota(hard|soft) sets a limit for that domain as a whole. So you
can say that domain can have 5GB storage, each user max. 2GB, just to
be sure that the 20 users of that domain cannot get 40GB storage
occupied.
comment: internal comment for special setups (human readable)
pridomain_idnr: this is the magic. normally NULL, but if a domain is an
aliasdomain, contains the domain_idnr of the primary domain. This way
it's easy to setup domain1.at and define that domain2.at and domain3.at
are aliasdomains. This means there can't be users in domain2/3, but a
defined address x...@domain1.at also works with x...@domain2/3.at; this makes
management a lot easier for aliases, and also simplifies MTA setup.
cust_idnr: to which customer does this domain belong?
active: is this domain able to receive mail, are it's users able to
login?
# \d zmi_customers
Spalte | Typ | Attribute
---++--
cust_idnr | bigint | not null default
nextval('zmi_customers_cust_idnr_seq'::regclass)
intname | character varying(200) |
name1 | character varying(200) | not null
name2 | character varying(200) |
addr1 | character varying(200) |
addr2 | character varying(200) |
zip | character varying(20) |
city | character varying(200) |
uid | character varying(30) |
maxaccounts |
