Context Navigation

source: trunk/dns.sql@ 86

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

/trunk

Tweak initial SQL definitions further so it actually works out of the box

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

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

Download in other formats: