[Postfixbuch-users] DB-Strukturen
Stefan Förster
cite+postfix-buch at incertum.net
Do Okt 30 18:29:07 CET 2008
* "Rainer Frey (Inxmail GmbH)" <rainer.frey at inxmail.de> wrote:
> Ja sicher. Ich bin auch an so was ähnlichem dran (als Spielprojekt um Ruby on
> Rails kennenzulernen). Da finde ich interessant, wie andere das machen.
> Gerade wenn man ein typisches RoR-Modell hat, werden die Abfragen von Postfix
> etwas umständlich.
Lustiger Zufall. Also, dann will ich mal zeigen, was ich mir überlegt
hatte - das wird jetzt garantiert eine riesengroße Blamage, weil ich
mit SQL das letzte mal an der Uni was zu tun hatte (und ich glaube,
das war sogar noch im Grundstudium).
Vorüberlegungen waren, daß die Struktur so sein sollte, daß man damit
Authentifizierung (dovecot), Mailrouting und natürlich die Anbindung
von Drittsoftware (über VIEWs, wo sinnvoll/notwendig) hinkriegen soll.
BTW, ich entschuldige mich schonmal für die Zeilenlänge.
Für die virtuellen Mailboxen ergbit sich bei mir:
mailsystem=> \d virtual_mailbox_domains
Table "public.virtual_mailbox_domains"
Column | Type | Modifiers
--------+-----------------------+----------------------------------------------------------------------
id | integer | not null default nextval('virtual_mailbox_domains_id_seq'::regclass)
name | character varying(64) | not null
Indexes:
"virtual_mailbox_domains_pkey" PRIMARY KEY, btree (id)
"virtual_mailbox_domains_name_key" UNIQUE, btree (name)
Das dazugehörige Query in der virtual_mailbox_domains-Map ist trivial:
query = SELECT id FROM virtual_mailbox_domains WHERE name = '%s';
Weiterhin:
Column | Type | Modifiers
--------------+-----------------------------+----------------------------------------------------------------
id | integer | not null default nextval('virtual_user_maps_id_seq'::regclass)
login | character varying(20) | not null
password | character varying(32) | not null
pwdlastset | timestamp without time zone | default now()
lastname | character varying(32) |
firstname | character varying(32) |
active | boolean | default false
uid | integer | default 5000
gid | integer | default 5000
quota_kbytes | integer | default 1048576
spam_policy | integer | default 1
Indexes:
"virtual_user_maps_pkey" PRIMARY KEY, btree (id)
"virtual_user_maps_login_key" UNIQUE, btree (login)
"virtual_user_maps_active_idx" btree (active)
"virtual_user_maps_spam_policy_idx" btree (spam_policy)
Foreign-key constraints:
"virtual_user_maps_spam_policy_fkey" FOREIGN KEY (spam_policy) REFERENCES policy(id)
Als Login wollte ich nicht die eMail-Adresse benutzen. Paßwörter würde
ich im Kalrtext abspeichern, was zwar der alten Security-Regel zuwider
läuft, daß man Paßwörter immer nur zurücksetzen, nie auslesen können
sollte, aber man kann ja auch zwei oder mehr Paßwort-Felder benutzen
und dann entsprechend hashen, wenn das SASL-Backend das kann (dovecot
kann es nicht). Die spam_policy würde ich defaultmäßig auf "aus"
setzen. Worüber man noch nachdenken könnte wäre ein Feld für das Datum
des letzten Logins.
Dazu dann die Mailboxen:
mailsystem=> \d virtual_mailbox_maps
Table "public.virtual_mailbox_maps"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------------------------------------
id | integer | not null default nextval('virtual_mailbox_maps_id_seq'::regclass)
domain | integer |
login | integer |
localpart | character varying(65) | not null
Indexes:
"virtual_mailbox_maps_pkey" PRIMARY KEY, btree (id)
"virtual_mailbox_maps_mailbox_idx" UNIQUE, btree (domain, login, localpart)
"virtual_mailbox_maps_localpart_idx" btree (localpart)
Foreign-key constraints:
"virtual_mailbox_maps_domain_fkey" FOREIGN KEY (domain) REFERENCES virtual_mailbox_domains(id)
"virtual_mailbox_maps_login_fkey" FOREIGN KEY (login) REFERENCES virtual_user_maps(id)
mailsystem=> \d virtual_mailbox_maps
Table "public.virtual_mailbox_maps"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------------------------------------
id | integer | not null default nextval('virtual_mailbox_maps_id_seq'::regclass)
domain | integer |
login | integer |
localpart | character varying(65) | not null
Indexes:
"virtual_mailbox_maps_pkey" PRIMARY KEY, btree (id)
"virtual_mailbox_maps_mailbox_idx" UNIQUE, btree (domain, login, localpart)
"virtual_mailbox_maps_localpart_idx" btree (localpart)
Foreign-key constraints:
"virtual_mailbox_maps_domain_fkey" FOREIGN KEY (domain) REFERENCES virtual_mailbox_domains(id)
"virtual_mailbox_maps_login_fkey" FOREIGN KEY (login) REFERENCES virtual_user_maps(id)
Keine Zauberei, führt dann zu folgender Map für virtual_mailbox_maps:
query = SELECT (SUBSTR(vum.login, 1, 1) || '/' || vum.login || '/Maildir/') AS mail FROM virtual_mailbox_domains vmd
LEFT JOIN virtual_mailbox_maps vmm ON (vmd.id = vmm.domain) LEFT JOIN virtual_user_maps vum ON (vmm.login = vum.id)
WHERE vmm.localpart = '%u' AND vmd.name='%d' AND vum.active;
Das Hashing kann man natürlich anders machen, also statt für
"cite at incertum.net" ein "c/citemailuser/Maildir" zurückzugeben steht
einem natürlich auch "i/incertum.net/c/cite/Maildir" offen - muß jeder
selber wissen. (das "AS mail" ist überflüssig).
Benutzt man dovecot-deliver, kann man auch einfach eine "1"
zurückgeben lassen und in dovecot dann z.B. folgendermaßen abfragen:
user_query = SELECT vum.uid, vum.gid, ('maildir:storage=' || vum.quota_kbytes) AS quota,
('/export/vmailboxes/' || SUBSTR(vum.login, 1, 1) || '/' || vum.login) AS home FROM
virtual_mailbox_domains vmd LEFT JOIN virtual_mailbox_maps vmm ON (vmd.id = vmm.domain)
LEFT JOIN virtual_user_maps AS vum ON (vmm.login = vum.id) WHERE
((vmm.localpart = '%n' AND vmd.name='%d') OR (vum.login = '%u')) AND vum.active
Soweit ist das alles ganz einfach, und es bleibt auch einfach, wenn
man nur Aliasse von/zu Domains aus virtual_mailbox_domains zulassen
will, also keine virtual_alias_domains hat:
mailsystem=> \d virtual_mailbox_aliases
Table "public.virtual_mailbox_aliases"
Column | Type | Modifiers
-----------+-----------------------+----------------------------------------------------------------------
id | integer | not null default nextval('virtual_mailbox_aliases_id_seq'::regclass)
localpart | character varying(65) | not null
domain | integer |
target | integer |
Indexes:
"virtual_mailbox_aliases_pkey" PRIMARY KEY, btree (id)
"virtual_mailbox_aliases_localpart_key" UNIQUE, btree (localpart, domain)
"virtual_mailbox_aliases_localpart_idx" btree (localpart)
Foreign-key constraints:
"virtual_mailbox_aliases_domain_fkey" FOREIGN KEY (domain) REFERENCES virtual_mailbox_domains(id)
"virtual_mailbox_aliases_target_fkey" FOREIGN KEY (target) REFERENCES virtual_mailbox_maps(id)
Was hier fehlt ist ein Constraint, der überprüft, ob es den
"localpart" in der Domain "target" schonmal gibt - hier nehme ich
gerne Hilfe an. Das führt dann zu folgender Map in virtual_alias_maps:
query = SELECT vmm.localpart || '@' || vmd.name FROM virtual_mailbox_domains vmd LEFT JOIN virtual_mailbox_maps vmm
ON vmd.id = vmm.domain RIGHT JOIN virtual_mailbox_aliases vma ON vmm.id = vma.target LEFT JOIN
virtual_mailbox_domains vmd2 ON vma.domain = vmd2.id WHERE vma.localpart = '%u' AND vmd2.name = '%d';
Arbeitet man mit virtual_alias_domains, wird es eklig. Die Tabelle für
die Domains legt man analog zu virtual_mailbox_domains an (ein
Constraint, wie ich überprüfen kann, daß eine Domain nicht in beiden
Tabellen auftaucht, wäre schön), das Query ebenso, die Tabelle für die
Aliase sieht bei mir so aus:
mailsystem=> \d virtual_alias_maps
Table "public.virtual_alias_maps"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------------------------------------
id | integer | not null default nextval('virtual_alias_maps_id_seq'::regclass)
localpart | character varying(65) |
domain | integer |
target | integer |
Indexes:
"virtual_alias_maps_pkey" PRIMARY KEY, btree (id)
"virtual_alias_maps_localpart_key" UNIQUE, btree (localpart, domain)
"virtual_alias_maps_localpart_idx" btree (localpart)
Foreign-key constraints:
"virtual_alias_maps_domain_fkey" FOREIGN KEY (domain) REFERENCES virtual_alias_maps(id)
"virtual_alias_maps_target_fkey" FOREIGN KEY (target) REFERENCES virtual_mailbox_maps(id)
Auch hier fehlt wieder der Constraint, der Überprüft, ob es
"localpart" in der Domain "target" schon gibt, die Anmerkung zur
Hilfestellung gilt analog :-)
Es ergibt sich folgendes etwas dubioses Query:
query = SELECT vmm.localpart || '@' || vmd.name FROM virtual_mailbox_domains vmd LEFT JOIN virtual_mailbox_maps vmm
ON vmd.id = vmm.domain RIGHT JOIN virtual_alias_maps vam ON vmm.id = vam.target LEFT JOIN virtual_alias_domains vad
ON vam.domain = vad.id WHERE (vam.localpart='%u' AND vad.name='%d') OR (vad.name = SUBSTRING('%s', '@(.*)') AND vam.localpart = '');
Sehr quick und sehr dirty könnte man für amavisd-new folgenden View
Bauen (als ich den gemacht habe, hatte ich noch keine
virtual_*alias*-Maps:
mailsystem=> \d users
View "public.users"
Column | Type | Modifiers
-----------+-------------------+-----------
id | integer |
priority | integer |
policy_id | integer |
email | bytea |
fullname | character varying |
local | bpchar |
View definition:
SELECT default_users.id, default_users.priority, default_users.policy_id, decode(replace(default_users.email::text, '\\'::text, '\\\\'::text), 'escape'::text) AS email, default_users.fullname, default_users.local
FROM default_users
UNION
SELECT vmm.id, 7 AS priority, vum.spam_policy AS policy_id, decode(replace((vmm.localpart::text || '@'::text) || vmd.name::text, '\\'::text, '\\\\'::text), 'escape'::text) AS email, NULL::unknown AS fullname, 'Y' AS local
FROM virtual_mailbox_domains vmd
LEFT JOIN virtual_mailbox_maps vmm ON vmd.id = vmm.domain
LEFT JOIN virtual_user_maps vum ON vmm.login = vum.id;
Wie gesagt, der UNION hier stammt aus der Zeit, in der ich noch keine
Alias-Maps hatte. Natürlich kann man auch einfach die
SQL-Select-Policies in amavisd-new abändern.
Wie ich das ganze jetzt in RoR umgesetzt kriege - keine Ahnung. Für
Feedback und Hilfe (gerade bei den Constraints) bin ich jederzeit
dankbar. Und um transport_maps bzw. relay_domains habe ich mich noch
gar nicht gekümmert - ich mach das nur so nebenbei als Spaßprojekt und
mir fehlt leider zu oft die Zeit, das mit der nötigen Ernsthaftigkeit
voranzutreiben.
Ciao
Stefan
--
Stefan Förster http://www.incertum.net/ Public Key: 0xBBE2A9E9
FdI #253: Interessante Projekte - Schwierige Dinge, die noch niemand mit
Erfolg angepackt hat. Vorzugsweise mit "Bleeding Edge Technology". (Michael
Olbricht)
Mehr Informationen über die Mailingliste Postfixbuch-users