michael.monnerie at is
Jun 24, 2009, 12:47 PM
Post #1 of 7
(396 views)
Permalink
|
|
request for extension: "domain" and "customers" table
|
|
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[at]domain1.at also works with x[at]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 user[at]aliasdomain goes in
as user[at]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 = '%u[at]domain' searches for a user
# WHERE alias = '@domain' searches a domain catch-all
# WHERE alias = '%u[at]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 postmaster[at]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 user[at]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
_______________________________________________
DBmail mailing list
DBmail[at]dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
|
signature.asc
