Context Navigation

source: trunk/dns.sql@ 99

Last change on this file since 99 was 91, checked in by Kris Deugau, 14 years ago

/trunk

SQL tabledef update:

Tweak log table definition to cope with looooong entries (mainly from AXFR warnings)

DNSDB.pm:

Add domainID and getRecCount subs in DNSDB
Return more user-friendly error on attempting to add a domain with no name
Nitpick-tweak DB calls and SQL formatting in domainName()
Replace placeholderish OK message in importAXFR()

dns.cgi:

Trim some more direct SQL out in reclist
Uninitialized-variable cleanup in AXFR import
Add logging to AXFR import
Don't complain about uninitialized variables in record-display

File size: 8.0 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	);
121
122	-- Types are required. NB: these are vaguely read-only too
123	-- data from http://www.iana.org/assignments/dns-parameters
124	COPY rectypes (val, name, stdflag, listorder) FROM stdin;
125	1 A 1 1
126	2 NS 1 2
127	3 MD 2 255
128	4 MF 2 255
129	5 CNAME 1 6
130	6 SOA 0 8
131	7 MB 3 255
132	8 MG 3 255
133	9 MR 3 255
134	10 NULL 3 255
135	11 WKS 3 255
136	12 PTR 2 4
137	13 HINFO 3 255
138	14 MINFO 3 255
139	15 MX 1 3
140	16 TXT 1 5
141	17 RP 2 255
142	18 AFSDB 3 255
143	19 X25 3 255
144	20 ISDN 3 255
145	21 RT 3 255
146	22 NSAP 3 255
147	23 NSAP-PTR 3 255
148	24 SIG 3 255
149	25 KEY 3 255
150	26 PX 3 255
151	27 GPOS 3 255
152	28 AAAA 2 2
153	29 LOC 3 255
154	30 NXT 3 255
155	31 EID 3 255
156	32 NIMLOC 3 255
157	33 SRV 1 7
158	34 ATMA 3 255
159	35 NAPTR 3 255
160	36 KX 3 255
161	37 CERT 3 255
162	38 A6 3 3
163	39 DNAME 3 255
164	40 SINK 3 255
165	41 OPT 3 255
166	42 APL 3 255
167	43 DS 3 255
168	44 SSHFP 3 255
169	45 IPSECKEY 3 255
170	46 RRSIG 3 255
171	47 NSEC 3 255
172	48 DNSKEY 3 255
173	49 DHCID 3 255
174	50 NSEC3 3 255
175	51 NSEC3PARAM 3 255
176	55 HIP 3 255
177	99 SPF 3 255
178	100 UINFO 3 255
179	101 UID 3 255
180	102 GID 3 255
181	103 UNSPEC 3 255
182	249 TKEY 3 255
183	250 TSIG 3 255
184	251 IXFR 3 255
185	252 AXFR 3 255
186	253 MAILB 3 255
187	254 MAILA 3 255
188	32768 TA 3 255
189	32769 DLV 3 255
190	\.
191
192	CREATE TABLE users (
193	user_id serial NOT NULL,
194	group_id integer DEFAULT 1 NOT NULL,
195	username character varying(60) NOT NULL,
196	"password" character varying(34) NOT NULL,
197	firstname character varying(60),
198	lastname character varying(60),
199	phone character varying(15),
200	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
201	status integer DEFAULT 1 NOT NULL,
202	permission_id integer DEFAULT 1 NOT NULL,
203	inherit_perm boolean DEFAULT true NOT NULL
204	);
205
206	-- create initial default user? may be better to create an "initialize" script or something
207	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
208	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
209	\.
210
211	--
212	-- contraints. add these here so initial data doesn't get added strangely.
213	--
214
215	-- primary keys
216	ALTER TABLE ONLY permissions
217	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
218
219	ALTER TABLE ONLY groups
220	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
221
222	ALTER TABLE ONLY domains
223	ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
224
225	ALTER TABLE ONLY domains
226	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
227
228	ALTER TABLE ONLY default_records
229	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
230
231	ALTER TABLE ONLY records
232	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
233
234	ALTER TABLE ONLY rectypes
235	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
236
237	ALTER TABLE ONLY users
238	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
239
240	ALTER TABLE ONLY users
241	ADD CONSTRAINT uidu UNIQUE (user_id);
242
243	-- foreign keys
244	-- fixme: permissions FK refs
245	ALTER TABLE ONLY domains
246	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
247
248	ALTER TABLE ONLY default_records
249	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
250
251	ALTER TABLE ONLY records
252	ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
253
254	ALTER TABLE ONLY users
255	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
256
257	ALTER TABLE ONLY groups
258	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
259
260	-- set starting sequence numbers, since we've inserted data before they're active
261	SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
262	SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
263	SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
264	SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
265	SELECT pg_catalog.setval('records_record_id_seq', 1, false);
266	SELECT pg_catalog.setval('users_user_id_seq', 2, false);

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

Download in other formats: