Context Navigation

source: trunk/dns.sql@ 214

Last change on this file since 214 was 212, checked in by Kris Deugau, 13 years ago

/trunk

Begin setting up the barest sketch of what will eventually become
a complete automagical DB init/upgrade feature. See #34.

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

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

Download in other formats: