smtp + sql = more than it seems so (part 9)

previously, we talked about procmail filters in exim+sql system. today we will add some nifty aliases capabilities.

this is actually the first part of this howto that deals only with “more than it seems so" part of title.

what i mean is that i will show you 2 new features without even touching exim configuration – all changes will be in sql code.

both changes are related to aliases handling.

first is quite simple: i would like to have “domain aliases".

what i mean by this. i want to add new domain to mail system, but whenever somebody sends mail to this new domain mail is automatically sent to same account in different domain.

usage? very simple: i have base domain depesz.com. let's assume i registered also depesz.cc, depesz.cx, depesz.pl and depesz.ai.

i dont want any separate accounts in all of these domains. if somebody sends mail to depesz@depesz.cc it should be delivered to depesz@depesz.com.

of course i can do it now – i just add new domain, and enter aliases in this domain pointing to depesz.com domain.

but this is very tedious. for every new account/alias in depesz.com i would have to add new alias in .cc, .cx, .pl and .ai. a lot of work. and a lot of possible places for errors.

let's do it better.

to do it i will need a way to say: this domain is merely an alias for different domain:

ALTER TABLE domains ADD COLUMN aliasing int4;
ALTER TABLE domains ADD FOREIGN KEY (aliasing) REFERENCES domains (id);

now, let's modify our domains a bit:

eximtest=> SELECT * FROM domains;
 id |  fullname   | customer_id | quota_mb | system_uid | aliasing
----+-------------+-------------+----------+------------+----------
  3 | exim.depesz |           1 |        4 |      10004 |
  1 | localhost   |           1 |        4 |      10002 |        3
  2 | example.com |           1 |        4 |      10003 |        3
  4 | badtest     |           1 |        4 |      10005 |        3
(4 ROWS)

now, since the domains are only aliases, we dont need any acounts/aliases in them:

eximtest=> DELETE FROM aliases WHERE domain_id <> 3;
DELETE 0
eximtest=> DELETE FROM accounts WHERE domain_id <> 3;
DELETE 6

ok. let's check if everything works now as expected. sending now mail to depesz@localhost should fail:

=> exim4 -bt depesz@localhost
R: pg_aliases for depesz@localhost
R: pg_vacation for depesz@localhost
R: pg_userforward for depesz@localhost
R: procmail for depesz@localhost
R: pg_user for depesz@localhost
depesz@localhost is undeliverable: Unrouteable address

but for depesz@exim.depesz it should work:

=> exim4 -bt depesz@exim.depesz
R: pg_aliases for depesz@exim.depesz
R: pg_vacation for depesz@exim.depesz
R: pg_userforward for depesz@exim.depesz
R: procmail for depesz@exim.depesz
depesz@exim.depesz
  router = procmail, transport = procmail_pipe

ok. so now, let's fix the depesz@localhost issue. to do so i will have to rewrite get_alias_destinations function:

CREATE OR REPLACE FUNCTION get_alias_destinations(in_username TEXT, in_domain TEXT) RETURNS SETOF TEXT AS $$
DECLARE
    use_username text := TRIM(BOTH FROM LOWER(in_username));
    use_domain   text := TRIM(BOTH FROM LOWER(in_domain));
    temprec      record;
BEGIN
    SELECT use_username || '@' || ad.fullname AS destination INTO temprec FROM domains d JOIN domains ad ON d.aliasing = ad.id WHERE d.fullname = use_domain;
    IF found THEN
        RETURN NEXT temprec.destination;
        RETURN;
    END IF;
    FOR temprec IN
        SELECT a.*
        FROM aliases a JOIN domains d ON a.domain_id = d.id
        WHERE a.username = use_username AND d.fullname = use_domain
    LOOP
        RETURN NEXT temprec.destination;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE 'plpgsql';

if you dont remember original get_alias_destinations function, i simply added this code:

    SELECT use_username || '@' || ad.fullname AS destination INTO temprec FROM domains d JOIN domains ad ON d.aliasing = ad.id WHERE d.fullname = use_domain;
    IF found THEN
        RETURN NEXT temprec.destination;
        RETURN;
    END IF;

since it is at the beginning – it will be more important than any other alias.

so, let's check if it works:

=> exim4 -bt depesz@localhost
R: pg_aliases for depesz@localhost
R: pg_aliases for depesz@exim.depesz
R: pg_vacation for depesz@exim.depesz
R: pg_userforward for depesz@exim.depesz
R: procmail for depesz@exim.depesz
depesz@exim.depesz
    <-- depesz@localhost
  router = procmail, transport = procmail_pipe

great. it works.

now. gmail “+" aliases.

if you dont know what i mean – great. you will see it here for the first time 🙂

gmail introduced interesting option: you can add “+something" to any email address, and it will be delivered anyway to base account.

for example: i can have depesz@gmail.com account, but give addresses like depesz+public@gmail.com, depesz+onet@gmail.com and so on.

this is actually pretty nifty because it lets you filter mails based on “to" address, while still getting them all on one account.

so, i'd like to add this functionality to my pg-exim 🙂

how it should work:

  • if given address is domain-alias, it should be treated as domain alias
  • if given address is standard alias it should be treated as standard alias. bu standard i mean: there is row in aliases table which username value matches given address.
  • otherwise, remove everything from “+", and try to match it against accounts or aliases!

looks simply, now, i have to (again) modify get_alias_destinations() function:

CREATE OR REPLACE FUNCTION get_alias_destinations(in_username TEXT, in_domain TEXT) RETURNS SETOF TEXT AS $$
DECLARE
    use_username  text := TRIM(BOTH FROM LOWER(in_username));
    use_domain    text := TRIM(BOTH FROM LOWER(in_domain));
    temprec       record;
    found_already bool := FALSE;
BEGIN
    SELECT use_username || '@' || ad.fullname AS destination INTO temprec FROM domains d JOIN domains ad ON d.aliasing = ad.id WHERE d.fullname = use_domain;
    IF found THEN
        RETURN NEXT temprec.destination;
        RETURN;
    END IF;
    FOR temprec IN
        SELECT a.*
        FROM aliases a JOIN domains d ON a.domain_id = d.id
        WHERE a.username = use_username AND d.fullname = use_domain
    LOOP
        RETURN NEXT temprec.destination;
        found_already := TRUE;
    END LOOP;
    IF ( found_already = TRUE ) OR ( POSITION('+' IN use_username) = 0 ) THEN
        RETURN;
    END IF;
    use_username := SUBSTRING( use_username FROM E'^(.*)\\+' );
    RETURN NEXT use_username || '@' || use_domain;
    RETURN;
END;
$$ LANGUAGE 'plpgsql';

so, how it works?

=> exim4 -bt depesz+qqq@exim.depesz
R: pg_aliases for depesz+qqq@exim.depesz
R: pg_aliases for depesz@exim.depesz
R: pg_vacation for depesz@exim.depesz
R: pg_userforward for depesz@exim.depesz
R: procmail for depesz@exim.depesz
depesz@exim.depesz
    <-- depesz+qqq@exim.depesz
  router = procmail, transport = procmail_pipe

of course it will also work with aliased domains:

=> exim4 -bt depesz+qqq@localhost
R: pg_aliases for depesz+qqq@localhost
R: pg_aliases for depesz+qqq@exim.depesz
R: pg_aliases for depesz@exim.depesz
R: pg_vacation for depesz@exim.depesz
R: pg_userforward for depesz@exim.depesz
R: procmail for depesz@exim.depesz
depesz@exim.depesz
    <-- depesz+qqq@exim.depesz
    <-- depesz+qqq@localhost
  router = procmail, transport = procmail_pipe

and it will also work with aliases:

eximtest=> INSERT INTO aliases (domain_id, username, destination) VALUES (3, 'newtest', 'depesz@exim.depesz');
INSERT 0 1

and now:

=> exim4 -bt newtest+qqq@localhost
R: pg_aliases for newtest+qqq@localhost
R: pg_aliases for newtest+qqq@exim.depesz
R: pg_aliases for newtest@exim.depesz
R: pg_aliases for depesz@exim.depesz
R: pg_vacation for depesz@exim.depesz
R: pg_userforward for depesz@exim.depesz
R: procmail for depesz@exim.depesz
depesz@exim.depesz
    <-- newtest@exim.depesz
    <-- newtest+qqq@exim.depesz
    <-- newtest+qqq@localhost
  router = procmail, transport = procmail_pipe

sweet, works.

One thought on “smtp + sql = more than it seems so (part 9)”

Comments are closed.