226                AND local_part = localpart)

   227                              AND local_part = localpart)

   227             LOOP

   228             LOOP

   228                 RETURN NEXT record;

   229                 RETURN NEXT record;

   229             END LOOP;

   230             END LOOP;

   230         RETURN;

   231         RETURN;

   231     END;

   232     END;

   232 $$ LANGUAGE plpgsql STABLE

   233 $$ LANGUAGE plpgsql STABLE

   233 RETURNS NULL ON NULL INPUT

   234 RETURNS NULL ON NULL INPUT

   234 EXTERNAL SECURITY INVOKER;

   235 EXTERNAL SECURITY INVOKER;

   236 

   237 -- ########################################################################## --

   238 

   239 -- ---

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

   241 -- ---

   242 CREATE TYPE recipient_uid AS (

   243     recipient   varchar(320),

   244     uid         bigint

   245 );

   246 

   247 -- ---

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

   249 --      varchar localpart

   250 --      varchar the_domain

   251 -- Returns: recipient_uid records

   252 --

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

   254 --      GRANT SELECT ON users, domain_name TO postfix;

   255 --

   256 -- For more details see postconf(5) section virtual_uid_maps

   257 -- ---

   258 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(

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

   260 AS $$

   261     DECLARE

   262         record recipient_uid;

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

   264     BEGIN

   265         FOR record IN

   266             SELECT recipient, uid

   267               FROM users

   268              WHERE gid = (SELECT gid

   269                             FROM domain_name

   270                            WHERE domainname = the_domain)

   271                AND local_part = localpart

   272             LOOP

   273                 RETURN NEXT record;

   274             END LOOP;

   275         RETURN;

   276     END;

   277 $$ LANGUAGE plpgsql STABLE

   278 RETURNS NULL ON NULL INPUT

   279 EXTERNAL SECURITY INVOKER;

   280 

   281 -- ########################################################################## --

   282 

   283 -- ---

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

   285 -- ---

   286 CREATE TYPE dovecotuser AS (

   287     userid      varchar(320),

   288     uid         bigint,

   289     gid         bigint,

   290     home        text,

   291     mail        text

   292 );

   293 

   294 -- ---

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

   296 --      varchar localpart

   297 --      varchar the_domain

   298 -- Returns: dovecotuser records

   299 --

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

   301 --      GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;

   302 --

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

   304 -- ---

   305 CREATE OR REPLACE FUNCTION dovecotuser(

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

   307 AS $$

   308     DECLARE

   309         record dovecotuser;

   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                    '~/'|| maillocation AS mail

   316               FROM users, domain_data, maillocation

   317              WHERE users.gid = did

   318                AND users.local_part = localpart

   319                AND maillocation.mid = users.mid

   320                AND domain_data.gid = did

   321             LOOP

   322                 RETURN NEXT record;

   323             END LOOP;

   324         RETURN;

   325     END;

   326 $$ LANGUAGE plpgsql STABLE

   327 RETURNS NULL ON NULL INPUT

   328 EXTERNAL SECURITY INVOKER;

   329 

   330 -- ########################################################################## --

   331 

   332 -- ---

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

   334 -- ---

   335 CREATE TYPE dovecotpassword AS (

   336     userid      varchar(320),

   337     password    varchar(74),

   338     smtp        boolean,

   339     pop3        boolean,

   340     imap        boolean,

   341     managesieve boolean

   342 );

   343 

   344 -- ---

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

   346 --      varchar localpart

   347 --      varchar the_domain

   348 -- Returns: dovecotpassword records

   349 --

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

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

   352 --

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

   354 -- ---

   355 CREATE OR REPLACE FUNCTION dovecotpassword(

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

   357 AS $$

   358     DECLARE

   359         record dovecotpassword;

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

   361     BEGIN

   362         FOR record IN

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

   364               FROM users

   365              WHERE gid = (SELECT gid

   366                             FROM domain_name

   367                            WHERE domainname = the_domain)

   368                AND local_part = localpart

   369             LOOP

   370                 RETURN NEXT record;

   371             END LOOP;

   372         RETURN;

   373     END;

   374 $$ LANGUAGE plpgsql STABLE

   375 RETURNS NULL ON NULL INPUT

   376 EXTERNAL SECURITY INVOKER;