1 SET client_encoding = 'UTF8';

     2 SET client_min_messages = warning;

     3 

     4 -- ---

     5 -- Create the new service_set table and insert all possible combinations

     6 -- --

     7 CREATE SEQUENCE service_set_id;

     8 

     9 CREATE TABLE service_set (

    10     ssid        bigint NOT NULL DEFAULT nextval('service_set_id'),

    11     smtp        boolean NOT NULL DEFAULT TRUE,

    12     pop3        boolean NOT NULL DEFAULT TRUE,

    13     imap        boolean NOT NULL DEFAULT TRUE,

    14     managesieve boolean NOT NULL DEFAULT TRUE,

    15     CONSTRAINT  pkey_service_set PRIMARY KEY (ssid),

    16     CONSTRAINT  ukey_service_set UNIQUE (smtp, pop3, imap, managesieve)

    17 );

    18 

    19 COPY service_set (smtp, pop3, imap, managesieve) FROM stdin;

    20 TRUE	TRUE	TRUE	TRUE

    21 FALSE	TRUE	TRUE	TRUE

    22 TRUE	FALSE	TRUE	TRUE

    23 FALSE	FALSE	TRUE	TRUE

    24 TRUE	TRUE	FALSE	TRUE

    25 FALSE	TRUE	FALSE	TRUE

    26 TRUE	FALSE	FALSE	TRUE

    27 FALSE	FALSE	FALSE	TRUE

    28 TRUE	TRUE	TRUE	FALSE

    29 FALSE	TRUE	TRUE	FALSE

    30 TRUE	FALSE	TRUE	FALSE

    31 FALSE	FALSE	TRUE	FALSE

    32 TRUE	TRUE	FALSE	FALSE

    33 FALSE	TRUE	FALSE	FALSE

    34 TRUE	FALSE	FALSE	FALSE

    35 FALSE	FALSE	FALSE	FALSE

    36 \.

    37 

    38 -- ---

    39 -- Drop the obsolete VIEWs, we've functions now.

    40 -- ---

    41 DROP VIEW dovecot_user;

    42 DROP VIEW dovecot_password;

    43 DROP VIEW postfix_alias;

    44 DROP VIEW postfix_maildir;

    45 DROP VIEW postfix_relocated;

    46 DROP VIEW postfix_transport;

    47 DROP VIEW postfix_uid;

    48 -- the vmm_domain_info view will be restored later

    49 DROP VIEW vmm_domain_info;

    50 

    51 CREATE SEQUENCE mailboxformat_id;

    52 CREATE SEQUENCE quotalimit_id;

    53 

    54 CREATE TABLE mailboxformat (

    55     fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),

    56     format      varchar(20) NOT NULL,

    57     CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),

    58     CONSTRAINT  ukey_mailboxformat UNIQUE (format)

    59 );

    60 -- Insert supported mailbox formats

    61 INSERT INTO mailboxformat(format) VALUES ('maildir');

    62 INSERT INTO mailboxformat(format) VALUES ('mdbox');

    63 INSERT INTO mailboxformat(format) VALUES ('sdbox');

    64 

    65 -- Adjust maillocation table

    66 ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;

    67 ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;

    68 ALTER TABLE maillocation

    69     ADD COLUMN fid bigint NOT NULL DEFAULT 1,

    70     ADD COLUMN extra varchar(1024);

    71 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat

    72     FOREIGN KEY (fid) REFERENCES mailboxformat (fid);

    73 

    74 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);

    75 

    76 -- ---

    77 -- Add quota stuff

    78 -- ---

    79 CREATE TABLE quotalimit (

    80     qid         bigint NOT NULL DEFAULT nextval('quotalimit_id'),

    81     bytes       bigint NOT NULL,

    82     messages    integer NOT NULL DEFAULT 0,

    83     CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),

    84     CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)

    85 );

    86 -- Insert default (non) quota limit

    87 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);

    88 

    89 -- Adjust tables (quota)

    90 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;

    91 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit

    92     FOREIGN KEY (qid) REFERENCES quotalimit (qid);

    93 

    94 ALTER TABLE users ADD COLUMN qid bigint NULL DEFAULT NULL;

    95 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit

    96     FOREIGN KEY (qid) REFERENCES quotalimit (qid);

    97 

    98 CREATE TABLE userquota_11 (

    99     uid         bigint NOT NULL,

   100     path        varchar(16) NOT NULL,

   101     current     bigint NOT NULL DEFAULT 0,

   102     CONSTRAINT  pkey_userquota_11 PRIMARY KEY (uid, path),

   103     CONSTRAINT  fkey_userquota_11_uid_users FOREIGN KEY (uid)

   104         REFERENCES users (uid) ON DELETE CASCADE

   105 );

   106 

   107 CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$

   108 BEGIN

   109     UPDATE userquota_11

   110        SET current = current + NEW.current

   111      WHERE uid = NEW.uid AND path = NEW.path;

   112     IF found THEN

   113         RETURN NULL;

   114     ELSE

   115         RETURN NEW;

   116     END IF;

   117 END;

   118 $$ LANGUAGE plpgsql;

   119 

   120 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11

   121     FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11();

   122 

   123 -- Adjust tables (services)

   124 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;

   125 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set

   126     FOREIGN KEY (ssid) REFERENCES service_set (ssid);

   127 

   128 ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL;

   129 -- save current service sets

   130 UPDATE users u

   131    SET ssid = ss.ssid

   132   FROM service_set ss

   133  WHERE ss.smtp = u.smtp

   134    AND ss.pop3 = u.pop3

   135    AND ss.imap = u.imap

   136    AND ss.managesieve = u.managesieve;

   137 

   138 ALTER TABLE users DROP COLUMN smtp;

   139 ALTER TABLE users DROP COLUMN pop3;

   140 ALTER TABLE users DROP COLUMN imap;

   141 ALTER TABLE users DROP COLUMN managesieve;

   142 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set

   143     FOREIGN KEY (ssid) REFERENCES service_set (ssid);

   144 

   145 -- ---

   146 -- Catchall

   147 -- ---

   148 -- Quota/Service/Transport inheritance

   149 -- ---

   150 ALTER TABLE users ALTER COLUMN tid DROP NOT NULL;

   151 ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL;

   152 -- The qid and ssid columns have already been defined accordingly above.

   153 -- The rest of the logic will take place in the functions.

   154 

   155 -- While qid and ssid are new and it's perfectly okay for existing users to

   156 -- get NULL values (i.e. inherit from the domain's default), tid existed in

   157 -- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid

   158 -- fields where the tid duplicates the value stored in the domain's record.

   159 UPDATE users

   160    SET tid = NULL

   161  WHERE tid = (SELECT tid

   162                 FROM domain_data

   163                WHERE domain_data.gid = users.gid);

   164 

   165 -- ---

   166 

   167 CREATE TABLE catchall (

   168     gid         bigint NOT NULL,

   169     destination varchar(320) NOT NULL,

   170     CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),

   171     CONSTRAINT  fkey_catchall_gid_domain_data FOREIGN KEY (gid)

   172         REFERENCES domain_data (gid)

   173 );

   174 

   175 -- ---

   176 -- Account/domain notes

   177 -- ---

   178 

   179 ALTER TABLE users ADD COLUMN note text NULL DEFAULT NULL;

   180 ALTER TABLE domain_data ADD COLUMN note text NULL DEFAULT NULL;

   181 

   182 -- ---

   183 -- Restore view

   184 -- ---

   185 CREATE VIEW vmm_domain_info AS

   186     SELECT gid, count(uid) AS accounts,

   187            (SELECT count(DISTINCT address)

   188               FROM alias

   189              WHERE alias.gid = domain_data.gid) AS aliases,

   190            (SELECT count(gid)

   191               FROM relocated

   192              WHERE relocated.gid = domain_data.gid) AS relocated,

   193            (SELECT count(gid)

   194               FROM domain_name

   195              WHERE domain_name.gid = domain_data.gid

   196                AND NOT domain_name.is_primary) AS aliasdomains,

   197            (SELECT count(gid)

   198               FROM catchall

   199              WHERE catchall.gid = domain_data.gid) AS catchall

   200       FROM domain_data

   201            LEFT JOIN domain_name USING (gid)

   202            LEFT JOIN users USING (gid)

   203      WHERE domain_name.is_primary

   204   GROUP BY gid;

   205 

   206 -- ---

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

   208 -- ---

   209 DROP TYPE address_maildir CASCADE;

   210 DROP TYPE dovecotpassword CASCADE;

   211 DROP TYPE dovecotuser CASCADE;

   212 DROP TYPE recipient_destination CASCADE;

   213 DROP TYPE recipient_transport CASCADE;

   214 DROP TYPE recipient_uid CASCADE;

   215 DROP TYPE sender_login CASCADE;

   216 

   217 -- ######################## TYPEs ########################################### --

   218 

   219 -- ---

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

   221 -- ---

   222 CREATE TYPE address_maildir AS (

   223     address varchar(320),

   224     maildir text

   225 );

   226 -- ---

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

   228 -- ---

   229 CREATE TYPE dovecotpassword AS (

   230     userid      varchar(320),

   231     password    varchar(270),

   232     smtp        boolean,

   233     pop3        boolean,

   234     imap        boolean,

   235     managesieve boolean

   236 );

   237 -- ---

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

   239 -- ---

   240 CREATE TYPE dovecotquotauser AS (

   241     userid      varchar(320),

   242     uid         bigint,

   243     gid         bigint,

   244     home        text,

   245     mail        text,

   246     quota_rule  text

   247 );

   248 -- ---

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

   250 -- ---

   251 CREATE TYPE dovecotuser AS (

   252     userid      varchar(320),

   253     uid         bigint,

   254     gid         bigint,

   255     home        text,

   256     mail        text

   257 );

   258 -- ---

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

   260 --                          postfix_virtual_alias_map(varchar, varchar)

   261 -- ---

   262 CREATE TYPE recipient_destination AS (

   263     recipient   varchar(320),

   264     destination text

   265 );

   266 -- ---

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

   268 -- ---

   269 CREATE TYPE recipient_transport AS (

   270     recipient   varchar(320),

   271     transport   text

   272 );

   273 -- ---

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

   275 -- ---

   276 CREATE TYPE recipient_uid AS (

   277     recipient   varchar(320),

   278     uid         bigint

   279 );

   280 -- ---

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

   282 -- ---

   283 CREATE TYPE sender_login AS (

   284     sender  varchar(320),

   285     login   text

   286 );

   287 

   288 -- ######################## FUNCTIONs ####################################### --

   289 

   290 -- ---

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

   292 --      varchar localpart

   293 --      varchar the_domain

   294 -- Returns: dovecotpassword records

   295 -- ---

   296 CREATE OR REPLACE FUNCTION dovecotpassword(

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

   298 AS $$

   299     DECLARE

   300         record dovecotpassword;

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

   302     BEGIN

   303         FOR record IN

   304             SELECT userid, passwd, smtp, pop3, imap, managesieve

   305               FROM users, service_set, domain_data

   306              WHERE users.gid = (SELECT gid

   307                                   FROM domain_name

   308                                  WHERE domainname = the_domain)

   309                AND local_part = localpart

   310                AND service_set.ssid = users.ssid

   311                AND users.gid = domain_data.gid

   312                AND CASE WHEN

   313                   users.ssid IS NOT NULL

   314                   THEN

   315                     service_set.ssid = users.ssid

   316                   ELSE

   317                     service_set.ssid = domain_data.ssid

   318                   END

   319             LOOP

   320                 RETURN NEXT record;

   321             END LOOP;

   322         RETURN;

   323     END;

   324 $$ LANGUAGE plpgsql STABLE

   325 RETURNS NULL ON NULL INPUT

   326 EXTERNAL SECURITY INVOKER;

   327 -- ---

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

   329 -- field quota_rule.

   330 -- ---

   331 CREATE OR REPLACE FUNCTION dovecotquotauser(

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

   333 AS $$

   334     DECLARE

   335         record dovecotquotauser;

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

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

   338     BEGIN

   339         FOR record IN

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

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

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

   343               FROM users, domain_data, mailboxformat, maillocation, quotalimit

   344              WHERE users.gid = did

   345                AND users.local_part = localpart

   346                AND maillocation.mid = users.mid

   347                AND mailboxformat.fid = maillocation.fid

   348                AND domain_data.gid = did

   349                AND CASE WHEN

   350                      users.qid IS NOT NULL

   351                    THEN

   352                      quotalimit.qid = users.qid

   353                    ELSE

   354                      quotalimit.qid = domain_data.qid

   355                    END

   356             LOOP

   357                 RETURN NEXT record;

   358             END LOOP;

   359         RETURN;

   360     END;

   361 $$ LANGUAGE plpgsql STABLE

   362 RETURNS NULL ON NULL INPUT

   363 EXTERNAL SECURITY INVOKER;

   364 -- ---

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

   366 --      varchar localpart

   367 --      varchar the_domain

   368 -- Returns: dovecotuser records

   369 -- ---

   370 CREATE OR REPLACE FUNCTION dovecotuser(

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

   372 AS $$

   373     DECLARE

   374         record dovecotuser;

   375         userid 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 userid, uid, did, domaindir || '/' || uid AS home,

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

   381               FROM users, domain_data, mailboxformat, maillocation

   382              WHERE users.gid = did

   383                AND users.local_part = localpart

   384                AND maillocation.mid = users.mid

   385                AND mailboxformat.fid = maillocation.fid

   386                AND domain_data.gid = did

   387             LOOP

   388                 RETURN NEXT record;

   389             END LOOP;

   390         RETURN;

   391     END;

   392 $$ LANGUAGE plpgsql STABLE

   393 RETURNS NULL ON NULL INPUT

   394 EXTERNAL SECURITY INVOKER;

   395 -- ---

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

   397 --      varchar localpart

   398 --      varchar the_domain

   399 -- Returns: recipient_destination records

   400 -- ---

   401 CREATE OR REPLACE FUNCTION postfix_relocated_map(

   402     IN localpart varchar, IN the_domain varchar)

   403     RETURNS SETOF recipient_destination

   404 AS $$

   405     DECLARE

   406         record recipient_destination;

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

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

   409     BEGIN

   410         FOR record IN

   411             SELECT recipient, destination

   412               FROM relocated

   413              WHERE gid = did

   414                AND address = localpart

   415             LOOP

   416                 RETURN NEXT record;

   417             END LOOP;

   418         RETURN;

   419     END;

   420 $$ LANGUAGE plpgsql STABLE

   421 RETURNS NULL ON NULL INPUT

   422 EXTERNAL SECURITY INVOKER;

   423 -- ---

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

   425 --      varchar localpart

   426 --      varchar the_domain

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

   428 --      set of sender_login records.

   429 -- ---

   430 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(

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

   432 AS $$

   433     DECLARE

   434         rec sender_login;

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

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

   437     BEGIN

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

   439         FOR rec IN

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

   441               FROM domain_name, users

   442              WHERE domain_name.gid = did

   443                AND users.gid = did

   444                AND users.local_part = localpart

   445             LOOP

   446                 RETURN NEXT rec;

   447             END LOOP;

   448         IF NOT FOUND THEN

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

   450             FOR rec IN

   451                 SELECT DISTINCT sender, destination

   452                   FROM alias

   453                  WHERE alias.gid = did

   454                    AND alias.address = localpart

   455                 LOOP

   456                     RETURN NEXT rec;

   457                 END LOOP;

   458         END IF;

   459         RETURN;

   460     END;

   461 $$ LANGUAGE plpgsql STABLE

   462 RETURNS NULL ON NULL INPUT

   463 EXTERNAL SECURITY INVOKER;

   464 -- ---

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

   466 --      varchar localpart

   467 --      varchar the_domain

   468 -- Returns: recipient_transport records

   469 -- ---

   470 CREATE OR REPLACE FUNCTION postfix_transport_map(

   471     IN localpart varchar, IN the_domain varchar)

   472     RETURNS SETOF recipient_transport

   473 AS $$

   474     DECLARE

   475         record recipient_transport;

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

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

   478         transport_id bigint;

   479     BEGIN

   480         IF did IS NULL THEN

   481             RETURN;

   482         END IF;

   483 

   484         SELECT tid INTO transport_id

   485           FROM users

   486          WHERE gid = did AND local_part = localpart;

   487 

   488         IF transport_id IS NULL THEN

   489             SELECT tid INTO STRICT transport_id

   490               FROM domain_data

   491              WHERE gid = did;

   492         END IF;

   493 

   494         FOR record IN

   495             SELECT recipient, transport

   496               FROM transport

   497              WHERE tid = transport_id

   498             LOOP

   499                 RETURN NEXT record;

   500             END LOOP;

   501         RETURN;

   502     END;

   503 $$ LANGUAGE plpgsql STABLE

   504 RETURNS NULL ON NULL INPUT

   505 EXTERNAL SECURITY INVOKER;

   506 -- ---

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

   508 --      varchar localpart

   509 --      varchar the_domain

   510 -- Returns: recipient_destination records

   511 -- ---

   512 CREATE OR REPLACE FUNCTION _interpolate_destination(

   513     IN destination varchar, localpart varchar, IN the_domain varchar)

   514     RETURNS varchar

   515 AS $$

   516     DECLARE

   517         result varchar(320);

   518     BEGIN

   519         IF position('%' in destination) = 0 THEN

   520             RETURN destination;

   521         END IF;

   522         result := replace(destination, '%n', localpart);

   523         result := replace(result, '%d', the_domain);

   524         result := replace(result, '%=', localpart || '=' || the_domain);

   525         RETURN result;

   526     END;

   527 $$ LANGUAGE plpgsql STABLE

   528 RETURNS NULL ON NULL INPUT

   529 EXTERNAL SECURITY INVOKER;

   530 

   531 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(

   532     IN localpart varchar, IN the_domain varchar)

   533     RETURNS SETOF recipient_destination

   534 AS $$

   535     DECLARE

   536         recordc recipient_destination;

   537         record recipient_destination;

   538         catchall_cursor refcursor;

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

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

   541     BEGIN

   542         FOR record IN

   543             SELECT recipient,

   544                 _interpolate_destination(destination, localpart, the_domain)

   545               FROM alias

   546              WHERE gid = did

   547                AND address = localpart

   548             LOOP

   549                 RETURN NEXT record;

   550             END LOOP;

   551 

   552         IF NOT FOUND THEN

   553             -- There is no matching virtual_alias. If there are no catchall

   554             -- records for this domain, we can just return NULL since Postfix

   555             -- will then later consult mailboxes/relocated itself. But if

   556             -- there is a catchall destination, then it would take precedence

   557             -- over mailboxes/relocated, which is not what we want. Therefore,

   558             -- we must first find out if the query is for an existing mailbox

   559             -- or relocated entry and return the identity mapping if that is

   560             -- the case

   561             OPEN catchall_cursor FOR

   562                 SELECT recipient,

   563                     _interpolate_destination(destination, localpart, the_domain)

   564                   FROM catchall

   565                  WHERE gid = did;

   566             FETCH NEXT FROM catchall_cursor INTO recordc;

   567 

   568             IF recordc IS NOT NULL THEN

   569                 -- Since there are catchall records for this domain

   570                 -- check the mailbox and relocated records and return identity

   571                 -- if a matching record exists.

   572                 FOR record IN

   573                     SELECT recipient, recipient as destination

   574                       FROM users

   575                     WHERE gid = did

   576                       AND local_part = localpart

   577                     UNION SELECT recipient, recipient as destination

   578                       FROM relocated

   579                     WHERE gid = did

   580                       AND address = localpart

   581                     LOOP

   582                         RETURN NEXT record;

   583                     END LOOP;

   584 

   585                 IF NOT FOUND THEN

   586                     -- There were no records found for mailboxes/relocated,

   587                     -- so now we can actually iterate the cursor and populate

   588                     -- the return set

   589                     LOOP

   590                         RETURN NEXT recordc;

   591                         FETCH NEXT FROM catchall_cursor INTO recordc;

   592                         EXIT WHEN recordc IS NULL;

   593                     END LOOP;

   594                 END IF;

   595             END IF;

   596             CLOSE catchall_cursor;

   597         END IF;

   598         RETURN;

   599     END;

   600 $$ LANGUAGE plpgsql STABLE

   601 RETURNS NULL ON NULL INPUT

   602 EXTERNAL SECURITY INVOKER;

   603 -- ---

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

   605 --      varchar localpart

   606 --      varchar the_domain

   607 -- Returns: address_maildir records

   608 -- ---

   609 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(

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

   611 AS $$

   612     DECLARE

   613         rec address_maildir;

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

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

   616     BEGIN

   617         FOR rec IN

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

   619               FROM domain_data, users, maillocation

   620              WHERE domain_data.gid = did

   621                AND users.gid = did

   622                AND users.local_part = localpart

   623                AND maillocation.mid = users.mid

   624             LOOP

   625                 RETURN NEXT rec;

   626             END LOOP;

   627         RETURN;

   628     END;

   629 $$ LANGUAGE plpgsql STABLE

   630 RETURNS NULL ON NULL INPUT

   631 EXTERNAL SECURITY INVOKER;

   632 -- ---

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

   634 --      varchar localpart

   635 --      varchar the_domain

   636 -- Returns: recipient_uid records

   637 -- ---

   638 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(

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

   640 AS $$

   641     DECLARE

   642         record recipient_uid;

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

   644     BEGIN

   645         FOR record IN

   646             SELECT recipient, uid

   647               FROM users

   648              WHERE gid = (SELECT gid

   649                             FROM domain_name

   650                            WHERE domainname = the_domain)

   651                AND local_part = localpart

   652             LOOP

   653                 RETURN NEXT record;

   654             END LOOP;

   655         RETURN;

   656     END;

   657 $$ LANGUAGE plpgsql STABLE

   658 RETURNS NULL ON NULL INPUT

   659 EXTERNAL SECURITY INVOKER;