at the end of previous part of this text, we got our system working, and accepting emails for local accounts. plus i promised to add quota now 🙂 so, let's do it.
first let's think a bit.
exim has built-in way to check and calculate quota on mail delivery.
but we can also use system-level quota.
it means that for every possible mail delivery we can check 3 separate quotas.
what can we use this for? let's count:
- account quota. we dont want specific account to go over some size. since this is easiest to be done, it's best to leave this to exim. even calculations.
- domain quota. we dont want some domain to use more than some size, even in case all its accounts are below their per-account quotas. let's use system quota for this.
- if we are selling email services, we might have customer that has multiple domains. we'd like to limit total size of his mails (across all domains and all accounts) to some limit. also system quota.
example usage:
we have customer depesz, with customer quota of 1gb.
this customer can have as many domains as he wants, but we (or perhaps he) dont want any of this domains to go over 0.5gb.
every domain can have any number of accounts, but given account cannot use more than 100mb.
of course we should be able to set account-quota for every account separately. same goes for domain quotas.
to do it, first we will need some database changes. first change is simple – let's add “customers" table:
CREATE TABLE customers ( id SERIAL, codename TEXT NOT NULL DEFAULT '' UNIQUE, quota_mb INT4 NOT NULL DEFAULT 0, PRIMARY KEY (id) );
quota “0" means – no limits.
now, per-domain things.
first – we will need quota field as well:
ALTER TABLE domains ADD COLUMN quota_mb INT4 NOT NULL DEFAULT 0;
but we will also need some way to conenct domain and customer:
ALTER TABLE domains ADD COLUMN customer_id INT4 REFERENCES customers (id); INSERT INTO customers (codename) VALUES ('DEFAULT'); UPDATE domains SET customer_id = currval('customers_id_seq'); ALTER TABLE domains ALTER COLUMN customer_id SET NOT NULL;
this is more complex as i also assigned all domains to “default" customer.
now, we also need quota information in accounts table:
ALTER TABLE accounts ADD COLUMN quota_mb INT4 NOT NULL DEFAULT 0;
now. let's add appropriate checks in exim (and system!) configuration.
first quota to be done will be account-quota.
luckily this is pretty simple.
we have to find pg_delivery transport.
it looks like this:
pg_delivery: debug_print = "T: pg_delivery for $local_part@$domain" driver = appendfile maildir_format directory = ${lookup pgsql{SELECT get_account_maildir('${local_part}', '${domain}')}}
(we added it in previous part of this blog-post).
now, we add to this:
quota = ${lookup pgsql{SELECT get_account_quota('${local_part}', '${domain}')}}
where get_account_quota is:
CREATE OR REPLACE FUNCTION get_account_quota(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$ DECLARE use_username text := TRIM(BOTH FROM LOWER(in_username)); use_domain text := TRIM(BOTH FROM LOWER(in_domain)); reply TEXT; BEGIN SELECT a.quota_mb::TEXT || 'M' INTO reply FROM accounts a JOIN domains d ON a.domain_id = d.id WHERE d.fullname = use_domain AND a.username = use_username; IF NOT FOUND THEN RETURN NULL; END IF; RETURN reply; END; $BODY$ LANGUAGE plpgsql;
it works as expected:
eximtest=> SELECT get_account_quota('depesz', 'exim.depesz'); get_account_quota ------------------- 0M (1 ROW) eximtest=> UPDATE accounts SET quota_mb = 10; UPDATE 1 eximtest=> SELECT get_account_quota('depesz', 'exim.depesz'); get_account_quota ------------------- 10M (1 ROW)
so, let's see how it will go with email delivery. exim restart to rebuild config, and then:
before tests:
=> du /mails/exim.depesz/depesz/maildir/ -h 4.0K /mails/exim.depesz/depesz/maildir/cur 4.0K /mails/exim.depesz/depesz/maildir/tmp 8.0K /mails/exim.depesz/depesz/maildir/new 20K /mails/exim.depesz/depesz/maildir/
let's sendd 2.5mb mail:
=> perl -e 'print "a"x49, "\n" for 1..50000' | mail -s test1 depesz@exim.depesz
logs from exim:
2008-02-12 22:14:53 1JP3Ou-0003Eu-JW <= root@xxx U=root P=local S=2500303 2008-02-12 22:14:53 1JP3Ou-0003Eu-JW => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-02-12 22:14:53 1JP3Ou-0003Eu-JW Completed
sweet. let's again:
2008-02-12 22:16:01 1JP3Q1-0003FY-6c <= root@xxx U=root P=local S=2500303 2008-02-12 22:16:01 1JP3Q1-0003FY-6c => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-02-12 22:16:01 1JP3Q1-0003FY-6c Completed
and again:
2008-02-12 22:16:22 1JP3QL-0003Ft-My <= root@xxx U=root P=local S=2500303 2008-02-12 22:16:22 1JP3QL-0003Ft-My => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-02-12 22:16:22 1JP3QL-0003Ft-My Completed
now, my test maildir tooks:
=> du /mails/exim.depesz/depesz/maildir/ -h 4.0K /mails/exim.depesz/depesz/maildir/cur 4.0K /mails/exim.depesz/depesz/maildir/tmp 7.2M /mails/exim.depesz/depesz/maildir/new 7.2M /mails/exim.depesz/depesz/maildir/
so, one more email should fit, but next should fail. 2 new emails sent, and logs show:
2008-02-12 22:17:38 1JP3Ra-0003GT-8e <= root@xxx U=root P=local S=2500303 2008-02-12 22:17:39 1JP3Ra-0003GT-8e => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-02-12 22:17:39 1JP3Ra-0003GT-8e Completed 2008-02-12 22:17:41 1JP3Rd-0003Gb-2K <= root@xxx U=root P=local S=2500303 2008-02-12 22:17:41 1JP3Rd-0003Gb-2K == depesz@exim.depesz R=pg_user T=pg_delivery defer (-22): mailbox is full (MTA-imposed quota exceeded while writing to tmp/1202854661.H958175P12565.localhost)
great!
what's important – take a look at last line. message 1JP3Rd-0003Gb-2K was not deliverered, but it is not “completed". it still sits in exim spool:
=> mailq
0m 2.4M 1JP3Rd-0003Gb-2K <root@xxx>
depesz@exim.depeszso, if i'll remove one of previous emails, exim will deliver this one (for some time only, defined in “retry" part of config.
it wasn't difficult, was it?
now, to the per-domain, and per-customer quotas. let's enter some dummy data:
i have one test customer:
eximtest=> SELECT * FROM customers; id | codename | quota_mb ----+----------+---------- 1 | DEFAULT | 10 (1 ROW)
this customer has 4 separate domains, please note that sum of their (domains) quota_mb is larger than quota_mb from customer!
eximtest=> SELECT * FROM domains; id | fullname | customer_id | quota_mb ----+-------------+-------------+---------- 1 | localhost | 1 | 4 2 | example.com | 1 | 4 3 | exim.depesz | 1 | 4 4 | badtest | 1 | 4 (4 ROWS)
and each of the domains has 2 accounts: depesz and test. summarized quota of both accounts is larger than quota of domain:
eximtest=> SELECT * FROM accounts; id | username | domain_id | password | quota_mb ----+----------+-----------+------------------------------------+---------- 1 | depesz | 3 | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 | 3 2 | test | 3 | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ | 3 3 | depesz | 1 | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 | 3 4 | test | 1 | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ | 3 5 | depesz | 4 | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 | 3 6 | test | 4 | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ | 3 7 | depesz | 2 | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 | 3 8 | test | 2 | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ | 3 (8 ROWS)
now. to enforce system-level quotas we will need to assign uids and gids of created files (mails).
so, first we need to have the ability to store the uid/gid information in database.
let's use gid (groups) for customers, and uids (users) for domains:
ALTER TABLE customers ADD COLUMN system_gid INT4; ALTER TABLE domains ADD COLUMN system_uid INT4;
now, we'd need some way to automatically assign ids.
this can be done with trigger on insert, but we will also need to specifiy what minimum id should be used.
i.e. – what id (gid/uid) use for first customer and domain?
triggers are actually quite simple:
CREATE OR REPLACE FUNCTION set_system_gid() RETURNS TRIGGER AS $BODY$ DECLARE tempint int4; BEGIN SELECT COALESCE(MAX(system_gid) + 1, 10000) INTO tempint FROM customers; NEW.system_gid := tempint; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER set_system_gid BEFORE INSERT ON customers FOR each ROW EXECUTE PROCEDURE set_system_gid();
CREATE OR REPLACE FUNCTION set_system_uid() RETURNS TRIGGER AS $BODY$ DECLARE tempint int4; BEGIN SELECT COALESCE(MAX(system_uid) + 1, 10000) INTO tempint FROM domains; NEW.system_uid := tempint; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER set_system_uid BEFORE INSERT ON domains FOR each ROW EXECUTE PROCEDURE set_system_uid();
nothing fancy. let's test if it works:
customers:
eximtest=> INSERT INTO customers (codename) VALUES ('t1'); INSERT 0 1 eximtest=> INSERT INTO customers (codename) VALUES ('t2'); INSERT 0 1 eximtest=> SELECT * FROM customers; id | codename | quota_mb | system_gid ----+----------+----------+------------ 1 | DEFAULT | 10 | 2 | t1 | 0 | 10000 3 | t2 | 0 | 10001 (3 ROWS)
domains:
eximtest=> INSERT INTO domains (fullname, customer_id) VALUES ('x', 2); INSERT 0 1 eximtest=> INSERT INTO domains (fullname, customer_id) VALUES ('y', 3); INSERT 0 1 eximtest=> SELECT * FROM domains; id | fullname | customer_id | quota_mb | system_uid ----+-------------+-------------+----------+------------ 1 | localhost | 1 | 4 | 2 | example.com | 1 | 4 | 3 | exim.depesz | 1 | 4 | 4 | badtest | 1 | 4 | 5 | x | 2 | 0 | 10000 6 | y | 3 | 0 | 10001 (6 ROWS)
looks cool, but there is slight “issue" – preexisting domains have system_uid NULL (same goes for customers and gid).
is there any simple way to fix it? sure:
eximtest=> CREATE TRIGGER set_system_gid_tmp BEFORE UPDATE ON customers FOR each ROW EXECUTE PROCEDURE set_system_gid(); CREATE TRIGGER eximtest=> UPDATE customers SET id = id WHERE system_gid IS NULL; UPDATE 1 eximtest=> DROP TRIGGER set_system_gid_tmp ON customers; DROP TRIGGER eximtest=> SELECT * FROM customers; id | codename | quota_mb | system_gid ----+----------+----------+------------ 2 | t1 | 0 | 10000 3 | t2 | 0 | 10001 1 | DEFAULT | 10 | 10002 (3 ROWS)
and (better looking) for domains:
eximtest=> CREATE TRIGGER set_system_uid_tmp BEFORE UPDATE ON domains FOR each ROW EXECUTE PROCEDURE set_system_uid(); CREATE TRIGGER eximtest=> UPDATE domains SET id = id WHERE system_uid IS NULL; UPDATE 4 eximtest=> DROP TRIGGER set_system_uid_tmp ON domains; DROP TRIGGER eximtest=> SELECT * FROM domains; id | fullname | customer_id | quota_mb | system_uid ----+-------------+-------------+----------+------------ 5 | x | 2 | 0 | 10000 6 | y | 3 | 0 | 10001 1 | localhost | 1 | 4 | 10002 2 | example.com | 1 | 4 | 10003 3 | exim.depesz | 1 | 4 | 10004 4 | badtest | 1 | 4 | 10005 (6 ROWS)
now, we can/should also fix the definition of the tables:
ALTER TABLE customers ALTER COLUMN system_gid SET NOT NULL; ALTER TABLE domains ALTER COLUMN system_uid SET NOT NULL; CREATE UNIQUE INDEX ui_customers_sg ON customers (system_gid); CREATE UNIQUE INDEX ui_domains_su ON domains (system_uid);
thanks to this we will have guarantee that any specific gid/uid can be used only once, and that every customer/domain has its own gid/uid.
now that we have all of it, we should create functions for returning user uid and gid:
CREATE OR REPLACE FUNCTION get_account_uid(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$ DECLARE use_username text := TRIM(BOTH FROM LOWER(in_username)); use_domain text := TRIM(BOTH FROM LOWER(in_domain)); reply INT4; BEGIN SELECT d.system_uid INTO reply FROM accounts a JOIN domains d ON a.domain_id = d.id WHERE d.fullname = use_domain AND a.username = use_username; IF NOT FOUND THEN RETURN NULL; END IF; RETURN reply; END; $BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION get_account_gid(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$ DECLARE use_username text := TRIM(BOTH FROM LOWER(in_username)); use_domain text := TRIM(BOTH FROM LOWER(in_domain)); reply INT4; BEGIN SELECT c.system_gid INTO reply FROM accounts a JOIN domains d ON a.domain_id = d.id JOIN customers c ON d.customer_id = c.id WHERE d.fullname = use_domain AND a.username = use_username; IF NOT FOUND THEN RETURN NULL; END IF; RETURN reply; END; $BODY$ LANGUAGE plpgsql;
how does it work? take a look:
eximtest=> SELECT a.username, d.fullname, get_account_uid(a.username, d.fullname), get_account_gid(a.username, d.fullname) FROM accounts a JOIN domains d ON a.domain_id = d.id ORDER BY a.username, d.fullname; username | fullname | get_account_uid | get_account_gid ----------+-------------+-----------------+----------------- depesz | badtest | 10005 | 10002 depesz | example.com | 10003 | 10002 depesz | exim.depesz | 10004 | 10002 depesz | localhost | 10002 | 10002 test | badtest | 10005 | 10002 test | example.com | 10003 | 10002 test | exim.depesz | 10004 | 10002 test | localhost | 10002 | 10002 (8 ROWS)
now we need to chown existing directories, so, let's create simple script:
to do it effectively, i'll add function:
CREATE OR REPLACE FUNCTION get_account_homedir(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$ DECLARE use_username text := TRIM(BOTH FROM LOWER(in_username)); use_domain text := TRIM(BOTH FROM LOWER(in_domain)); temptext TEXT; BEGIN temptext := is_local_user(in_username, in_domain); IF temptext IS NULL THEN RETURN NULL; END IF; RETURN setup_get('mails_top_dir') || '/' || use_domain || '/' || use_username; END; $BODY$ LANGUAGE plpgsql;
now i can:
eximtest=> SELECT 'chown -R ' || get_account_uid(a.username, d.fullname) || ' ' || get_account_homedir(a.username, d.fullname) FROM accounts a JOIN domains d ON a.domain_id = d.id; ?COLUMN? ------------------------------------------ chown -R 10004 /mails/exim.depesz/depesz chown -R 10004 /mails/exim.depesz/test chown -R 10002 /mails/localhost/depesz chown -R 10002 /mails/localhost/test chown -R 10005 /mails/badtest/depesz chown -R 10005 /mails/badtest/test chown -R 10003 /mails/example.com/depesz chown -R 10003 /mails/example.com/test (8 ROWS)
eximtest=> SELECT 'chgrp -R ' || get_account_gid(a.username, d.fullname) || ' ' || get_account_homedir(a.username, d.fullname) FROM accounts a JOIN domains d ON a.domain_id = d.id; ?COLUMN? ------------------------------------------ chgrp -R 10002 /mails/exim.depesz/depesz chgrp -R 10002 /mails/exim.depesz/test chgrp -R 10002 /mails/localhost/depesz chgrp -R 10002 /mails/localhost/test chgrp -R 10002 /mails/badtest/depesz chgrp -R 10002 /mails/badtest/test chgrp -R 10002 /mails/example.com/depesz chgrp -R 10002 /mails/example.com/test (8 ROWS)
in case i didn't made yet the dirs (postgresql technically can be forced to make the dirs for you but i wouldn't suggest to do it, as it would require postgres to be able to get root privileges – not really good idea) i can make them that way:
eximtest=> SELECT 'mkdir -pv ' || get_account_maildir(a.username, d.fullname) || '/{cur,new,tmp}' FROM accounts a JOIN domains d ON a.domain_id = d.id; ?COLUMN? ----------------------------------------------------------- mkdir -pv /mails/exim.depesz/depesz/maildir/{cur,NEW,tmp} mkdir -pv /mails/exim.depesz/test/maildir/{cur,NEW,tmp} mkdir -pv /mails/localhost/depesz/maildir/{cur,NEW,tmp} mkdir -pv /mails/localhost/test/maildir/{cur,NEW,tmp} mkdir -pv /mails/badtest/depesz/maildir/{cur,NEW,tmp} mkdir -pv /mails/badtest/test/maildir/{cur,NEW,tmp} mkdir -pv /mails/example.com/depesz/maildir/{cur,NEW,tmp} mkdir -pv /mails/example.com/test/maildir/{cur,NEW,tmp} (8 ROWS)
using above examples, i made this directory structure:
=> find /mails/ -type d -print0 | xargs -0 ls -lad drwxr-xr-x 6 root root 4096 2008-02-13 21:31 /mails/ drwxr-xr-x 4 root root 4096 2008-02-13 21:31 /mails/badtest drwxr-xr-x 3 10005 10002 4096 2008-02-13 21:31 /mails/badtest/depesz drwxr-xr-x 5 10005 10002 4096 2008-02-13 21:31 /mails/badtest/depesz/maildir drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/depesz/maildir/cur drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/depesz/maildir/new drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/depesz/maildir/tmp drwxr-xr-x 3 10005 10002 4096 2008-02-13 21:31 /mails/badtest/test drwxr-xr-x 5 10005 10002 4096 2008-02-13 21:31 /mails/badtest/test/maildir drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/test/maildir/cur drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/test/maildir/new drwxr-xr-x 2 10005 10002 4096 2008-02-13 21:31 /mails/badtest/test/maildir/tmp drwxr-xr-x 4 root root 4096 2008-02-13 21:31 /mails/example.com drwxr-xr-x 3 10003 10002 4096 2008-02-13 21:31 /mails/example.com/depesz drwxr-xr-x 5 10003 10002 4096 2008-02-13 21:31 /mails/example.com/depesz/maildir drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/depesz/maildir/cur drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/depesz/maildir/new drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/depesz/maildir/tmp drwxr-xr-x 3 10003 10002 4096 2008-02-13 21:31 /mails/example.com/test drwxr-xr-x 5 10003 10002 4096 2008-02-13 21:31 /mails/example.com/test/maildir drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/test/maildir/cur drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/test/maildir/new drwxr-xr-x 2 10003 10002 4096 2008-02-13 21:31 /mails/example.com/test/maildir/tmp drwxr-xr-x 4 root root 4096 2008-02-13 21:31 /mails/exim.depesz drwxr-xr-x 3 10004 10002 4096 2008-02-02 23:57 /mails/exim.depesz/depesz drwxr-xr-x 5 10004 10002 4096 2008-02-02 23:58 /mails/exim.depesz/depesz/maildir drwxr-xr-x 2 10004 10002 4096 2008-02-02 23:58 /mails/exim.depesz/depesz/maildir/cur drwxr-xr-x 2 10004 10002 4096 2008-02-12 22:17 /mails/exim.depesz/depesz/maildir/new drwxr-xr-x 2 10004 10002 4096 2008-02-12 22:17 /mails/exim.depesz/depesz/maildir/tmp drwxr-xr-x 3 10004 10002 4096 2008-02-13 21:31 /mails/exim.depesz/test drwxr-xr-x 5 10004 10002 4096 2008-02-13 21:31 /mails/exim.depesz/test/maildir drwxr-xr-x 2 10004 10002 4096 2008-02-13 21:31 /mails/exim.depesz/test/maildir/cur drwxr-xr-x 2 10004 10002 4096 2008-02-13 21:31 /mails/exim.depesz/test/maildir/new drwxr-xr-x 2 10004 10002 4096 2008-02-13 21:31 /mails/exim.depesz/test/maildir/tmp drwxr-xr-x 4 root root 4096 2008-02-13 21:31 /mails/localhost drwxr-xr-x 3 10002 10002 4096 2008-02-13 21:31 /mails/localhost/depesz drwxr-xr-x 5 10002 10002 4096 2008-02-13 21:31 /mails/localhost/depesz/maildir drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/depesz/maildir/cur drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/depesz/maildir/new drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/depesz/maildir/tmp drwxr-xr-x 3 10002 10002 4096 2008-02-13 21:31 /mails/localhost/test drwxr-xr-x 5 10002 10002 4096 2008-02-13 21:31 /mails/localhost/test/maildir drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/test/maildir/cur drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/test/maildir/new drwxr-xr-x 2 10002 10002 4096 2008-02-13 21:31 /mails/localhost/test/maildir/tmp
you could also change the “mode" to 700 (rwx——), but since it's trivial given above examples, i leave it as an excersise for the reader.
now. we have the dirs. postgresql knows what quota should be set, so we need to set this quota. nothing simpler:
eximtest=> SELECT 'setquota -g ' || system_gid || ' ' || (quota_mb * 1024)::text || ' ' || (quota_mb * 1024)::text || ' 0 0 /' FROM customers; ?COLUMN? ------------------------------------- setquota -g 10000 0 0 0 0 / setquota -g 10001 0 0 0 0 / setquota -g 10002 10240 10240 0 0 / (3 ROWS)
for groups (customers). and for users (domains):
eximtest=> SELECT 'setquota -u ' || system_uid || ' ' || (quota_mb * 1024)::text || ' ' || (quota_mb * 1024)::text || ' 0 0 /' FROM domains; ?COLUMN? ----------------------------------- setquota -u 10000 0 0 0 0 / setquota -u 10001 0 0 0 0 / setquota -u 10002 4096 4096 0 0 / setquota -u 10003 4096 4096 0 0 / setquota -u 10004 4096 4096 0 0 / setquota -u 10005 4096 4096 0 0 / (6 ROWS)
of course – this should be redone after every change of quota values in database.
so, at the moment system is ready. all we need is to make exim use our uid/gid when storing mail in maildir.
luckily – this is really simple. first, you need to find (again) pg_delivery transport. and add these 2 lines to it:
USER = ${lookup pgsql{SELECT get_account_uid('${local_part}', '${domain}')}} GROUP = ${lookup pgsql{SELECT get_account_gid('${local_part}', '${domain}')}}
after this, this transport should look like this:
pg_delivery: debug_print = "T: pg_delivery for $local_part@$domain" driver = appendfile maildir_format directory = ${lookup pgsql{SELECT get_account_maildir('${local_part}', '${domain}')}} quota = ${lookup pgsql{SELECT get_account_quota('${local_part}', '${domain}')}} USER = ${lookup pgsql{SELECT get_account_uid('${local_part}', '${domain}')}} GROUP = ${lookup pgsql{SELECT get_account_gid('${local_part}', '${domain}')}}
so, let's restart exim, and do some tests.
root@xxx:~# repquota -g /
*** Report for group quotas on device /dev/sda2
Block grace time: 7days; Inode grace time: 7days
Block limits File limits
Group used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10002 -- 160 10240 10240 40 0 0root@xxx:~# repquota -u /
*** Report for user quotas on device /dev/sda2
Block grace time: 7days; Inode grace time: 7days
Block limits File limits
User used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10004 -- 40 4096 4096 10 0 0
#10002 -- 40 4096 4096 10 0 0
#10005 -- 40 4096 4096 10 0 0
#10003 -- 40 4096 4096 10 0 0no mails in maildirs, everything fine.
now, let's try to deliver 2 mails (2.5mb each) to one account:
first delivery:
=> perl -e 'print "a"x49, "\n" for 1..50000' | mail -s test1 depesz@exim.depesz
logs:
2008-02-13 21:43:38 1JPPOE-0002l0-3a <= root@xxx U=root P=local S=2500303 2008-02-13 21:43:39 1JPPOE-0002l0-3a => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-02-13 21:43:39 1JPPOE-0002l0-3a Completed
delivered mail is saved:
=> ls -lad /mails/exim.depesz/depesz/maildir/new/1202939019.H61789P10607.localhost -rw------- 1 10004 10002 2500303 2008-02-13 21:43 /mails/exim.depesz/depesz/maildir/new/1202939019.H61789P10607.localhost
please not uid/gid of file 🙂
now, let's try another delivery:
=> perl -e 'print "a"x49, "\n" for 1..50000' | mail -s test1 depesz@exim.depesz
but this time, in logs:
2008-02-13 21:45:17 1JPPPo-0002lq-Lq <= root@xxx U=root P=local S=2500303 2008-02-13 21:45:17 1JPPPo-0002lq-Lq == depesz@exim.depesz R=pg_user T=pg_delivery defer (-22): mailbox is full (MTA-imposed quota exceeded while writing to tmp/1202939117.H418339P10659.localhost)
nice.
mail was put in queue.
now, this domain (exim.depesz) has quota 4mb. what will happen if i'll try to deliver 1mb mail to another account in this domain?
=> perl -e 'print "a"x49, "\n" for 1..20000' | mail -s test1 test@exim.depesz
logs:
2008-02-13 21:47:22 1JPPRp-0002mu-RP <= root@xxx U=root P=local S=1000299 2008-02-13 21:47:22 1JPPRp-0002mu-RP => test <test@exim.depesz> R=pg_user T=pg_delivery 2008-02-13 21:47:22 1JPPRp-0002mu-RP Completed
this domain has user 10004, let's check it's quota:
root@xxx:~# repquota -u /
Block limits File limits
User used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10004 -- 3472 4096 4096 12 0 0so, it has only 600k till the limit will finish. but test@exim.depesz account has only 1 mb in mails, and exim-imposed quota is 3mb. so, let's try to deliver another 1mb:
=> perl -e 'print "a"x49, "\n" for 1..20000' | mail -s test1 test@exim.depesz
logs:
2008-02-13 21:49:20 1JPPTk-0002nu-N0 <= root@xxx U=root P=local S=1000299 2008-02-13 21:49:21 1JPPTk-0002nu-N0 == test@exim.depesz R=pg_user T=pg_delivery defer (122): Disk quota exceeded: mailbox is full
now – take a look at different message. now we can tell that it was not user-quota, but rather cutomer or domain quota that was exceeded.
great. so, let's check if i can exceed group quota. current usage is:
root@xxx:~# repquota -g /
Block limits File limits
Group used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10002 -- 3592 10240 10240 42 0 02 mails:
=> perl -e 'print "a"x49, "\n" for 1..60000' | mail -s test1 test@localhost => perl -e 'print "a"x49, "\n" for 1..60000' | mail -s test1 test@example.com
logs:
2008-02-13 21:51:24 1JPPVj-0002oo-Ms <= root@xxx U=root P=local S=3000295 2008-02-13 21:51:24 1JPPVj-0002oo-Ms => test <test@localhost> R=pg_user T=pg_delivery 2008-02-13 21:51:24 1JPPVj-0002oo-Ms Completed 2008-02-13 21:51:41 1JPPW1-0002p3-AN <= root@xxx U=root P=local S=3000299 2008-02-13 21:51:42 1JPPW1-0002p3-AN => test <test@example.com> R=pg_user T=pg_delivery 2008-02-13 21:51:42 1JPPW1-0002p3-AN Completed
and now, let's try to send 1mb mail to domain “badtest":
=> perl -e 'print "a"x49, "\n" for 1..20000' | mail -s test1 test@badtest
logs:
2008-02-13 21:52:43 1JPPX0-0002pb-SN <= root@xxx U=root P=local S=1000291 2008-02-13 21:52:43 1JPPX0-0002pb-SN == test@badtest R=pg_user T=pg_delivery defer (122): Disk quota exceeded: mailbox is full
hmm .. and how does the repquota look?
root@xxx:~# repquota -g /
Block limits File limits
Group used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10002 -- 9464 10240 10240 44 0 0root@xxx:~# repquota -u /
Block limits File limits
User used soft hard grace used soft hard grace
----------------------------------------------------------------------
...
#10004 -- 3472 4096 4096 12 0 0
#10002 -- 2976 4096 4096 11 0 0
#10005 -- 40 4096 4096 10 0 0
#10003 -- 2976 4096 4096 11 0 0please note that uid 10005 (which relates to domain “badtest") has practically zero usage of diskspace.
bad thing is that we cannot differentiate whether it was customer or domain quota that was exceeded, but with a simple repquota you can easily find it out.
so, to summarize:
we can (and i showed how) add 3 levels of quota: for customer, domains and user. we can easily differentiate between user-quota and other-quota execeeding, but it's not as trivial to differentiate customer and domain quota violations.
together with previous parts of this blog, we have fully working smtp server, with ability to deliver mail to local mailboxes, use aliases, and enforce quotas.
in next part i'll show how to add database-based smtp-auth for mail sending authorization, and courier configuration in a way that courier pop/imap will be able to serve your mails 🙂
copy of exim4.conf.template with changes from this post is available here. regenerated config from /var/lib/exim4 is available here.
Hi, thanks very much for these articles. I’ve been trying to set up exim and postgres some time ago with no luck, but this is really excellent! Waiting for the next part 🙂
@Tomas Kejzlar:
i’m glad somebody likes it 🙂
next part should be on wednesday, but, to be honest, i didn’t write it yet.
Great. Really great. Pinpoint…
Thanks a lot for this job.