Index: trunk/TODO =================================================================== --- trunk/TODO (revision 49) +++ trunk/TODO (revision 50) @@ -24,2 +24,8 @@ - Wrap non-critical bits like Net::Whois::Raw so that they don't just cause a failure, and the bits that need them are only available if they're installed + +2009/12/16 + - Add record type editor - note, just to edit which types are visible + - Subclass some of the specifics of record handling? + - would let users create plugin code to support arbitrary types + - Push DB name, host, username, password into config file Index: trunk/dns.sql =================================================================== --- trunk/dns.sql (revision 50) +++ trunk/dns.sql (revision 50) @@ -0,0 +1,227 @@ +-- these lines could be run as a superuser. alter database name, username, password, group as appropriate. +-- make sure to alter dnsdb.conf to match +-- CREATE GROUP dnsdb; +-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb; +-- CREATE DATABASE dnsdb OWNED BY dnsdb; +-- SET SESSION AUTHORIZATION 'dnsdb'; + +-- tabledefs and preloaded data bits +CREATE TABLE groups ( + group_id serial NOT NULL, + parent_group_id integer DEFAULT 1 NOT NULL, + group_name character varying(255) DEFAULT ''::character varying NOT NULL +); + +-- Provide a basic default group +COPY groups (group_id, parent_group_id, group_name) FROM stdin; +1 1 default +\. + +CREATE TABLE domains ( + domain_id serial NOT NULL, + "domain" character varying(80) NOT NULL, + group_id integer DEFAULT 1 NOT NULL, + description character varying(255) DEFAULT ''::character varying NOT NULL, + status integer DEFAULT 1 NOT NULL, + zserial integer, + sertype character(1) DEFAULT 'D'::bpchar +); + +CREATE TABLE default_records ( + record_id serial NOT NULL, + group_id integer DEFAULT 1 NOT NULL, + host character varying(100) DEFAULT ''::character varying NOT NULL, + "type" integer DEFAULT 1 NOT NULL, + val character varying(100) DEFAULT ''::character varying NOT NULL, + distance integer DEFAULT 0 NOT NULL, + weight integer DEFAULT 0 NOT NULL, + port integer DEFAULT 0 NOT NULL, + ttl integer DEFAULT 86400 NOT NULL, + description character varying(255) +); + +-- default records for the default group +COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin; +1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N +2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N +3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N +4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N +5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N +6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N +7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N +\. + +CREATE TABLE records ( + domain_id integer NOT NULL, + record_id serial NOT NULL, + host character varying(100) DEFAULT ''::character varying NOT NULL, + "type" integer DEFAULT 1 NOT NULL, + val character varying(100) DEFAULT ''::character varying NOT NULL, + distance integer DEFAULT 0 NOT NULL, + weight integer DEFAULT 0 NOT NULL, + port integer DEFAULT 0 NOT NULL, + ttl integer DEFAULT 86400 NOT NULL, + description character varying(255) +); + +CREATE TABLE rectypes ( + val integer NOT NULL, + name character varying(12) NOT NULL, + stdflag integer DEFAULT 1 NOT NULL, + listorder integer DEFAULT 255 NOT NULL +); + +-- Types are required. NB: these are vaguely read-only too +COPY rectypes (val, name, stdflag, listorder) FROM stdin; +1 A 1 1 +2 NS 1 2 +3 MD 2 255 +4 MF 2 255 +5 CNAME 1 6 +6 SOA 0 8 +7 MB 3 255 +8 MG 3 255 +9 MR 3 255 +10 NULL 3 255 +11 WKS 3 255 +12 PTR 2 4 +13 HINFO 3 255 +14 MINFO 3 255 +15 MX 1 3 +16 TXT 1 5 +17 RP 2 255 +18 AFSDB 3 255 +19 X25 3 255 +20 ISDN 3 255 +21 RT 3 255 +22 NSAP 3 255 +23 NSAP-PTR 3 255 +24 SIG 3 255 +25 KEY 3 255 +26 PX 3 255 +27 GPOS 3 255 +28 AAAA 2 2 +29 LOC 3 255 +30 NXT 3 255 +31 EID 3 255 +32 NIMLOC 3 255 +33 SRV 1 7 +34 ATMA 3 255 +35 NAPTR 3 255 +36 KX 3 255 +37 CERT 3 255 +38 A6 3 3 +39 DNAME 3 255 +40 SINK 3 255 +41 OPT 3 255 +42 APL 3 255 +43 DS 3 255 +44 SSHFP 3 255 +45 IPSECKEY 3 255 +46 RRSIG 3 255 +47 NSEC 3 255 +48 DNSKEY 3 255 +49 DHCID 3 255 +50 NSEC3 3 255 +51 NSEC3PARAM 3 255 +55 HIP 3 255 +99 SPF 3 255 +100 UINFO 3 255 +101 UID 3 255 +102 GID 3 255 +103 UNSPEC 3 255 +249 TKEY 3 255 +250 TSIG 3 255 +251 IXFR 3 255 +252 AXFR 3 255 +253 MAILB 3 255 +254 MAILA 3 255 +32768 TA 3 255 +32769 DLV 3 255 +\. + +CREATE TABLE users ( + user_id serial NOT NULL, + group_id integer DEFAULT 1 NOT NULL, + username character varying(60) NOT NULL, + "password" character varying(34) NOT NULL, + firstname character varying(30), + lastname character varying(30), + phone character varying(15), + "type" character(1) DEFAULT 'S'::bpchar NOT NULL, + status integer DEFAULT 1 NOT NULL, + acl character varying(40) DEFAULT 'b'::character varying NOT NULL +); + +-- create initial default user? may be better to create an "initialize" script or something +COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, acl) FROM stdin; +1 1 test@test $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1 \N \N \N S 1 A +\. + +CREATE TABLE log ( + domain_id integer, + user_id integer, + group_id integer, + email character varying(60), + name character varying(60), + entry character varying(200), + stamp timestamp with time zone +); + +-- +-- contraints. add these here so initial data doesn't get added strangely. +-- + +-- primary keys +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_group_id_key UNIQUE (group_id); + +ALTER TABLE ONLY domains + ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain"); + +ALTER TABLE ONLY domains + ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id); + +ALTER TABLE ONLY default_records + ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id); + +ALTER TABLE ONLY records + ADD CONSTRAINT records_pkey PRIMARY KEY (record_id); + +ALTER TABLE ONLY rectypes + ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name); + +ALTER TABLE ONLY users + ADD CONSTRAINT users_pkey PRIMARY KEY (username); + +ALTER TABLE ONLY users + ADD CONSTRAINT uidu UNIQUE (user_id); + +-- foreign keys +ALTER TABLE ONLY domains + ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); + +ALTER TABLE ONLY default_records + ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); + +ALTER TABLE ONLY records + ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id); + +ALTER TABLE ONLY users + ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); + +ALTER TABLE ONLY groups + ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id); + +-- set sequence start values - make sure we don't screw up adding +-- records to tables that already have a few entries + +SELECT pg_catalog.setval('groups_group_id_seq', 52, true); + +SELECT pg_catalog.setval('domains_domain_id_seq', 953, true); + +SELECT pg_catalog.setval('default_records_record_id_seq', 320, true); + +SELECT pg_catalog.setval('records_record_id_seq', 660, true); + +SELECT pg_catalog.setval('users_user_id_seq', 37, true);