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();$$