email¶
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
Tables¶
email.address
¶
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
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)
localpart
email.t_localpart- Local part
email.alias
¶
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
dns.t_hostnameDomain name
service
commons.t_keyService
service_entity_name
dns.t_hostnameent. name
subservice
commons.t_keySubservice (e.g. account, alias)
backend_status
NULL | backend.t_statusStatus 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_localpartLocal part
mailbox_localpart
email.t_localpartMailbox to which the mails will be delivered
mailbox_domain
dns.t_hostnameMailbox to which the mails will be delivered
email.list
¶
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
dns.t_hostnameDomain name
service
commons.t_keyService
service_entity_name
dns.t_hostnameent. name
subservice
commons.t_keySubservice (e.g. account, alias)
owner
user.t_userOwner
References user.user.owner
On Update: CASCADE
backend_status
NULL | backend.t_statusStatus 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
jsonbFree options in JSON format
- Default
'{}'
localpart
email.t_localpartLocal part of the email list address
admin
email.t_addressEmail address of the list admin
options
NULL | jsonbArbitrary options
email.list_subscriber
¶
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 | backend.t_statusStatus 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
jsonbFree options in JSON format
- Default
'{}'
address
email.t_addressSubscribers address
list_localpart
email.t_localpartList
list_domain
dns.t_hostnameList
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
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
dns.t_hostnameDomain name
service
commons.t_keyService
service_entity_name
dns.t_hostnameent. name
subservice
commons.t_keySubservice (e.g. account, alias)
owner
user.t_userOwner
References user.user.owner
On Update: CASCADE
backend_status
NULL | backend.t_statusStatus 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
jsonbFree options in JSON format
- Default
'{}'
uid
integerUnix user identifier
- Default
nextval('commons.uid')
localpart
email.t_localpartLocal part
password
commons.t_passwordUnix shadow crypt format
quota
NULL | intQuota for mailbox in MiB
email.redirection
¶
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
dns.t_hostnameDomain name
service
commons.t_keyService
service_entity_name
dns.t_hostnameent. name
subservice
commons.t_keySubservice (e.g. account, alias)
owner
user.t_userOwner
References user.user.owner
On Update: CASCADE
backend_status
NULL | backend.t_statusStatus 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_localpartLocal part
destination
email.t_addressExternal address to which the mails will be delivered
Functions¶
email._address
¶
List all addresses
- Parameters
- None
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnameowner
user.t_usersubservice
commons.t_key
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
p_localpart
email.t_localpartp_domain
dns.t_hostname
- 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
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_mailbox_localpart
email.t_localpartp_mailbox_domain
dns.t_hostname
- Variables defined for body
v_owner
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
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
p_domain
dns.t_hostnamep_localpart
email.t_localpart
- Variables defined for body
v_owner
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
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
p_list_localpart
email.t_localpartp_list_domain
dns.t_hostnamep_address
email.t_address
- Variables defined for body
v_owner
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
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
p_localpart
email.t_localpartp_domain
dns.t_hostname
- Variables defined for body
v_owner
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
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
p_localpart
email.t_localpartp_domain
dns.t_hostname
- Variables defined for body
v_owner
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
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
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_mailbox_localpart
email.t_localpartp_mailbox_domain
dns.t_hostname
- Variables defined for body
v_subservice
commons.t_key (default:'alias'
)v_num_total
intv_num_domain
intv_owner
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);
email.ins_list
¶
Creates a mailing list
- Parameters
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_admin
email.t_address
- Variables defined for body
v_subservice
commons.t_key (default:'list'
)v_num_total
intv_num_domain
intv_owner
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);
email.ins_list_subscriber
¶
Adds a subscriber to a mailing list
- Parameters
p_address
email.t_addressp_list_localpart
email.t_localpartp_list_domain
dns.t_hostname
- Variables defined for body
v_owner
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 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
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_password
commons.t_password_plaintext
- Variables defined for body
v_subservice
commons.t_key (default:'mailbox'
)v_num_total
intv_num_domain
intv_owner
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);
email.ins_redirection
¶
Creates a redirection
- Parameters
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_destination
email.t_address
- Variables defined for body
v_subservice
commons.t_key (default:'redirection'
)v_num_total
intv_num_domain
intv_owner
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);
email.sel_alias
¶
Select aliases
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnamemailbox_localpart
email.t_localpartmailbox_domain
dns.t_hostnamebackend_status
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;
email.sel_list
¶
List all lists
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
domain
dns.t_hostnamelocalpart
email.t_localpartowner
user.t_useradmin
email.t_addressbackend_status
backend.t_statusoption
jsonbnum_subscribers
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
;
email.sel_list_subscriber
¶
a
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
address
email.t_addresslist_localpart
email.t_localpartlist_domain
dns.t_hostnamebackend_status
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
;
email.sel_mailbox
¶
List all mailboxes
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
domain
dns.t_hostnamelocalpart
email.t_localpartowner
user.t_userquota
intbackend_status
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.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
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
domain
dns.t_hostnamelocalpart
email.t_localpartdestination
email.t_addressbackend_status
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.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
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnamemailbox_localpart
email.t_localpartmailbox_domain
dns.t_hostnamebackend_status
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);
email.srv_list
¶
Lists all mailinglists
- Parameters
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnameadmin
email.t_addressoption
jsonbbackend_status
backend.t_status
- 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
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnameaddress
email.t_addressbackend_status
backend.t_status
- 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
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnamepassword
commons.t_passworduid
integerquota
integeroption
jsonbbackend_status
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);
email.srv_redirection
¶
Lists all mailinglists
- Parameters
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
localpart
email.t_localpartdomain
dns.t_hostnamedestination
email.t_addressbackend_status
backend.t_status
- 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
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_admin
email.t_address
- Variables defined for body
v_owner
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
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
p_localpart
email.t_localpartp_domain
dns.t_hostnamep_password
commons.t_password_plaintext
- Variables defined for body
v_owner
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
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) <> '.'