Context Navigation

source: trunk/dns.sql@ 89

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

/trunk

SQL tabledef updates:

references and definition for longrecs table to contain data for records that need >100 chars
Basic initial group permissions switched to include domain and record add/delete/update
Initial admin user created with a somewhat better username, and first/last name filled in

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

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

Download in other formats: