Context Navigation

source: trunk/dns.sql@ 405

Last change on this file since 405 was 395, checked in by Kris Deugau, 12 years ago

/trunk

Fix thinko in recording tabledef update for record location for new DB. See #10.
Remove duplicate primary key definition in new DB.

File size: 10.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	-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
9	CREATE TABLE misc (
10	misc_id serial NOT NULL,
11	key text DEFAULT '' NOT NULL,
12	value text DEFAULT '' NOT NULL
13	);
14
15	COPY misc (misc_id, key, value) FROM stdin;
16	1 dbversion 1.2
17	\.
18
19	CREATE TABLE locations (
20	location character varying (4) PRIMARY KEY,
21	loc_id serial UNIQUE,
22	group_id integer NOT NULL DEFAULT 1,
23	iplist text NOT NULL DEFAULT '',
24	description character varying(40) NOT NULL DEFAULT '',
25	comments text NOT NULL DEFAULT ''
26	);
27
28	CREATE TABLE default_records (
29	record_id serial NOT NULL,
30	group_id integer DEFAULT 1 NOT NULL,
31	host text DEFAULT '' NOT NULL,
32	"type" integer DEFAULT 1 NOT NULL,
33	val text DEFAULT '' NOT NULL,
34	distance integer DEFAULT 0 NOT NULL,
35	weight integer DEFAULT 0 NOT NULL,
36	port integer DEFAULT 0 NOT NULL,
37	ttl integer DEFAULT 86400 NOT NULL,
38	description text
39	);
40
41	-- default records for the default group
42	COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
43	1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
44	2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
45	3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
46	4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
47	5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
48	6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
49	7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
50	\.
51
52	CREATE TABLE default_rev_records (
53	record_id serial NOT NULL,
54	group_id integer DEFAULT 1 NOT NULL,
55	host text DEFAULT '' NOT NULL,
56	"type" integer DEFAULT 1 NOT NULL,
57	val text DEFAULT '' NOT NULL,
58	ttl integer DEFAULT 86400 NOT NULL,
59	description text
60	);
61
62	COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
63	1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
64	2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
65	3 1 ns2.example.com 2 ZONE 7200 \N
66	4 1 ns1.example.com 2 ZONE 7200 \N
67	\.
68
69	CREATE TABLE domains (
70	domain_id serial NOT NULL,
71	"domain" character varying(80) NOT NULL PRIMARY KEY,
72	group_id integer DEFAULT 1 NOT NULL,
73	description character varying(255) DEFAULT ''::character varying NOT NULL,
74	status integer DEFAULT 1 NOT NULL,
75	zserial integer,
76	sertype character(1) DEFAULT 'D'::bpchar,
77	changed boolean DEFAULT true NOT NULL,
78	default_location character varying (4) DEFAULT '' NOT NULL
79	);
80
81	CREATE TABLE revzones (
82	rdns_id serial NOT NULL,
83	revnet cidr NOT NULL PRIMARY KEY,
84	group_id integer DEFAULT 1 NOT NULL,
85	description character varying(255) DEFAULT ''::character varying NOT NULL,
86	status integer DEFAULT 1 NOT NULL,
87	zserial integer,
88	sertype character(1) DEFAULT 'D'::bpchar,
89	changed boolean DEFAULT true NOT NULL,
90	default_location character varying (4) DEFAULT '' NOT NULL
91	);
92
93	CREATE TABLE groups (
94	group_id serial NOT NULL,
95	parent_group_id integer DEFAULT 1 NOT NULL,
96	group_name character varying(255) DEFAULT ''::character varying NOT NULL,
97	permission_id integer DEFAULT 1 NOT NULL,
98	inherit_perm boolean DEFAULT true NOT NULL
99	);
100
101	-- Provide a basic default group
102	COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
103	1 1 1 default
104	\.
105
106	-- entry is text due to possible long entries from AXFR - a domain with "many"
107	-- odd records will overflow varchar(200)
108	CREATE TABLE log (
109	log_id serial NOT NULL,
110	domain_id integer,
111	rdns_id integer,
112	user_id integer,
113	group_id integer,
114	email character varying(60),
115	name character varying(60),
116	entry text,
117	stamp timestamp with time zone DEFAULT now()
118	);
119
120	CREATE TABLE permissions (
121	permission_id serial NOT NULL,
122	"admin" boolean DEFAULT false NOT NULL,
123	self_edit boolean DEFAULT false NOT NULL,
124	group_create boolean DEFAULT false NOT NULL,
125	group_edit boolean DEFAULT false NOT NULL,
126	group_delete boolean DEFAULT false NOT NULL,
127	user_create boolean DEFAULT false NOT NULL,
128	user_edit boolean DEFAULT false NOT NULL,
129	user_delete boolean DEFAULT false NOT NULL,
130	domain_create boolean DEFAULT false NOT NULL,
131	domain_edit boolean DEFAULT false NOT NULL,
132	domain_delete boolean DEFAULT false NOT NULL,
133	record_create boolean DEFAULT false NOT NULL,
134	record_edit boolean DEFAULT false NOT NULL,
135	record_delete boolean DEFAULT false NOT NULL,
136	record_locchg boolean DEFAULT false NOT NULL,
137	location_create boolean DEFAULT false NOT NULL,
138	location_edit boolean DEFAULT false NOT NULL,
139	location_delete boolean DEFAULT false NOT NULL,
140	location_view boolean DEFAULT false NOT NULL,
141	user_id integer UNIQUE,
142	group_id integer UNIQUE
143	);
144
145	-- Need two basic permissions; one for the initial group, one for the default admin user
146	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, record_locchg, location_create, location_edit, location_delete, location_view, user_id, group_id) FROM stdin;
147	1 f f f f f f f f t t t t t t f f f f f \N 1
148	2 t f f f f f f f f f f f f f f f f f f 1 \N
149	\.
150
151	-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
152	CREATE TABLE records (
153	domain_id integer NOT NULL DEFAULT 0,
154	rdns_id integer NOT NULL DEFAULT 0,
155	record_id serial NOT NULL,
156	host text DEFAULT '' NOT NULL,
157	"type" integer DEFAULT 1 NOT NULL,
158	val text DEFAULT '' NOT NULL,
159	distance integer DEFAULT 0 NOT NULL,
160	weight integer DEFAULT 0 NOT NULL,
161	port integer DEFAULT 0 NOT NULL,
162	ttl integer DEFAULT 7200 NOT NULL,
163	description text,
164	location character varying (4) DEFAULT '' NOT NULL
165	);
166
167	CREATE TABLE rectypes (
168	val integer NOT NULL,
169	name character varying(20) NOT NULL,
170	stdflag integer DEFAULT 1 NOT NULL,
171	listorder integer DEFAULT 255 NOT NULL,
172	alphaorder integer DEFAULT 32768 NOT NULL
173	);
174
175	-- Types are required. NB: these are vaguely read-only too
176	-- data from http://www.iana.org/assignments/dns-parameters
177	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
178	1 A 1 1 1
179	2 NS 2 9 37
180	3 MD 5 255 29
181	4 MF 5 255 30
182	5 CNAME 2 11 9
183	6 SOA 0 0 53
184	7 MB 5 255 28
185	8 MG 5 255 31
186	9 MR 5 255 33
187	10 NULL 5 255 43
188	11 WKS 5 255 64
189	12 PTR 3 5 46
190	13 HINFO 5 255 18
191	14 MINFO 5 255 32
192	15 MX 1 10 34
193	16 TXT 2 12 60
194	17 RP 4 255 48
195	18 AFSDB 5 255 4
196	19 X25 5 255 65
197	20 ISDN 5 255 21
198	21 RT 5 255 50
199	22 NSAP 5 255 38
200	23 NSAP-PTR 5 255 39
201	24 SIG 5 255 51
202	25 KEY 5 255 23
203	26 PX 5 255 47
204	27 GPOS 5 255 17
205	28 AAAA 1 3 3
206	29 LOC 5 255 25
207	30 NXT 5 255 44
208	31 EID 5 255 15
209	32 NIMLOC 5 255 36
210	33 SRV 1 13 55
211	34 ATMA 5 255 6
212	35 NAPTR 5 255 35
213	36 KX 5 255 24
214	37 CERT 5 255 8
215	38 A6 5 3 2
216	39 DNAME 5 255 12
217	40 SINK 5 255 52
218	41 OPT 5 255 45
219	42 APL 5 255 5
220	43 DS 5 255 14
221	44 SSHFP 5 255 56
222	45 IPSECKEY 5 255 20
223	46 RRSIG 5 255 49
224	47 NSEC 5 255 40
225	48 DNSKEY 5 255 13
226	49 DHCID 5 255 10
227	50 NSEC3 5 255 41
228	51 NSEC3PARAM 5 255 42
229	55 HIP 5 255 19
230	99 SPF 5 255 54
231	100 UINFO 5 255 62
232	101 UID 5 255 61
233	102 GID 5 255 16
234	103 UNSPEC 5 255 63
235	249 TKEY 5 255 58
236	250 TSIG 5 255 59
237	251 IXFR 5 255 22
238	252 AXFR 5 255 7
239	253 MAILB 5 255 27
240	254 MAILA 5 255 26
241	32768 TA 5 255 57
242	32769 DLV 5 255 11
243	\.
244
245	-- Custom types (ab)using the "Private use" range from 65280 to 65534
246	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
247	65280 A+PTR 2 2 2
248	65281 AAAA+PTR 2 4 4
249	65282 PTR template 3 6 2
250	65283 A+PTR template 2 7 2
251	65284 AAAA+PTR template 8 13 2
252	65285 Delegation 2 8 2
253	\.
254
255	CREATE TABLE users (
256	user_id serial NOT NULL,
257	group_id integer DEFAULT 1 NOT NULL,
258	username character varying(60) NOT NULL,
259	"password" character varying(34) NOT NULL,
260	firstname character varying(60),
261	lastname character varying(60),
262	phone character varying(15),
263	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
264	status integer DEFAULT 1 NOT NULL,
265	permission_id integer DEFAULT 1 NOT NULL,
266	inherit_perm boolean DEFAULT true NOT NULL
267	);
268
269	-- create initial default user? may be better to create an "initialize" script or something
270	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
271	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
272	\.
273
274	--
275	-- contraints. add these here so initial data doesn't get added strangely.
276	--
277
278	-- primary keys
279	ALTER TABLE ONLY permissions
280	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
281
282	ALTER TABLE ONLY groups
283	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
284
285	ALTER TABLE ONLY domains
286	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
287
288	ALTER TABLE ONLY default_records
289	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
290
291	ALTER TABLE ONLY records
292	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
293
294	ALTER TABLE ONLY rectypes
295	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
296
297	ALTER TABLE ONLY users
298	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
299
300	ALTER TABLE ONLY users
301	ADD CONSTRAINT uidu UNIQUE (user_id);
302
303	-- foreign keys
304	-- fixme: permissions FK refs
305	ALTER TABLE ONLY locations
306	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
307
308	ALTER TABLE ONLY domains
309	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
310
311	ALTER TABLE ONLY revzones
312	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
313
314	ALTER TABLE ONLY default_records
315	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
316
317	ALTER TABLE ONLY users
318	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
319
320	ALTER TABLE ONLY groups
321	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
322
323	-- set starting sequence numbers, since we've inserted data before they're active
324	SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
325	SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
326	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
327	SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
328	SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
329	SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
330	SELECT pg_catalog.setval('records_record_id_seq', 1, false);
331	SELECT pg_catalog.setval('users_user_id_seq', 2, false);

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

Download in other formats: