Context Navigation

source: trunk/dns.sql@ 649

Last change on this file since 649 was 599, checked in by Kris Deugau, 11 years ago

/trunk

Prepare for changes to allow any record in either forward or reverse
zones, no matter how silly.

This commit adds a database function to catch errors with CASTing a
column as inet, so that any place we need to sort records based on the
IP value of a column succeeds by treating non-IP values as 0.0.0.0/0.

File size: 11.5 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.2.4
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 PRIMARY KEY,
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 PRIMARY KEY,
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 '' 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
141	CREATE TABLE permissions (
142	permission_id serial NOT NULL,
143	"admin" boolean DEFAULT false NOT NULL,
144	self_edit boolean DEFAULT false NOT NULL,
145	group_create boolean DEFAULT false NOT NULL,
146	group_edit boolean DEFAULT false NOT NULL,
147	group_delete boolean DEFAULT false NOT NULL,
148	user_create boolean DEFAULT false NOT NULL,
149	user_edit boolean DEFAULT false NOT NULL,
150	user_delete boolean DEFAULT false NOT NULL,
151	domain_create boolean DEFAULT false NOT NULL,
152	domain_edit boolean DEFAULT false NOT NULL,
153	domain_delete boolean DEFAULT false NOT NULL,
154	record_create boolean DEFAULT false NOT NULL,
155	record_edit boolean DEFAULT false NOT NULL,
156	record_delete boolean DEFAULT false NOT NULL,
157	user_id integer UNIQUE,
158	group_id integer UNIQUE,
159	record_locchg boolean DEFAULT false NOT NULL,
160	location_create boolean DEFAULT false NOT NULL,
161	location_edit boolean DEFAULT false NOT NULL,
162	location_delete boolean DEFAULT false NOT NULL,
163	location_view boolean DEFAULT false NOT NULL
164	);
165
166	-- Need two basic permissions; one for the initial group, one for the default admin user
167	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;
168	1 f f f f f f f f t t t t t t \N 1 f f f f f
169	2 t f f f f f f f f f f f f f 1 \N f f f f f
170	\.
171
172	-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
173	CREATE TABLE records (
174	domain_id integer NOT NULL DEFAULT 0,
175	record_id serial NOT NULL,
176	host text DEFAULT '' NOT NULL,
177	"type" integer DEFAULT 1 NOT NULL,
178	val text DEFAULT '' NOT NULL,
179	distance integer DEFAULT 0 NOT NULL,
180	weight integer DEFAULT 0 NOT NULL,
181	port integer DEFAULT 0 NOT NULL,
182	ttl integer DEFAULT 7200 NOT NULL,
183	description text,
184	rdns_id integer NOT NULL DEFAULT 0,
185	location character varying (4) DEFAULT '' NOT NULL,
186	stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
187	expires boolean DEFAULT 'n' NOT NULL,
188	stampactive boolean DEFAULT 'n' NOT NULL
189	);
190	CREATE INDEX rec_domain_index ON records USING btree (domain_id);
191	CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
192	CREATE INDEX rec_types_index ON records USING btree ("type");
193
194	CREATE TABLE rectypes (
195	val integer NOT NULL,
196	name character varying(20) NOT NULL,
197	stdflag integer DEFAULT 1 NOT NULL,
198	listorder integer DEFAULT 255 NOT NULL,
199	alphaorder integer DEFAULT 32768 NOT NULL
200	);
201
202	-- Types are required. NB: these are vaguely read-only too
203	-- data from http://www.iana.org/assignments/dns-parameters
204	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
205	1 A 1 1 1
206	2 NS 2 10 37
207	3 MD 5 255 29
208	4 MF 5 255 30
209	5 CNAME 2 12 9
210	6 SOA 0 0 53
211	7 MB 5 255 28
212	8 MG 5 255 31
213	9 MR 5 255 33
214	10 NULL 5 255 43
215	11 WKS 5 255 64
216	12 PTR 3 5 46
217	13 HINFO 5 255 18
218	14 MINFO 5 255 32
219	15 MX 1 11 34
220	16 TXT 2 13 60
221	17 RP 4 255 48
222	18 AFSDB 5 255 4
223	19 X25 5 255 65
224	20 ISDN 5 255 21
225	21 RT 5 255 50
226	22 NSAP 5 255 38
227	23 NSAP-PTR 5 255 39
228	24 SIG 5 255 51
229	25 KEY 5 255 23
230	26 PX 5 255 47
231	27 GPOS 5 255 17
232	28 AAAA 1 3 3
233	29 LOC 5 255 25
234	30 NXT 5 255 44
235	31 EID 5 255 15
236	32 NIMLOC 5 255 36
237	33 SRV 1 14 55
238	34 ATMA 5 255 6
239	35 NAPTR 5 255 35
240	36 KX 5 255 24
241	37 CERT 5 255 8
242	38 A6 5 3 2
243	39 DNAME 5 255 12
244	40 SINK 5 255 52
245	41 OPT 5 255 45
246	42 APL 5 255 5
247	43 DS 5 255 14
248	44 SSHFP 5 255 56
249	45 IPSECKEY 5 255 20
250	46 RRSIG 5 255 49
251	47 NSEC 5 255 40
252	48 DNSKEY 5 255 13
253	49 DHCID 5 255 10
254	50 NSEC3 5 255 41
255	51 NSEC3PARAM 5 255 42
256	55 HIP 5 255 19
257	99 SPF 5 255 54
258	100 UINFO 5 255 62
259	101 UID 5 255 61
260	102 GID 5 255 16
261	103 UNSPEC 5 255 63
262	249 TKEY 5 255 58
263	250 TSIG 5 255 59
264	251 IXFR 5 255 22
265	252 AXFR 5 255 7
266	253 MAILB 5 255 27
267	254 MAILA 5 255 26
268	32768 TA 5 255 57
269	32769 DLV 5 255 11
270	\.
271
272	-- Custom types (ab)using the "Private use" range from 65280 to 65534
273	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
274	65280 A+PTR 2 2 2
275	65281 AAAA+PTR 2 4 4
276	65282 PTR template 3 6 2
277	65283 A+PTR template 2 7 2
278	65284 AAAA+PTR template 2 8 2
279	65285 Delegation 2 9 2
280	\.
281
282	CREATE TABLE users (
283	user_id serial NOT NULL,
284	group_id integer DEFAULT 1 NOT NULL,
285	username character varying(60) NOT NULL,
286	"password" character varying(34) NOT NULL,
287	firstname character varying(60),
288	lastname character varying(60),
289	phone character varying(15),
290	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
291	status integer DEFAULT 1 NOT NULL,
292	permission_id integer DEFAULT 1 NOT NULL,
293	inherit_perm boolean DEFAULT true NOT NULL
294	);
295
296	-- create initial default user? may be better to create an "initialize" script or something
297	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
298	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
299	\.
300
301	--
302	-- contraints. add these here so initial data doesn't get added strangely.
303	--
304
305	-- primary keys
306	ALTER TABLE ONLY permissions
307	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
308
309	ALTER TABLE ONLY groups
310	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
311
312	ALTER TABLE ONLY domains
313	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
314
315	ALTER TABLE ONLY default_records
316	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
317
318	ALTER TABLE ONLY records
319	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
320
321	ALTER TABLE ONLY rectypes
322	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
323
324	ALTER TABLE ONLY users
325	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
326
327	ALTER TABLE ONLY users
328	ADD CONSTRAINT uidu UNIQUE (user_id);
329
330	-- foreign keys
331	-- fixme: permissions FK refs
332	ALTER TABLE ONLY locations
333	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
334
335	ALTER TABLE ONLY domains
336	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
337
338	ALTER TABLE ONLY default_records
339	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
340
341	ALTER TABLE ONLY users
342	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
343
344	ALTER TABLE ONLY revzones
345	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
346
347	ALTER TABLE ONLY groups
348	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
349
350	-- set starting sequence numbers, since we've inserted data before they're active
351	-- only set the ones that have data loaded with \copy, and obey the convention
352	-- that comes out of pg_dump
353	SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
354	SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
355	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
356	SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
357	SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
358	SELECT pg_catalog.setval('users_user_id_seq', 1, true);

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

Download in other formats: