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 | bigint | not null default 0 domainquota | bigint | not null default 100 custquotasoft | bigint | not null default 100 custquotahard | bigint | not null default 100 comment | text | maxdomains | bigint | not null default 1 active | boolean | not null default true Indexe: »zmicust_pk« PRIMARY KEY, btree (cust_idnr) »zmi_customers_intname« btree (intname, name1, name2) CLUSTER cust_idnr is a SERIAL intname: how is that customer called. We use it to encode resellers names there, and sort in the web app by this key. This makes support for resellers more easy. name/addr/zip/city are customers data uid is the TAX ID in the european union, needed for billing maxaccounts: defines the max. number of *users* for this customer, for a sum of all domains of that customer. domainquota: defines default domainquota for new domains custquotasoft/hard: max. disk space allowed for this customer for all domains and all users in sum. Just to ensure a max. disk space. comment is an internal, human readable reminder for specialities. maxdomains: how many domains can this customer have? active: false if they didn't pay, or moved to somewhere else A bit off topic: Then I also have a contactperson table: # \d zmi_contactperson Tabelle »public.zmi_contactperson« Spalte | Typ | Attribute -----------------+--------+----------- domain_idnr | bigint | not null user_idnr | bigint | not null informtype_idnr | bigint | not null Indexe: »zmi_contactperson_pkey« PRIMARY KEY, btree (domain_idnr, user_idnr, informtype_idnr) Fremdschlüssel-Constraints: »zmi_contactperson_domain_idnr_fkey« FOREIGN KEY (domain_idnr) REFERENCES zmi_domains(domain_idnr) ON UPDATE CASCADE ON DELETE CASCADE »zmi_contactperson_informtype_idnr_fkey« FOREIGN KEY (informtype_idnr) REFERENCES zmi_informtypes(informtype_idnr) ON UPDATE CASCADE ON DELETE CASCADE »zmi_contactperson_user_idnr_fkey« FOREIGN KEY (user_idnr) REFERENCES dbmail_users(user_idnr) ON UPDATE CASCADE ON DELETE CASCADE This defines a list of "who is to be informed of an event of type X for a domain?". Example: technical maintenance, sales e-mail, etc. informtype_idnr is a simple integer, it's meaning defined in the web app. (Currently under development) The advantages of the domains/customers table: - List of domains easy to retrieve for postfix: virtual_mailbox_domains = pgsql:/etc/postfix/domains.sql query = SELECT domain FROM zmi_domains WHERE domain='%s' - Postfix easily rewrite aliasdomains, so that u...@aliasdomain goes in as u...@primarydomain into dbmail: recipient_canonical_maps = pgsql:/etc/postfix/recipients-rewrite.sql query = SELECT '%u@' || domain FROM zmi_domains WHERE domain_idnr = ( SELECT pridomain_idnr FROM zmi_domains pridom WHERE pridom.domain='%d') - The query to find if an e-mail is accepted on our system looks a bit complicated, but it catches all possible forms of aliases: # WHERE userid = '%...@domain' searches for a user # WHERE alias = '@domain' searches a domain catch-all # WHERE alias = '%...@domain' search an alias # WHERE alias = '%u@' search a server catch-all (postmaster@) query = SELECT 1 FROM dbmail_users, dbmail_aliases WHERE userid = ( SELECT '%u@' || domain FROM zmi_domains WHERE domain_idnr = ( SELECT COALESCE(pridom.pridomain_idnr, pridom.domain_idnr) FROM zmi_domains pridom WHERE pridom.domain=substring('%s' from position('@' in '%s')+1)) ) OR alias = ( SELECT '@' || domain FROM zmi_domains WHERE domain_idnr = ( SELECT COALESCE(pridom.pridomain_idnr, pridom.domain_idnr) FROM zmi_domains pridom WHERE pridom.domain=substring('%s' from position('@' in '%s')+1)) ) OR alias = ( SELECT '%u@' || domain FROM zmi_domains WHERE domain_idnr = ( SELECT COALESCE(pridom.pridomain_idnr, pridom.domain_idnr) FROM zmi_domains pridom WHERE pridom.domain=substring('%s' from position('@' in '%s')+1)) ) OR alias = '%u@' LIMIT 1 - we especially appreciate the postmaster@, webmaster@ etc. aliases, so for each customer we automatically have the "important" aliases routed directly into our mailbox, as we provide that service for them. Those who want to do that themselves, just define postmas...@mydomain.com - It helps stop backscatter easily, because you don't accept e-mail for aliases that don't exist, even in alias domains. - Alias domains never need to be checked really, as postfix rewrites them to u...@primarydomain itself - It's easy to see which domains are just aliases. This makes displaying on the web interface much more fun. Disadvantage: - DB schema change necessary - at least two more tables (domains, customers) All this might be biased for our purposes, but I guess there are lots of features many of you would appreciate. For example, the per-domain and/or per-customer quotas. Those are currently not implemented in dbmail, but that shouldn't be too hard. We use is from the web interface for a simple display. The customer gets a call in case of heavy use :-) PS: we've also modified dbmail_aliases to include the "domain_idnr", "visible" and "active" fields. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev