pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 502 e1b32377032f
parent 501 f2387d60624b
child 503 492c179094c9
equal deleted inserted replaced
501:f2387d60624b 502:e1b32377032f
     1 -- --- Information:
       
     2 -- This file contains some data types and functions these should speed up some
       
     3 -- operations. Read the comment on each data type/functions for more details.
       
     4 -- ---
       
     5 
       
     6 -- ---
       
     7 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
       
     8 -- ---
       
     9 CREATE TYPE sender_login AS (
       
    10     sender  varchar(320),
       
    11     login   text
       
    12 );
       
    13 
       
    14 -- ---
       
    15 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
       
    16 --      varchar localpart
       
    17 --      varchar the_domain
       
    18 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
       
    19 --      set of sender_login records.
       
    20 --
       
    21 -- Required access privileges for your postfix database user:
       
    22 --      GRANT SELECT ON domain_name, users, alias TO postfix;
       
    23 --
       
    24 -- For more details see postconf(5) section smtpd_sender_login_maps
       
    25 -- ---
       
    26 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
       
    27     IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
       
    28 AS $$
       
    29     DECLARE
       
    30         rec sender_login;
       
    31         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
    32         sender varchar(320) := localpart || '@' || the_domain;
       
    33     BEGIN
       
    34         -- Get all addresses for 'localpart' in the primary and aliased domains
       
    35         FOR rec IN
       
    36             SELECT sender, local_part || '@' || domainname
       
    37               FROM domain_name, users
       
    38              WHERE domain_name.gid = did
       
    39                AND users.gid = did
       
    40                AND users.local_part = localpart
       
    41             LOOP
       
    42                 RETURN NEXT rec;
       
    43             END LOOP;
       
    44         IF NOT FOUND THEN
       
    45             -- Loop over the alias addresses for localpart@the_domain
       
    46             FOR rec IN
       
    47                 SELECT DISTINCT sender, destination
       
    48                   FROM alias
       
    49                  WHERE alias.gid = did
       
    50                    AND alias.address = localpart
       
    51                 LOOP
       
    52                     RETURN NEXT rec;
       
    53                 END LOOP;
       
    54         END IF;
       
    55         RETURN;
       
    56     END;
       
    57 $$ LANGUAGE plpgsql STABLE
       
    58 RETURNS NULL ON NULL INPUT
       
    59 EXTERNAL SECURITY INVOKER;
       
    60 
       
    61 -- ########################################################################## --
       
    62 
       
    63 -- ---
       
    64 -- Data type for function postfix_virtual_mailbox(varchar, varchar)
       
    65 -- ---
       
    66 CREATE TYPE address_maildir AS (
       
    67     address varchar(320),
       
    68     maildir text
       
    69 );
       
    70 
       
    71 -- ---
       
    72 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
       
    73 --      varchar localpart
       
    74 --      varchar the_domain
       
    75 -- Returns: address_maildir records
       
    76 --
       
    77 -- Required access privileges for your postfix database user:
       
    78 --      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
       
    79 --
       
    80 -- For more details see postconf(5) section virtual_mailbox_maps
       
    81 -- ---
       
    82 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
       
    83    IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
       
    84 AS $$
       
    85     DECLARE
       
    86         rec address_maildir;
       
    87         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
    88         address varchar(320) := localpart || '@' || the_domain;
       
    89     BEGIN
       
    90         FOR rec IN
       
    91             SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'
       
    92               FROM domain_data, users, maillocation
       
    93              WHERE domain_data.gid = did
       
    94                AND users.gid = did
       
    95                AND users.local_part = localpart
       
    96                AND maillocation.mid = users.mid
       
    97             LOOP
       
    98                 RETURN NEXT rec;
       
    99             END LOOP;
       
   100         RETURN;
       
   101     END;
       
   102 $$ LANGUAGE plpgsql STABLE
       
   103 RETURNS NULL ON NULL INPUT
       
   104 EXTERNAL SECURITY INVOKER;
       
   105 
       
   106 -- ########################################################################## --
       
   107 
       
   108 -- ---
       
   109 -- Data type for functions: postfix_relocated_map(varchar, varchar)
       
   110 --                          postfix_virtual_alias_map(varchar, varchar)
       
   111 -- ---
       
   112 CREATE TYPE recipient_destination AS (
       
   113     recipient   varchar(320),
       
   114     destination text
       
   115 );
       
   116 
       
   117 -- ---
       
   118 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
       
   119 --      varchar localpart
       
   120 --      varchar the_domain
       
   121 -- Returns: recipient_destination records
       
   122 --
       
   123 -- Required access privileges for your postfix database user:
       
   124 --      GRANT SELECT ON alias, domain_name TO postfix;
       
   125 --
       
   126 -- For more details see postconf(5) section virtual_alias_maps and virtual(5)
       
   127 -- ---
       
   128 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
       
   129     IN localpart varchar, IN the_domain varchar)
       
   130     RETURNS SETOF recipient_destination
       
   131 AS $$
       
   132     DECLARE
       
   133         record recipient_destination;
       
   134         recipient varchar(320) := localpart || '@' || the_domain;
       
   135         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
   136     BEGIN
       
   137         FOR record IN
       
   138             SELECT recipient, destination
       
   139               FROM alias
       
   140              WHERE gid = did
       
   141                AND address = localpart
       
   142             LOOP
       
   143                 RETURN NEXT record;
       
   144             END LOOP;
       
   145         RETURN;
       
   146     END;
       
   147 $$ LANGUAGE plpgsql STABLE
       
   148 RETURNS NULL ON NULL INPUT
       
   149 EXTERNAL SECURITY INVOKER;
       
   150 
       
   151 -- ########################################################################## --
       
   152 
       
   153 -- ---
       
   154 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
       
   155 --      varchar localpart
       
   156 --      varchar the_domain
       
   157 -- Returns: recipient_destination records
       
   158 --
       
   159 -- Required access privileges for your postfix database user:
       
   160 --      GRANT SELECT ON domain_name, relocated TO postfix;
       
   161 --
       
   162 -- For more details see postconf(5) section relocated_maps and relocated(5)
       
   163 -- ---
       
   164 CREATE OR REPLACE FUNCTION postfix_relocated_map(
       
   165     IN localpart varchar, IN the_domain varchar)
       
   166     RETURNS SETOF recipient_destination
       
   167 AS $$
       
   168     DECLARE
       
   169         record recipient_destination;
       
   170         recipient varchar(320) := localpart || '@' || the_domain;
       
   171         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
   172     BEGIN
       
   173         FOR record IN
       
   174             SELECT recipient, destination
       
   175               FROM relocated
       
   176              WHERE gid = did
       
   177                AND address = localpart
       
   178             LOOP
       
   179                 RETURN NEXT record;
       
   180             END LOOP;
       
   181         RETURN;
       
   182     END;
       
   183 $$ LANGUAGE plpgsql STABLE
       
   184 RETURNS NULL ON NULL INPUT
       
   185 EXTERNAL SECURITY INVOKER;
       
   186 
       
   187 -- ########################################################################## --
       
   188 
       
   189 -- ---
       
   190 -- Data type for function postfix_transport_map(varchar, varchar)
       
   191 -- ---
       
   192 CREATE TYPE recipient_transport AS (
       
   193     recipient   varchar(320),
       
   194     transport   text
       
   195 );
       
   196 
       
   197 -- ---
       
   198 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
       
   199 --      varchar localpart
       
   200 --      varchar the_domain
       
   201 -- Returns: recipient_transport records
       
   202 --
       
   203 -- Required access privileges for your postfix database user:
       
   204 --      GRANT SELECT ON users, transport, domain_name TO postfix;
       
   205 --
       
   206 -- For more details see postconf(5) section transport_maps and transport(5)
       
   207 -- ---
       
   208 CREATE OR REPLACE FUNCTION postfix_transport_map(
       
   209     IN localpart varchar, IN the_domain varchar)
       
   210     RETURNS SETOF recipient_transport
       
   211 AS $$
       
   212     DECLARE
       
   213         record recipient_transport;
       
   214         recipient varchar(320) := localpart || '@' || the_domain;
       
   215     BEGIN
       
   216         FOR record IN
       
   217             SELECT recipient, transport
       
   218               FROM transport
       
   219              WHERE tid = (SELECT tid
       
   220                             FROM users
       
   221                            WHERE gid = (SELECT gid
       
   222                                           FROM domain_name
       
   223                                          WHERE domainname = the_domain)
       
   224                              AND local_part = localpart)
       
   225             LOOP
       
   226                 RETURN NEXT record;
       
   227             END LOOP;
       
   228         RETURN;
       
   229     END;
       
   230 $$ LANGUAGE plpgsql STABLE
       
   231 RETURNS NULL ON NULL INPUT
       
   232 EXTERNAL SECURITY INVOKER;
       
   233 
       
   234 -- ########################################################################## --
       
   235 
       
   236 -- ---
       
   237 -- Data type for function postfix_virtual_uid_map(varchar, varchar)
       
   238 -- ---
       
   239 CREATE TYPE recipient_uid AS (
       
   240     recipient   varchar(320),
       
   241     uid         bigint
       
   242 );
       
   243 
       
   244 -- ---
       
   245 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
       
   246 --      varchar localpart
       
   247 --      varchar the_domain
       
   248 -- Returns: recipient_uid records
       
   249 --
       
   250 -- Required access privileges for your postfix database user:
       
   251 --      GRANT SELECT ON users, domain_name TO postfix;
       
   252 --
       
   253 -- For more details see postconf(5) section virtual_uid_maps
       
   254 -- ---
       
   255 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
       
   256     IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
       
   257 AS $$
       
   258     DECLARE
       
   259         record recipient_uid;
       
   260         recipient varchar(320) := localpart || '@' || the_domain;
       
   261     BEGIN
       
   262         FOR record IN
       
   263             SELECT recipient, uid
       
   264               FROM users
       
   265              WHERE gid = (SELECT gid
       
   266                             FROM domain_name
       
   267                            WHERE domainname = the_domain)
       
   268                AND local_part = localpart
       
   269             LOOP
       
   270                 RETURN NEXT record;
       
   271             END LOOP;
       
   272         RETURN;
       
   273     END;
       
   274 $$ LANGUAGE plpgsql STABLE
       
   275 RETURNS NULL ON NULL INPUT
       
   276 EXTERNAL SECURITY INVOKER;
       
   277 
       
   278 -- ########################################################################## --
       
   279 
       
   280 -- ---
       
   281 -- Data type for function dovecotuser(varchar, varchar)
       
   282 -- ---
       
   283 CREATE TYPE dovecotuser AS (
       
   284     userid      varchar(320),
       
   285     uid         bigint,
       
   286     gid         bigint,
       
   287     home        text,
       
   288     mail        text
       
   289 );
       
   290 -- ---
       
   291 -- Data type for function dovecotquotauser(varchar, varchar)
       
   292 -- ---
       
   293 CREATE TYPE dovecotquotauser AS (
       
   294     userid      varchar(320),
       
   295     uid         bigint,
       
   296     gid         bigint,
       
   297     home        text,
       
   298     mail        text,
       
   299     quota_rule  text
       
   300 );
       
   301 
       
   302 -- ---
       
   303 -- Parameters (from login name [localpart@the_domain]):
       
   304 --      varchar localpart
       
   305 --      varchar the_domain
       
   306 -- Returns: dovecotuser records
       
   307 --
       
   308 -- Required access privileges for your dovecot database user:
       
   309 --      GRANT SELECT
       
   310 --          ON users, domain_data, domain_name, maillocation, mailboxformat
       
   311 --          TO dovecot;
       
   312 --
       
   313 -- For more details see http://wiki.dovecot.org/UserDatabase
       
   314 -- ---
       
   315 CREATE OR REPLACE FUNCTION dovecotuser(
       
   316     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
       
   317 AS $$
       
   318     DECLARE
       
   319         record dovecotuser;
       
   320         userid varchar(320) := localpart || '@' || the_domain;
       
   321         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
   322     BEGIN
       
   323         FOR record IN
       
   324             SELECT userid, uid, did, domaindir || '/' || uid AS home,
       
   325                    format || ':~/' || directory AS mail
       
   326               FROM users, domain_data, mailboxformat, maillocation
       
   327              WHERE users.gid = did
       
   328                AND users.local_part = localpart
       
   329                AND maillocation.mid = users.mid
       
   330                AND mailboxformat.fid = maillocation.fid
       
   331                AND domain_data.gid = did
       
   332             LOOP
       
   333                 RETURN NEXT record;
       
   334             END LOOP;
       
   335         RETURN;
       
   336     END;
       
   337 $$ LANGUAGE plpgsql STABLE
       
   338 RETURNS NULL ON NULL INPUT
       
   339 EXTERNAL SECURITY INVOKER;
       
   340 
       
   341 -- ---
       
   342 -- Nearly the same as function dovecotuser above. It returns additionally the
       
   343 -- field quota_rule.
       
   344 --
       
   345 -- Required access privileges for your dovecot database user:
       
   346 --      GRANT SELECT
       
   347 --          ON users, domain_data, domain_name, maillocation, mailboxformat,
       
   348 --             quotalimit
       
   349 --          TO dovecot;
       
   350 -- ---
       
   351 CREATE OR REPLACE FUNCTION dovecotquotauser(
       
   352     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser
       
   353 AS $$
       
   354     DECLARE
       
   355         record dovecotquotauser;
       
   356         userid varchar(320) := localpart || '@' || the_domain;
       
   357         did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
       
   358     BEGIN
       
   359         FOR record IN
       
   360             SELECT userid, uid, did, domaindir || '/' || uid AS home,
       
   361                    format || ':~/' || directory AS mail, '*:bytes=' ||
       
   362                    bytes || ':messages=' || messages AS quota_rule
       
   363               FROM users, domain_data, mailboxformat, maillocation, quotalimit
       
   364              WHERE users.gid = did
       
   365                AND users.local_part = localpart
       
   366                AND maillocation.mid = users.mid
       
   367                AND mailboxformat.fid = maillocation.fid
       
   368                AND domain_data.gid = did
       
   369                AND quotalimit.qid = users.qid
       
   370             LOOP
       
   371                 RETURN NEXT record;
       
   372             END LOOP;
       
   373         RETURN;
       
   374     END;
       
   375 $$ LANGUAGE plpgsql STABLE
       
   376 RETURNS NULL ON NULL INPUT
       
   377 EXTERNAL SECURITY INVOKER;
       
   378 
       
   379 -- ########################################################################## --
       
   380 
       
   381 -- ---
       
   382 -- Data type for function dovecotpassword(varchar, varchar)
       
   383 -- ---
       
   384 CREATE TYPE dovecotpassword AS (
       
   385     userid    varchar(320),
       
   386     password  varchar(270),
       
   387     smtp      boolean,
       
   388     pop3      boolean,
       
   389     imap      boolean,
       
   390     sieve     boolean
       
   391 );
       
   392 
       
   393 -- ---
       
   394 -- Parameters (from login name [localpart@the_domain]):
       
   395 --      varchar localpart
       
   396 --      varchar the_domain
       
   397 -- Returns: dovecotpassword records
       
   398 --
       
   399 -- Required access privileges for your dovecot database user:
       
   400 --      GRANT SELECT ON users, domain_name, service_set TO dovecot;
       
   401 --
       
   402 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
       
   403 -- ---
       
   404 CREATE OR REPLACE FUNCTION dovecotpassword(
       
   405     IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
       
   406 AS $$
       
   407     DECLARE
       
   408         record dovecotpassword;
       
   409         userid varchar(320) := localpart || '@' || the_domain;
       
   410     BEGIN
       
   411         FOR record IN
       
   412             SELECT userid, passwd, smtp, pop3, imap, sieve
       
   413               FROM users, service_set
       
   414              WHERE gid = (SELECT gid
       
   415                             FROM domain_name
       
   416                            WHERE domainname = the_domain)
       
   417                AND local_part = localpart
       
   418                AND service_set.ssid = users.ssid
       
   419             LOOP
       
   420                 RETURN NEXT record;
       
   421             END LOOP;
       
   422         RETURN;
       
   423     END;
       
   424 $$ LANGUAGE plpgsql STABLE
       
   425 RETURNS NULL ON NULL INPUT
       
   426 EXTERNAL SECURITY INVOKER;