4 -- This file contains some data types and functions these should speed up some |
4 -- This file contains some data types and functions these should speed up some |
5 -- operations. Read the comment on each data type/functions for more details. |
5 -- operations. Read the comment on each data type/functions for more details. |
6 -- --- |
6 -- --- |
7 |
7 |
8 -- --- |
8 -- --- |
9 -- Data type for function postfix_smtpd_sender_login(varchar, varchar) |
9 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
10 -- --- |
10 -- --- |
11 CREATE TYPE sender_login AS ( |
11 CREATE TYPE sender_login AS ( |
12 sender varchar(320), |
12 sender varchar(320), |
13 login text |
13 login text |
14 ); |
14 ); |
15 |
15 |
16 -- --- |
16 -- --- |
17 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
17 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
18 -- varchar localpart |
18 -- varchar localpart |
23 -- Required access privileges for your postfix database user: |
23 -- Required access privileges for your postfix database user: |
24 -- GRANT SELECT ON domain_name, users, alias TO postfix; |
24 -- GRANT SELECT ON domain_name, users, alias TO postfix; |
25 -- |
25 -- |
26 -- For more details see postconf(5) section smtpd_sender_login_maps |
26 -- For more details see postconf(5) section smtpd_sender_login_maps |
27 -- --- |
27 -- --- |
28 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login( |
28 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
29 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
29 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
30 AS $$ |
30 AS $$ |
31 DECLARE |
31 DECLARE |
32 rec sender_login; |
32 rec sender_login; |
33 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
33 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
102 END; |
102 END; |
103 $$ LANGUAGE plpgsql STABLE |
103 $$ LANGUAGE plpgsql STABLE |
104 RETURNS NULL ON NULL INPUT |
104 RETURNS NULL ON NULL INPUT |
105 EXTERNAL SECURITY INVOKER; |
105 EXTERNAL SECURITY INVOKER; |
106 |
106 |
|
107 -- ########################################################################## -- |
|
108 |
|
109 -- --- |
|
110 -- Data type for function postfix_virtual_alias_map(varchar, varchar) |
|
111 -- --- |
|
112 CREATE TYPE recipient_destination AS ( |
|
113 recipient varchar(320), |
|
114 destination text |
|
115 ); |
|
116 |
|
117 -- --- |
|
118 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
119 -- varchar localpart |
|
120 -- varchar the_domain |
|
121 -- Returns: recipient_destination records |
|
122 -- |
|
123 -- Required access privileges for your postfix database user: |
|
124 -- GRANT SELECT ON alias TO postfix; |
|
125 -- |
|
126 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
|
127 -- --- |
|
128 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
129 IN localpart varchar, IN the_domain varchar) |
|
130 RETURNS SETOF recipient_destination |
|
131 AS $$ |
|
132 DECLARE |
|
133 record recipient_destination; |
|
134 recipient varchar(320) := localpart || '@' || the_domain; |
|
135 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
136 BEGIN |
|
137 FOR record IN |
|
138 SELECT recipient, destination |
|
139 FROM alias |
|
140 WHERE gid = did |
|
141 AND address = localpart |
|
142 LOOP |
|
143 RETURN NEXT record; |
|
144 END LOOP; |
|
145 RETURN; |
|
146 END; |
|
147 $$ LANGUAGE plpgsql STABLE |
|
148 RETURNS NULL ON NULL INPUT |
|
149 EXTERNAL SECURITY INVOKER; |