582 -- Required access privileges for your postfix database user: |
582 -- Required access privileges for your postfix database user: |
583 -- GRANT SELECT ON alias, domain_name TO postfix; |
583 -- GRANT SELECT ON alias, domain_name TO postfix; |
584 -- |
584 -- |
585 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
585 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
586 -- --- |
586 -- --- |
|
587 CREATE OR REPLACE FUNCTION _interpolate_destination( |
|
588 IN destination varchar, localpart varchar, IN the_domain varchar) |
|
589 RETURNS varchar |
|
590 AS $$ |
|
591 DECLARE |
|
592 result varchar(320); |
|
593 BEGIN |
|
594 IF position('%' in destination) = 0 THEN |
|
595 RETURN destination; |
|
596 END IF; |
|
597 result := replace(destination, '%n', localpart); |
|
598 result := replace(result, '%d', the_domain); |
|
599 result := replace(result, '%=', localpart || '=' || the_domain); |
|
600 RETURN result; |
|
601 END; |
|
602 $$ LANGUAGE plpgsql STABLE |
|
603 RETURNS NULL ON NULL INPUT |
|
604 EXTERNAL SECURITY INVOKER; |
|
605 |
587 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
606 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
588 IN localpart varchar, IN the_domain varchar) |
607 IN localpart varchar, IN the_domain varchar) |
589 RETURNS SETOF recipient_destination |
608 RETURNS SETOF recipient_destination |
590 AS $$ |
609 AS $$ |
591 DECLARE |
610 DECLARE |
594 catchall_cursor refcursor; |
613 catchall_cursor refcursor; |
595 recipient varchar(320) := localpart || '@' || the_domain; |
614 recipient varchar(320) := localpart || '@' || the_domain; |
596 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
615 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
597 BEGIN |
616 BEGIN |
598 FOR record IN |
617 FOR record IN |
599 SELECT recipient, destination |
618 SELECT recipient, |
|
619 _interpolate_destination(destination, localpart, the_domain) |
600 FROM alias |
620 FROM alias |
601 WHERE gid = did |
621 WHERE gid = did |
602 AND address = localpart |
622 AND address = localpart |
603 LOOP |
623 LOOP |
604 RETURN NEXT record; |
624 RETURN NEXT record; |
612 -- over mailboxes/relocated, which is not what we want. Therefore, |
632 -- over mailboxes/relocated, which is not what we want. Therefore, |
613 -- we must first find out if the query is for an existing mailbox |
633 -- we must first find out if the query is for an existing mailbox |
614 -- or relocated entry and return the identity mapping if that is |
634 -- or relocated entry and return the identity mapping if that is |
615 -- the case |
635 -- the case |
616 OPEN catchall_cursor FOR |
636 OPEN catchall_cursor FOR |
617 SELECT recipient, destination |
637 SELECT recipient, |
|
638 _interpolate_destination(destination, localpart, the_domain) |
618 FROM catchall |
639 FROM catchall |
619 WHERE gid = did; |
640 WHERE gid = did; |
620 FETCH NEXT FROM catchall_cursor INTO recordc; |
641 FETCH NEXT FROM catchall_cursor INTO recordc; |
621 |
642 |
622 IF recordc IS NOT NULL THEN |
643 IF recordc IS NOT NULL THEN |