source: trunk/dns.sql@ 507

Last change on this file since 507 was 507, checked in by Kris Deugau, 11 years ago

/trunk

Re-re-re-review and tweak the new-database initialization dns.sql script
and the dns-1.0-1.2.sql upgrade script to ensure the resulting structure
is as close as possible to identical to minimize any possible breakage.

Creating a database with the 1.0 SQL init, then applying the 1.0-1.2
upgrade script, will result in pg_dump output with only one difference;
the setval for the sequence on user_id in the users table. Sematically,
there is no difference in the database behaviour (the first new user
created will have ID 2), but the changes make it easier to copy-paste
pg_dump schema fragments if further updates to the default schema are
required.
File size: 10.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-- 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-- ~2x performance boost iff most zones are fed to output from the cache
81CREATE INDEX dom_status_index ON domains (status);
82
83
84CREATE TABLE revzones (
85 rdns_id serial NOT NULL,
86 revnet cidr NOT NULL PRIMARY KEY,
87 group_id integer DEFAULT 1 NOT NULL,
88 description character varying(255) DEFAULT ''::character varying NOT NULL,
89 status integer DEFAULT 1 NOT NULL,
90 zserial integer,
91 sertype character(1) DEFAULT 'D'::bpchar,
92 changed boolean DEFAULT true NOT NULL,
93 default_location character varying (4) DEFAULT '' NOT NULL
94);
95CREATE INDEX rev_status_index ON revzones USING btree (status);
96
97CREATE TABLE groups (
98 group_id serial NOT NULL,
99 parent_group_id integer DEFAULT 1 NOT NULL,
100 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
101 permission_id integer DEFAULT 1 NOT NULL,
102 inherit_perm boolean DEFAULT true NOT NULL
103);
104
105-- Provide a basic default group
106COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
1071 1 1 default
108\.
109
110-- entry is text due to possible long entries from AXFR - a domain with "many"
111-- odd records will overflow varchar(200)
112CREATE TABLE log (
113 log_id serial NOT NULL,
114 domain_id integer,
115 user_id integer,
116 group_id integer,
117 email character varying(60),
118 name character varying(60),
119 entry text,
120 stamp timestamp with time zone DEFAULT now(),
121 rdns_id integer
122);
123
124CREATE TABLE permissions (
125 permission_id serial NOT NULL,
126 "admin" boolean DEFAULT false NOT NULL,
127 self_edit boolean DEFAULT false NOT NULL,
128 group_create boolean DEFAULT false NOT NULL,
129 group_edit boolean DEFAULT false NOT NULL,
130 group_delete boolean DEFAULT false NOT NULL,
131 user_create boolean DEFAULT false NOT NULL,
132 user_edit boolean DEFAULT false NOT NULL,
133 user_delete boolean DEFAULT false NOT NULL,
134 domain_create boolean DEFAULT false NOT NULL,
135 domain_edit boolean DEFAULT false NOT NULL,
136 domain_delete boolean DEFAULT false NOT NULL,
137 record_create boolean DEFAULT false NOT NULL,
138 record_edit boolean DEFAULT false NOT NULL,
139 record_delete boolean DEFAULT false NOT NULL,
140 user_id integer UNIQUE,
141 group_id integer UNIQUE,
142 record_locchg boolean DEFAULT false NOT NULL,
143 location_create boolean DEFAULT false NOT NULL,
144 location_edit boolean DEFAULT false NOT NULL,
145 location_delete boolean DEFAULT false NOT NULL,
146 location_view boolean DEFAULT false NOT NULL
147);
148
149-- Need *two* basic permissions; one for the initial group, one for the default admin user
150COPY 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, record_locchg, location_create, location_edit, location_delete, location_view) FROM stdin;
1511 f f f f f f f f t t t t t t \N 1 f f f f f
1522 t f f f f f f f f f f f f f 1 \N f f f f f
153\.
154
155-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
156CREATE TABLE records (
157 domain_id integer NOT NULL DEFAULT 0,
158 record_id serial NOT NULL,
159 host text DEFAULT '' NOT NULL,
160 "type" integer DEFAULT 1 NOT NULL,
161 val text DEFAULT '' NOT NULL,
162 distance integer DEFAULT 0 NOT NULL,
163 weight integer DEFAULT 0 NOT NULL,
164 port integer DEFAULT 0 NOT NULL,
165 ttl integer DEFAULT 7200 NOT NULL,
166 description text,
167 rdns_id integer NOT NULL DEFAULT 0,
168 location character varying (4) DEFAULT '' NOT NULL
169);
170CREATE INDEX rec_domain_index ON records USING btree (domain_id);
171CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
172CREATE INDEX rec_types_index ON records USING btree ("type");
173
174CREATE TABLE rectypes (
175 val integer NOT NULL,
176 name character varying(20) NOT NULL,
177 stdflag integer DEFAULT 1 NOT NULL,
178 listorder integer DEFAULT 255 NOT NULL,
179 alphaorder integer DEFAULT 32768 NOT NULL
180);
181
182-- Types are required. NB: these are vaguely read-only too
183-- data from http://www.iana.org/assignments/dns-parameters
184COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1851 A 1 1 1
1862 NS 2 10 37
1873 MD 5 255 29
1884 MF 5 255 30
1895 CNAME 2 12 9
1906 SOA 0 0 53
1917 MB 5 255 28
1928 MG 5 255 31
1939 MR 5 255 33
19410 NULL 5 255 43
19511 WKS 5 255 64
19612 PTR 3 5 46
19713 HINFO 5 255 18
19814 MINFO 5 255 32
19915 MX 1 11 34
20016 TXT 2 13 60
20117 RP 4 255 48
20218 AFSDB 5 255 4
20319 X25 5 255 65
20420 ISDN 5 255 21
20521 RT 5 255 50
20622 NSAP 5 255 38
20723 NSAP-PTR 5 255 39
20824 SIG 5 255 51
20925 KEY 5 255 23
21026 PX 5 255 47
21127 GPOS 5 255 17
21228 AAAA 1 3 3
21329 LOC 5 255 25
21430 NXT 5 255 44
21531 EID 5 255 15
21632 NIMLOC 5 255 36
21733 SRV 1 14 55
21834 ATMA 5 255 6
21935 NAPTR 5 255 35
22036 KX 5 255 24
22137 CERT 5 255 8
22238 A6 5 3 2
22339 DNAME 5 255 12
22440 SINK 5 255 52
22541 OPT 5 255 45
22642 APL 5 255 5
22743 DS 5 255 14
22844 SSHFP 5 255 56
22945 IPSECKEY 5 255 20
23046 RRSIG 5 255 49
23147 NSEC 5 255 40
23248 DNSKEY 5 255 13
23349 DHCID 5 255 10
23450 NSEC3 5 255 41
23551 NSEC3PARAM 5 255 42
23655 HIP 5 255 19
23799 SPF 5 255 54
238100 UINFO 5 255 62
239101 UID 5 255 61
240102 GID 5 255 16
241103 UNSPEC 5 255 63
242249 TKEY 5 255 58
243250 TSIG 5 255 59
244251 IXFR 5 255 22
245252 AXFR 5 255 7
246253 MAILB 5 255 27
247254 MAILA 5 255 26
24832768 TA 5 255 57
24932769 DLV 5 255 11
250\.
251
252-- Custom types (ab)using the "Private use" range from 65280 to 65534
253COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
25465280 A+PTR 2 2 2
25565281 AAAA+PTR 2 4 4
25665282 PTR template 3 6 2
25765283 A+PTR template 2 7 2
25865284 AAAA+PTR template 2 8 2
25965285 Delegation 2 9 2
260\.
261
262CREATE TABLE users (
263 user_id serial NOT NULL,
264 group_id integer DEFAULT 1 NOT NULL,
265 username character varying(60) NOT NULL,
266 "password" character varying(34) NOT NULL,
267 firstname character varying(60),
268 lastname character varying(60),
269 phone character varying(15),
270 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
271 status integer DEFAULT 1 NOT NULL,
272 permission_id integer DEFAULT 1 NOT NULL,
273 inherit_perm boolean DEFAULT true NOT NULL
274);
275
276-- create initial default user? may be better to create an "initialize" script or something
277COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2781 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
279\.
280
281--
282-- contraints. add these here so initial data doesn't get added strangely.
283--
284
285-- primary keys
286ALTER TABLE ONLY permissions
287 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
288
289ALTER TABLE ONLY groups
290 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
291
292ALTER TABLE ONLY domains
293 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
294
295ALTER TABLE ONLY default_records
296 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
297
298ALTER TABLE ONLY records
299 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
300
301ALTER TABLE ONLY rectypes
302 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
303
304ALTER TABLE ONLY users
305 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
306
307ALTER TABLE ONLY users
308 ADD CONSTRAINT uidu UNIQUE (user_id);
309
310-- foreign keys
311-- fixme: permissions FK refs
312ALTER TABLE ONLY locations
313 ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
314
315ALTER TABLE ONLY domains
316 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
317
318ALTER TABLE ONLY default_records
319 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
320
321ALTER TABLE ONLY users
322 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
323
324ALTER TABLE ONLY revzones
325 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
326
327ALTER TABLE ONLY groups
328 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
329
330-- set starting sequence numbers, since we've inserted data before they're active
331-- only set the ones that have data loaded with \copy, and obey the convention
332-- that comes out of pg_dump
333SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
334SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
335SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
336SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
337SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
338SELECT pg_catalog.setval('users_user_id_seq', 1, true);
Note: See TracBrowser for help on using the repository browser.