-- 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'; -- 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.2 \. 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 PRIMARY KEY, 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 PRIMARY KEY, 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 ); 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 ); 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 ); 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 http://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 55 HIP 5 255 19 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 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 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 \. 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 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 -- 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);