Context Navigation

source: trunk/dns.sql@ 734

Last change on this file since 734 was 734, checked in by Kris Deugau, 8 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
9	CREATE 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
15	CREATE OR REPLACE FUNCTION inetlazy (rdata text) RETURNS inet AS $$
16	BEGIN
17	RETURN CAST(rdata AS inet);
18	EXCEPTION
19	WHEN OTHERS THEN
20	RETURN CAST('0.0.0.0/0' AS inet);
21	END;
22	$$ LANGUAGE plpgsql;
23
24
25	-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
26	CREATE TABLE misc (
27	misc_id serial NOT NULL,
28	key text DEFAULT '' NOT NULL,
29	value text DEFAULT '' NOT NULL
30	);
31
32	COPY misc (misc_id, key, value) FROM stdin;
33	1 dbversion 1.4.0
34	\.
35
36	CREATE 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
45	CREATE 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
59	COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
60	1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
61	2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
62	3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
63	4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
64	5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
65	6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
66	7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
67	\.
68
69	CREATE 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
79	COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
80	1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
81	2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
82	3 1 ns2.example.com 2 ZONE 7200 \N
83	4 1 ns1.example.com 2 ZONE 7200 \N
84	\.
85
86	CREATE 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
98	CREATE INDEX dom_status_index ON domains (status);
99
100
101	CREATE 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	);
112	CREATE INDEX rev_status_index ON revzones USING btree (status);
113
114	CREATE 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
123	COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
124	1 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)
129	CREATE 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	);
140	CREATE INDEX log_domain_id_index ON log(domain_id);
141	CREATE INDEX log_user_id_index ON log(user_id);
142	CREATE INDEX log_group_id_index ON log(group_id);
143	CREATE INDEX log_rdns_id_index ON log(rdns_id);
144
145	CREATE 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
171	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;
172	1 f f f f f f f f t t t t t t \N 1 f f f f f
173	2 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.
177	CREATE 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	);
194	CREATE INDEX rec_domain_index ON records USING btree (domain_id);
195	CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
196	CREATE INDEX rec_types_index ON records USING btree ("type");
197
198	CREATE 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
208	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
209	1 A 1 1 1
210	2 NS 2 10 37
211	3 MD 5 255 29
212	4 MF 5 255 30
213	5 CNAME 2 12 9
214	6 SOA 0 0 53
215	7 MB 5 255 28
216	8 MG 5 255 31
217	9 MR 5 255 33
218	10 NULL 5 255 43
219	11 WKS 5 255 64
220	12 PTR 3 5 46
221	13 HINFO 5 255 18
222	14 MINFO 5 255 32
223	15 MX 1 11 34
224	16 TXT 2 13 60
225	17 RP 4 255 48
226	18 AFSDB 5 255 4
227	19 X25 5 255 65
228	20 ISDN 5 255 21
229	21 RT 5 255 50
230	22 NSAP 5 255 38
231	23 NSAP-PTR 5 255 39
232	24 SIG 5 255 51
233	25 KEY 5 255 23
234	26 PX 5 255 47
235	27 GPOS 5 255 17
236	28 AAAA 1 3 3
237	29 LOC 5 255 25
238	30 NXT 5 255 44
239	31 EID 5 255 15
240	32 NIMLOC 5 255 36
241	33 SRV 1 14 55
242	34 ATMA 5 255 6
243	35 NAPTR 5 255 35
244	36 KX 5 255 24
245	37 CERT 5 255 8
246	38 A6 5 3 2
247	39 DNAME 5 255 12
248	40 SINK 5 255 52
249	41 OPT 5 255 45
250	42 APL 5 255 5
251	43 DS 5 255 14
252	44 SSHFP 5 255 56
253	45 IPSECKEY 5 255 20
254	46 RRSIG 5 255 49
255	47 NSEC 5 255 40
256	48 DNSKEY 5 255 13
257	49 DHCID 5 255 10
258	50 NSEC3 5 255 41
259	51 NSEC3PARAM 5 255 42
260	55 HIP 5 255 19
261	99 SPF 5 255 54
262	100 UINFO 5 255 62
263	101 UID 5 255 61
264	102 GID 5 255 16
265	103 UNSPEC 5 255 63
266	249 TKEY 5 255 58
267	250 TSIG 5 255 59
268	251 IXFR 5 255 22
269	252 AXFR 5 255 7
270	253 MAILB 5 255 27
271	254 MAILA 5 255 26
272	32768 TA 5 255 57
273	32769 DLV 5 255 11
274	\.
275
276	-- Custom types (ab)using the "Private use" range from 65280 to 65534
277	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
278	65280 A+PTR 2 2 2
279	65281 AAAA+PTR 2 4 4
280	65282 PTR template 3 6 2
281	65283 A+PTR template 2 7 2
282	65284 AAAA+PTR template 2 8 2
283	65285 Delegation 2 9 2
284	\.
285
286	CREATE 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
301	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
302	1 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
310	ALTER TABLE ONLY permissions
311	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
312
313	ALTER TABLE ONLY groups
314	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
315
316	ALTER TABLE ONLY domains
317	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
318
319	ALTER TABLE ONLY domains
320	ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);
321
322	ALTER TABLE ONLY default_records
323	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
324
325	ALTER TABLE ONLY records
326	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
327
328	ALTER TABLE ONLY rectypes
329	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
330
331	ALTER TABLE ONLY revzones
332	ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);
333
334	ALTER TABLE ONLY revzones
335	ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);
336
337	ALTER TABLE ONLY users
338	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
339
340	ALTER TABLE ONLY users
341	ADD CONSTRAINT uidu UNIQUE (user_id);
342
343	-- foreign keys
344	-- fixme: permissions FK refs
345	ALTER TABLE ONLY locations
346	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
347
348	ALTER TABLE ONLY domains
349	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
350
351	ALTER TABLE ONLY default_records
352	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
353
354	ALTER TABLE ONLY users
355	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
356
357	ALTER TABLE ONLY revzones
358	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
359
360	ALTER 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
366	SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
367	SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
368	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
369	SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
370	SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
371	SELECT pg_catalog.setval('users_user_id_seq', 1, true);

Note: See TracBrowser for help on using the repository browser.

Download in other formats: