PowerDNS, psql – prosto i przyjemnie.

Posted on by

Strukturę deko zmieniłem, bo ta dostępna w examples jest do bani.

Wygląda to tak:

parenaście razy wywalałem baze (szybciej) bo nie wychodziło jak miało
wychodzić ;) do tego prosty zbiór komend na użytkowniku postgres:

dropdb   masterdns  #kasyjemy baze
createdb masterdns  #tworzymy baze
createlang plpgsql masterdns #dodajemy obsługę perl'a dla bazy

ostatnia wymagała doinstalowania bibliotek ;)

Struktura bazy:

CREATE TABLE dns_zones (
  id                 SERIAL8 PRIMARY KEY,
  origin             VARCHAR(255) UNIQUE NOT NULL,
  ns                 VARCHAR(255) NOT NULL,
  mbox               VARCHAR(255) NOT NULL,
  serial             INT8 NOT NULL DEFAULT '1',
  refresh            INT4 NOT NULL DEFAULT '10800',  -- 3 hours
  retry              INT4 NOT NULL DEFAULT '3600',   -- 1 hour
  expire             INT4 NOT NULL DEFAULT '604800', -- 1 week
  ttl                INT4 NOT NULL DEFAULT '3600');  -- 1 hour

CREATE TABLE dns_resource_types (
  id                 SERIAL8 PRIMARY KEY,
  name               VARCHAR(255) NOT NULL,
  description        TEXT
);
INSERT INTO dns_resource_types (name, description) VALUES
    ('A', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('AAAA', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('CNAME', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('HINFO', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('MX', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('NS', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('PTR', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('SRV', '');
INSERT INTO dns_resource_types (name, description) VALUES
    ('TXT', '');

CREATE TABLE dns_resources (
  id                 SERIAL8 PRIMARY KEY,
  dns_zone_id        INT8 NOT NULL REFERENCES dns_zones ON DELETE CASCADE ON UPDATE CASCADE,
  dns_resource_type_id INT8 NOT NULL REFERENCES dns_resource_types,
  name               VARCHAR(64) NOT NULL,
  DATA               VARCHAR(255) NOT NULL,
  aux                INT4 NULL DEFAULT NULL,
  ttl                INT4 NULL DEFAULT NULL);

Należy stworzyć użytkownika pdns, nadać prawa etc..

-- Create pdns role

CREATE SCHEMA pdns AUTHORIZATION pdns;
SET search_path TO pdns,public;

-- Pole możemy oczywiście dowolnie zmieniać i modyfikować:

CREATE TYPE record_type AS ENUM ('A', 'AAAA', 'AFSDB', 'CERT', 'CNAME',
    'DNSKEY', 'DS', 'HINFO', 'KEY', 'LOC', 'MX', 'NAPTR', 'NS', 'NSEC', 'PTR',
    'RP', 'RRSIG', 'SOA', 'SPF', 'SSHFP', 'SRV', 'TXT');

CREATE TYPE domain_type AS ENUM ('NATIVE', 'MASTER', 'SLAVE',
    'SUPERSLAVE');

CREATE TABLE domains (
  id                 INT8 PRIMARY KEY,
  name               VARCHAR(255) UNIQUE NOT NULL,
  master             VARCHAR(255) DEFAULT NULL,
  last_check         INT DEFAULT NULL,
  TYPE               DOMAIN_TYPE NOT NULL,
  notified_serial    INT DEFAULT NULL,
  account            VARCHAR(40) DEFAULT NULL
);

CREATE TABLE records (
  id                 SERIAL8 PRIMARY KEY,
  domain_id          INT8 NOT NULL REFERENCES domains ON DELETE CASCADE,
  name               VARCHAR(255) NOT NULL,
  TYPE               RECORD_TYPE NOT NULL,
  content            VARCHAR(255) NOT NULL,
  ttl                INT DEFAULT NULL,
  prio               INT DEFAULT NULL,
  change_date        INT DEFAULT NULL,
  ref_id             INT8 DEFAULT NULL
);
CREATE INDEX records_name_index ON records (name);
CREATE INDEX records_name_type_index ON records (name, TYPE);
CREATE INDEX records_domain_id_index ON records (domain_id);

SET search_path TO "$user",public;

GRANT ALL PRIVILEGES ON pdns.domains TO pdns;
GRANT ALL PRIVILEGES ON pdns.records TO pdns;

A teraz najtrudniejsza sprawa, czyli zdefiniowanie logiki dla danych

CREATE OR REPLACE FUNCTION concat_hosts_func(text, text) RETURNS text AS $$
  DECLARE
  BEGIN
    IF ($1 = '' OR $1 IS NULL) THEN
      RETURN $2;
    END IF;
    RETURN ($1 || '.' || $2);
  END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION dns_zones_update_increment_serial_func () RETURNS TRIGGER AS $$
  DECLARE
  BEGIN
    IF (NEW.serial <= OLD.serial) THEN
      NEW.serial := OLD.serial + 1;
    END IF;
    RETURN NEW;
  END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dns_resources_increment_dns_zones_serial_func () RETURNS TRIGGER AS $$
  DECLARE
  BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      UPDATE dns_zones SET serial=serial+1 WHERE id=NEW.dns_zone_id;
      RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE dns_zones SET serial=serial+1 WHERE id=OLD.dns_zone_id;
      RETURN OLD;
    END IF;
    RETURN NULL;
  END
$$ LANGUAGE plpgsql;

CREATE TRIGGER dns_zones_update_increment_serial_trigger
  BEFORE UPDATE ON dns_zones
  FOR EACH ROW EXECUTE PROCEDURE
    dns_zones_update_increment_serial_func();

CREATE TRIGGER dns_resources_increment_dns_zones_serial_trigger
  AFTER INSERT OR UPDATE OR DELETE ON dns_resources
  FOR EACH ROW EXECUTE PROCEDURE
    dns_resources_increment_dns_zones_serial_func();

CREATE OR REPLACE FUNCTION dns_zones_powerdns_func () RETURNS TRIGGER AS $$
  DECLARE
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      INSERT INTO pdns.domains (id, name, TYPE) VALUES (NEW.id, NEW.origin, 'MASTER');
      INSERT INTO pdns.records (domain_id, name, TYPE, content, ttl) VALUES (NEW.id, NEW.origin, 'SOA',
        (NEW.ns || ' ' || NEW.mbox || ' ' || NEW.serial || ' ' || NEW.refresh || ' ' ||
        NEW.retry || ' ' || NEW.expire || ' ' || NEW.ttl), NEW.ttl);
      RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
      IF (NEW.origin <> OLD.origin) THEN
        UPDATE pdns.domains SET name=NEW.origin WHERE id=NEW.id;
        UPDATE pdns.records SET name=NEW.origin WHERE domain_id=NEW.id AND TYPE='SOA';
      END IF;

      IF (NEW.ns <> OLD.ns OR
          NEW.mbox <> OLD.mbox OR
          NEW.serial <> OLD.serial OR
          NEW.refresh <> OLD.refresh OR
          NEW.retry <> OLD.retry OR
          NEW.expire <> OLD.expire OR
          NEW.ttl <> OLD.ttl) THEN
        UPDATE pdns.records SET content=(NEW.ns || ' ' ||
            NEW.mbox || ' ' || NEW.serial || ' ' || NEW.refresh || ' ' ||
            NEW.retry || ' ' || NEW.expire || ' ' || NEW.ttl),
            ttl=NEW.ttl
          WHERE domain_id=NEW.id AND TYPE='SOA';
      END IF;
      RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM pdns.domains WHERE id=OLD.id;
      RETURN OLD;
    END IF;
    RETURN NULL;
  END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dns_resources_powerdns_func () RETURNS TRIGGER AS $$
  DECLARE
    origin_l VARCHAR(255);
    type_l pdns.RECORD_TYPE;
    ttl_l INT4;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      DELETE FROM pdns.records WHERE ref_id=OLD.id;
      RETURN OLD;
    END IF;

    IF (NEW.ttl IS NULL) THEN
      SELECT ttl INTO ttl_l FROM dns_zones WHERE id=NEW.dns_zone_id;
    ELSE
      ttl_l := NEW.ttl;
    END IF;

    IF (TG_OP = 'INSERT') THEN
      SELECT origin INTO origin_l FROM dns_zones WHERE id=NEW.dns_zone_id;
      SELECT name INTO type_l FROM dns_resource_types WHERE id=NEW.dns_resource_type_id;
      INSERT INTO pdns.records (domain_id, name, TYPE, content, ttl, prio, ref_id) VALUES
        (NEW.dns_zone_id, concat_hosts_func(NEW.name, origin_l), type_l,
         NEW.data, ttl_l, NEW.aux, NEW.id);
      RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
      SELECT origin INTO origin_l FROM dns_zones WHERE id=NEW.dns_zone_id;
      SELECT name INTO type_l FROM dns_resource_types WHERE id=NEW.dns_resource_type_id;
      UPDATE pdns.records SET domain_id=NEW.dns_zone_id,
        name=concat_hosts_func(NEW.name, origin_l), TYPE=type_l,
        content=NEW.data, ttl=ttl_l, prio=NEW.aux WHERE ref_id=NEW.id;
      RETURN NEW;
    END IF;
    RETURN NULL;
  END
$$ LANGUAGE plpgsql;

CREATE TRIGGER dns_zones_powerdns_trigger
  AFTER INSERT OR UPDATE OR DELETE ON dns_zones
  FOR EACH ROW EXECUTE PROCEDURE
    dns_zones_powerdns_func();

CREATE TRIGGER dns_resources_powerdns_trigger
  AFTER INSERT OR UPDATE OR DELETE ON dns_resources
  FOR EACH ROW EXECUTE PROCEDURE
    dns_resources_powerdns_func();