Contents

Carnivora

https://readthedocs.org/projects/carnivora/badge/?version=stable https://readthedocs.org/projects/carnivora/badge/?version=latest

A powerfull backend for web-service management. Written in YamSql.

The documentation can be found online at carnivora.readthedocs.io or as sphinx source in docs/.

Client Software

Canini
Full privileged superadmin CLI. Supports adding additional modules via config. Written in Python 3.
Edentata
An unprivileged webinterface without superadmin capabilities. Targeting end-users and focused on usability. Supports adding additional modules via config. Written in PHP 7.
Genconfig
A generic config producer which can use carnivora as backend. Written in Python 3.
PgListend
Daemon that executes tasks on PostgreSQL push signals. Designed to call genconfig on database updates. Written in Python 3.
LibInternetX
PHP library for connecting to the InterNetX domain reseller XML API. Includes CLI coupling to Carnivora.

Installing Carnivora

Perequisites

The setup is performed via HamSql. It should be callable as hamsql in your shell.

Install PostgreSQL on Debian

apt install postgresql postgresql-contrib postgresql-plpython3

Configuration

You can configure accounts that can connect to the database via /etc/carnivora/_postgresql_user/module.yaml. The accounts generated via this config have the names carnivora_edentata and carnivora_machine_example.

name: _postgresql_user
description: PostgreSQL users and their priviledges

roles:
 -
  name: edentata
  login: true
  description: Account for edentata web frontend
  member_in:
   - userlogin

 -
  name: machine_example
  description: Account for machine example
  login: true
  member_in:
   - backend

Running the Setup

Simplest way to execute the setup on a system with a default PostgreSQL configuration is to run

su postgres -c "hamsql install -s examples/setup.yml -c postgres://postgres@/carnivora"

Supplying the database name (here carnivora) via the -c option is mandatory. The database will be create if it is not present. Additional or deviating connection options can be provided.

API Conventions

Function Naming

Prefix _
Internal functions which do not belong to API.

Frontend API

Prefix del_
Delete object from database. Returns void.
Prefix ins_
Insert object to database. Returns void.
Prefix sel_
Gives all object for which the user has ownership. Returns a recordset that can be used as <table> in a SELECT ... FROM <table> statement.
Prefix upd_
Updates objects in database. Returns void.

Backend API

Prefix srv_
Gives all object designated to the connected machine. Returns a recordset.
Prefix fwd_
Forwards informations from a machine to carnivora. Similar to upd_ but for backend.

Backend Notification Naming

Carnivora sends push notifications (usign the PostgreSQL NOTFIY command) if objects are changed. Machines can connect to to channels that only give notifications relevant to them.

Channel
carnivora/ machine name
Payload

service entity name / service / subservice

Example mail.example.org/mail/mailbox.

The emitted signals are documented in the schema description. The sevice entity name is silently omitted in those documentations.

Development

Todo List

Todo

Document managed_custom. Unclear if this is even properly supported or checked.

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas/dns.rst, line 14.)

Todo

checks might be off

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas/dns.rst, line 1384.)

Todo

validity checks

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas/email.rst, line 2287.)

Todo

Fix missing owner verification (not critical)

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas/web.rst, line 856.)

Todo

check owner and contingent

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas/web.rst, line 1002.)

Todo

propper checking of format

(The original entry is located in /home/docs/checkouts/readthedocs.org/user_builds/carnivora/checkouts/v0.13.1/docs/schemas_system/commons.rst, line 277.)

dns

DNS and Registered Domains

The entity name for domain_registered services are considered the nameservers used for this domain. In case of the unmanaged subservice, a a fake name or the responsible nameserver that is not managed by the system can be given.

To allow service activation, the service needs a dns_activatable subservice entity.

Todo

Document managed_custom. Unclear if this is even properly supported or checked.

Tables

dns.custom

Direct name server entries.

Primary key
  • id
Columns
  • type dns.t_type

    Type (A, AAAA, CNAME, MX, SRV, TXT, …)

  • rdata dns.t_rdata

    fancy rdata storage

  • ttl NULL | dns.t_ttl

    Time to live, NULL indicates default value

  • 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'
  • registered dns.t_hostname

    Registered domain of which domain is a sub domain

    References dns.registered.domain

    On Delete: CASCADE

  • domain dns.t_domain

    domain of entry

  • id uuid

    uuid serial number to identify database elements uniquely

    Default
    commons._uuid()

dns.registered

Domains registered under a public suffix.

Primary key
  • domain
Foreign keys
Columns
  • 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'
  • service_entity_name dns.t_hostname

    Service entity name

  • service commons.t_key

    Service (e.g. email, jabber)

  • subservice commons.t_key

    Subservice (e.g. account, alias)

  • domain dns.t_hostname

    Domain

  • public_suffix varchar

    Public Suffix

dns.service

Name server entries based on system.service (i.e. system.service_dns)

Primary key
  • domain
  • service
Foreign keys
Columns
  • service_entity_name dns.t_hostname

    Service entity name

  • service commons.t_key

    Service (e.g. email, jabber)

  • 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'
  • registered dns.t_hostname

    Registered domain of which domain is a sub domain

    References dns.registered.domain

  • domain dns.t_hostname

    domain for which the entries should be created

Functions

dns._domain_order

ORDER

Parameters
Returns
varchar[]
Execute privilege
RETURN commons._reverse_array(regexp_split_to_array(p_domain, E'\\.'));

dns._is_subdomain_of

Checks if p_subdomain is a subdomain of p_domain

Parameters
Returns
bool
RETURN p_domain = p_subdomain OR
 '.' || p_domain = right(p_subdomain, char_length(p_domain) + 1);

dns._rdata_txtdata_valid

Rdata txt-data valid

Parameters
Returns
bool
RETURN ((
   SELECT DISTINCT TRUE
       FROM UNNEST(p_txtdata) AS s
       WHERE octet_length(s) > 255
) IS NULL);

dns.del_custom

Delete Custom

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 dns.custom AS t
       SET backend_status = 'del'
FROM dns.registered AS s
WHERE
    s.domain = t.registered AND

    t.id = p_id AND
    s.owner = v_owner

RETURNING s.service_entity_name, s.subservice
INTO v_nameserver, v_managed;

PERFORM backend._conditional_notify_service_entity_name(
    FOUND, v_nameserver, 'dns', v_managed
);

dns.del_registered

Delete registered domain

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 dns.registered
SET backend_status = 'del'
WHERE domain = p_domain
 AND owner = v_owner
RETURNING service_entity_name, subservice
   INTO v_nameserver, v_managed;

PERFORM backend._conditional_notify_service_entity_name(
   FOUND, v_nameserver, 'domain_registered', v_managed
);

dns.del_service

deletes all service entries of a specific domain

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


BEGIN
    -- perform DELETE to trigger potential foreign key errors
    DELETE FROM dns.service AS t
    USING dns.registered AS s
    WHERE
        s.domain = t.registered AND

        t.domain = p_domain AND
        t.service = p_service AND
        s.owner = v_owner;

    -- if not failed yet, emulate rollback of DELETE
    RAISE transaction_rollback;
EXCEPTION
    WHEN transaction_rollback THEN
        UPDATE dns.service AS t
               SET backend_status = 'del'
        FROM dns.registered AS s
        WHERE
            s.domain = t.registered AND

            t.domain = p_domain AND
            t.service = p_service AND
            s.owner = v_owner
        RETURNING s.service_entity_name, s.subservice
        INTO v_nameserver, v_managed;

        PERFORM backend._conditional_notify_service_entity_name(
            FOUND, v_nameserver, 'dns', v_managed
        );

END;

dns.fwd_registered_status

Update status

Parameters
Returns
void
Execute privilege
PERFORM backend._get_login();


UPDATE dns.registered
SET
    backend_status = p_backend_status
WHERE domain = p_domain;

dns.ins_custom

Ins Custom

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


SELECT service_entity_name, subservice INTO v_nameserver, v_managed FROM dns.registered
WHERE
    domain = p_registered AND
    owner = v_owner;

IF v_nameserver IS NULL THEN
    PERFORM commons._raise_inaccessible_or_missing();
END IF;

IF v_nameserver IS NULL THEN
    PERFORM commons._raise_inaccessible_or_missing();
END IF;

INSERT INTO dns.custom
(registered, domain, type, rdata, ttl)
VALUES
(p_registered, p_domain, p_type, p_rdata, p_ttl);

PERFORM backend._notify_service_entity_name(v_nameserver, 'dns', v_managed);

dns.ins_registered

registeres new domain

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


INSERT INTO dns.registered
(domain, public_suffix, owner, service, subservice, service_entity_name)
VALUES
(p_domain, p_public_suffix, v_owner, 'domain_registered', p_subservice, p_service_entity_name);

PERFORM backend._notify_service_entity_name(p_service_entity_name, 'domain_registered', p_subservice);

dns.ins_service

Creates service dns entry

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


SELECT service_entity_name, subservice INTO v_nameserver, v_managed FROM dns.registered
    WHERE
        domain = p_registered AND
        owner = v_owner;

IF v_nameserver IS NULL THEN
    PERFORM commons._raise_inaccessible_or_missing();
END IF;

INSERT INTO dns.service (registered, domain, service_entity_name, service)
    VALUES (p_registered, p_domain, p_service_entity_name, p_service);

PERFORM backend._notify_service_entity_name(v_nameserver, 'dns', v_managed);

dns.sel_activatable_service

Activatable services

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
    COALESCE(t.service, s.service) AS service,
    COALESCE(t.service_entity_name, s.service_entity_name) AS service_entity_name
FROM system._effective_contingent() AS t
FULL OUTER JOIN system._effective_contingent_domain() AS s
USING (service, subservice, service_entity_name, owner)
WHERE
    COALESCE(t.subservice, s.subservice) = 'dns_activatable' AND
    COALESCE(t.owner, s.owner) = v_owner

  ORDER BY service, service_entity_name
;

dns.sel_custom

sel custom

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.id,
        t.registered,
        t.domain,
        t.type,
        t.rdata,
        t.ttl,
        t.backend_status
    FROM dns.custom AS t
    JOIN dns.registered AS s
        ON s.domain = t.registered
    WHERE
        s.owner = v_owner
    ORDER BY backend_status, registered, dns._domain_order(t.domain);

dns.sel_nameserver

Select available nameservers

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
    COALESCE(t.subservice, s.subservice) AS subservice,
    COALESCE(t.service_entity_name, s.service_entity_name) AS service_entity_name
  FROM system._effective_contingent() AS t

  FULL OUTER JOIN system._effective_contingent_domain() AS s
    USING (service, subservice, service_entity_name, owner)

  WHERE
    COALESCE(t.service, s.service) = 'domain_registered' AND
    COALESCE(t.owner, s.owner) = v_owner

  ORDER BY subservice, service_entity_name
;

dns.sel_registered

List registered domains

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.public_suffix, t.backend_status, t.subservice, t.service_entity_name
    FROM dns.registered AS t
    WHERE
        t.owner = v_owner
    ORDER BY backend_status, domain;

dns.sel_service

Select service based dns entries

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.registered,
        t.domain,
        t.service,
        t.service_entity_name,
        t.backend_status
    FROM dns.service AS t
    JOIN dns.registered AS s
        ON s.domain = t.registered
    WHERE
        s.owner = v_owner
    ORDER BY backend_status, registered, dns._domain_order(t.domain), service, service_entity_name;

dns.sel_usable_domain

Usable domains

Parameters
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.service_entity_name FROM dns.service AS t
    JOIN dns.registered AS d
        ON d.domain = t.registered
    LEFT JOIN system._effective_contingent_domain() AS contingent_d
        ON
            contingent_d.domain = t.domain AND
            contingent_d.service = t.service AND
            contingent_d.subservice = p_subservice AND
            contingent_d.service_entity_name = t.service_entity_name AND
            contingent_d.owner = v_owner

    LEFT JOIN system._effective_contingent() AS contingent
        ON
            contingent.service = t.service AND
            contingent.subservice = p_subservice AND
            contingent.owner = v_owner AND
            d.owner = v_owner

    WHERE
        t.service = p_service AND
        COALESCE(contingent_d.domain_contingent, contingent.domain_contingent, 0) > 0
    ORDER BY
        t.domain
;

dns.srv_record

Servers both record types combined: Raw entries and the ones assembled from records templates for services (system.service_entity_dns).

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


RETURN QUERY
    WITH

    -- DELETE
    d_s AS (
        DELETE FROM dns.service AS t
        USING dns.registered AS s
        WHERE
            s.domain = t.registered AND
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged_service('dns', s.service_entity_name)
    ),

    d_c AS (
        DELETE FROM dns.custom AS t
        USING dns.registered AS s
        WHERE
            s.domain = t.registered AND
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged_service('dns', s.service_entity_name)
    ),

    -- UPDATE
    u_s AS (
        UPDATE dns.service AS t
            SET backend_status = NULL
        FROM dns.registered AS s
        WHERE
            s.domain = t.registered AND
            backend._machine_priviledged_service('dns', s.service_entity_name) AND
            backend._active(t.backend_status)
    ),

    u_c AS (
        UPDATE dns.custom AS t
            SET backend_status = NULL
        FROM dns.registered AS s
        WHERE
            s.domain = t.registered AND
            backend._machine_priviledged_service('dns', s.service_entity_name) AND
            backend._active(t.backend_status)
    )

    SELECT
        t.registered,
        COALESCE(s.domain_prefix || t.domain, t.domain)::varchar,
        s.type,
        s.rdata,
        s.ttl,
        t.backend_status
    FROM dns.service AS t
    JOIN system.service_entity_dns AS s
        USING (service, service_entity_name)
    JOIN dns.registered AS u
        ON t.registered = u.domain
    WHERE
        u.subservice = 'managed' AND
        backend._machine_priviledged_service('dns', u.service_entity_name) AND
        (backend._active(t.backend_status) OR p_include_inactive)

    UNION ALL

    SELECT
        t.registered,
        t.domain,
        t.type,
        t.rdata,
        t.ttl,
        t.backend_status
    FROM dns.custom AS t
    JOIN dns.registered AS u
        ON t.registered = u.domain
    WHERE
        u.subservice = 'managed' AND
        backend._machine_priviledged_service('dns', u.service_entity_name) AND
        (backend._active(t.backend_status) OR p_include_inactive)
    ;

dns.upd_custom

Ins Custom

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 dns.custom AS t
    SET
        rdata = p_rdata,
        ttl = p_ttl,
        backend_status = 'upd'
FROM dns.registered AS s

WHERE
    s.domain = t.registered AND

    t.id = p_id AND
    s.owner = v_owner
RETURNING s.service_entity_name, s.subservice INTO v_nameserver, v_managed;

PERFORM backend._notify_service_entity_name(v_nameserver, 'dns', v_managed);

Domains

dns.t_domain

Fully qualified hostname (without trailing dot)

Checks
  • hostname valid regex

    Hostname

    VALUE ~ '^[a-z\d_-]{1,63}(\.[a-z\d_-]{1,63})+$' AND
octet_length(VALUE) <= 253

dns.t_domain_rdata

Fully qualified or relative domain name. Trailing dot marks a FQDN.

Todo

checks might be off

Checks
  • invalid rdata domain

    check

    (VALUE ~ '^([a-z\d][a-z\d-]{0,62}\.)+$' OR
 VALUE ~ '^([a-z\d][a-z\d-]{0,62}\.)*[a-z\d][a-z\d-]{1,63}$') AND
octet_length(VALUE) <= 253

dns.t_hostname

Fully qualified hostname (without trailing dot)

Checks
  • hostname valid regex

    Hostname

    VALUE ~ '^([a-z\d]|[a-z\d][a-z\d-]{0,61}[a-z\d])(\.([a-z\d]|[a-z\d][a-z\d-]{0,61}[a-z\d]))+$' AND
octet_length(VALUE) <= 253

dns.t_rdata

Resource record data (Rdata)

dns.t_ttl

time to live

Checks
  • ttl range

    Ensure that TTL is at least one minute and put maximum to 48h

    VALUE >= 60 AND VALUE <= 172800

dns.t_type

Resource record type

Checks
  • Invalid or unsupported resource type

    Resource type (A, AAAA, CNAME, MX, SRV, TXT, …)

    VALUE IN (
 'A',
 'AAAA',
 'CNAME',
 'MX',
 'NS',
 'SRV',
 'SSHFP',
 'TXT'
)

domain_reseller

Features for Domains Registered via a Reseller

Stores additional details for dns.registered domains. Also supports storing contact informations (handles).

This module sends the following signals:
  • domain_reseller/handle
  • domain_registered/managed
  • domain_registered/unmanaged

Tables

domain_reseller.handle

Handles (Domain Contacts)

Domain contacts that can be used as owner, admin-c, tech-c or zone-c.

Primary key
  • alias
Foreign keys
Columns

domain_reseller.registered

Addtional informations to those stored in dns.registered

Primary key
  • domain
Columns

Functions

domain_reseller.del_handle

Deletes handle

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


BEGIN
    -- perform DELETE to trigger potential foreign key errors
    DELETE FROM domain_reseller.handle
    WHERE
        alias = p_alias AND
        owner = v_owner;

    -- if not failed yet, emulate rollback of DELETE
    RAISE transaction_rollback;
EXCEPTION
    WHEN transaction_rollback THEN
        UPDATE domain_reseller.handle
               SET backend_status = 'del'
        WHERE
            alias = p_alias AND
            owner = v_owner
        RETURNING service_entity_name INTO v_service_entity_name;

        PERFORM backend._conditional_notify_service_entity_name(
            FOUND, v_service_entity_name, 'domain_reseller', 'handle'
        );
END;

domain_reseller.fwd_handle_id

Insert handle id

Parameters
Returns
void
Execute privilege
PERFORM backend._get_login();


UPDATE domain_reseller.handle
    SET id = p_id
    WHERE alias = p_alias;

domain_reseller.fwd_registered_status

Update status

Parameters
Returns
void
Execute privilege
PERFORM backend._get_login();


UPDATE domain_reseller.registered
SET
    payable = p_payable,
    period = p_period,
    registrar_status = p_registrar_status,
    registry_status = p_registry_status,
    last_status = p_last_status
WHERE domain = p_domain;

domain_reseller.ins_handle

Inserts handle

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


INSERT INTO domain_reseller.handle
(
  service_entity_name,
  service,
  subservice,
  owner,
  alias,
  fname,
  lname,
  address,
  pcode,
  city,
  country,
  state,
  email,
  phone,
  organization,
  fax,
  mobile_phone
)
VALUES
(
  p_service_entity_name,
  'domain_reseller',
  'handle',
  v_owner,
  p_alias,
  p_fname,
  p_lname,
  p_address,
  p_pcode,
  p_city,
  p_country,
  p_state,
  p_email,
  p_phone,
  p_organization,
  p_fax,
  p_mobile_phone
);

PERFORM backend._notify_service_entity_name(p_service_entity_name, 'domain_reseller', 'handle');

domain_reseller.ins_registered

Inserts details for registered domain

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


INSERT INTO domain_reseller.registered
    (domain, registrant, admin_c)
VALUES
    (p_domain, p_registrant, p_admin_c);

domain_reseller.sel_handle

Selects handles

Parameters
  • p_hide_foreign bool
Variables defined for body
Returns
SETOF domain_reseller.”handle”
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
    SELECT * FROM domain_reseller.handle
WHERE
    owner=v_owner OR (owner="user"._login_user() AND NOT p_hide_foreign)
ORDER BY backend_status, fname, lname, alias;

domain_reseller.sel_registered

Selects details for registered domains

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.registrant,
        t.admin_c,
        t.tech_c,
        t.zone_c,
        t.payable,
        t.period,
        t.registrar_status,
        t.registry_status,
        t.last_status,
        s.backend_status
    FROM domain_reseller.registered AS t
    JOIN dns.registered AS s
        USING (domain)
    WHERE
        s.owner = v_owner
    ORDER BY backend_status, domain
;

domain_reseller.sel_reseller

Selects available resellers

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
    COALESCE(t.subservice, s.subservice) AS subservice,
    COALESCE(t.service_entity_name, s.service_entity_name) AS service_entity_name
FROM system._effective_contingent() AS t
FULL OUTER JOIN system._effective_contingent_domain() AS s
USING (service, subservice, service_entity_name, owner)
WHERE
    COALESCE(t.service, s.service) = 'domain_reseller' AND
    COALESCE(t.owner, s.owner) = v_owner

    ORDER BY subservice, service_entity_name
;

domain_reseller.srv_handle

Serves handles

Parameters
Returns
SETOF domain_reseller.”handle”
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM domain_reseller.handle AS t
        WHERE
            backend._machine_priviledged_service(t.service, t.service_entity_name) AND
            backend._deleted(t.backend_status)
    ),

    -- UPDATE
    s AS (
        UPDATE domain_reseller.handle AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged_service(t.service, t.service_entity_name) AND
            backend._active(t.backend_status)
    )

    SELECT * FROM domain_reseller.handle AS t
    WHERE
        backend._machine_priviledged_service(t.service, t.service_entity_name) AND
        (backend._active(t.backend_status) OR p_include_inactive);

domain_reseller.srv_registered

Serves details for registered domains

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


RETURN QUERY
    SELECT
        t.domain,
        t.registrant,
        (SELECT id FROM domain_reseller.handle WHERE alias = t.registrant),
        t.admin_c,
        (SELECT id FROM domain_reseller.handle WHERE alias = t.admin_c),
        t.tech_c,
        (SELECT id FROM domain_reseller.handle WHERE alias = t.tech_c),
        t.zone_c,
        (SELECT id FROM domain_reseller.handle WHERE alias = t.zone_c),
        s.backend_status
     FROM domain_reseller.registered AS t
    JOIN dns.registered AS s USING (domain)
    WHERE
    backend._machine_priviledged_service(s.service, s.service_entity_name) AND
    (backend._active(s.backend_status) OR p_include_inactive);

domain_reseller.upd_handle

Updates handle

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 domain_reseller.handle
    SET
        backend_status = 'upd',
        address = p_address,
        pcode = p_pcode,
        city = p_city,
        country = p_country,
        state = p_state,
        email = p_email,
        phone = p_phone,
        organization = p_organization,
        fax = p_fax,
        mobile_phone = p_mobile_phone

WHERE
    alias = p_alias AND
    owner = v_owner
RETURNING service_entity_name INTO v_service_entity_name;

PERFORM backend._conditional_notify_service_entity_name(
    FOUND, v_service_entity_name, 'domain_reseller', 'handle'
);

domain_reseller.upd_registered

Updates details for registered domain

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 domain_reseller.registered AS t
    SET
        admin_c = p_admin_c
FROM dns.registered AS s
WHERE
    s.domain = t.domain AND
    s.owner = v_owner AND

    t.domain = p_domain;

UPDATE dns.registered AS t
    SET backend_status = 'upd'
WHERE
    t.owner = v_owner AND
    t.domain = p_domain AND
    -- don't change domains that are in some transition status
    (t.backend_status = 'upd' OR t.backend_status IS NULL)
RETURNING t.service_entity_name, t.subservice
        INTO v_nameserver, v_managed;

PERFORM backend._conditional_notify_service_entity_name(
    FOUND, v_nameserver, 'domain_registered', v_managed
);

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

jabber

Jabber (XMPP)

This module sends the following signals:
  • jabber/account

Tables

jabber.account

Jabber accounts

Primary key
  • node
  • domain
Foreign keys
Columns

Functions

jabber.del_account

Delete jabber account

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 jabber.account
SET backend_status = 'del'
WHERE
    node = p_node AND
    domain = p_domain AND
    owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'jabber', 'account', p_domain);

jabber.ins_account

Insert jabber account

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


v_num_total := (SELECT COUNT(*) FROM jabber.account AS t WHERE t.owner=v_owner);
v_num_domain := (SELECT COUNT(*) FROM jabber.account AS t WHERE t.owner=v_owner AND t.domain = p_domain);

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

INSERT INTO jabber.account
    (service, subservice, node, domain, owner, password, service_entity_name) VALUES
    ('jabber', 'account', p_node, p_domain, v_owner, commons._hash_password(p_password),
    (SELECT service_entity_name FROM dns.service WHERE service='jabber' AND domain = p_domain));

PERFORM backend._notify_domain('jabber', 'account', p_domain);

jabber.sel_account

Select jabber accounts

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.node,
        t.domain,
        t.backend_status
    FROM jabber.account AS t
    WHERE
        t.owner = v_owner
    ORDER BY t.backend_status, t.node, t.domain;

jabber.srv_account

Lists all jabber accounts

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


RETURN QUERY
    WITH

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

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

    -- SELECT
    SELECT
        t.node,
        t.domain,
        t.password,
        t.backend_status
    FROM jabber.account AS t

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

jabber.upd_account

Change jabber account 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 jabber.account
    SET
        password = commons._hash_password(p_password)
WHERE
    node = p_node AND
    domain = p_domain AND
    owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'jabber', 'account', p_domain);

server_access

Server Access

Explicit passwd entries for shell acounts and sftp.

This module sends the following signals:
  • server_access/sftp
  • server_access/ssh

Tables

server_access.user

unix user

Primary key
  • user
Foreign keys
Columns
  • service_entity_name dns.t_hostname

    Service entity name

  • service commons.t_key

    Service (e.g. email, jabber)

  • 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'
  • owner user.t_user

    Owner

    References user.user.owner

    On Update: CASCADE

  • uid integer

    Unix user identifier

    Default
    nextval('commons.uid')
  • user server_access.t_user

    User

  • password NULL | commons.t_password

    Unix shadow crypt format

Functions

server_access.del_user

delete

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


BEGIN
    -- perform DELETE to trigger potential foreign key errors
    DELETE FROM server_access.user
    WHERE
        "user" = p_user AND
        service_entity_name = p_service_entity_name AND
        owner = v_owner;

    -- if not failed yet, emulate rollback of DELETE
    RAISE transaction_rollback;
EXCEPTION
    WHEN transaction_rollback THEN
        UPDATE server_access.user
            SET backend_status = 'del'
        WHERE
            "user" = p_user AND
            service_entity_name = p_service_entity_name AND
            owner = v_owner
        RETURNING subservice INTO v_subservice;

        PERFORM backend._conditional_notify_service_entity_name(
             FOUND,  p_service_entity_name, 'server_access', v_subservice
         );
END;

server_access.ins_user

ins user

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


IF p_password IS NULL THEN
    v_password := NULL;
ELSE
    v_password := commons._hash_password(p_password);
END IF;

INSERT INTO server_access.user
    (service, subservice, service_entity_name, "user", password, owner)
VALUES
    ('server_access', p_subservice, p_service_entity_name, p_user, v_password, v_owner);

PERFORM backend._notify_service_entity_name(p_service_entity_name, 'server_access', p_subservice);

server_access.sel_user

sel user

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.user,
        t.password IS NOT NULL,
        t.service,
        t.subservice,
        t.service_entity_name,
        t.backend_status
    FROM
        server_access.user AS t
    WHERE
        owner = v_owner
    ORDER BY backend_status, "user"
;

server_access.srv_user

backend server_access.user

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


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM server_access.user AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged_service(t.service, t.service_entity_name)
    ),

    -- UPDATE
    s AS (
        UPDATE server_access.user AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged_service(t.service, t.service_entity_name) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.user,
        t.password,
        t.service,
        t.subservice,
        t.service_entity_name,
        t.backend_status,
        t.uid
    FROM server_access.user AS t

    WHERE
        backend._machine_priviledged_service(t.service, t.service_entity_name) AND
        (backend._active(t.backend_status) OR p_include_inactive);

server_access.upd_user

passwd user

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


IF p_password IS NOT NULL THEN
    v_password := commons._hash_password(p_password);
END IF;

UPDATE server_access.user
SET
    password = v_password,
    backend_status = 'upd'
WHERE
    "user" = p_user AND
    service_entity_name = p_service_entity_name AND
    owner = v_owner
RETURNING subservice INTO v_subservice;

PERFORM backend._conditional_notify_service_entity_name(
    FOUND, p_service_entity_name, 'server_access', v_subservice
);

Domains

server_access.t_user

Unix user. This type only allows a subset of those names allowed by POSIX.

Checks

web

Websites

This module sends the following signals:
  • web/alias
  • web/site

Tables

web.alias

Aliases

Primary key
  • domain
  • site_port
Foreign keys
Columns

web.https

stores https information

Primary key
  • identifier
  • domain
  • port
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'
  • identifier commons.t_key

    PK

  • domain dns.t_hostname

    Domain

  • port commons.t_port

    Port

  • x509_request NULL | web.t_cert

    Certificate request

  • x509_certificate NULL | web.t_cert

    Certificate

  • authority_key_identifier NULL | varchar

    Identifier of the certificate that has signed this cert. The Authority Key Identifier allows to build the chain of trust. See <http://www.ietf.org/rfc/rfc3280.txt>. Hopefully there exists an entry in web.intermediate_cert or a root certificate with an equal subjectKeyIdentifier.

    Is NULL whenever x509_certificate is NULL.

web.intermediate_cert

Intermediate certificates

Primary key
  • subject_key_identifier
Columns
  • subject_key_identifier varchar
    Identifies this certificate
  • authority_key_identifier varchar
    Subject key identifier of the cert that has signed this cert. NULL is not allowed, since self signed cert do not belong into intermediate certs.
  • x509_certificate web.t_cert
    Intermediate certificate

web.intermediate_chain

xxx

Primary key
  • domain
  • port
  • identifier
  • subject_key_identifier
Foreign keys
Columns

web.site

Website

Primary key
  • domain
  • port
Foreign keys
Columns

Functions

web.del_alias

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 web.alias AS t
    SET backend_status = 'del'
FROM web.site AS s, server_access.user AS u
WHERE
    -- JOIN web.site
    s.domain = t.site AND

    -- JOIN server_access.user
    u.service_entity_name = t.service_entity_name AND
    u.user = s.user AND

    u.owner = v_owner AND
    t.domain = p_domain AND
    t.site_port = p_site_port;

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

web.del_intermediate_chain

sdf

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 web.intermediate_chain
    WHERE
        domain = p_domain AND
        port = p_port AND
        identifier = p_identifier;

web.del_site

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 web.site AS t
    SET backend_status = 'del'
FROM server_access.user AS s
WHERE
    -- JOIN server_access.user
    s.user = t.user AND
    s.service_entity_name = t.service_entity_name AND

    t.domain = p_domain AND
    t.port = p_port AND
    s.owner = v_owner;

PERFORM backend._conditional_notify(FOUND, 'web', 'site', p_domain);

web.fwd_x509_request

x509 request

Parameters
Returns
void
Execute privilege
PERFORM backend._get_login();


UPDATE web.https
    SET x509_request = p_x509_request
WHERE
    domain = p_domain AND
    port = p_port AND
    identifier = p_identifier;

web.ins_alias

Insert 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


PERFORM commons._raise_inaccessible_or_missing(
    EXISTS(
        SELECT TRUE FROM web.site AS t
        JOIN server_access.user AS s
            USING ("user", service_entity_name)
        WHERE
            t.domain = p_site AND
            t.port = p_site_port AND
            s.owner = v_owner
    )
);

INSERT INTO web.alias
    (domain, service, subservice, site, site_port, service_entity_name)
VALUES
    (
        p_domain,
        'web',
        'alias',
        p_site,
        p_site_port,
        (SELECT service_entity_name FROM web.site WHERE domain = p_site AND port = p_site_port)
    );

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

web.ins_https

Create new HTTPS certificate

Todo

Fix missing owner verification (not critical)

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


INSERT INTO web.https
    (domain, port, identifier)
    VALUES
    (p_domain, p_port, p_identifier);

PERFORM backend._notify_domain('web', 'site', p_domain);

web.ins_intermediate_cert

Xxx

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


INSERT INTO web.intermediate_cert
    (subject_key_identifier, authority_key_identifier, x509_certificate)
    VALUES
    (p_subject_key_identifier, p_authority_key_identifier, p_x509_certificate);

web.ins_intermediate_chain

sdf

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


INSERT INTO web.intermediate_chain
    (domain, port, identifier, "order", subject_key_identifier)
    VALUES
    (p_domain, p_port, p_identifier, p_order, p_subject_key_identifier);

web.ins_site

Insert site

Todo

check owner and contingent

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 system._contingent_ensure(
    p_owner:=v_owner,
    p_domain:=p_domain,
    p_service:='web',
    p_subservice:='site',
    p_current_quantity_total:=
        (SELECT COUNT(*) FROM web.site WHERE owner=v_owner)::int,
    p_current_quantity_domain:=
        (SELECT COUNT(*) FROM web.site WHERE owner=v_owner AND domain = p_domain)::int
    );

INSERT INTO web.site
    (domain, service, subservice, port, "user", service_entity_name, owner)
    VALUES
    (p_domain, 'web', 'site', p_port, p_user, p_service_entity_name, v_owner);

    PERFORM backend._notify_domain('web', 'site', p_domain);

web.sel_alias

Select alias

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.site,
        t.site_port,
        t.backend_status
    FROM web.alias AS t

    JOIN web.site AS u
        ON
            u.domain = t.site AND
            u.port = t.site_port

    JOIN server_access.user AS s
        ON
            u.user = s.user AND
            s.service_entity_name = t.service_entity_name

    WHERE s.owner = v_owner
    ORDER BY t.backend_status, t.domain;

web.sel_https

sel https

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.identifier,
        t.domain,
        t.port,
        t.x509_request,
        t.x509_certificate,
        t.authority_key_identifier,
        t.backend_status
    FROM web.https AS t
    ORDER BY t.backend_status, t.identifier;

web.sel_intermediate_cert

int

Parameters
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.subject_key_identifier,
        t.authority_key_identifier,
        t.x509_certificate
    FROM web.intermediate_cert AS t
    WHERE
        t.subject_key_identifier = p_subject_key_identifier;

web.sel_intermediate_chain

sel

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.port,
        t.identifier,
        t.subject_key_identifier,
        s.x509_certificate,
        t.order
    FROM web.intermediate_chain AS t
    JOIN web.intermediate_cert AS s
        USING (subject_key_identifier)
    ORDER BY t.order;

web.sel_site

Owner defined via server_access

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.service,
        t.subservice,
        t.domain,
        t.port,
        t.user,
        t.service_entity_name,
        t.https,
        t.backend_status,
        t.option
    FROM web.site AS t
    JOIN server_access.user AS s
        USING ("user", service_entity_name)
    WHERE
        s.owner = v_owner
    ORDER BY t.backend_status, t.domain, t.port;

web.srv_alias

backend web.alias

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


RETURN QUERY
    WITH

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

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

    -- SELECT
    SELECT
        t.domain,
        t.site,
        t.site_port,
        t.backend_status
    FROM web.alias AS t

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

web.srv_https

Certs

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


RETURN QUERY
    WITH

    -- NO DELETE OPTION

    -- UPDATE
    s AS (
        UPDATE web.https AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged('web', t.domain) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.identifier,
        t.domain,
        t.port,
        t.x509_request,
        t.x509_certificate,
        ARRAY(
            SELECT s.x509_certificate::varchar
            FROM web.intermediate_chain AS u
            JOIN web.intermediate_cert AS s
                USING (subject_key_identifier)
            WHERE
                u.domain = t.domain AND
                u.port = t.port AND
                u.identifier = t.identifier
            ORDER by "order"
        ),
        t.backend_status
    FROM web.https AS t

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

web.srv_site

backend web.site

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


RETURN QUERY
    WITH

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

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

    -- SELECT
    SELECT
        t.domain,
        t.port,
        t.user,
        t.service_entity_name,
        t.https,
        t.subservice,
        t.option,
        t.backend_status
    FROM web.site AS t

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

web.upd_https

upd https

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 web.https
    SET
        x509_certificate = p_x509_certificate,
        authority_key_identifier = p_authority_key_identifier
WHERE
    domain = p_domain AND
    port = p_port AND
    identifier = p_identifier;

PERFORM backend._conditional_notify(FOUND, 'web', 'site', p_domain);

web.upd_site

set https identif.

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 web.site AS s
    SET https = p_identifier
WHERE
    s.owner = v_owner AND
    s.domain = p_domain AND
    s.port = p_port;

PERFORM backend._conditional_notify(FOUND, 'web', 'site', p_domain);

Domains

web.t_cert

PEM cert

Checks
  • base64

    no newlines in db

    VALUE ~ '^[a-zA-Z\d/+]+[=]{0,2}$'

backend

Carnivora Backend

The backend module provides everything required for the backend API. The backend API delivers content required for building configs etc. to clients, called machines.

Tables

backend.auth

Grants rights to backend API clients based on SQL roles.

Primary key
  • role
Columns
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • role commons.t_key

    Grantee for right to access the backend date for the defined machine. A role is basically a user or a user group on the SQL server.

  • machine dns.t_hostname

    Machine for which the rights are granted.

    References backend.machine.name

    On Delete: CASCADE

backend.machine

Physical or virtual machines that hosts services.

Primary key
  • name
Columns

Functions

backend._active

Is not ‘del’

Parameters
Returns
boolean
RETURN backend_status IS NULL OR (backend_status <> 'del' AND backend_status <> 'old');

backend._conditional_notify

Notifies if first argument is true. Throws inaccessible otherwise.

Parameters
Returns
void
IF p_condition THEN
    PERFORM backend._notify_domain(p_service, p_subservice, p_domain);
ELSE
    PERFORM commons._raise_inaccessible_or_missing();
END IF;

backend._conditional_notify_service_entity_name

Notifies if first argument is true. Throws inaccessible otherwise.

Parameters
Returns
void
IF p_condition THEN
    PERFORM backend._notify_service_entity_name(p_service_entity_name, p_service, p_subservice);
ELSE
    PERFORM commons._raise_inaccessible_or_missing();
END IF;

backend._deleted

Is ‘del’

Parameters
Returns
boolean
RETURN backend_status IS NOT NULL AND backend_status = 'del';

backend._get_login

Shows informations for the current backend login. Throws an error if the current user is not a grantee for a machine.

Parameters
None
Returns
TABLE
Returned columns
IF (SELECT TRUE FROM "backend"."auth"
   WHERE "role"=session_user)
THEN
   RETURN QUERY SELECT backend.auth.machine FROM backend.auth
       WHERE "role"=session_user;
ELSE
   RAISE 'Connected role `%` is not a grantee for a machine.', session_user;
END IF;

backend._login_machine

Shows machine for the current backend login.

Parameters
None
Returns
dns.t_hostname
RETURN (SELECT machine FROM backend._get_login());

backend._machine_priviledged

Checks if a currently connected machine is priviledged to obtain data for a certain service for a certain domain name.

Warning

The parameter p_domain must be a domain, which means an entry in the column dns.service.domain. It must not be confused with a service_entity_name.

Parameters
Returns
boolean
RETURN COALESCE(
    (
    SELECT TRUE FROM system.service_entity_machine AS t
        JOIN dns.service AS s
        ON
            s.service = p_service AND
            s.domain = p_domain

        WHERE
            t.service = p_service AND
            t.service_entity_name = s.service_entity_name AND
            t.machine_name = backend._login_machine()
    )
, FALSE);

backend._machine_priviledged_service

Checks if a currently connected machine is priviledged to obtain data for a certain service for a certain servicee name.

Warning

The parameter p_service_entity_name must be the name of a service entity. It must not be confused with a domain.

Parameters
Returns
boolean
RETURN COALESCE(
    (
    SELECT TRUE FROM system.service_entity_machine AS t
        WHERE
            t.service = p_service AND
            t.service_entity_name = p_service_entity_name AND
            t.machine_name = backend._login_machine()
    )
, FALSE);

backend._notify

Informs a machine about changes. To listen to signals use

LISTEN "carnivora/machine.name.example"

on the machine. The payload has the form <service_entity_name>/<service>/<subservice>. For example mail.domain.example/email/mailbox for a mailbox related update.

Parameters
Returns
void
PERFORM
    pg_notify(
        'carnivora/' || p_machine,
         p_service_entity_name || '/' || p_service || '/' || p_subservice
        );

backend._notify_domain

Informs all machines about changes.

Warning

The parameter p_domain must be a domain, which means an entry in the column dns.service.domain. It must not be confused with a service_entity_name.

Parameters
Returns
void
PERFORM
    backend._notify(machine_name, s.service_entity_name, p_service, p_subservice)

FROM system.service_entity_machine AS t
    JOIN dns.service AS s
    ON
        s.service = p_service AND
        s.domain = p_domain

    WHERE
        t.service = p_service AND
        t.service_entity_name = s.service_entity_name
;

backend._notify_service_entity_name

Informs all machines about changes.

Warning

The parameter p_service_entity_name must be a servcie name. It must not be confused with a domain.

Parameters
Returns
void
PERFORM
    backend._notify(machine_name, p_service_entity_name, p_service, p_subservice)

FROM system.service_entity_machine AS t
    WHERE
        t.service = p_service AND
        t.service_entity_name = p_service_entity_name
;

Domains

backend.t_status

Backend status

Roles

backend

vms

Login
Disabled

commons

Carnivora Commons

Usefull templates, functions and domains.

Functions

commons._hash_password

SHA512 hash of the password with 16 charcters random salt. The returned format is the traditional ‘crypt(3)’ format.

Parameters
Language
plpython3u
Returns
commons.t_password
import crypt

return crypt.crypt(p_password, crypt.METHOD_SHA512)

commons._idn

Converts a unicode domain name to IDN (ASCII)

Currently using IDNA2003.

Parameters
Language
plpython3u
Returns
varchar
Execute privilege
if p_domain is None:
   return None

if p_domain.lower() != p_domain:
    raise plpy.Error('Only lower case IDNs are allowed and can be handled.')

return p_domain.encode('idna').decode()

commons._jsonb_to_array

Converts a JSONB array to a PostgreSQL text[] array

Parameters
Returns
text[]
RETURN ARRAY(SELECT jsonb_array_elements_text(p_jsonb));

commons._passwords_equal

Compares a plaintext password with an arbitrary ‘crypt(3)’ hashed password.

Uses <https://docs.python.org/3/library/hmac.html>

Parameters
Language
plpython3u
Returns
boolean
import crypt
from hmac import compare_digest as compare_hash

# Giving crypt.crypt the full hash as second argument fixes the use of the
# right salt and algorithm. Using compare_hash to avoid timing attacks.
return compare_hash(crypt.crypt(p_password_plaintext, p_password_hash), p_password_hash)

commons._raise_inaccessible_or_missing

Raised whenever a operation on an object failes because it is not owned by the user or it is not found.

Parameters

  • p_raise boolean

    Controls if the exception is raised

Returns
void
IF NOT COALESCE(p_raise, FALSE) THEN
    RAISE 'Object inaccessible or missing'
        USING DETAIL = '$carnivora:commons:inaccessible_or_missing$';
END IF;

commons._reverse_array

Copied from <https://wiki.postgresql.org/wiki/Array_reverse>

Parameters
Language
sql
Returns
anyarray
Execute privilege
SELECT
    ARRAY(
        SELECT $1[i]
        FROM generate_subscripts($1,1) AS s(i)
        ORDER BY i DESC
    );

commons._uuid

Returns a random uuid

Parameters
None
Returns
uuid
RETURN public.uuid_generate_v4();

Domains

commons.t_port

Port

Checks
  • invalid_port

    Only allow port values

    VALUE >= 0 AND VALUE <= 65535

commons.t_password

unix hash thingy

Todo

propper checking of format

Checks
  • crypt(3) password format

    Only allows SHA512 strings.

    VALUE ~ '^\$6\$[.\/a-zA-Z0-9]{8,16}\$[.\/a-zA-Z0-9]{86}$'

commons.t_password_plaintext

Password in plaintext

Checks
  • minimum password length 8

    Ensures that passwords at least have 8 chars

    character_length(VALUE) >= 8

commons.t_hexvarchar

Varchar only with HEX values

Checks
  • invalid characters

    Only allows numbers and chars a-f for hex representation

    VALUE ~ '^[0-9a-f]*$'

Sequences

commons.uid

Unix user id

system

Carnivora System

Manages services, service entities and contingents.

Tables

system.inherit_contingent

Contingents inherited from other users.

Precedence is unambiguous via primary key.

Primary key
  • owner
  • priority
Columns

system.service

Services

Just a list of services that exist. Modules do register their services here. Use system._setup_register_service(<module>, <service>) to insert into this table.

Primary key
  • service
Columns
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • service commons.t_key

    Service name

  • module commons.t_key

    Module name, just to keep track who uses this name

system.service_entity

Service Entity

Names under which services are made available. For example (mail.example.org, email) could be a mail-server system referred to as mail.example.org by carnivora. Such a system can consist of multiple physical or virtual machines. The corresponding machines are listed in system.service_entity_machine. A core feature of services is the definition of ‘templates’ for dns records which have to be present for every domain that uses this service. Such ‘templates’ can be defined in system.service_dns. Domain names can be enabled for services in dns.service. Service enabled domains are automatically equipped with the required dns entries accorting to the existing ‘templates’.

The service_entity_name might be exposed to users as the address of this service. For example as SMTP or SSH server etc. The exact interpretation of the service_entity_name depends on the module and the frontend.

Primary key
  • service_entity_name
  • service
Columns

system.service_entity_dns

Service Entity DNS

Resource records that have to be present to use a service. The records in this table can be understood as ‘templates’. The table does not contain a name (domain) for the records. Rather for every domain that uses this service, all appropriate records are created for this domain based on this table. The assignment from domain to services can be found in dns.service.

Primary key
  • id
Foreign keys
Columns
  • service_entity_name dns.t_hostname

    Service entity name

  • service commons.t_key

    Service (e.g. email, jabber)

  • type dns.t_type

    Type (A, AAAA, CNAME, MX, SRV, TXT, …)

  • rdata dns.t_rdata

    fancy rdata storage

  • ttl NULL | dns.t_ttl

    Time to live, NULL indicates default value

  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • id uuid

    uuid serial number to identify database elements uniquely

    Default
    commons._uuid()
  • domain_prefix NULL | varchar

    Domain prefix

system.service_entity_machine

Service Entity Machine

List of machines that provice a certain service. This information is used to provide these machines access to the data they need to provide the service. See also the module ‘backend’.

Primary key
  • machine_name
  • service_entity_name
  • service
Foreign keys
Columns

system.subservice

Subservices

Primary key
  • service
  • subservice
Columns

system.subservice_entity

Subservice Entity

Names under which subservices are made available.

See also: Table system.service_entity

Primary key
  • service_entity_name
  • service
  • subservice
Foreign keys
Columns

system.subservice_entity_contingent

Subservice entity contingent

Primary key
  • service
  • subservice
  • service_entity_name
  • owner
Foreign keys
Columns

system.subservice_entity_domain_contingent

Subservice entity per domain contingent

Primary key
  • service
  • subservice
  • service_entity_name
  • domain
  • owner
Foreign keys
Columns

Functions

system._contingent_ensure

Throws exceptions if the contingent is exceeded

Parameters
Variables defined for body
Returns
void
IF p_owner IS NULL
THEN
    RAISE 'Owner argument must not be NULL.';
END IF;

SELECT
    t.service_entity_name,
    s.owner
INTO
    v_service_entity_name,
    v_domain_owner
FROM dns.service AS t
JOIN dns.registered AS s
    ON s.domain = t.registered

WHERE
    t.domain = p_domain AND
    t.service = p_service;

-- check dns.service entry
IF v_domain_owner IS NULL
THEN
    RAISE 'Contingent check impossible, since dns.service entry missing.'
        USING
            DETAIL = '$carnivora:system:no_contingent$',
            HINT = (p_owner, p_service, p_domain);
END IF;

SELECT domain_contingent, total_contingent
    INTO v_domain_contingent_default, v_total_contingent
FROM system._effective_contingent()
WHERE
    service = p_service AND
    subservice = p_subservice AND
    service_entity_name = v_service_entity_name AND
    owner = p_owner
;

SELECT domain_contingent
    INTO v_domain_contingent_specific
FROM system._effective_contingent_domain()
WHERE
    service = p_service AND
    subservice = p_subservice AND
    service_entity_name = v_service_entity_name AND
    owner = p_owner
;

v_domain_contingent :=
    COALESCE(v_domain_contingent_default, v_domain_contingent_specific);

IF
    v_total_contingent IS NULL AND
    v_domain_contingent IS NULL
THEN
    RAISE 'You do no have a contingent'
        USING
            DETAIL = '$carnivora:system:no_contingent$',
            HINT = (p_owner, p_service, v_service_entity_name);
END IF;

IF v_domain_contingent IS NULL AND p_owner <> v_domain_owner
THEN
    RAISE 'You are not the owner of the registered domain'
        USING
            DETAIL = '$carnivora:system:contingent_not_owner$',
            HINT = (p_owner, p_service, v_service_entity_name);
END IF;

IF v_total_contingent <= p_current_quantity_total
THEN
    RAISE 'Total contingent exceeded'
        USING
            DETAIL = '$carnivora:system:contingent_total_exceeded$',
            HINT = (p_owner, p_service, p_domain, v_total_contingent);
END IF;

IF v_domain_contingent <= p_current_quantity_domain
THEN
    RAISE 'Domain contingent exceeded'
        USING
            DETAIL = '$carnivora:system:contingent_domain_exceeded$',
            HINT = (p_owner, p_service, p_domain, v_domain_contingent);
END IF;

system._effective_contingent

contingent

Parameters
None
Returns
TABLE
Returned columns
RETURN QUERY
 SELECT
  DISTINCT ON
  (contingent.service, contingent.subservice, contingent.service_entity_name, usr.owner)
  contingent.service,
  contingent.subservice,
  contingent.service_entity_name,
  usr.owner,
  contingent.domain_contingent,
  contingent.total_contingent
 FROM system.subservice_entity_contingent AS contingent

 CROSS JOIN "user"."user" AS usr

 JOIN system._inherit_contingent_donor(usr.owner) AS des
   ON des.donor = contingent.owner

 ORDER BY
  contingent.service,
  contingent.subservice,
  contingent.service_entity_name,
  usr.owner,
  des.priority_list DESC;

system._effective_contingent_domain

contingent

Parameters
None
Returns
TABLE
Returned columns
RETURN QUERY
  SELECT
   DISTINCT ON
   (contingent.service, contingent.subservice, contingent.service_entity_name, contingent.domain, usr.owner)
   contingent.service,
   contingent.subservice,
   contingent.service_entity_name,
   contingent.domain,
   usr.owner,
   contingent.domain_contingent
  FROM system.subservice_entity_domain_contingent AS contingent

  CROSS JOIN "user"."user" AS usr

  JOIN system._inherit_contingent_donor(usr.owner) AS des
    ON des.donor = contingent.owner

  ORDER BY
   contingent.service,
   contingent.subservice,
   contingent.service_entity_name,
   contingent.domain,
   usr.owner,
   des.priority_list DESC;

system._inherit_contingent_donor

Returns all contingent donors for a given user with their priority.

Parameters
Returns
TABLE
Returned columns
RETURN QUERY
WITH RECURSIVE contingent_donor(donor, priority_list, cycle_detector) AS
(
   -- cast to varchar, since arrays of t_user are not defined
   SELECT p_owner, ARRAY[]::integer[], ARRAY[CAST(p_owner AS varchar)]

   UNION

   SELECT
    curr.donor,
    prev.priority_list || curr.priority,
    cycle_detector || CAST(curr.donor AS varchar)
   FROM system.inherit_contingent AS curr
    JOIN contingent_donor AS prev
    ON
     prev.donor = curr.owner AND
     curr.donor <> ALL (prev.cycle_detector)
)
SELECT
 contingent_donor.donor,
 array_append(contingent_donor.priority_list, NULL)
FROM contingent_donor
-- Appending the NULL changes the ordering between arrays with different size
ORDER BY array_append(contingent_donor.priority_list, NULL) DESC;

system._setup_register_service

Allows modules to register their services during setup. Returns the total number of service names registered for this module.

Parameters
Returns
void
INSERT INTO system.service
 (module, service)
 SELECT p_module, p_service
  WHERE NOT EXISTS (
   SELECT service FROM system.service
    WHERE module=p_module AND service=p_service
   );

system._setup_register_subservice

Allows modules to register their services during setup. Returns the total number of service names registered for this module.

Parameters
Returns
void
INSERT INTO system.subservice
 (service, subservice)
 SELECT p_service, p_subservice
  WHERE NOT EXISTS (
   SELECT service FROM system.subservice
    WHERE service=p_service AND subservice=p_subservice
   );

system.sel_inherit_contingent

Select inherit contingent

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.owner, t.donor, t.priority
    FROM system.inherit_contingent AS t
    ORDER BY t.owner, t.priority;

system.sel_usable_host

Usable hosts

Parameters
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.subservice, t.service_entity_name FROM system._effective_contingent() AS t
        WHERE
            owner = v_owner AND
            t.service = p_service AND
            t.total_contingent > 0
        ORDER BY
            t.service_entity_name
    ;

user

Carnivora Users: Users own things objects in the DB, and they can login into frontends (edentata)

Tables

user.deputy

Deputies for users

Primary key
  • deputy
  • represented
Columns

user.session

User login sessions

Primary key
  • id
Columns

user.user

Users

Users with password set to NULL can be used as groups.

Primary key
  • owner
Columns
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • owner user.t_user

    User name, login name

  • password NULL | commons.t_password

    Unix shadow crypt format, NULL value disables login

  • contact_email NULL | email.t_address

    Optional contact email address, can be used as login name

Functions

user._get_login

Shows informations for the current user login. Throws an exception if no login is associated to the current database connection.

Parameters
None
Returns
TABLE
Returned columns
IF (SELECT TRUE FROM "user"."session"
   WHERE "id"="user"._session_id())
THEN
   RETURN QUERY SELECT t.owner, t.act_as FROM "user"."session" AS t
       WHERE "id"="user"._session_id();
ELSE
   RAISE 'Database connection is not associated to a user login.'
       USING HINT := 'Use user.ins_login(...) first.';
END IF;

user._login_user

Shows informations for the current user login. Throws an exception if no login is associated to the current database connection.

Parameters
None
Returns
user.t_user
RETURN (SELECT owner FROM "user"._get_login());

user._session_id

Gives an id for the database connection that is unique over all database connections. It is used to identify user logins.

Not sure if this stays unique with distributed infrastructure!

Parameters
None
Returns
varchar
RETURN
    pg_backend_pid() || '.' ||
    COALESCE((SELECT backend_start FROM pg_stat_get_activity(pg_backend_pid()))::varchar, 'xxx') || '.' ||
    pg_conf_load_time();

user.del_login

Try to logout

Parameters
None
Returns
void
Execute privilege
DELETE FROM "user".session WHERE id = "user"._session_id();

IF NOT FOUND THEN
   RAISE 'Carnivora: user logout failed, not logged in'
    USING DETAIL = '$carnivora:user:logout_failed$';
END IF;

user.ins_deputy

Act as deputy

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 "user".session AS t
    SET act_as = p_act_as
    FROM "user".deputy AS s
    WHERE
        s.deputy = t.owner AND
        s.represented = p_act_as AND
        t.id = "user"._session_id() AND
        t.owner = v_owner;

IF NOT FOUND THEN
    RAISE 'Acting as deputy failed.'
        USING DETAIL := '$carnivora:user:deputy_failed$';
END IF;

user.ins_login

Try to bind database connection to new user session.

Parameters
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
SELECT owner INTO v_login_owner FROM "user"."user" AS t
       WHERE
           p_login IS NOT NULL AND
           t.password IS NOT NULL AND
           lower(p_login) IN (owner, contact_email) AND
           commons._passwords_equal(p_password, t.password);

IF v_login_owner IS NOT NULL THEN
   INSERT INTO "user"."session" (owner, act_as) VALUES (v_login_owner, v_login_owner);
   RETURN QUERY SELECT v_login_owner;
ELSE
   RAISE 'Carnivora: invalid user login'
    USING DETAIL = '$carnivora:user:login_invalid$';
END IF;

user.sel_deputy

sel deputy

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.represented FROM "user".deputy AS t
    WHERE t.deputy = "user"._login_user()
    ORDER BY t.represented;

user.upd_user

change user passwd

Parameters
Returns
void
UPDATE "user".user
    SET password = commons._hash_password(p_password)

WHERE
    owner = "user"._login_user();

Domains

user.t_user

Username

Checks
  • valid_characters

    Only lower-case letters, numbers and .-_

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

Roles

userlogin

Do user actions via this group

Login
Disabled

system

Highly priviledged user

Login
Disabled

YamSql

Types

serial

Managed via HamSql