email

Email and Mailing lists

This module sends the following signals:
  • email/alias
  • email/list
  • email/mailbox
  • email/redirection

Tables

email.address

Collection of all known addresses

Primary key
  • localpart
  • domain
Foreign keys
Columns

email.alias

Aliases for e-mail mailboxes, owner is determined by mailbox.owner

Primary key
  • localpart
  • domain
Foreign keys
Columns
  • domain dns.t_hostname

    Domain name

  • service commons.t_key

    Service

  • service_entity_name dns.t_hostname

    ent. name

  • subservice commons.t_key

    Subservice (e.g. account, alias)

  • backend_status NULL | backend.t_status

    Status of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.

    Default
    'ins'
  • localpart email.t_localpart

    Local part

  • mailbox_localpart email.t_localpart

    Mailbox to which the mails will be delivered

  • mailbox_domain dns.t_hostname

    Mailbox to which the mails will be delivered

email.list

Mailing lists

Primary key
  • localpart
  • domain
Foreign keys
Columns
  • domain dns.t_hostname

    Domain name

  • service commons.t_key

    Service

  • service_entity_name dns.t_hostname

    ent. name

  • subservice commons.t_key

    Subservice (e.g. account, alias)

  • owner user.t_user

    Owner

    References user.user.owner

    On Update: CASCADE

  • backend_status NULL | backend.t_status

    Status of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.

    Default
    'ins'
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • localpart email.t_localpart

    Local part of the email list address

  • admin email.t_address

    Email address of the list admin

  • options NULL | jsonb

    Arbitrary options

email.list_subscriber

list subscribers

Primary key
  • address
  • list_localpart
  • list_domain
Foreign keys
Columns
  • backend_status NULL | backend.t_status

    Status of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.

    Default
    'ins'
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • address email.t_address

    Subscribers address

  • list_localpart email.t_localpart

    List

  • list_domain dns.t_hostname

    List

email.mailbox

E-mail mailboxs correspond to something a mail user can login into. Basically a mailbox represents a mailbox. A mailbox is bound to a specific address. Further addresses can be linked to mailboxs via aliases.

Primary key
  • localpart
  • domain
Foreign keys
Columns
  • domain dns.t_hostname

    Domain name

  • service commons.t_key

    Service

  • service_entity_name dns.t_hostname

    ent. name

  • subservice commons.t_key

    Subservice (e.g. account, alias)

  • owner user.t_user

    Owner

    References user.user.owner

    On Update: CASCADE

  • backend_status NULL | backend.t_status

    Status of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.

    Default
    'ins'
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • uid integer

    Unix user identifier

    Default
    nextval('commons.uid')
  • localpart email.t_localpart

    Local part

  • password commons.t_password

    Unix shadow crypt format

  • quota NULL | int

    Quota for mailbox in MiB

email.redirection

Redirections

Primary key
  • localpart
  • domain
Foreign keys
Columns

Functions

email._address

List all addresses

Parameters
None
Returns
TABLE
Returned columns
RETURN QUERY (
 SELECT t.localpart, t.domain, t.owner, t.subservice FROM email.mailbox AS t
 UNION ALL
 SELECT t.localpart, t.domain, t.owner, t.subservice FROM email.redirection AS t
 UNION ALL
 SELECT t.localpart, t.domain, s.owner, t.subservice FROM email.alias AS t
  LEFT JOIN email.mailbox AS s
   ON
    t.mailbox_localpart = s.localpart AND
    t.mailbox_domain = s.domain
 UNION ALL
 SELECT t.localpart, t.domain, t.owner, t.subservice FROM email.list AS t
);

email._address_valid

x

Parameters
Returns
void
IF (
    SELECT TRUE FROM email._address()
    WHERE
        localpart = p_localpart AND
        domain = p_domain
) THEN
    RAISE 'Email address already exists.'
        USING DETAIL = '$carnivora:email:address_already_exists$';
END IF;

email.del_alias

Delete Alias

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.alias AS t
    SET backend_status = 'del'
FROM email.mailbox AS s
WHERE
    -- JOIN
    t.mailbox_localpart = s.localpart AND
    t.mailbox_domain = s.domain AND

    t.localpart = p_localpart AND
    t.domain = p_domain AND
    s.localpart = p_mailbox_localpart AND
    s.domain = p_mailbox_domain AND

    s.owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'email', 'alias', p_domain);

email.del_list

Delete mailing list

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


DELETE FROM email.list
WHERE
    domain = p_domain AND
    localpart = p_localpart AND
    owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'email', 'list', p_domain);

email.del_list_subscriber

del

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.list_subscriber AS t
    SET backend_status = 'del'

    FROM email.list AS s
    WHERE
        s.localpart = t.list_localpart AND
        s.domain = t.list_domain AND
        s.owner = v_owner AND

        t.list_localpart = p_list_localpart AND
        t.list_domain = p_list_domain AND
        t.address = p_address;

PERFORM backend._conditional_notify(FOUND, 'email', 'list', p_list_domain);

email.del_mailbox

Delete mailbox

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.mailbox
        SET backend_status = 'del'
    WHERE
        localpart = p_localpart AND
        domain = p_domain AND
        owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'email', 'mailbox', p_domain);

email.del_redirection

Delete redirection

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.redirection
        SET backend_status = 'del'
    WHERE
        localpart = p_localpart AND
        domain = p_domain AND
        owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'email', 'redirection', p_domain);

email.ins_alias

Create e-mail aliases

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude

PERFORM email._address_valid(p_localpart, p_domain);

v_num_total := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice);
v_num_domain := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice AND t.domain = p_domain);

PERFORM system._contingent_ensure(
    p_owner:=v_owner,
    p_domain:=p_domain,
    p_service:='email',
    p_subservice:=v_subservice,
    p_current_quantity_total:=v_num_total,
    p_current_quantity_domain:=v_num_domain);


PERFORM email._address_valid(p_localpart, p_domain);
LOCK TABLE email.mailbox;

PERFORM commons._raise_inaccessible_or_missing(
EXISTS(
    SELECT TRUE FROM email.mailbox
    WHERE
        domain=p_mailbox_domain AND
        localpart=p_mailbox_localpart AND
        owner=v_owner AND
        backend._active(backend_status)
 ));

INSERT INTO email.alias
    (service, subservice, localpart, domain, mailbox_localpart, mailbox_domain, service_entity_name)
VALUES
    ('email', 'alias', p_localpart, p_domain, p_mailbox_localpart, p_mailbox_domain,
    (SELECT service_entity_name FROM dns.service WHERE service='email' AND domain = p_domain));

PERFORM backend._notify_domain('email', 'alias', p_domain);

email.ins_list

Creates a mailing list

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude

PERFORM email._address_valid(p_localpart, p_domain);

v_num_total := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice);
v_num_domain := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice AND t.domain = p_domain);

PERFORM system._contingent_ensure(
    p_owner:=v_owner,
    p_domain:=p_domain,
    p_service:='email',
    p_subservice:=v_subservice,
    p_current_quantity_total:=v_num_total,
    p_current_quantity_domain:=v_num_domain);


INSERT INTO email.list
    (service, subservice, localpart, domain, owner, admin, service_entity_name) VALUES
    ('email', 'list', p_localpart, p_domain, v_owner, p_admin,
    (SELECT service_entity_name FROM dns.service WHERE service='email' AND domain = p_domain));

PERFORM backend._notify_domain('email', 'list', p_domain);

email.ins_list_subscriber

Adds a subscriber to a mailing list

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


PERFORM commons._raise_inaccessible_or_missing(
    EXISTS(
        SELECT TRUE FROM email.list
        WHERE
            localpart = p_list_localpart AND
            domain =  p_list_domain AND
            owner = v_owner
    )
);

INSERT INTO email.list_subscriber
    (address, list_localpart, list_domain)
VALUES
    (p_address, p_list_localpart, p_list_domain);

PERFORM backend._notify_domain('email', 'list', p_list_domain);

email.ins_mailbox

Creates an email box

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude

PERFORM email._address_valid(p_localpart, p_domain);

v_num_total := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice);
v_num_domain := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice AND t.domain = p_domain);

PERFORM system._contingent_ensure(
    p_owner:=v_owner,
    p_domain:=p_domain,
    p_service:='email',
    p_subservice:=v_subservice,
    p_current_quantity_total:=v_num_total,
    p_current_quantity_domain:=v_num_domain);


PERFORM email._address_valid(p_localpart, p_domain);

INSERT INTO email.mailbox
    (service, subservice, localpart, domain, owner, password, service_entity_name) VALUES
    ('email', 'mailbox', p_localpart, p_domain, v_owner, commons._hash_password(p_password),
    (SELECT service_entity_name FROM dns.service WHERE service='email' AND domain = p_domain)
    );

PERFORM backend._notify_domain('email', 'mailbox', p_domain);

email.ins_redirection

Creates a redirection

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude

PERFORM email._address_valid(p_localpart, p_domain);

v_num_total := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice);
v_num_domain := (SELECT COUNT(*) FROM email._address() AS t WHERE t.owner=v_owner AND t.subservice=v_subservice AND t.domain = p_domain);

PERFORM system._contingent_ensure(
    p_owner:=v_owner,
    p_domain:=p_domain,
    p_service:='email',
    p_subservice:=v_subservice,
    p_current_quantity_total:=v_num_total,
    p_current_quantity_domain:=v_num_domain);


PERFORM email._address_valid(p_localpart, p_domain);

INSERT INTO email.redirection
    (service, subservice, localpart, domain, destination, owner, service_entity_name) VALUES
    ('email', 'redirection', p_localpart, p_domain, p_destination, v_owner,
    (SELECT service_entity_name FROM dns.service WHERE service='email' AND domain = p_domain));

PERFORM backend._notify_domain('email', 'redirection', p_domain);

email.sel_alias

Select aliases

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
SELECT
    t.localpart,
    t.domain,
    t.mailbox_localpart,
    t.mailbox_domain,
    t.backend_status
FROM email.alias AS t

INNER JOIN email.mailbox AS s
    ON
        t.mailbox_localpart = s.localpart AND
        t.mailbox_domain = s.domain
WHERE s.owner = v_owner

ORDER BY t.backend_status, t.localpart, t.domain;

email.sel_list

List all lists

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
    SELECT
        t.domain,
        t.localpart,
        t.owner,
        t.admin,
        t.backend_status,
        t.option,
        (SELECT COUNT(*) FROM email.list_subscriber AS s
        WHERE s.list_localpart=t.localpart AND s.list_domain=t.domain)
    FROM
        email.list AS t
    WHERE
        t.owner = v_owner
    ORDER BY t.backend_status, t.localpart, t.domain
;

email.sel_list_subscriber

a

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
    SELECT
        t.address,
        t.list_localpart,
        t.list_domain,
        t.backend_status
    FROM email.list_subscriber AS t
    JOIN email.list AS s
    ON
        t.list_localpart = s.localpart AND
        t.list_domain = s.domain
    WHERE
        s.owner = v_owner
    ORDER BY list_localpart, list_domain, backend_status, address
;

email.sel_mailbox

List all mailboxes

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
 SELECT
  t.domain,
  t.localpart,
  t.owner,
  t.quota,
  t.backend_status
 FROM
  email.mailbox AS t
 WHERE
  t.owner = v_owner
 ORDER BY backend_status, localpart, domain
;

email.sel_redirection

Lists all redirections

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
 SELECT
  t.domain,
  t.localpart,
  t.destination,
  t.backend_status
 FROM
  email.redirection AS t
 WHERE
  t.owner = v_owner
 ORDER BY t.backend_status, t.localpart, t.domain;

email.srv_alias

Lists all email aliases

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM email.alias AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged(t.service, t.domain)
    ),

    -- UPDATE
    s AS (
        UPDATE email.alias AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged(t.service, t.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.localpart,
        t.domain,
        t.mailbox_localpart,
        t.mailbox_domain,
        t.backend_status
    FROM email.alias AS t

    WHERE
        backend._machine_priviledged(t.service, t.domain) AND
        (backend._active(t.backend_status) OR p_include_inactive);

email.srv_list

Lists all mailinglists

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM email.list AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged(t.service, t.domain)
    ),

    -- UPDATE
    s AS (
        UPDATE email.list AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged(t.service, t.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.localpart,
        t.domain,
        t.admin,
        t.option,
        t.backend_status
    FROM email.list AS t

    WHERE
        backend._machine_priviledged(t.service, t.domain) AND
        (backend._active(t.backend_status) OR p_include_inactive);

email.srv_list_subscriber

Lists all mailinglist subscribers

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM email.list_subscriber AS t
        USING email.list AS l
        WHERE
            t.list_domain = l.domain AND
            t.list_localpart = l.localpart AND

            backend._deleted(t.backend_status) AND
            backend._machine_priviledged(l.service, l.domain)
    ),

    -- UPDATE
    s AS (
        UPDATE email.list_subscriber AS t
            SET backend_status = NULL
        FROM email.list AS l
        WHERE
            t.list_domain = l.domain AND
            t.list_localpart = l.localpart AND

            backend._machine_priviledged(l.service, l.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.list_localpart,
        t.list_domain,
        t.address,
        t.backend_status
    FROM email.list_subscriber AS t

    JOIN email.list AS l ON
        t.list_domain = l.domain AND
        t.list_localpart = l.localpart

    WHERE
        backend._machine_priviledged(l.service, l.domain) AND
        (backend._active(t.backend_status) OR p_include_inactive);

email.srv_mailbox

Lists all mailboxes

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM email.mailbox AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged(t.service, t.domain)
    ),

    -- UPDATE
    s AS (
        UPDATE email.mailbox AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged(t.service, t.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.localpart,
        t.domain,
        t.password,
        t.uid,
        t.quota,
        t.option,
        t.backend_status
    FROM email.mailbox AS t

    WHERE
        backend._machine_priviledged(t.service, t.domain) AND
        (backend._active(t.backend_status) OR p_include_inactive);

email.srv_redirection

Lists all mailinglists

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM email.redirection AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged(t.service, t.domain)
    ),

    -- UPDATE
    s AS (
        UPDATE email.redirection AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged(t.service, t.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.localpart,
        t.domain,
        t.destination,
        t.backend_status
    FROM email.redirection AS t

    WHERE
        backend._machine_priviledged(t.service, t.domain) AND
        (backend._active(t.backend_status) OR p_include_inactive);

email.upd_list

Change list admin

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.list
    SET
        admin = p_admin,
        backend_status = 'upd'
WHERE
    localpart = p_localpart AND
    domain = p_domain AND
    owner = v_owner AND
    backend._active(backend_status);

PERFORM backend._conditional_notify(FOUND, 'email', 'list', p_domain);

email.upd_mailbox

Change mailbox password

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE email.mailbox
    SET
        password = commons._hash_password(p_password),
        backend_status = 'upd'
WHERE
    localpart = p_localpart AND
    domain = p_domain AND
    owner = v_owner AND
    backend._active(backend_status);

PERFORM backend._conditional_notify(FOUND, 'email', 'mailbox', p_domain);

Domains

email.t_localpart

Local part of an email address, the thing in front of the @

Checks
  • valid_characters

    Only allow lower-case addresses

    VALUE ~ '^[a-z0-9.\-]+$'
  • no_starting_dot

    b

    left(VALUE, 1) <> '.'
  • no_ending_dot

    c

    right(VALUE, 1) <> '.'

email.t_address

Email address

Todo

validity checks