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
    ;