1 -- ---

     2 -- Clean out the old stuff

     3 -- ---

     4 DROP TYPE dovecotpassword CASCADE;

     5 

     6 -- ---

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

     8 -- ---

     9 CREATE TYPE dovecotpassword AS (

    10     userid    varchar(320),

    11     password  varchar(74),

    12     smtp      boolean,

    13     pop3      boolean,

    14     imap      boolean,

    15     sieve     boolean

    16 );

    17 

    18 -- ---

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

    20 --      varchar localpart

    21 --      varchar the_domain

    22 -- Returns: dovecotpassword records

    23 --

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

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

    26 --

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

    28 -- ---

    29 CREATE OR REPLACE FUNCTION dovecotpassword(

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

    31 AS $$

    32     DECLARE

    33         record dovecotpassword;

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

    35     BEGIN

    36         FOR record IN

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

    38               FROM users

    39              WHERE gid = (SELECT gid

    40                             FROM domain_name

    41                            WHERE domainname = the_domain)

    42                AND local_part = localpart

    43             LOOP

    44                 RETURN NEXT record;

    45             END LOOP;

    46         RETURN;

    47     END;

    48 $$ LANGUAGE plpgsql STABLE

    49 RETURNS NULL ON NULL INPUT

    50 EXTERNAL SECURITY INVOKER;

    51