Email and Mailing lists
- This module sends the following signals:
- email/alias
- email/list
- email/mailbox
- email/redirection
Schema Contents
- Tables
- Functions
email._address
email._address_valid
email.del_alias
email.del_list
email.del_list_subscriber
email.del_mailbox
email.del_redirection
email.ins_alias
email.ins_list
email.ins_list_subscriber
email.ins_mailbox
email.ins_redirection
email.sel_alias
email.sel_list
email.sel_list_subscriber
email.sel_mailbox
email.sel_redirection
email.srv_alias
email.srv_list
email.srv_list_subscriber
email.srv_mailbox
email.srv_redirection
email.upd_list
email.upd_mailbox
- Domains
Collection of all known addresses
- Primary key
- localpart
- domain
- Foreign keys
reference dns (service)
- Local Columns
- domain
- service
- service_entity_name
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
- Columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`- Domain name
service
:ref:`commons.t_key <DOMAIN-commons.t_key>`- Service
service_entity_name
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`- ent. name
subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>`- Subservice (e.g. account, alias)
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`- Local part
Aliases for e-mail mailboxes, owner is determined by mailbox.owner
- Primary key
- localpart
- domain
- Foreign keys
reference dns (service)
- Local Columns
- domain
- service
- service_entity_name
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
reference to a mailbox
- Local Columns
- mailbox_localpart
- mailbox_domain
- Referenced Columns
- Columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`Domain name
service
:ref:`commons.t_key <DOMAIN-commons.t_key>`Service
service_entity_name
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`ent. name
subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>`Subservice (e.g. account, alias)
backend_status
NULL | :ref:`backend.t_status <DOMAIN-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
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`Local part
mailbox_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`Mailbox to which the mails will be delivered
mailbox_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`Mailbox to which the mails will be delivered
Mailing lists
- Primary key
- localpart
- domain
- Foreign keys
reference dns (service)
- Local Columns
- domain
- service
- service_entity_name
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
- Columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`Domain name
service
:ref:`commons.t_key <DOMAIN-commons.t_key>`Service
service_entity_name
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`ent. name
subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>`Subservice (e.g. account, alias)
owner
:ref:`user.t_user <DOMAIN-user.t_user>`Owner
References :ref:`user.user.owner <COLUMN-user.user.owner>`
On Update: CASCADE
backend_status
NULL | :ref:`backend.t_status <DOMAIN-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
:ref:`jsonb <DOMAIN-jsonb>`Free options in JSON format
- Default
'{}'
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`Local part of the email list address
admin
:ref:`email.t_address <DOMAIN-email.t_address>`Email address of the list admin
options
NULL | :ref:`jsonb <DOMAIN-jsonb>`Arbitrary options
list subscribers
- Primary key
- address
- list_localpart
- list_domain
- Foreign keys
reference to a list
- Local Columns
- list_localpart
- list_domain
- Referenced Columns
- Columns
backend_status
NULL | :ref:`backend.t_status <DOMAIN-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
:ref:`jsonb <DOMAIN-jsonb>`Free options in JSON format
- Default
'{}'
address
:ref:`email.t_address <DOMAIN-email.t_address>`Subscribers address
list_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`List
list_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`List
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
reference dns (service)
- Local Columns
- domain
- service
- service_entity_name
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
- Columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`Domain name
service
:ref:`commons.t_key <DOMAIN-commons.t_key>`Service
service_entity_name
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`ent. name
subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>`Subservice (e.g. account, alias)
owner
:ref:`user.t_user <DOMAIN-user.t_user>`Owner
References :ref:`user.user.owner <COLUMN-user.user.owner>`
On Update: CASCADE
backend_status
NULL | :ref:`backend.t_status <DOMAIN-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
:ref:`jsonb <DOMAIN-jsonb>`Free options in JSON format
- Default
'{}'
uid
:ref:`integer <DOMAIN-integer>`Unix user identifier
- Default
nextval('commons.uid')
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`Local part
password
:ref:`commons.t_password <DOMAIN-commons.t_password>`Unix shadow crypt format
quota
NULL | :ref:`int <DOMAIN-int>`Quota for mailbox in MiB
Redirections
- Primary key
- localpart
- domain
- Foreign keys
reference dns (service)
- Local Columns
- domain
- service
- service_entity_name
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
- Columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`Domain name
service
:ref:`commons.t_key <DOMAIN-commons.t_key>`Service
service_entity_name
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`ent. name
subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>`Subservice (e.g. account, alias)
owner
:ref:`user.t_user <DOMAIN-user.t_user>`Owner
References :ref:`user.user.owner <COLUMN-user.user.owner>`
On Update: CASCADE
backend_status
NULL | :ref:`backend.t_status <DOMAIN-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
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`Local part
destination
:ref:`email.t_address <DOMAIN-email.t_address>`External address to which the mails will be delivered
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
);
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;
Delete Alias
- Parameters
p_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`p_mailbox_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_mailbox_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`
- 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);
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);
del
- Parameters
p_list_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_list_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`p_address
:ref:`email.t_address <DOMAIN-email.t_address>`
- 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);
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);
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);
Create e-mail aliases
- Parameters
p_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`p_mailbox_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_mailbox_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`
- Variables defined for body
v_subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>` (default:'alias'
)v_num_total
:ref:`int <DOMAIN-int>`v_num_domain
:ref:`int <DOMAIN-int>`v_owner
:ref:`user.t_user <DOMAIN-user.t_user>`
- 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);
Creates a mailing list
- Parameters
- Variables defined for body
v_subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>` (default:'list'
)v_num_total
:ref:`int <DOMAIN-int>`v_num_domain
:ref:`int <DOMAIN-int>`v_owner
:ref:`user.t_user <DOMAIN-user.t_user>`
- 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);
Adds a subscriber to a mailing list
- Parameters
p_address
:ref:`email.t_address <DOMAIN-email.t_address>`p_list_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_list_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`
- 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);
Creates an email box
- Parameters
- Variables defined for body
v_subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>` (default:'mailbox'
)v_num_total
:ref:`int <DOMAIN-int>`v_num_domain
:ref:`int <DOMAIN-int>`v_owner
:ref:`user.t_user <DOMAIN-user.t_user>`
- 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);
Creates a redirection
- Parameters
p_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`p_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`p_destination
:ref:`email.t_address <DOMAIN-email.t_address>`
- Variables defined for body
v_subservice
:ref:`commons.t_key <DOMAIN-commons.t_key>` (default:'redirection'
)v_num_total
:ref:`int <DOMAIN-int>`v_num_domain
:ref:`int <DOMAIN-int>`v_owner
:ref:`user.t_user <DOMAIN-user.t_user>`
- 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);
Select aliases
- Parameters
- None
- Variables defined for body
- Returns
- TABLE
- Returned columns
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`mailbox_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`mailbox_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`backend_status
:ref:`backend.t_status <DOMAIN-backend.t_status>`
- 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;
List all lists
- Parameters
- None
- Variables defined for body
- Returns
- TABLE
- Returned columns
domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`owner
:ref:`user.t_user <DOMAIN-user.t_user>`admin
:ref:`email.t_address <DOMAIN-email.t_address>`backend_status
:ref:`backend.t_status <DOMAIN-backend.t_status>`option
:ref:`jsonb <DOMAIN-jsonb>`num_subscribers
:ref:`bigint <DOMAIN-bigint>`
- 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
;
a
- Parameters
- None
- Variables defined for body
- Returns
- TABLE
- Returned columns
address
:ref:`email.t_address <DOMAIN-email.t_address>`list_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`list_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`backend_status
:ref:`backend.t_status <DOMAIN-backend.t_status>`
- 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
;
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
;
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;
Lists all email aliases
- Parameters
p_include_inactive
:ref:`boolean <DOMAIN-boolean>`
- Returns
- TABLE
- Returned columns
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`mailbox_localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`mailbox_domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`backend_status
:ref:`backend.t_status <DOMAIN-backend.t_status>`
- 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);
Lists all mailinglists
- Parameters
p_include_inactive
:ref:`boolean <DOMAIN-boolean>`
- 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);
Lists all mailinglist subscribers
- Parameters
p_include_inactive
:ref:`boolean <DOMAIN-boolean>`
- 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);
Lists all mailboxes
- Parameters
p_include_inactive
:ref:`boolean <DOMAIN-boolean>`
- Returns
- TABLE
- Returned columns
localpart
:ref:`email.t_localpart <DOMAIN-email.t_localpart>`domain
:ref:`dns.t_hostname <DOMAIN-dns.t_hostname>`password
:ref:`commons.t_password <DOMAIN-commons.t_password>`uid
:ref:`integer <DOMAIN-integer>`quota
:ref:`integer <DOMAIN-integer>`option
:ref:`jsonb <DOMAIN-jsonb>`backend_status
:ref:`backend.t_status <DOMAIN-backend.t_status>`
- 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);
Lists all mailinglists
- Parameters
p_include_inactive
:ref:`boolean <DOMAIN-boolean>`
- 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);
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);
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);
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 address
.. todo:: validity checks