source: trunk/dns.sql

Last change on this file was 883, checked in by Kris Deugau, 13 months ago

/trunk

Refresh initial-install dns.sql with long-in-production updates

File size: 12.5 KB
RevLine 
[50]1-- these lines could be run as a superuser. alter database name, username, password, group as appropriate.
2-- make sure to alter dnsdb.conf to match
3-- CREATE GROUP dnsdb;
4-- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb;
5-- CREATE DATABASE dnsdb OWNED BY dnsdb;
6-- SET SESSION AUTHORIZATION 'dnsdb';
7
[599]8-- pre-pg8.3, this must be run as a superuser
9CREATE LANGUAGE plpgsql;
10-- it's required for:
11
12-- Return proper conversion of string to inet, or 0.0.0.0/0 if the string is
13-- not a valid inet value. We need to do this to support "funky" records that
14-- may not actually have valid IP address values. Used for ORDER BY
15CREATE OR REPLACE FUNCTION inetlazy (rdata text) RETURNS inet AS $$
16BEGIN
17 RETURN CAST(rdata AS inet);
18EXCEPTION
19 WHEN OTHERS THEN
20 RETURN CAST('0.0.0.0/0' AS inet);
21END;
22$$ LANGUAGE plpgsql;
23
24
[212]25-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
26CREATE TABLE misc (
27 misc_id serial NOT NULL,
28 key text DEFAULT '' NOT NULL,
[215]29 value text DEFAULT '' NOT NULL
[212]30);
31
32COPY misc (misc_id, key, value) FROM stdin;
[883]331 dbversion 1.4.2
[212]34\.
35
[370]36CREATE TABLE locations (
37 location character varying (4) PRIMARY KEY,
[375]38 loc_id serial UNIQUE,
[370]39 group_id integer NOT NULL DEFAULT 1,
40 iplist text NOT NULL DEFAULT '',
[375]41 description character varying(40) NOT NULL DEFAULT '',
42 comments text NOT NULL DEFAULT ''
[370]43);
44
[50]45CREATE TABLE default_records (
46 record_id serial NOT NULL,
47 group_id integer DEFAULT 1 NOT NULL,
[130]48 host text DEFAULT '' NOT NULL,
[50]49 "type" integer DEFAULT 1 NOT NULL,
[130]50 val text DEFAULT '' NOT NULL,
[50]51 distance integer DEFAULT 0 NOT NULL,
52 weight integer DEFAULT 0 NOT NULL,
53 port integer DEFAULT 0 NOT NULL,
54 ttl integer DEFAULT 86400 NOT NULL,
[130]55 description text
[50]56);
57
58-- default records for the default group
59COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
601 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
612 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
623 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
634 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
645 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
656 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
667 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
67\.
68
[224]69CREATE TABLE default_rev_records (
70 record_id serial NOT NULL,
71 group_id integer DEFAULT 1 NOT NULL,
72 host text DEFAULT '' NOT NULL,
73 "type" integer DEFAULT 1 NOT NULL,
74 val text DEFAULT '' NOT NULL,
75 ttl integer DEFAULT 86400 NOT NULL,
76 description text
77);
78
[249]79COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
[224]801 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
812 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
[328]823 1 ns2.example.com 2 ZONE 7200 \N
834 1 ns1.example.com 2 ZONE 7200 \N
[224]84\.
85
[85]86CREATE TABLE domains (
87 domain_id serial NOT NULL,
[718]88 "domain" character varying(80) NOT NULL,
[85]89 group_id integer DEFAULT 1 NOT NULL,
90 description character varying(255) DEFAULT ''::character varying NOT NULL,
91 status integer DEFAULT 1 NOT NULL,
92 zserial integer,
[366]93 sertype character(1) DEFAULT 'D'::bpchar,
[370]94 changed boolean DEFAULT true NOT NULL,
95 default_location character varying (4) DEFAULT '' NOT NULL
[85]96);
[507]97-- ~2x performance boost iff most zones are fed to output from the cache
98CREATE INDEX dom_status_index ON domains (status);
[85]99
[507]100
[224]101CREATE TABLE revzones (
102 rdns_id serial NOT NULL,
[718]103 revnet cidr NOT NULL,
[224]104 group_id integer DEFAULT 1 NOT NULL,
105 description character varying(255) DEFAULT ''::character varying NOT NULL,
106 status integer DEFAULT 1 NOT NULL,
107 zserial integer,
[366]108 sertype character(1) DEFAULT 'D'::bpchar,
[370]109 changed boolean DEFAULT true NOT NULL,
[718]110 default_location character varying(4) DEFAULT ''::character varying NOT NULL
[224]111);
[507]112CREATE INDEX rev_status_index ON revzones USING btree (status);
[224]113
[85]114CREATE TABLE groups (
115 group_id serial NOT NULL,
116 parent_group_id integer DEFAULT 1 NOT NULL,
117 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
118 permission_id integer DEFAULT 1 NOT NULL,
119 inherit_perm boolean DEFAULT true NOT NULL
120);
121
122-- Provide a basic default group
[86]123COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
[85]1241 1 1 default
125\.
126
[91]127-- entry is text due to possible long entries from AXFR - a domain with "many"
128-- odd records will overflow varchar(200)
[85]129CREATE TABLE log (
[89]130 log_id serial NOT NULL,
[85]131 domain_id integer,
132 user_id integer,
133 group_id integer,
134 email character varying(60),
135 name character varying(60),
[91]136 entry text,
[507]137 stamp timestamp with time zone DEFAULT now(),
[739]138 rdns_id integer,
139 logparent integer NOT NULL DEFAULT 0
[85]140);
[734]141CREATE INDEX log_domain_id_index ON log(domain_id);
142CREATE INDEX log_user_id_index ON log(user_id);
143CREATE INDEX log_group_id_index ON log(group_id);
144CREATE INDEX log_rdns_id_index ON log(rdns_id);
[85]145
146CREATE TABLE permissions (
147 permission_id serial NOT NULL,
148 "admin" boolean DEFAULT false NOT NULL,
149 self_edit boolean DEFAULT false NOT NULL,
150 group_create boolean DEFAULT false NOT NULL,
151 group_edit boolean DEFAULT false NOT NULL,
152 group_delete boolean DEFAULT false NOT NULL,
153 user_create boolean DEFAULT false NOT NULL,
154 user_edit boolean DEFAULT false NOT NULL,
155 user_delete boolean DEFAULT false NOT NULL,
156 domain_create boolean DEFAULT false NOT NULL,
157 domain_edit boolean DEFAULT false NOT NULL,
158 domain_delete boolean DEFAULT false NOT NULL,
159 record_create boolean DEFAULT false NOT NULL,
160 record_edit boolean DEFAULT false NOT NULL,
161 record_delete boolean DEFAULT false NOT NULL,
[507]162 user_id integer UNIQUE,
163 group_id integer UNIQUE,
[387]164 record_locchg boolean DEFAULT false NOT NULL,
[370]165 location_create boolean DEFAULT false NOT NULL,
166 location_edit boolean DEFAULT false NOT NULL,
167 location_delete boolean DEFAULT false NOT NULL,
[507]168 location_view boolean DEFAULT false NOT NULL
[85]169);
170
171-- Need *two* basic permissions; one for the initial group, one for the default admin user
[507]172COPY 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;
1731 f f f f f f f f t t t t t t \N 1 f f f f f
1742 t f f f f f f f f f f f f f 1 \N f f f f f
[85]175\.
176
[224]177-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
[50]178CREATE TABLE records (
[224]179 domain_id integer NOT NULL DEFAULT 0,
[50]180 record_id serial NOT NULL,
[130]181 host text DEFAULT '' NOT NULL,
[50]182 "type" integer DEFAULT 1 NOT NULL,
[130]183 val text DEFAULT '' NOT NULL,
[50]184 distance integer DEFAULT 0 NOT NULL,
185 weight integer DEFAULT 0 NOT NULL,
186 port integer DEFAULT 0 NOT NULL,
[85]187 ttl integer DEFAULT 7200 NOT NULL,
[370]188 description text,
[507]189 rdns_id integer NOT NULL DEFAULT 0,
[543]190 location character varying (4) DEFAULT '' NOT NULL,
191 stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
192 expires boolean DEFAULT 'n' NOT NULL,
[751]193 stampactive boolean DEFAULT 'n' NOT NULL,
194 auxdata text
[50]195);
[507]196CREATE INDEX rec_domain_index ON records USING btree (domain_id);
197CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
198CREATE INDEX rec_types_index ON records USING btree ("type");
[50]199
200CREATE TABLE rectypes (
201 val integer NOT NULL,
[224]202 name character varying(20) NOT NULL,
[50]203 stdflag integer DEFAULT 1 NOT NULL,
[102]204 listorder integer DEFAULT 255 NOT NULL,
205 alphaorder integer DEFAULT 32768 NOT NULL
[50]206);
207
208-- Types are required. NB: these are vaguely read-only too
[751]209-- data from https://www.iana.org/assignments/dns-parameters
[102]210COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
2111 A 1 1 1
[456]2122 NS 2 10 37
[224]2133 MD 5 255 29
2144 MF 5 255 30
[456]2155 CNAME 2 12 9
[224]2166 SOA 0 0 53
2177 MB 5 255 28
2188 MG 5 255 31
2199 MR 5 255 33
22010 NULL 5 255 43
22111 WKS 5 255 64
[300]22212 PTR 3 5 46
[224]22313 HINFO 5 255 18
22414 MINFO 5 255 32
[456]22515 MX 1 11 34
22616 TXT 2 13 60
[224]22717 RP 4 255 48
22818 AFSDB 5 255 4
22919 X25 5 255 65
23020 ISDN 5 255 21
23121 RT 5 255 50
23222 NSAP 5 255 38
23323 NSAP-PTR 5 255 39
23424 SIG 5 255 51
23525 KEY 5 255 23
23626 PX 5 255 47
23727 GPOS 5 255 17
23828 AAAA 1 3 3
23929 LOC 5 255 25
24030 NXT 5 255 44
24131 EID 5 255 15
24232 NIMLOC 5 255 36
[456]24333 SRV 1 14 55
[224]24434 ATMA 5 255 6
24535 NAPTR 5 255 35
24636 KX 5 255 24
24737 CERT 5 255 8
24838 A6 5 3 2
24939 DNAME 5 255 12
25040 SINK 5 255 52
25141 OPT 5 255 45
25242 APL 5 255 5
25343 DS 5 255 14
25444 SSHFP 5 255 56
25545 IPSECKEY 5 255 20
25646 RRSIG 5 255 49
25747 NSEC 5 255 40
25848 DNSKEY 5 255 13
25949 DHCID 5 255 10
26050 NSEC3 5 255 41
26151 NSEC3PARAM 5 255 42
[751]26252 TLSA 5 255 255
26353 SMIMEA 5 255 255
[224]26455 HIP 5 255 19
[751]26556 NINFO 5 255 255
26657 RKEY 5 255 255
26758 TALINK 5 255 255
26859 CDS 5 255 255
26960 CDNSKEY 5 255 255
27061 OPENPGPKEY 5 255 255
27162 CSYNC 5 255 255
[883]27263 ZONEMD 255 255 255
27364 SVCB 255 255 255
27465 HTTPS 255 255 255
[224]27599 SPF 5 255 54
276100 UINFO 5 255 62
277101 UID 5 255 61
278102 GID 5 255 16
279103 UNSPEC 5 255 63
[751]280104 NID 5 255 255
281105 L32 5 255 255
282106 L64 5 255 255
283107 LP 5 255 255
284108 EUI48 5 255 255
285109 EUI64 5 255 255
[224]286249 TKEY 5 255 58
287250 TSIG 5 255 59
288251 IXFR 5 255 22
289252 AXFR 5 255 7
290253 MAILB 5 255 27
291254 MAILA 5 255 26
[751]292255 * 5 255 255
293256 URI 5 255 255
[883]294257 CAA 1 17 255
[751]295258 AVC 5 255 255
[883]296259 DOA 255 255 255
297260 AMTRELAY 255 255 255
[224]29832768 TA 5 255 57
29932769 DLV 5 255 11
[50]300\.
301
[224]302-- Custom types (ab)using the "Private use" range from 65280 to 65534
303COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
30465280 A+PTR 2 2 2
30565281 AAAA+PTR 2 4 4
[300]30665282 PTR template 3 6 2
[342]30765283 A+PTR template 2 7 2
[456]30865284 AAAA+PTR template 2 8 2
30965285 Delegation 2 9 2
[759]31065300 ALIAS 2 16 255
[224]311\.
312
[50]313CREATE TABLE users (
314 user_id serial NOT NULL,
315 group_id integer DEFAULT 1 NOT NULL,
316 username character varying(60) NOT NULL,
317 "password" character varying(34) NOT NULL,
[85]318 firstname character varying(60),
319 lastname character varying(60),
[50]320 phone character varying(15),
321 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
322 status integer DEFAULT 1 NOT NULL,
[85]323 permission_id integer DEFAULT 1 NOT NULL,
324 inherit_perm boolean DEFAULT true NOT NULL
[50]325);
326
327-- create initial default user? may be better to create an "initialize" script or something
[86]328COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
[89]3291 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
[50]330\.
331
332--
333-- contraints. add these here so initial data doesn't get added strangely.
334--
335
336-- primary keys
[65]337ALTER TABLE ONLY permissions
338 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
339
[50]340ALTER TABLE ONLY groups
341 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
342
343ALTER TABLE ONLY domains
344 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
345
[718]346ALTER TABLE ONLY domains
347 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);
348
[50]349ALTER TABLE ONLY default_records
350 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
351
352ALTER TABLE ONLY records
353 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
354
355ALTER TABLE ONLY rectypes
356 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
357
[718]358ALTER TABLE ONLY revzones
359 ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);
360
361ALTER TABLE ONLY revzones
362 ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);
363
[50]364ALTER TABLE ONLY users
365 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
366
367ALTER TABLE ONLY users
368 ADD CONSTRAINT uidu UNIQUE (user_id);
369
370-- foreign keys
[65]371-- fixme: permissions FK refs
[370]372ALTER TABLE ONLY locations
373 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
374
[50]375ALTER TABLE ONLY domains
376 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
377
378ALTER TABLE ONLY default_records
379 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
380
381ALTER TABLE ONLY users
382 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
383
[507]384ALTER TABLE ONLY revzones
385 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
386
[50]387ALTER TABLE ONLY groups
388 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
[86]389
390-- set starting sequence numbers, since we've inserted data before they're active
[507]391-- only set the ones that have data loaded with \copy, and obey the convention
392-- that comes out of pg_dump
393SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
394SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
395SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
396SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
397SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
398SELECT pg_catalog.setval('users_user_id_seq', 1, true);
Note: See TracBrowser for help on using the repository browser.