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)::dns.t_domain,
        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 BETWEEN 60 AND EXTRACT(EPOCH FROM INTERVAL '2 days')

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'
)