# HG changeset patch # User martin f. krafft # Date 1334091390 -7200 # Node ID d863a44a6353f5d1378028204a1012ad2086c7fa # Parent 557c4703986cbfbd5b3c5aae321e9edc7f4a4e14 Make PL/pgSQL function feed back identity for mailboxes/relocated when there are catchall destinations. Without catchall aliases, if no virtual_alias matches, the query can just return NULL and Postfix will later check mailboxes/relocated for the address to rewrite. However, since virtual aliases are handled long before mailboxes/relocated, a catchall alias would also catch mail to mailboxes and relocated addresses, which we do not want. The way to tell postfix to keep delivering is for the virtual alias map to return the search key itself (identity function). This patch changes the postfix_virtual_alias_maps Pl/pgSQL function to do exactly that, but only if there are catchall destinations defined for the domain in question — otherwise it returns NULL when no match is found. diff -r 557c4703986c -r d863a44a6353 pgsql/create_tables-dovecot-1.2.x.pgsql --- a/pgsql/create_tables-dovecot-1.2.x.pgsql Tue Apr 10 01:08:25 2012 +0200 +++ b/pgsql/create_tables-dovecot-1.2.x.pgsql Tue Apr 10 22:56:30 2012 +0200 @@ -586,7 +586,9 @@ RETURNS SETOF recipient_destination AS $$ DECLARE + recordc recipient_destination; record recipient_destination; + catchall_cursor refcursor; recipient varchar(320) := localpart || '@' || the_domain; did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); BEGIN @@ -598,31 +600,51 @@ LOOP RETURN NEXT record; END LOOP; + IF NOT FOUND THEN - -- First, we have to check existing mailboxes and - -- return identity if one exists - FOR record IN - SELECT recipient, recipient as destination - FROM users - WHERE gid = did - AND local_part = localpart - UNION SELECT recipient, recipient as destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - -- Only now can we think about catchalls... - IF NOT FOUND THEN + -- There is no matching virtual_alias. If there are no catchall + -- records for this domain, we can just return NULL since Postfix + -- will then later consult mailboxes/relocated itself. But if + -- there is a catchall destination, then it would take precedence + -- over mailboxes/relocated, which is not what we want. Therefore, + -- we must first find out if the query is for an existing mailbox + -- or relocated entry and return the identity mapping if that is + -- the case + OPEN catchall_cursor FOR + SELECT recipient, destination + FROM catchall + WHERE gid = did; + FETCH NEXT FROM catchall_cursor INTO recordc; + + IF recordc IS NOT NULL THEN + -- Since there are catchall records for this domain + -- check the mailbox and relocated records and return identity + -- if a matching record exists. FOR record IN - SELECT recipient, destination - FROM catchall + SELECT recipient, recipient as destination + FROM users WHERE gid = did + AND local_part = localpart + UNION SELECT recipient, recipient as destination + FROM relocated + WHERE gid = did + AND address = localpart LOOP RETURN NEXT record; END LOOP; + + IF NOT FOUND THEN + -- There were no records found for mailboxes/relocated, + -- so now we can actually iterate the cursor and populate + -- the return set + LOOP + RETURN NEXT recordc; + FETCH NEXT FROM catchall_cursor INTO recordc; + EXIT WHEN recordc IS NULL; + END LOOP; + END IF; END IF; + CLOSE catchall_cursor; END IF; RETURN; END; diff -r 557c4703986c -r d863a44a6353 pgsql/create_tables.pgsql --- a/pgsql/create_tables.pgsql Tue Apr 10 01:08:25 2012 +0200 +++ b/pgsql/create_tables.pgsql Tue Apr 10 22:56:30 2012 +0200 @@ -563,7 +563,9 @@ RETURNS SETOF recipient_destination AS $$ DECLARE + recordc recipient_destination; record recipient_destination; + catchall_cursor refcursor; recipient varchar(320) := localpart || '@' || the_domain; did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); BEGIN @@ -575,31 +577,51 @@ LOOP RETURN NEXT record; END LOOP; + IF NOT FOUND THEN - -- First, we have to check existing mailboxes and - -- return identity if one exists - FOR record IN - SELECT recipient, recipient as destination - FROM users - WHERE gid = did - AND local_part = localpart - UNION SELECT recipient, recipient as destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - -- Only now can we think about catchalls... - IF NOT FOUND THEN + -- There is no matching virtual_alias. If there are no catchall + -- records for this domain, we can just return NULL since Postfix + -- will then later consult mailboxes/relocated itself. But if + -- there is a catchall destination, then it would take precedence + -- over mailboxes/relocated, which is not what we want. Therefore, + -- we must first find out if the query is for an existing mailbox + -- or relocated entry and return the identity mapping if that is + -- the case + OPEN catchall_cursor FOR + SELECT recipient, destination + FROM catchall + WHERE gid = did; + FETCH NEXT FROM catchall_cursor INTO recordc; + + IF recordc IS NOT NULL THEN + -- Since there are catchall records for this domain + -- check the mailbox and relocated records and return identity + -- if a matching record exists. FOR record IN - SELECT recipient, destination - FROM catchall + SELECT recipient, recipient as destination + FROM users WHERE gid = did + AND local_part = localpart + UNION SELECT recipient, recipient as destination + FROM relocated + WHERE gid = did + AND address = localpart LOOP RETURN NEXT record; END LOOP; + + IF NOT FOUND THEN + -- There were no records found for mailboxes/relocated, + -- so now we can actually iterate the cursor and populate + -- the return set + LOOP + RETURN NEXT recordc; + FETCH NEXT FROM catchall_cursor INTO recordc; + EXIT WHEN recordc IS NULL; + END LOOP; + END IF; END IF; + CLOSE catchall_cursor; END IF; RETURN; END; diff -r 557c4703986c -r d863a44a6353 pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql --- a/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Tue Apr 10 01:08:25 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql Tue Apr 10 22:56:30 2012 +0200 @@ -483,7 +483,9 @@ RETURNS SETOF recipient_destination AS $$ DECLARE + recordc recipient_destination; record recipient_destination; + catchall_cursor refcursor; recipient varchar(320) := localpart || '@' || the_domain; did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); BEGIN @@ -495,31 +497,51 @@ LOOP RETURN NEXT record; END LOOP; + IF NOT FOUND THEN - -- First, we have to check existing mailboxes and - -- return identity if one exists - FOR record IN - SELECT recipient, recipient as destination - FROM users - WHERE gid = did - AND local_part = localpart - UNION SELECT recipient, recipient as destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - -- Only now can we think about catchalls... - IF NOT FOUND THEN + -- There is no matching virtual_alias. If there are no catchall + -- records for this domain, we can just return NULL since Postfix + -- will then later consult mailboxes/relocated itself. But if + -- there is a catchall destination, then it would take precedence + -- over mailboxes/relocated, which is not what we want. Therefore, + -- we must first find out if the query is for an existing mailbox + -- or relocated entry and return the identity mapping if that is + -- the case + OPEN catchall_cursor FOR + SELECT recipient, destination + FROM catchall + WHERE gid = did; + FETCH NEXT FROM catchall_cursor INTO recordc; + + IF recordc IS NOT NULL THEN + -- Since there are catchall records for this domain + -- check the mailbox and relocated records and return identity + -- if a matching record exists. FOR record IN - SELECT recipient, destination - FROM catchall + SELECT recipient, recipient as destination + FROM users WHERE gid = did + AND local_part = localpart + UNION SELECT recipient, recipient as destination + FROM relocated + WHERE gid = did + AND address = localpart LOOP RETURN NEXT record; END LOOP; + + IF NOT FOUND THEN + -- There were no records found for mailboxes/relocated, + -- so now we can actually iterate the cursor and populate + -- the return set + LOOP + RETURN NEXT recordc; + FETCH NEXT FROM catchall_cursor INTO recordc; + EXIT WHEN recordc IS NULL; + END LOOP; + END IF; END IF; + CLOSE catchall_cursor; END IF; RETURN; END; diff -r 557c4703986c -r d863a44a6353 pgsql/update_tables_0.5.x-0.6.pgsql --- a/pgsql/update_tables_0.5.x-0.6.pgsql Tue Apr 10 01:08:25 2012 +0200 +++ b/pgsql/update_tables_0.5.x-0.6.pgsql Tue Apr 10 22:56:30 2012 +0200 @@ -461,7 +461,9 @@ RETURNS SETOF recipient_destination AS $$ DECLARE + recordc recipient_destination; record recipient_destination; + catchall_cursor refcursor; recipient varchar(320) := localpart || '@' || the_domain; did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); BEGIN @@ -473,31 +475,51 @@ LOOP RETURN NEXT record; END LOOP; + IF NOT FOUND THEN - -- First, we have to check existing mailboxes and - -- return identity if one exists - FOR record IN - SELECT recipient, recipient as destination - FROM users - WHERE gid = did - AND local_part = localpart - UNION SELECT recipient, recipient as destination - FROM relocated - WHERE gid = did - AND address = localpart - LOOP - RETURN NEXT record; - END LOOP; - -- Only now can we think about catchalls... - IF NOT FOUND THEN + -- There is no matching virtual_alias. If there are no catchall + -- records for this domain, we can just return NULL since Postfix + -- will then later consult mailboxes/relocated itself. But if + -- there is a catchall destination, then it would take precedence + -- over mailboxes/relocated, which is not what we want. Therefore, + -- we must first find out if the query is for an existing mailbox + -- or relocated entry and return the identity mapping if that is + -- the case + OPEN catchall_cursor FOR + SELECT recipient, destination + FROM catchall + WHERE gid = did; + FETCH NEXT FROM catchall_cursor INTO recordc; + + IF recordc IS NOT NULL THEN + -- Since there are catchall records for this domain + -- check the mailbox and relocated records and return identity + -- if a matching record exists. FOR record IN - SELECT recipient, destination - FROM catchall + SELECT recipient, recipient as destination + FROM users WHERE gid = did + AND local_part = localpart + UNION SELECT recipient, recipient as destination + FROM relocated + WHERE gid = did + AND address = localpart LOOP RETURN NEXT record; END LOOP; + + IF NOT FOUND THEN + -- There were no records found for mailboxes/relocated, + -- so now we can actually iterate the cursor and populate + -- the return set + LOOP + RETURN NEXT recordc; + FETCH NEXT FROM catchall_cursor INTO recordc; + EXIT WHEN recordc IS NULL; + END LOOP; + END IF; END IF; + CLOSE catchall_cursor; END IF; RETURN; END;