source: trunk/dns.sql@ 377

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

/trunk

Checkpoint adding locations/views. See #10.

  • Tweak new locations table again with an internal serial for sorting and finding the "highest" previous location
  • Complete "Add location" stub
  • Fill in initial display page for "Edit location"
  • Tweak location add/edit/update template a little, add new comments field to match new field in table
  • Tweak location import so we don't end up with great long space-free fields similar to what can't be avoided with TXT (usually SPF/DomainKeys) records
  • Fill in addLoc() stub
  • Add getLoc()
  • Tweak location export to strip commas and whitespace properly
File size: 10.5 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 loc_id serial UNIQUE,
22 group_id integer NOT NULL DEFAULT 1,
23 iplist text NOT NULL DEFAULT '',
24 description character varying(40) NOT NULL DEFAULT '',
25 comments text NOT NULL DEFAULT ''
26);
27
28CREATE TABLE default_records (
29 record_id serial NOT NULL,
30 group_id integer DEFAULT 1 NOT NULL,
31 host text DEFAULT '' NOT NULL,
32 "type" integer DEFAULT 1 NOT NULL,
33 val text DEFAULT '' NOT NULL,
34 distance integer DEFAULT 0 NOT NULL,
35 weight integer DEFAULT 0 NOT NULL,
36 port integer DEFAULT 0 NOT NULL,
37 ttl integer DEFAULT 86400 NOT NULL,
38 description text
39);
40
41-- default records for the default group
42COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
431 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
442 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
453 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
464 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
475 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
486 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
497 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
50\.
51
52CREATE TABLE default_rev_records (
53 record_id serial NOT NULL,
54 group_id integer DEFAULT 1 NOT NULL,
55 host text DEFAULT '' NOT NULL,
56 "type" integer DEFAULT 1 NOT NULL,
57 val text DEFAULT '' NOT NULL,
58 ttl integer DEFAULT 86400 NOT NULL,
59 description text
60);
61
62COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
631 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
642 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
653 1 ns2.example.com 2 ZONE 7200 \N
664 1 ns1.example.com 2 ZONE 7200 \N
67\.
68
69CREATE TABLE domains (
70 domain_id serial NOT NULL,
71 "domain" character varying(80) NOT NULL PRIMARY KEY,
72 group_id integer DEFAULT 1 NOT NULL,
73 description character varying(255) DEFAULT ''::character varying NOT NULL,
74 status integer DEFAULT 1 NOT NULL,
75 zserial integer,
76 sertype character(1) DEFAULT 'D'::bpchar,
77 changed boolean DEFAULT true NOT NULL,
78 default_location character varying (4) DEFAULT '' NOT NULL
79);
80
81CREATE TABLE revzones (
82 rdns_id serial NOT NULL,
83 revnet cidr NOT NULL PRIMARY KEY,
84 group_id integer DEFAULT 1 NOT NULL,
85 description character varying(255) DEFAULT ''::character varying NOT NULL,
86 status integer DEFAULT 1 NOT NULL,
87 zserial integer,
88 sertype character(1) DEFAULT 'D'::bpchar,
89 changed boolean DEFAULT true NOT NULL,
90 default_location character varying (4) DEFAULT '' NOT NULL
91);
92
93CREATE TABLE groups (
94 group_id serial NOT NULL,
95 parent_group_id integer DEFAULT 1 NOT NULL,
96 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
97 permission_id integer DEFAULT 1 NOT NULL,
98 inherit_perm boolean DEFAULT true NOT NULL
99);
100
101-- Provide a basic default group
102COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1031 1 1 default
104\.
105
106-- entry is text due to possible long entries from AXFR - a domain with "many"
107-- odd records will overflow varchar(200)
108CREATE TABLE log (
109 log_id serial NOT NULL,
110 domain_id integer,
111 rdns_id integer,
112 user_id integer,
113 group_id integer,
114 email character varying(60),
115 name character varying(60),
116 entry text,
117 stamp timestamp with time zone DEFAULT now()
118);
119
120CREATE TABLE permissions (
121 permission_id serial NOT NULL,
122 "admin" boolean DEFAULT false NOT NULL,
123 self_edit boolean DEFAULT false NOT NULL,
124 group_create boolean DEFAULT false NOT NULL,
125 group_edit boolean DEFAULT false NOT NULL,
126 group_delete boolean DEFAULT false NOT NULL,
127 user_create boolean DEFAULT false NOT NULL,
128 user_edit boolean DEFAULT false NOT NULL,
129 user_delete boolean DEFAULT false NOT NULL,
130 domain_create boolean DEFAULT false NOT NULL,
131 domain_edit boolean DEFAULT false NOT NULL,
132 domain_delete boolean DEFAULT false NOT NULL,
133 record_create boolean DEFAULT false NOT NULL,
134 record_edit boolean DEFAULT false NOT NULL,
135 record_delete boolean DEFAULT false NOT NULL,
136 location_create boolean DEFAULT false NOT NULL,
137 location_edit boolean DEFAULT false NOT NULL,
138 location_delete boolean DEFAULT false NOT NULL,
139 location_view boolean DEFAULT false NOT NULL,
140 user_id integer UNIQUE,
141 group_id integer UNIQUE
142);
143
144-- Need *two* basic permissions; one for the initial group, one for the default admin user
145COPY 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;
1461 f f f f f f f f t t t t t t f f f f \N 1
1472 t f f f f f f f f f f f f f f f f f 1 \N
148\.
149
150-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
151CREATE TABLE records (
152 domain_id integer NOT NULL DEFAULT 0,
153 rdns_id integer NOT NULL DEFAULT 0,
154 record_id serial NOT NULL,
155 host text DEFAULT '' NOT NULL,
156 "type" integer DEFAULT 1 NOT NULL,
157 val text DEFAULT '' NOT NULL,
158 distance integer DEFAULT 0 NOT NULL,
159 weight integer DEFAULT 0 NOT NULL,
160 port integer DEFAULT 0 NOT NULL,
161 ttl integer DEFAULT 7200 NOT NULL,
162 description text,
163 default_location character varying (4) DEFAULT '' NOT NULL
164);
165
166CREATE TABLE rectypes (
167 val integer NOT NULL,
168 name character varying(20) NOT NULL,
169 stdflag integer DEFAULT 1 NOT NULL,
170 listorder integer DEFAULT 255 NOT NULL,
171 alphaorder integer DEFAULT 32768 NOT NULL
172);
173
174-- Types are required. NB: these are vaguely read-only too
175-- data from http://www.iana.org/assignments/dns-parameters
176COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1771 A 1 1 1
1782 NS 2 9 37
1793 MD 5 255 29
1804 MF 5 255 30
1815 CNAME 2 11 9
1826 SOA 0 0 53
1837 MB 5 255 28
1848 MG 5 255 31
1859 MR 5 255 33
18610 NULL 5 255 43
18711 WKS 5 255 64
18812 PTR 3 5 46
18913 HINFO 5 255 18
19014 MINFO 5 255 32
19115 MX 1 10 34
19216 TXT 2 12 60
19317 RP 4 255 48
19418 AFSDB 5 255 4
19519 X25 5 255 65
19620 ISDN 5 255 21
19721 RT 5 255 50
19822 NSAP 5 255 38
19923 NSAP-PTR 5 255 39
20024 SIG 5 255 51
20125 KEY 5 255 23
20226 PX 5 255 47
20327 GPOS 5 255 17
20428 AAAA 1 3 3
20529 LOC 5 255 25
20630 NXT 5 255 44
20731 EID 5 255 15
20832 NIMLOC 5 255 36
20933 SRV 1 13 55
21034 ATMA 5 255 6
21135 NAPTR 5 255 35
21236 KX 5 255 24
21337 CERT 5 255 8
21438 A6 5 3 2
21539 DNAME 5 255 12
21640 SINK 5 255 52
21741 OPT 5 255 45
21842 APL 5 255 5
21943 DS 5 255 14
22044 SSHFP 5 255 56
22145 IPSECKEY 5 255 20
22246 RRSIG 5 255 49
22347 NSEC 5 255 40
22448 DNSKEY 5 255 13
22549 DHCID 5 255 10
22650 NSEC3 5 255 41
22751 NSEC3PARAM 5 255 42
22855 HIP 5 255 19
22999 SPF 5 255 54
230100 UINFO 5 255 62
231101 UID 5 255 61
232102 GID 5 255 16
233103 UNSPEC 5 255 63
234249 TKEY 5 255 58
235250 TSIG 5 255 59
236251 IXFR 5 255 22
237252 AXFR 5 255 7
238253 MAILB 5 255 27
239254 MAILA 5 255 26
24032768 TA 5 255 57
24132769 DLV 5 255 11
242\.
243
244-- Custom types (ab)using the "Private use" range from 65280 to 65534
245COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
24665280 A+PTR 2 2 2
24765281 AAAA+PTR 2 4 4
24865282 PTR template 3 6 2
24965283 A+PTR template 2 7 2
25065284 AAAA+PTR template 8 13 2
25165285 Delegation 2 8 2
252\.
253
254CREATE TABLE users (
255 user_id serial NOT NULL,
256 group_id integer DEFAULT 1 NOT NULL,
257 username character varying(60) NOT NULL,
258 "password" character varying(34) NOT NULL,
259 firstname character varying(60),
260 lastname character varying(60),
261 phone character varying(15),
262 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
263 status integer DEFAULT 1 NOT NULL,
264 permission_id integer DEFAULT 1 NOT NULL,
265 inherit_perm boolean DEFAULT true NOT NULL
266);
267
268-- create initial default user? may be better to create an "initialize" script or something
269COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2701 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
271\.
272
273--
274-- contraints. add these here so initial data doesn't get added strangely.
275--
276
277-- primary keys
278ALTER TABLE ONLY permissions
279 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
280
281ALTER TABLE ONLY groups
282 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
283
284ALTER TABLE ONLY domains
285 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
286
287ALTER TABLE ONLY domains
288 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
289
290ALTER TABLE ONLY default_records
291 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
292
293ALTER TABLE ONLY records
294 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
295
296ALTER TABLE ONLY rectypes
297 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
298
299ALTER TABLE ONLY users
300 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
301
302ALTER TABLE ONLY users
303 ADD CONSTRAINT uidu UNIQUE (user_id);
304
305-- foreign keys
306-- fixme: permissions FK refs
307ALTER TABLE ONLY locations
308 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
309
310ALTER TABLE ONLY domains
311 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
312
313ALTER TABLE ONLY revzones
314 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
315
316ALTER TABLE ONLY default_records
317 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
318
319ALTER TABLE ONLY users
320 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
321
322ALTER TABLE ONLY groups
323 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
324
325-- set starting sequence numbers, since we've inserted data before they're active
326SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
327SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
328SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
329SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
330SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
331SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
332SELECT pg_catalog.setval('records_record_id_seq', 1, false);
333SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.