187 

   188 -- ---

   189 -- Drop all known v0.5 types (the dirty way)

   190 -- ---

   191 DROP TYPE address_maildir CASCADE;

   192 DROP TYPE dovecotpassword CASCADE;

   193 DROP TYPE dovecotuser CASCADE;

   194 DROP TYPE recipient_destination CASCADE;

   195 DROP TYPE recipient_transport CASCADE;

   196 DROP TYPE recipient_uid CASCADE;

   197 DROP TYPE sender_login CASCADE;

   198 

   199 -- ######################## TYPEs ########################################### --

   200 

   201 -- ---

   202 -- Data type for function postfix_virtual_mailbox(varchar, varchar)

   203 -- ---

   204 CREATE TYPE address_maildir AS (

   205     address varchar(320),

   206     maildir text

   207 );

   208 -- ---

   209 -- Data type for function dovecotpassword(varchar, varchar)

   210 -- ---

   211 CREATE TYPE dovecotpassword AS (

   212     userid    varchar(320),

   213     password  varchar(270),

   214     smtp      boolean,

   215     pop3      boolean,

   216     imap      boolean,

   217     sieve     boolean

   218 );

   219 -- ---

   220 -- Data type for function dovecotquotauser(varchar, varchar)

   221 -- ---

   222 CREATE TYPE dovecotquotauser AS (

   223     userid      varchar(320),

   224     uid         bigint,

   225     gid         bigint,

   226     home        text,

   227     mail        text,

   228     quota_rule  text

   229 );

   230 -- ---

   231 -- Data type for function dovecotuser(varchar, varchar)

   232 -- ---

   233 CREATE TYPE dovecotuser AS (

   234     userid      varchar(320),

   235     uid         bigint,

   236     gid         bigint,

   237     home        text,

   238     mail        text

   239 );

   240 -- ---

   241 -- Data type for functions: postfix_relocated_map(varchar, varchar)

   242 --                          postfix_virtual_alias_map(varchar, varchar)

   243 -- ---

   244 CREATE TYPE recipient_destination AS (

   245     recipient   varchar(320),

   246     destination text

   247 );

   248 -- ---

   249 -- Data type for function postfix_transport_map(varchar, varchar)

   250 -- ---

   251 CREATE TYPE recipient_transport AS (

   252     recipient   varchar(320),

   253     transport   text

   254 );

   255 -- ---

   256 -- Data type for function postfix_virtual_uid_map(varchar, varchar)

   257 -- ---

   258 CREATE TYPE recipient_uid AS (

   259     recipient   varchar(320),

   260     uid         bigint

   261 );

   262 -- ---

   263 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)

   264 -- ---

   265 CREATE TYPE sender_login AS (

   266     sender  varchar(320),

   267     login   text

   268 );

   269 

   270 -- ######################## FUNCTIONs ####################################### --

   271 

   272 -- ---

   273 -- Parameters (from login name [localpart@the_domain]):

   274 --      varchar localpart

   275 --      varchar the_domain

   276 -- Returns: dovecotpassword records

   277 -- ---

   278 CREATE OR REPLACE FUNCTION dovecotpassword(

   279     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword

   280 AS $$

   281     DECLARE

   282         record dovecotpassword;

   283         userid varchar(320) := localpart || '@' || the_domain;

   284     BEGIN

   285         FOR record IN

   286             SELECT userid, passwd, smtp, pop3, imap, sieve

   287               FROM users, service_set

   288              WHERE gid = (SELECT gid

   289                             FROM domain_name

   290                            WHERE domainname = the_domain)

   291                AND local_part = localpart

   292                AND service_set.ssid = users.ssid

   293             LOOP

   294                 RETURN NEXT record;

   295             END LOOP;

   296         RETURN;

   297     END;

   298 $$ LANGUAGE plpgsql STABLE

   299 RETURNS NULL ON NULL INPUT

   300 EXTERNAL SECURITY INVOKER;

   301 -- ---

   302 -- Nearly the same as function dovecotuser below. It returns additionally the

   303 -- field quota_rule.

   304 -- ---

   305 CREATE OR REPLACE FUNCTION dovecotquotauser(

   306     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser

   307 AS $$

   308     DECLARE

   309         record dovecotquotauser;

   310         userid varchar(320) := localpart || '@' || the_domain;

   311         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   312     BEGIN

   313         FOR record IN

   314             SELECT userid, uid, did, domaindir || '/' || uid AS home,

   315                    format || ':~/' || directory AS mail, '*:bytes=' ||

   316                    bytes || ':messages=' || messages AS quota_rule

   317               FROM users, domain_data, mailboxformat, maillocation, quotalimit

   318              WHERE users.gid = did

   319                AND users.local_part = localpart

   320                AND maillocation.mid = users.mid

   321                AND mailboxformat.fid = maillocation.fid

   322                AND domain_data.gid = did

   323                AND quotalimit.qid = users.qid

   324             LOOP

   325                 RETURN NEXT record;

   326             END LOOP;

   327         RETURN;

   328     END;

   329 $$ LANGUAGE plpgsql STABLE

   330 RETURNS NULL ON NULL INPUT

   331 EXTERNAL SECURITY INVOKER;

   332 -- ---

   333 -- Parameters (from login name [localpart@the_domain]):

   334 --      varchar localpart

   335 --      varchar the_domain

   336 -- Returns: dovecotuser records

   337 -- ---

   338 CREATE OR REPLACE FUNCTION dovecotuser(

   339     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser

   340 AS $$

   341     DECLARE

   342         record dovecotuser;

   343         userid varchar(320) := localpart || '@' || the_domain;

   344         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   345     BEGIN

   346         FOR record IN

   347             SELECT userid, uid, did, domaindir || '/' || uid AS home,

   348                    format || ':~/' || directory AS mail

   349               FROM users, domain_data, mailboxformat, maillocation

   350              WHERE users.gid = did

   351                AND users.local_part = localpart

   352                AND maillocation.mid = users.mid

   353                AND mailboxformat.fid = maillocation.fid

   354                AND domain_data.gid = did

   355             LOOP

   356                 RETURN NEXT record;

   357             END LOOP;

   358         RETURN;

   359     END;

   360 $$ LANGUAGE plpgsql STABLE

   361 RETURNS NULL ON NULL INPUT

   362 EXTERNAL SECURITY INVOKER;

   363 -- ---

   364 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   365 --      varchar localpart

   366 --      varchar the_domain

   367 -- Returns: recipient_destination records

   368 -- ---

   369 CREATE OR REPLACE FUNCTION postfix_relocated_map(

   370     IN localpart varchar, IN the_domain varchar)

   371     RETURNS SETOF recipient_destination

   372 AS $$

   373     DECLARE

   374         record recipient_destination;

   375         recipient varchar(320) := localpart || '@' || the_domain;

   376         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   377     BEGIN

   378         FOR record IN

   379             SELECT recipient, destination

   380               FROM relocated

   381              WHERE gid = did

   382                AND address = localpart

   383             LOOP

   384                 RETURN NEXT record;

   385             END LOOP;

   386         RETURN;

   387     END;

   388 $$ LANGUAGE plpgsql STABLE

   389 RETURNS NULL ON NULL INPUT

   390 EXTERNAL SECURITY INVOKER;

   391 -- ---

   392 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):

   393 --      varchar localpart

   394 --      varchar the_domain

   395 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):

   396 --      set of sender_login records.

   397 -- ---

   398 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(

   399     IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login

   400 AS $$

   401     DECLARE

   402         rec sender_login;

   403         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   404         sender varchar(320) := localpart || '@' || the_domain;

   405     BEGIN

   406         -- Get all addresses for 'localpart' in the primary and aliased domains

   407         FOR rec IN

   408             SELECT sender, local_part || '@' || domainname

   409               FROM domain_name, users

   410              WHERE domain_name.gid = did

   411                AND users.gid = did

   412                AND users.local_part = localpart

   413             LOOP

   414                 RETURN NEXT rec;

   415             END LOOP;

   416         IF NOT FOUND THEN

   417             -- Loop over the alias addresses for localpart@the_domain

   418             FOR rec IN

   419                 SELECT DISTINCT sender, destination

   420                   FROM alias

   421                  WHERE alias.gid = did

   422                    AND alias.address = localpart

   423                 LOOP

   424                     RETURN NEXT rec;

   425                 END LOOP;

   426         END IF;

   427         RETURN;

   428     END;

   429 $$ LANGUAGE plpgsql STABLE

   430 RETURNS NULL ON NULL INPUT

   431 EXTERNAL SECURITY INVOKER;

   432 -- ---

   433 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   434 --      varchar localpart

   435 --      varchar the_domain

   436 -- Returns: recipient_transport records

   437 -- ---

   438 CREATE OR REPLACE FUNCTION postfix_transport_map(

   439     IN localpart varchar, IN the_domain varchar)

   440     RETURNS SETOF recipient_transport

   441 AS $$

   442     DECLARE

   443         record recipient_transport;

   444         recipient varchar(320) := localpart || '@' || the_domain;

   445     BEGIN

   446         FOR record IN

   447             SELECT recipient, transport

   448               FROM transport

   449              WHERE tid = (SELECT tid

   450                             FROM users

   451                            WHERE gid = (SELECT gid

   452                                           FROM domain_name

   453                                          WHERE domainname = the_domain)

   454                              AND local_part = localpart)

   455             LOOP

   456                 RETURN NEXT record;

   457             END LOOP;

   458         RETURN;

   459     END;

   460 $$ LANGUAGE plpgsql STABLE

   461 RETURNS NULL ON NULL INPUT

   462 EXTERNAL SECURITY INVOKER;

   463 -- ---

   464 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   465 --      varchar localpart

   466 --      varchar the_domain

   467 -- Returns: recipient_destination records

   468 -- ---

   469 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(

   470     IN localpart varchar, IN the_domain varchar)

   471     RETURNS SETOF recipient_destination

   472 AS $$

   473     DECLARE

   474         record recipient_destination;

   475         recipient varchar(320) := localpart || '@' || the_domain;

   476         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   477     BEGIN

   478         FOR record IN

   479             SELECT recipient, destination

   480               FROM alias

   481              WHERE gid = did

   482                AND address = localpart

   483             LOOP

   484                 RETURN NEXT record;

   485             END LOOP;

   486         RETURN;

   487     END;

   488 $$ LANGUAGE plpgsql STABLE

   489 RETURNS NULL ON NULL INPUT

   490 EXTERNAL SECURITY INVOKER;

   491 -- ---

   492 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   493 --      varchar localpart

   494 --      varchar the_domain

   495 -- Returns: address_maildir records

   496 -- ---

   497 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(

   498    IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir

   499 AS $$

   500     DECLARE

   501         rec address_maildir;

   502         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);

   503         address varchar(320) := localpart || '@' || the_domain;

   504     BEGIN

   505         FOR rec IN

   506             SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'

   507               FROM domain_data, users, maillocation

   508              WHERE domain_data.gid = did

   509                AND users.gid = did

   510                AND users.local_part = localpart

   511                AND maillocation.mid = users.mid

   512             LOOP

   513                 RETURN NEXT rec;

   514             END LOOP;

   515         RETURN;

   516     END;

   517 $$ LANGUAGE plpgsql STABLE

   518 RETURNS NULL ON NULL INPUT

   519 EXTERNAL SECURITY INVOKER;

   520 -- ---

   521 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):

   522 --      varchar localpart

   523 --      varchar the_domain

   524 -- Returns: recipient_uid records

   525 -- ---

   526 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(

   527     IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid

   528 AS $$

   529     DECLARE

   530         record recipient_uid;

   531         recipient varchar(320) := localpart || '@' || the_domain;

   532     BEGIN

   533         FOR record IN

   534             SELECT recipient, uid

   535               FROM users

   536              WHERE gid = (SELECT gid

   537                             FROM domain_name

   538                            WHERE domainname = the_domain)

   539                AND local_part = localpart

   540             LOOP

   541                 RETURN NEXT record;

   542             END LOOP;

   543         RETURN;

   544     END;

   545 $$ LANGUAGE plpgsql STABLE

   546 RETURNS NULL ON NULL INPUT

   547 EXTERNAL SECURITY INVOKER;