source: trunk/dns.sql@ 405

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

/trunk

Fix thinko in recording tabledef update for record location for new DB. See #10.
Remove duplicate primary key definition in new DB.

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 record_locchg boolean DEFAULT false NOT NULL,
137 location_create boolean DEFAULT false NOT NULL,
138 location_edit boolean DEFAULT false NOT NULL,
139 location_delete boolean DEFAULT false NOT NULL,
140 location_view boolean DEFAULT false NOT NULL,
141 user_id integer UNIQUE,
142 group_id integer UNIQUE
143);
144
145-- Need *two* basic permissions; one for the initial group, one for the default admin user
146COPY 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, record_locchg, location_create, location_edit, location_delete, location_view, user_id, group_id) FROM stdin;
1471 f f f f f f f f t t t t t t f f f f f \N 1
1482 t f f f f f f f f f f f f f f f f f f 1 \N
149\.
150
151-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
152CREATE TABLE records (
153 domain_id integer NOT NULL DEFAULT 0,
154 rdns_id integer NOT NULL DEFAULT 0,
155 record_id serial NOT NULL,
156 host text DEFAULT '' NOT NULL,
157 "type" integer DEFAULT 1 NOT NULL,
158 val text DEFAULT '' NOT NULL,
159 distance integer DEFAULT 0 NOT NULL,
160 weight integer DEFAULT 0 NOT NULL,
161 port integer DEFAULT 0 NOT NULL,
162 ttl integer DEFAULT 7200 NOT NULL,
163 description text,
164 location character varying (4) DEFAULT '' NOT NULL
165);
166
167CREATE TABLE rectypes (
168 val integer NOT NULL,
169 name character varying(20) NOT NULL,
170 stdflag integer DEFAULT 1 NOT NULL,
171 listorder integer DEFAULT 255 NOT NULL,
172 alphaorder integer DEFAULT 32768 NOT NULL
173);
174
175-- Types are required. NB: these are vaguely read-only too
176-- data from http://www.iana.org/assignments/dns-parameters
177COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1781 A 1 1 1
1792 NS 2 9 37
1803 MD 5 255 29
1814 MF 5 255 30
1825 CNAME 2 11 9
1836 SOA 0 0 53
1847 MB 5 255 28
1858 MG 5 255 31
1869 MR 5 255 33
18710 NULL 5 255 43
18811 WKS 5 255 64
18912 PTR 3 5 46
19013 HINFO 5 255 18
19114 MINFO 5 255 32
19215 MX 1 10 34
19316 TXT 2 12 60
19417 RP 4 255 48
19518 AFSDB 5 255 4
19619 X25 5 255 65
19720 ISDN 5 255 21
19821 RT 5 255 50
19922 NSAP 5 255 38
20023 NSAP-PTR 5 255 39
20124 SIG 5 255 51
20225 KEY 5 255 23
20326 PX 5 255 47
20427 GPOS 5 255 17
20528 AAAA 1 3 3
20629 LOC 5 255 25
20730 NXT 5 255 44
20831 EID 5 255 15
20932 NIMLOC 5 255 36
21033 SRV 1 13 55
21134 ATMA 5 255 6
21235 NAPTR 5 255 35
21336 KX 5 255 24
21437 CERT 5 255 8
21538 A6 5 3 2
21639 DNAME 5 255 12
21740 SINK 5 255 52
21841 OPT 5 255 45
21942 APL 5 255 5
22043 DS 5 255 14
22144 SSHFP 5 255 56
22245 IPSECKEY 5 255 20
22346 RRSIG 5 255 49
22447 NSEC 5 255 40
22548 DNSKEY 5 255 13
22649 DHCID 5 255 10
22750 NSEC3 5 255 41
22851 NSEC3PARAM 5 255 42
22955 HIP 5 255 19
23099 SPF 5 255 54
231100 UINFO 5 255 62
232101 UID 5 255 61
233102 GID 5 255 16
234103 UNSPEC 5 255 63
235249 TKEY 5 255 58
236250 TSIG 5 255 59
237251 IXFR 5 255 22
238252 AXFR 5 255 7
239253 MAILB 5 255 27
240254 MAILA 5 255 26
24132768 TA 5 255 57
24232769 DLV 5 255 11
243\.
244
245-- Custom types (ab)using the "Private use" range from 65280 to 65534
246COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
24765280 A+PTR 2 2 2
24865281 AAAA+PTR 2 4 4
24965282 PTR template 3 6 2
25065283 A+PTR template 2 7 2
25165284 AAAA+PTR template 8 13 2
25265285 Delegation 2 8 2
253\.
254
255CREATE TABLE users (
256 user_id serial NOT NULL,
257 group_id integer DEFAULT 1 NOT NULL,
258 username character varying(60) NOT NULL,
259 "password" character varying(34) NOT NULL,
260 firstname character varying(60),
261 lastname character varying(60),
262 phone character varying(15),
263 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
264 status integer DEFAULT 1 NOT NULL,
265 permission_id integer DEFAULT 1 NOT NULL,
266 inherit_perm boolean DEFAULT true NOT NULL
267);
268
269-- create initial default user? may be better to create an "initialize" script or something
270COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2711 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
272\.
273
274--
275-- contraints. add these here so initial data doesn't get added strangely.
276--
277
278-- primary keys
279ALTER TABLE ONLY permissions
280 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
281
282ALTER TABLE ONLY groups
283 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
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.