-- 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'; -- pre-pg8.3, this must be run as a superuser CREATE LANGUAGE plpgsql; -- it's required for: -- Return proper conversion of string to inet, or 0.0.0.0/0 if the string is -- not a valid inet value. We need to do this to support "funky" records that -- may not actually have valid IP address values. Used for ORDER BY CREATE OR REPLACE FUNCTION inetlazy (rdata text) RETURNS inet AS $$ BEGIN RETURN CAST(rdata AS inet); EXCEPTION WHEN OTHERS THEN RETURN CAST('0.0.0.0/0' AS inet); END; $$ LANGUAGE plpgsql; -- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB CREATE TABLE misc ( misc_id serial NOT NULL, key text DEFAULT '' NOT NULL, value text DEFAULT '' NOT NULL ); COPY misc (misc_id, key, value) FROM stdin; 1 dbversion 1.4.0 \. CREATE TABLE locations ( location character varying (4) PRIMARY KEY, loc_id serial UNIQUE, group_id integer NOT NULL DEFAULT 1, iplist text NOT NULL DEFAULT '', description character varying(40) NOT NULL DEFAULT '', comments text NOT NULL DEFAULT '' ); CREATE TABLE default_records ( record_id serial NOT NULL, group_id integer DEFAULT 1 NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' 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 text ); -- 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 default_rev_records ( record_id serial NOT NULL, group_id integer DEFAULT 1 NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' NOT NULL, ttl integer DEFAULT 86400 NOT NULL, description text ); COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin; 1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600 2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600 3 1 ns2.example.com 2 ZONE 7200 \N 4 1 ns1.example.com 2 ZONE 7200 \N \. 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, changed boolean DEFAULT true NOT NULL, default_location character varying (4) DEFAULT '' NOT NULL ); -- ~2x performance boost iff most zones are fed to output from the cache CREATE INDEX dom_status_index ON domains (status); CREATE TABLE revzones ( rdns_id serial NOT NULL, revnet cidr 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, changed boolean DEFAULT true NOT NULL, default_location character varying(4) DEFAULT ''::character varying NOT NULL ); CREATE INDEX rev_status_index ON revzones USING btree (status); 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, permission_id integer DEFAULT 1 NOT NULL, inherit_perm boolean DEFAULT true NOT NULL ); -- Provide a basic default group COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin; 1 1 1 default \. -- entry is text due to possible long entries from AXFR - a domain with "many" -- odd records will overflow varchar(200) CREATE TABLE log ( log_id serial NOT NULL, domain_id integer, user_id integer, group_id integer, email character varying(60), name character varying(60), entry text, stamp timestamp with time zone DEFAULT now(), rdns_id integer, logparent integer NOT NULL DEFAULT 0 ); CREATE INDEX log_domain_id_index ON log(domain_id); CREATE INDEX log_user_id_index ON log(user_id); CREATE INDEX log_group_id_index ON log(group_id); CREATE INDEX log_rdns_id_index ON log(rdns_id); CREATE TABLE permissions ( permission_id serial NOT NULL, "admin" boolean DEFAULT false NOT NULL, self_edit boolean DEFAULT false NOT NULL, group_create boolean DEFAULT false NOT NULL, group_edit boolean DEFAULT false NOT NULL, group_delete boolean DEFAULT false NOT NULL, user_create boolean DEFAULT false NOT NULL, user_edit boolean DEFAULT false NOT NULL, user_delete boolean DEFAULT false NOT NULL, domain_create boolean DEFAULT false NOT NULL, domain_edit boolean DEFAULT false NOT NULL, domain_delete boolean DEFAULT false NOT NULL, record_create boolean DEFAULT false NOT NULL, record_edit boolean DEFAULT false NOT NULL, record_delete boolean DEFAULT false NOT NULL, user_id integer UNIQUE, group_id integer UNIQUE, record_locchg boolean DEFAULT false NOT NULL, location_create boolean DEFAULT false NOT NULL, location_edit boolean DEFAULT false NOT NULL, location_delete boolean DEFAULT false NOT NULL, location_view boolean DEFAULT false NOT NULL ); -- Need *two* basic permissions; one for the initial group, one for the default admin user COPY permissions (permission_id, "admin", self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, user_id, group_id, record_locchg, location_create, location_edit, location_delete, location_view) FROM stdin; 1 f f f f f f f f t t t t t t \N 1 f f f f f 2 t f f f f f f f f f f f f f 1 \N f f f f f \. -- rdns_id defaults to 0 since many records will not have an associated rDNS entry. CREATE TABLE records ( domain_id integer NOT NULL DEFAULT 0, record_id serial NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' NOT NULL, distance integer DEFAULT 0 NOT NULL, weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, ttl integer DEFAULT 7200 NOT NULL, description text, rdns_id integer NOT NULL DEFAULT 0, location character varying (4) DEFAULT '' NOT NULL, stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL, expires boolean DEFAULT 'n' NOT NULL, stampactive boolean DEFAULT 'n' NOT NULL, auxdata text ); CREATE INDEX rec_domain_index ON records USING btree (domain_id); CREATE INDEX rec_revzone_index ON records USING btree (rdns_id); CREATE INDEX rec_types_index ON records USING btree ("type"); CREATE TABLE rectypes ( val integer NOT NULL, name character varying(20) NOT NULL, stdflag integer DEFAULT 1 NOT NULL, listorder integer DEFAULT 255 NOT NULL, alphaorder integer DEFAULT 32768 NOT NULL ); -- Types are required. NB: these are vaguely read-only too -- data from https://www.iana.org/assignments/dns-parameters COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin; 1 A 1 1 1 2 NS 2 10 37 3 MD 5 255 29 4 MF 5 255 30 5 CNAME 2 12 9 6 SOA 0 0 53 7 MB 5 255 28 8 MG 5 255 31 9 MR 5 255 33 10 NULL 5 255 43 11 WKS 5 255 64 12 PTR 3 5 46 13 HINFO 5 255 18 14 MINFO 5 255 32 15 MX 1 11 34 16 TXT 2 13 60 17 RP 4 255 48 18 AFSDB 5 255 4 19 X25 5 255 65 20 ISDN 5 255 21 21 RT 5 255 50 22 NSAP 5 255 38 23 NSAP-PTR 5 255 39 24 SIG 5 255 51 25 KEY 5 255 23 26 PX 5 255 47 27 GPOS 5 255 17 28 AAAA 1 3 3 29 LOC 5 255 25 30 NXT 5 255 44 31 EID 5 255 15 32 NIMLOC 5 255 36 33 SRV 1 14 55 34 ATMA 5 255 6 35 NAPTR 5 255 35 36 KX 5 255 24 37 CERT 5 255 8 38 A6 5 3 2 39 DNAME 5 255 12 40 SINK 5 255 52 41 OPT 5 255 45 42 APL 5 255 5 43 DS 5 255 14 44 SSHFP 5 255 56 45 IPSECKEY 5 255 20 46 RRSIG 5 255 49 47 NSEC 5 255 40 48 DNSKEY 5 255 13 49 DHCID 5 255 10 50 NSEC3 5 255 41 51 NSEC3PARAM 5 255 42 52 TLSA 5 255 255 53 SMIMEA 5 255 255 55 HIP 5 255 19 56 NINFO 5 255 255 57 RKEY 5 255 255 58 TALINK 5 255 255 59 CDS 5 255 255 60 CDNSKEY 5 255 255 61 OPENPGPKEY 5 255 255 62 CSYNC 5 255 255 99 SPF 5 255 54 100 UINFO 5 255 62 101 UID 5 255 61 102 GID 5 255 16 103 UNSPEC 5 255 63 104 NID 5 255 255 105 L32 5 255 255 106 L64 5 255 255 107 LP 5 255 255 108 EUI48 5 255 255 109 EUI64 5 255 255 249 TKEY 5 255 58 250 TSIG 5 255 59 251 IXFR 5 255 22 252 AXFR 5 255 7 253 MAILB 5 255 27 254 MAILA 5 255 26 255 * 5 255 255 256 URI 5 255 255 257 CAA 5 255 255 258 AVC 5 255 255 32768 TA 5 255 57 32769 DLV 5 255 11 \. -- Custom types (ab)using the "Private use" range from 65280 to 65534 COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin; 65280 A+PTR 2 2 2 65281 AAAA+PTR 2 4 4 65282 PTR template 3 6 2 65283 A+PTR template 2 7 2 65284 AAAA+PTR template 2 8 2 65285 Delegation 2 9 2 65300 ALIAS 2 16 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(60), lastname character varying(60), phone character varying(15), "type" character(1) DEFAULT 'S'::bpchar NOT NULL, status integer DEFAULT 1 NOT NULL, permission_id integer DEFAULT 1 NOT NULL, inherit_perm boolean DEFAULT true 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, permission_id, inherit_perm) FROM stdin; 1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f \. -- -- contraints. add these here so initial data doesn't get added strangely. -- -- primary keys ALTER TABLE ONLY permissions ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id); ALTER TABLE ONLY groups ADD CONSTRAINT groups_group_id_key UNIQUE (group_id); ALTER TABLE ONLY domains ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id); ALTER TABLE ONLY domains ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location); 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 revzones ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id); ALTER TABLE ONLY revzones ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (username); ALTER TABLE ONLY users ADD CONSTRAINT uidu UNIQUE (user_id); -- foreign keys -- fixme: permissions FK refs ALTER TABLE ONLY locations ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id); 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 users ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); ALTER TABLE ONLY revzones 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 starting sequence numbers, since we've inserted data before they're active -- only set the ones that have data loaded with \copy, and obey the convention -- that comes out of pg_dump SELECT pg_catalog.setval('misc_misc_id_seq', 1, true); SELECT pg_catalog.setval('default_records_record_id_seq', 8, true); SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true); SELECT pg_catalog.setval('groups_group_id_seq', 1, true); SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true); SELECT pg_catalog.setval('users_user_id_seq', 1, true);