Context Navigation

← Previous Revision
Next Revision →
Blame
Revision Log

source: trunk/dns.sql

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

/trunk

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

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

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

Context Navigation

source: trunk/dns.sql

Download in other formats: