source: trunk/dns.sql@ 373

Last change on this file since 373 was 370, checked in by Kris Deugau, 12 years ago

/trunk

Checkpoint; adding location/view support. See #10.

  • add location to menu
  • add table and link fields to initial tabledef and upgrade SQL
  • add listing subs and list page
  • update permissions list and subpage template with new permissions
File size: 10.4 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
9CREATE TABLE misc (
10 misc_id serial NOT NULL,
11 key text DEFAULT '' NOT NULL,
12 value text DEFAULT '' NOT NULL
13);
14
15COPY misc (misc_id, key, value) FROM stdin;
161 dbversion 1.2
17\.
18
19CREATE TABLE locations (
20 location character varying (4) PRIMARY KEY,
21 group_id integer NOT NULL DEFAULT 1,
22 iplist text NOT NULL DEFAULT '',
23 description text NOT NULL DEFAULT ''
24);
25
26CREATE TABLE default_records (
27 record_id serial NOT NULL,
28 group_id integer DEFAULT 1 NOT NULL,
29 host text DEFAULT '' NOT NULL,
30 "type" integer DEFAULT 1 NOT NULL,
31 val text DEFAULT '' NOT NULL,
32 distance integer DEFAULT 0 NOT NULL,
33 weight integer DEFAULT 0 NOT NULL,
34 port integer DEFAULT 0 NOT NULL,
35 ttl integer DEFAULT 86400 NOT NULL,
36 description text
37);
38
39-- default records for the default group
40COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
411 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
422 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
433 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
444 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
455 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
466 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
477 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
48\.
49
50CREATE TABLE default_rev_records (
51 record_id serial NOT NULL,
52 group_id integer DEFAULT 1 NOT NULL,
53 host text DEFAULT '' NOT NULL,
54 "type" integer DEFAULT 1 NOT NULL,
55 val text DEFAULT '' NOT NULL,
56 ttl integer DEFAULT 86400 NOT NULL,
57 description text
58);
59
60COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
611 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
622 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
633 1 ns2.example.com 2 ZONE 7200 \N
644 1 ns1.example.com 2 ZONE 7200 \N
65\.
66
67CREATE TABLE domains (
68 domain_id serial NOT NULL,
69 "domain" character varying(80) NOT NULL PRIMARY KEY,
70 group_id integer DEFAULT 1 NOT NULL,
71 description character varying(255) DEFAULT ''::character varying NOT NULL,
72 status integer DEFAULT 1 NOT NULL,
73 zserial integer,
74 sertype character(1) DEFAULT 'D'::bpchar,
75 changed boolean DEFAULT true NOT NULL,
76 default_location character varying (4) DEFAULT '' NOT NULL
77);
78
79CREATE TABLE revzones (
80 rdns_id serial NOT NULL,
81 revnet cidr NOT NULL PRIMARY KEY,
82 group_id integer DEFAULT 1 NOT NULL,
83 description character varying(255) DEFAULT ''::character varying NOT NULL,
84 status integer DEFAULT 1 NOT NULL,
85 zserial integer,
86 sertype character(1) DEFAULT 'D'::bpchar,
87 changed boolean DEFAULT true NOT NULL,
88 default_location character varying (4) DEFAULT '' NOT NULL
89);
90
91CREATE TABLE groups (
92 group_id serial NOT NULL,
93 parent_group_id integer DEFAULT 1 NOT NULL,
94 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
95 permission_id integer DEFAULT 1 NOT NULL,
96 inherit_perm boolean DEFAULT true NOT NULL
97);
98
99-- Provide a basic default group
100COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1011 1 1 default
102\.
103
104-- entry is text due to possible long entries from AXFR - a domain with "many"
105-- odd records will overflow varchar(200)
106CREATE TABLE log (
107 log_id serial NOT NULL,
108 domain_id integer,
109 rdns_id integer,
110 user_id integer,
111 group_id integer,
112 email character varying(60),
113 name character varying(60),
114 entry text,
115 stamp timestamp with time zone DEFAULT now()
116);
117
118CREATE TABLE permissions (
119 permission_id serial NOT NULL,
120 "admin" boolean DEFAULT false NOT NULL,
121 self_edit boolean DEFAULT false NOT NULL,
122 group_create boolean DEFAULT false NOT NULL,
123 group_edit boolean DEFAULT false NOT NULL,
124 group_delete boolean DEFAULT false NOT NULL,
125 user_create boolean DEFAULT false NOT NULL,
126 user_edit boolean DEFAULT false NOT NULL,
127 user_delete boolean DEFAULT false NOT NULL,
128 domain_create boolean DEFAULT false NOT NULL,
129 domain_edit boolean DEFAULT false NOT NULL,
130 domain_delete boolean DEFAULT false NOT NULL,
131 record_create boolean DEFAULT false NOT NULL,
132 record_edit boolean DEFAULT false NOT NULL,
133 record_delete boolean DEFAULT false NOT NULL,
134 location_create boolean DEFAULT false NOT NULL,
135 location_edit boolean DEFAULT false NOT NULL,
136 location_delete boolean DEFAULT false NOT NULL,
137 location_view boolean DEFAULT false NOT NULL,
138 user_id integer UNIQUE,
139 group_id integer UNIQUE
140);
141
142-- Need *two* basic permissions; one for the initial group, one for the default admin user
143COPY 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;
1441 f f f f f f f f t t t t t t f f f f \N 1
1452 t f f f f f f f f f f f f f f f f f 1 \N
146\.
147
148-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
149CREATE TABLE records (
150 domain_id integer NOT NULL DEFAULT 0,
151 rdns_id integer NOT NULL DEFAULT 0,
152 record_id serial NOT NULL,
153 host text DEFAULT '' NOT NULL,
154 "type" integer DEFAULT 1 NOT NULL,
155 val text DEFAULT '' NOT NULL,
156 distance integer DEFAULT 0 NOT NULL,
157 weight integer DEFAULT 0 NOT NULL,
158 port integer DEFAULT 0 NOT NULL,
159 ttl integer DEFAULT 7200 NOT NULL,
160 description text,
161 default_location character varying (4) DEFAULT '' NOT NULL
162);
163
164CREATE TABLE rectypes (
165 val integer NOT NULL,
166 name character varying(20) NOT NULL,
167 stdflag integer DEFAULT 1 NOT NULL,
168 listorder integer DEFAULT 255 NOT NULL,
169 alphaorder integer DEFAULT 32768 NOT NULL
170);
171
172-- Types are required. NB: these are vaguely read-only too
173-- data from http://www.iana.org/assignments/dns-parameters
174COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1751 A 1 1 1
1762 NS 2 9 37
1773 MD 5 255 29
1784 MF 5 255 30
1795 CNAME 2 11 9
1806 SOA 0 0 53
1817 MB 5 255 28
1828 MG 5 255 31
1839 MR 5 255 33
18410 NULL 5 255 43
18511 WKS 5 255 64
18612 PTR 3 5 46
18713 HINFO 5 255 18
18814 MINFO 5 255 32
18915 MX 1 10 34
19016 TXT 2 12 60
19117 RP 4 255 48
19218 AFSDB 5 255 4
19319 X25 5 255 65
19420 ISDN 5 255 21
19521 RT 5 255 50
19622 NSAP 5 255 38
19723 NSAP-PTR 5 255 39
19824 SIG 5 255 51
19925 KEY 5 255 23
20026 PX 5 255 47
20127 GPOS 5 255 17
20228 AAAA 1 3 3
20329 LOC 5 255 25
20430 NXT 5 255 44
20531 EID 5 255 15
20632 NIMLOC 5 255 36
20733 SRV 1 13 55
20834 ATMA 5 255 6
20935 NAPTR 5 255 35
21036 KX 5 255 24
21137 CERT 5 255 8
21238 A6 5 3 2
21339 DNAME 5 255 12
21440 SINK 5 255 52
21541 OPT 5 255 45
21642 APL 5 255 5
21743 DS 5 255 14
21844 SSHFP 5 255 56
21945 IPSECKEY 5 255 20
22046 RRSIG 5 255 49
22147 NSEC 5 255 40
22248 DNSKEY 5 255 13
22349 DHCID 5 255 10
22450 NSEC3 5 255 41
22551 NSEC3PARAM 5 255 42
22655 HIP 5 255 19
22799 SPF 5 255 54
228100 UINFO 5 255 62
229101 UID 5 255 61
230102 GID 5 255 16
231103 UNSPEC 5 255 63
232249 TKEY 5 255 58
233250 TSIG 5 255 59
234251 IXFR 5 255 22
235252 AXFR 5 255 7
236253 MAILB 5 255 27
237254 MAILA 5 255 26
23832768 TA 5 255 57
23932769 DLV 5 255 11
240\.
241
242-- Custom types (ab)using the "Private use" range from 65280 to 65534
243COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
24465280 A+PTR 2 2 2
24565281 AAAA+PTR 2 4 4
24665282 PTR template 3 6 2
24765283 A+PTR template 2 7 2
24865284 AAAA+PTR template 8 13 2
24965285 Delegation 2 8 2
250\.
251
252CREATE TABLE users (
253 user_id serial NOT NULL,
254 group_id integer DEFAULT 1 NOT NULL,
255 username character varying(60) NOT NULL,
256 "password" character varying(34) NOT NULL,
257 firstname character varying(60),
258 lastname character varying(60),
259 phone character varying(15),
260 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
261 status integer DEFAULT 1 NOT NULL,
262 permission_id integer DEFAULT 1 NOT NULL,
263 inherit_perm boolean DEFAULT true NOT NULL
264);
265
266-- create initial default user? may be better to create an "initialize" script or something
267COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2681 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
269\.
270
271--
272-- contraints. add these here so initial data doesn't get added strangely.
273--
274
275-- primary keys
276ALTER TABLE ONLY permissions
277 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
278
279ALTER TABLE ONLY groups
280 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
281
282ALTER TABLE ONLY domains
283 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
284
285ALTER TABLE ONLY domains
286 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
287
288ALTER TABLE ONLY default_records
289 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
290
291ALTER TABLE ONLY records
292 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
293
294ALTER TABLE ONLY rectypes
295 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
296
297ALTER TABLE ONLY users
298 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
299
300ALTER TABLE ONLY users
301 ADD CONSTRAINT uidu UNIQUE (user_id);
302
303-- foreign keys
304-- fixme: permissions FK refs
305ALTER TABLE ONLY locations
306 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
307
308ALTER TABLE ONLY domains
309 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
310
311ALTER TABLE ONLY revzones
312 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
313
314ALTER TABLE ONLY default_records
315 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
316
317ALTER TABLE ONLY users
318 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
319
320ALTER TABLE ONLY groups
321 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
322
323-- set starting sequence numbers, since we've inserted data before they're active
324SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
325SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
326SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
327SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
328SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
329SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
330SELECT pg_catalog.setval('records_record_id_seq', 1, false);
331SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.