Context Navigation

source: trunk/dns.sql@ 367

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

/trunk

Update initial tabledef and 1.0 -> 1.2 conversion SQL to add "changed"
flag intended to help speed up export of large datasets by not actually
actively exporting all zones each time. Note for tinydns this will
require a static cache directory for a workspace so that unchanged
zones can have their data streamed into the main tinydns data file.

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

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

Download in other formats: