559 -- Required access privileges for your postfix database user: |
559 -- Required access privileges for your postfix database user: |
560 -- GRANT SELECT ON alias, domain_name TO postfix; |
560 -- GRANT SELECT ON alias, domain_name TO postfix; |
561 -- |
561 -- |
562 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
562 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
563 -- --- |
563 -- --- |
|
564 CREATE OR REPLACE FUNCTION _interpolate_destination( |
|
565 IN destination varchar, localpart varchar, IN the_domain varchar) |
|
566 RETURNS varchar |
|
567 AS $$ |
|
568 DECLARE |
|
569 result varchar(320); |
|
570 BEGIN |
|
571 IF position('%' in destination) = 0 THEN |
|
572 RETURN destination; |
|
573 END IF; |
|
574 result := replace(destination, '%n', localpart); |
|
575 result := replace(result, '%d', the_domain); |
|
576 result := replace(result, '%=', localpart || '=' || the_domain); |
|
577 RETURN result; |
|
578 END; |
|
579 $$ LANGUAGE plpgsql STABLE |
|
580 RETURNS NULL ON NULL INPUT |
|
581 EXTERNAL SECURITY INVOKER; |
|
582 |
564 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
583 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
565 IN localpart varchar, IN the_domain varchar) |
584 IN localpart varchar, IN the_domain varchar) |
566 RETURNS SETOF recipient_destination |
585 RETURNS SETOF recipient_destination |
567 AS $$ |
586 AS $$ |
568 DECLARE |
587 DECLARE |
571 catchall_cursor refcursor; |
590 catchall_cursor refcursor; |
572 recipient varchar(320) := localpart || '@' || the_domain; |
591 recipient varchar(320) := localpart || '@' || the_domain; |
573 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
592 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
574 BEGIN |
593 BEGIN |
575 FOR record IN |
594 FOR record IN |
576 SELECT recipient, destination |
595 SELECT recipient, |
|
596 _interpolate_destination(destination, localpart, the_domain) |
577 FROM alias |
597 FROM alias |
578 WHERE gid = did |
598 WHERE gid = did |
579 AND address = localpart |
599 AND address = localpart |
580 LOOP |
600 LOOP |
581 RETURN NEXT record; |
601 RETURN NEXT record; |
589 -- over mailboxes/relocated, which is not what we want. Therefore, |
609 -- over mailboxes/relocated, which is not what we want. Therefore, |
590 -- we must first find out if the query is for an existing mailbox |
610 -- we must first find out if the query is for an existing mailbox |
591 -- or relocated entry and return the identity mapping if that is |
611 -- or relocated entry and return the identity mapping if that is |
592 -- the case |
612 -- the case |
593 OPEN catchall_cursor FOR |
613 OPEN catchall_cursor FOR |
594 SELECT recipient, destination |
614 SELECT recipient, |
|
615 _interpolate_destination(destination, localpart, the_domain) |
595 FROM catchall |
616 FROM catchall |
596 WHERE gid = did; |
617 WHERE gid = did; |
597 FETCH NEXT FROM catchall_cursor INTO recordc; |
618 FETCH NEXT FROM catchall_cursor INTO recordc; |
598 |
619 |
599 IF recordc IS NOT NULL THEN |
620 IF recordc IS NOT NULL THEN |