Context Navigation

source: trunk/dns.sql@ 109

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

/trunk

Add alphaorder column in rectypes table; this fixes the data source

for "properly" sorting by type (alphabetically vs by value)

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

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

Download in other formats: