Dariush Debian Diary
Diariusz Dariusza
, 14, 2007
PowerDNS on PostgreSQL
CREATE TABLE domains (
id serial NOT NULL,
name character varying(255) NOT NULL,
master character varying(20),
last_check integer,
"type" character varying(6) NOT NULL,
notified_serial integer,
account character varying(40)
);
CREATE TABLE records (
id serial NOT NULL,
domain_id integer,
name character varying(255),
"type" character varying(6),
content character varying(255),
ttl integer,
prio integer,
change_date integer
);
CREATE TABLE supermasters (
ip character varying(25) NOT NULL,
nameserver character varying(255) NOT NULL,
account character varying(40)
);
COPY domains (id, name, master, last_check, "type", notified_serial, account) FROM stdin;
1 kuszelas.eu \N \N NATIVE \N \N
\.
--
-- Data for TOC entry 22 (OID 17153)
-- Name: records; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY records (id, domain_id, name, "type", content, ttl, prio, change_date) FROM stdin;
2 1 localhost.kuszelas.eu A 127.0.0.1 120 \N \N
20 1 kuszelas.eu MX mail2.kuszelas.eu 300 10 \N
21 1 mail2.kuszelas.eu A 212.182.115.24 120 \N \N
1 1 kuszelas.eu SOA localhost user.kuszelas.eu 1 86400 \N \N
17 1 kuszelas.eu NS ns.kuszelas.eu 300 \N \N
18 1 kuszelas.eu MX mail.kuszelas.eu 300 5 \N
12 1 _jabber._tcp.jabber.kuszelas.eu SRV 0 5269 kuszelas.eu 300 10 \N
13 1 _xmpp-server._tcp.jabber.kuszelas.eu SRV 0 5269 kuszelas.eu 300 10 \N
14 1 _xmpp-client._tcp.jabber.kuszelas.eu SRV 0 5222 kuszelas.eu 300 10 \N
16 1 kuszelas.eu TXT Serwer 300 \N \N
3 1 www.kuszelas.eu A 195.242.124.71 120 \N \N
5 1 dns.kuszelas.eu A 195.242.124.71 120 \N \N
6 1 ftp.kuszelas.eu A 195.242.124.71 120 \N \N
7 1 poczta.kuszelas.eu A 195.242.124.71 120 \N \N
8 1 pop3.kuszelas.eu A 195.242.124.71 120 \N \N
9 1 smtp.kuszelas.eu A 195.242.124.71 120 \N \N
10 1 ssh.kuszelas.eu A 195.242.124.71 120 \N \N
11 1 jabber.kuszelas.eu A 195.242.124.71 120 \N \N
4 1 mail.kuszelas.eu A 195.242.124.71 120 \N \N
\.
Audit trail:
CREATE TABLE audit_domains (
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
id serial NOT NULL,
name character varying(255) NOT NULL,
master character varying(20),
last_check integer,
"type" character varying(6) NOT NULL,
notified_serial integer,
account character varying(40)
);
CREATE TABLE audit_records (
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
id serial NOT NULL,
domain_id integer,
name character varying(255),
"type" character varying(6),
content character varying(255),
ttl integer,
prio integer,
change_date integer
);
CREATE OR REPLACE FUNCTION process_audit_domains() RETURNS TRIGGER AS $audit_domains$
BEGIN
--
-- Create a row in audit_domains to reflect the operation performed on domains,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit_domains SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_domains SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_domains SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$audit_domains$ LANGUAGE plpgsql;
CREATE TRIGGER audit_domains
AFTER INSERT OR UPDATE OR DELETE ON domains
FOR EACH ROW EXECUTE PROCEDURE process_audit_domains();
--
CREATE OR REPLACE FUNCTION process_audit_records() RETURNS TRIGGER AS $audit_records$
BEGIN
--
-- Create a row in audit_records to reflect the operation performed on records,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit_records SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_records SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_records SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$audit_records$ LANGUAGE plpgsql;
CREATE TRIGGER audit_records
AFTER INSERT OR UPDATE OR DELETE ON records
FOR EACH ROW EXECUTE PROCEDURE process_audit_records();
Last modified on
Copyright ©1995-2004 Dariush Pietrzak. All content on this website, unless otherwise noted, is licensed under a Creative Commons License.
Using desktop theme, version 0.5. Check the Blosxom site for the latest copy.
