1 SET client_encoding = 'UTF8';

     2 SET client_min_messages = warning;

     3 

     4 

     5 -- ---

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

     7 --      varchar localpart

     8 --      varchar the_domain

     9 -- Returns: address_maildir records

    10 --

    11 -- Required access privileges for your postfix database user:

    12 --      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;

    13 --

    14 -- For more details see postconf(5) section virtual_mailbox_maps

    15 -- ---

    16 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(

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

    18 AS $$

    19     DECLARE

    20         rec address_maildir;

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

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

    23     BEGIN

    24         FOR rec IN

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

    26               FROM domain_data, users, maillocation

    27              WHERE domain_data.gid = did

    28                AND users.gid = did

    29                AND users.local_part = localpart

    30                AND maillocation.mid = users.mid

    31             LOOP

    32                 RETURN NEXT rec;

    33             END LOOP;

    34         RETURN;

    35     END;

    36 $$ LANGUAGE plpgsql STABLE

    37 RETURNS NULL ON NULL INPUT

    38 EXTERNAL SECURITY INVOKER;

    39 

    40 

    41 DROP TYPE dovecotpassword CASCADE;

    42 -- ---

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

    44 -- ---

    45 CREATE TYPE dovecotpassword AS (

    46     userid      varchar(320),

    47     password    varchar(270),

    48     smtp        boolean,

    49     pop3        boolean,

    50     imap        boolean,

    51     managesieve boolean

    52 );

    53 -- ---

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

    55 --      varchar localpart

    56 --      varchar the_domain

    57 -- Returns: dovecotpassword records

    58 --

    59 -- Required access privileges for your dovecot database user:

    60 --      GRANT SELECT ON users, domain_name TO dovecot;

    61 --

    62 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL

    63 -- ---

    64 CREATE OR REPLACE FUNCTION dovecotpassword(

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

    66 AS $$

    67     DECLARE

    68         record dovecotpassword;

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

    70     BEGIN

    71         FOR record IN

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

    73               FROM users

    74              WHERE gid = (SELECT gid

    75                             FROM domain_name

    76                            WHERE domainname = the_domain)

    77                AND local_part = localpart

    78             LOOP

    79                 RETURN NEXT record;

    80             END LOOP;

    81         RETURN;

    82     END;

    83 $$ LANGUAGE plpgsql STABLE

    84 RETURNS NULL ON NULL INPUT

    85 EXTERNAL SECURITY INVOKER;

    86 -- ---

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

    88 --      varchar localpart

    89 --      varchar the_domain

    90 -- Returns: dovecotuser records

    91 --

    92 -- Required access privileges for your dovecot database user:

    93 --      GRANT SELECT

    94 --          ON users, domain_data, domain_name, maillocation, mailboxformat

    95 --          TO dovecot;

    96 --

    97 -- For more details see http://wiki.dovecot.org/UserDatabase

    98 -- ---

    99 CREATE OR REPLACE FUNCTION dovecotuser(

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

   101 AS $$

   102     DECLARE

   103         record dovecotuser;

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

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

   106     BEGIN

   107         FOR record IN

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

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

   110               FROM users, domain_data, mailboxformat, maillocation

   111              WHERE users.gid = did

   112                AND users.local_part = localpart

   113                AND maillocation.mid = users.mid

   114                AND mailboxformat.fid = maillocation.fid

   115                AND domain_data.gid = did

   116             LOOP

   117                 RETURN NEXT record;

   118             END LOOP;

   119         RETURN;

   120     END;

   121 $$ LANGUAGE plpgsql STABLE

   122 RETURNS NULL ON NULL INPUT

   123 EXTERNAL SECURITY INVOKER;