source: trunk/dns.sql@ 736

Last change on this file since 736 was 734, checked in by Kris Deugau, 9 years ago

/trunk

Add some missing indexes in the targeted 1.4.0 SQL schema.

File size: 12.0 KB
Line 
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
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
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,
29 value text DEFAULT '' NOT NULL
30);
31
32COPY misc (misc_id, key, value) FROM stdin;
331 dbversion 1.4.0
34\.
35
36CREATE TABLE locations (
37 location character varying (4) PRIMARY KEY,
38 loc_id serial UNIQUE,
39 group_id integer NOT NULL DEFAULT 1,
40 iplist text NOT NULL DEFAULT '',
41 description character varying(40) NOT NULL DEFAULT '',
42 comments text NOT NULL DEFAULT ''
43);
44
45CREATE TABLE default_records (
46 record_id serial NOT NULL,
47 group_id integer DEFAULT 1 NOT NULL,
48 host text DEFAULT '' NOT NULL,
49 "type" integer DEFAULT 1 NOT NULL,
50 val text DEFAULT '' NOT NULL,
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,
55 description text
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
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
79COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
801 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
812 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
823 1 ns2.example.com 2 ZONE 7200 \N
834 1 ns1.example.com 2 ZONE 7200 \N
84\.
85
86CREATE TABLE domains (
87 domain_id serial NOT NULL,
88 "domain" character varying(80) NOT NULL,
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,
93 sertype character(1) DEFAULT 'D'::bpchar,
94 changed boolean DEFAULT true NOT NULL,
95 default_location character varying (4) DEFAULT '' NOT NULL
96);
97-- ~2x performance boost iff most zones are fed to output from the cache
98CREATE INDEX dom_status_index ON domains (status);
99
100
101CREATE TABLE revzones (
102 rdns_id serial NOT NULL,
103 revnet cidr NOT NULL,
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,
108 sertype character(1) DEFAULT 'D'::bpchar,
109 changed boolean DEFAULT true NOT NULL,
110 default_location character varying(4) DEFAULT ''::character varying NOT NULL
111);
112CREATE INDEX rev_status_index ON revzones USING btree (status);
113
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
123COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1241 1 1 default
125\.
126
127-- entry is text due to possible long entries from AXFR - a domain with "many"
128-- odd records will overflow varchar(200)
129CREATE TABLE log (
130 log_id serial NOT NULL,
131 domain_id integer,
132 user_id integer,
133 group_id integer,
134 email character varying(60),
135 name character varying(60),
136 entry text,
137 stamp timestamp with time zone DEFAULT now(),
138 rdns_id integer
139);
140CREATE INDEX log_domain_id_index ON log(domain_id);
141CREATE INDEX log_user_id_index ON log(user_id);
142CREATE INDEX log_group_id_index ON log(group_id);
143CREATE INDEX log_rdns_id_index ON log(rdns_id);
144
145CREATE TABLE permissions (
146 permission_id serial NOT NULL,
147 "admin" boolean DEFAULT false NOT NULL,
148 self_edit boolean DEFAULT false NOT NULL,
149 group_create boolean DEFAULT false NOT NULL,
150 group_edit boolean DEFAULT false NOT NULL,
151 group_delete boolean DEFAULT false NOT NULL,
152 user_create boolean DEFAULT false NOT NULL,
153 user_edit boolean DEFAULT false NOT NULL,
154 user_delete boolean DEFAULT false NOT NULL,
155 domain_create boolean DEFAULT false NOT NULL,
156 domain_edit boolean DEFAULT false NOT NULL,
157 domain_delete boolean DEFAULT false NOT NULL,
158 record_create boolean DEFAULT false NOT NULL,
159 record_edit boolean DEFAULT false NOT NULL,
160 record_delete boolean DEFAULT false NOT NULL,
161 user_id integer UNIQUE,
162 group_id integer UNIQUE,
163 record_locchg boolean DEFAULT false NOT NULL,
164 location_create boolean DEFAULT false NOT NULL,
165 location_edit boolean DEFAULT false NOT NULL,
166 location_delete boolean DEFAULT false NOT NULL,
167 location_view boolean DEFAULT false NOT NULL
168);
169
170-- Need *two* basic permissions; one for the initial group, one for the default admin user
171COPY 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;
1721 f f f f f f f f t t t t t t \N 1 f f f f f
1732 t f f f f f f f f f f f f f 1 \N f f f f f
174\.
175
176-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
177CREATE TABLE records (
178 domain_id integer NOT NULL DEFAULT 0,
179 record_id serial NOT NULL,
180 host text DEFAULT '' NOT NULL,
181 "type" integer DEFAULT 1 NOT NULL,
182 val text DEFAULT '' NOT NULL,
183 distance integer DEFAULT 0 NOT NULL,
184 weight integer DEFAULT 0 NOT NULL,
185 port integer DEFAULT 0 NOT NULL,
186 ttl integer DEFAULT 7200 NOT NULL,
187 description text,
188 rdns_id integer NOT NULL DEFAULT 0,
189 location character varying (4) DEFAULT '' NOT NULL,
190 stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
191 expires boolean DEFAULT 'n' NOT NULL,
192 stampactive boolean DEFAULT 'n' NOT NULL
193);
194CREATE INDEX rec_domain_index ON records USING btree (domain_id);
195CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
196CREATE INDEX rec_types_index ON records USING btree ("type");
197
198CREATE TABLE rectypes (
199 val integer NOT NULL,
200 name character varying(20) NOT NULL,
201 stdflag integer DEFAULT 1 NOT NULL,
202 listorder integer DEFAULT 255 NOT NULL,
203 alphaorder integer DEFAULT 32768 NOT NULL
204);
205
206-- Types are required. NB: these are vaguely read-only too
207-- data from http://www.iana.org/assignments/dns-parameters
208COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
2091 A 1 1 1
2102 NS 2 10 37
2113 MD 5 255 29
2124 MF 5 255 30
2135 CNAME 2 12 9
2146 SOA 0 0 53
2157 MB 5 255 28
2168 MG 5 255 31
2179 MR 5 255 33
21810 NULL 5 255 43
21911 WKS 5 255 64
22012 PTR 3 5 46
22113 HINFO 5 255 18
22214 MINFO 5 255 32
22315 MX 1 11 34
22416 TXT 2 13 60
22517 RP 4 255 48
22618 AFSDB 5 255 4
22719 X25 5 255 65
22820 ISDN 5 255 21
22921 RT 5 255 50
23022 NSAP 5 255 38
23123 NSAP-PTR 5 255 39
23224 SIG 5 255 51
23325 KEY 5 255 23
23426 PX 5 255 47
23527 GPOS 5 255 17
23628 AAAA 1 3 3
23729 LOC 5 255 25
23830 NXT 5 255 44
23931 EID 5 255 15
24032 NIMLOC 5 255 36
24133 SRV 1 14 55
24234 ATMA 5 255 6
24335 NAPTR 5 255 35
24436 KX 5 255 24
24537 CERT 5 255 8
24638 A6 5 3 2
24739 DNAME 5 255 12
24840 SINK 5 255 52
24941 OPT 5 255 45
25042 APL 5 255 5
25143 DS 5 255 14
25244 SSHFP 5 255 56
25345 IPSECKEY 5 255 20
25446 RRSIG 5 255 49
25547 NSEC 5 255 40
25648 DNSKEY 5 255 13
25749 DHCID 5 255 10
25850 NSEC3 5 255 41
25951 NSEC3PARAM 5 255 42
26055 HIP 5 255 19
26199 SPF 5 255 54
262100 UINFO 5 255 62
263101 UID 5 255 61
264102 GID 5 255 16
265103 UNSPEC 5 255 63
266249 TKEY 5 255 58
267250 TSIG 5 255 59
268251 IXFR 5 255 22
269252 AXFR 5 255 7
270253 MAILB 5 255 27
271254 MAILA 5 255 26
27232768 TA 5 255 57
27332769 DLV 5 255 11
274\.
275
276-- Custom types (ab)using the "Private use" range from 65280 to 65534
277COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
27865280 A+PTR 2 2 2
27965281 AAAA+PTR 2 4 4
28065282 PTR template 3 6 2
28165283 A+PTR template 2 7 2
28265284 AAAA+PTR template 2 8 2
28365285 Delegation 2 9 2
284\.
285
286CREATE TABLE users (
287 user_id serial NOT NULL,
288 group_id integer DEFAULT 1 NOT NULL,
289 username character varying(60) NOT NULL,
290 "password" character varying(34) NOT NULL,
291 firstname character varying(60),
292 lastname character varying(60),
293 phone character varying(15),
294 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
295 status integer DEFAULT 1 NOT NULL,
296 permission_id integer DEFAULT 1 NOT NULL,
297 inherit_perm boolean DEFAULT true NOT NULL
298);
299
300-- create initial default user? may be better to create an "initialize" script or something
301COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
3021 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
303\.
304
305--
306-- contraints. add these here so initial data doesn't get added strangely.
307--
308
309-- primary keys
310ALTER TABLE ONLY permissions
311 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
312
313ALTER TABLE ONLY groups
314 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
315
316ALTER TABLE ONLY domains
317 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
318
319ALTER TABLE ONLY domains
320 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);
321
322ALTER TABLE ONLY default_records
323 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
324
325ALTER TABLE ONLY records
326 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
327
328ALTER TABLE ONLY rectypes
329 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
330
331ALTER TABLE ONLY revzones
332 ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);
333
334ALTER TABLE ONLY revzones
335 ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);
336
337ALTER TABLE ONLY users
338 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
339
340ALTER TABLE ONLY users
341 ADD CONSTRAINT uidu UNIQUE (user_id);
342
343-- foreign keys
344-- fixme: permissions FK refs
345ALTER TABLE ONLY locations
346 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
347
348ALTER TABLE ONLY domains
349 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
350
351ALTER TABLE ONLY default_records
352 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
353
354ALTER TABLE ONLY users
355 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
356
357ALTER TABLE ONLY revzones
358 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
359
360ALTER TABLE ONLY groups
361 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
362
363-- set starting sequence numbers, since we've inserted data before they're active
364-- only set the ones that have data loaded with \copy, and obey the convention
365-- that comes out of pg_dump
366SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
367SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
368SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
369SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
370SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
371SELECT pg_catalog.setval('users_user_id_seq', 1, true);
Note: See TracBrowser for help on using the repository browser.