source: trunk/dns.sql@ 367

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

/trunk

Update initial tabledef and 1.0 -> 1.2 conversion SQL to add "changed"
flag intended to help speed up export of large datasets by not actually
actively exporting all zones each time. Note for tinydns this will
require a static cache directory for a workspace so that unchanged
zones can have their data streamed into the main tinydns data file.

File size: 9.6 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 default_records (
20 record_id serial NOT NULL,
21 group_id integer DEFAULT 1 NOT NULL,
22 host text DEFAULT '' NOT NULL,
23 "type" integer DEFAULT 1 NOT NULL,
24 val text DEFAULT '' NOT NULL,
25 distance integer DEFAULT 0 NOT NULL,
26 weight integer DEFAULT 0 NOT NULL,
27 port integer DEFAULT 0 NOT NULL,
28 ttl integer DEFAULT 86400 NOT NULL,
29 description text
30);
31
32-- default records for the default group
33COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
341 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
352 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
363 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
374 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
385 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
396 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
407 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
41\.
42
43CREATE TABLE default_rev_records (
44 record_id serial NOT NULL,
45 group_id integer DEFAULT 1 NOT NULL,
46 host text DEFAULT '' NOT NULL,
47 "type" integer DEFAULT 1 NOT NULL,
48 val text DEFAULT '' NOT NULL,
49 ttl integer DEFAULT 86400 NOT NULL,
50 description text
51);
52
53COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
541 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
552 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
563 1 ns2.example.com 2 ZONE 7200 \N
574 1 ns1.example.com 2 ZONE 7200 \N
58\.
59
60CREATE TABLE domains (
61 domain_id serial NOT NULL,
62 "domain" character varying(80) NOT NULL PRIMARY KEY,
63 group_id integer DEFAULT 1 NOT NULL,
64 description character varying(255) DEFAULT ''::character varying NOT NULL,
65 status integer DEFAULT 1 NOT NULL,
66 zserial integer,
67 sertype character(1) DEFAULT 'D'::bpchar,
68 changed boolean DEFAULT true NOT NULL
69);
70
71CREATE TABLE revzones (
72 rdns_id serial NOT NULL,
73 revnet cidr NOT NULL PRIMARY KEY,
74 group_id integer DEFAULT 1 NOT NULL,
75 description character varying(255) DEFAULT ''::character varying NOT NULL,
76 status integer DEFAULT 1 NOT NULL,
77 zserial integer,
78 sertype character(1) DEFAULT 'D'::bpchar,
79 changed boolean DEFAULT true NOT NULL
80);
81
82CREATE TABLE groups (
83 group_id serial NOT NULL,
84 parent_group_id integer DEFAULT 1 NOT NULL,
85 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
86 permission_id integer DEFAULT 1 NOT NULL,
87 inherit_perm boolean DEFAULT true NOT NULL
88);
89
90-- Provide a basic default group
91COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
921 1 1 default
93\.
94
95-- entry is text due to possible long entries from AXFR - a domain with "many"
96-- odd records will overflow varchar(200)
97CREATE TABLE log (
98 log_id serial NOT NULL,
99 domain_id integer,
100 rdns_id integer,
101 user_id integer,
102 group_id integer,
103 email character varying(60),
104 name character varying(60),
105 entry text,
106 stamp timestamp with time zone DEFAULT now()
107);
108
109CREATE TABLE permissions (
110 permission_id serial NOT NULL,
111 "admin" boolean DEFAULT false NOT NULL,
112 self_edit boolean DEFAULT false NOT NULL,
113 group_create boolean DEFAULT false NOT NULL,
114 group_edit boolean DEFAULT false NOT NULL,
115 group_delete boolean DEFAULT false NOT NULL,
116 user_create boolean DEFAULT false NOT NULL,
117 user_edit boolean DEFAULT false NOT NULL,
118 user_delete boolean DEFAULT false NOT NULL,
119 domain_create boolean DEFAULT false NOT NULL,
120 domain_edit boolean DEFAULT false NOT NULL,
121 domain_delete boolean DEFAULT false NOT NULL,
122 record_create boolean DEFAULT false NOT NULL,
123 record_edit boolean DEFAULT false NOT NULL,
124 record_delete boolean DEFAULT false NOT NULL,
125 user_id integer UNIQUE,
126 group_id integer UNIQUE
127);
128
129-- Need *two* basic permissions; one for the initial group, one for the default admin user
130COPY 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;
1311 f f f f f f f f t t t t t t \N 1
1322 t f f f f f f f f f f f f f 1 \N
133\.
134
135-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
136CREATE TABLE records (
137 domain_id integer NOT NULL DEFAULT 0,
138 rdns_id integer NOT NULL DEFAULT 0,
139 record_id serial NOT NULL,
140 host text DEFAULT '' NOT NULL,
141 "type" integer DEFAULT 1 NOT NULL,
142 val text DEFAULT '' NOT NULL,
143 distance integer DEFAULT 0 NOT NULL,
144 weight integer DEFAULT 0 NOT NULL,
145 port integer DEFAULT 0 NOT NULL,
146 ttl integer DEFAULT 7200 NOT NULL,
147 description text
148);
149
150CREATE TABLE rectypes (
151 val integer NOT NULL,
152 name character varying(20) NOT NULL,
153 stdflag integer DEFAULT 1 NOT NULL,
154 listorder integer DEFAULT 255 NOT NULL,
155 alphaorder integer DEFAULT 32768 NOT NULL
156);
157
158-- Types are required. NB: these are vaguely read-only too
159-- data from http://www.iana.org/assignments/dns-parameters
160COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1611 A 1 1 1
1622 NS 2 9 37
1633 MD 5 255 29
1644 MF 5 255 30
1655 CNAME 2 11 9
1666 SOA 0 0 53
1677 MB 5 255 28
1688 MG 5 255 31
1699 MR 5 255 33
17010 NULL 5 255 43
17111 WKS 5 255 64
17212 PTR 3 5 46
17313 HINFO 5 255 18
17414 MINFO 5 255 32
17515 MX 1 10 34
17616 TXT 2 12 60
17717 RP 4 255 48
17818 AFSDB 5 255 4
17919 X25 5 255 65
18020 ISDN 5 255 21
18121 RT 5 255 50
18222 NSAP 5 255 38
18323 NSAP-PTR 5 255 39
18424 SIG 5 255 51
18525 KEY 5 255 23
18626 PX 5 255 47
18727 GPOS 5 255 17
18828 AAAA 1 3 3
18929 LOC 5 255 25
19030 NXT 5 255 44
19131 EID 5 255 15
19232 NIMLOC 5 255 36
19333 SRV 1 13 55
19434 ATMA 5 255 6
19535 NAPTR 5 255 35
19636 KX 5 255 24
19737 CERT 5 255 8
19838 A6 5 3 2
19939 DNAME 5 255 12
20040 SINK 5 255 52
20141 OPT 5 255 45
20242 APL 5 255 5
20343 DS 5 255 14
20444 SSHFP 5 255 56
20545 IPSECKEY 5 255 20
20646 RRSIG 5 255 49
20747 NSEC 5 255 40
20848 DNSKEY 5 255 13
20949 DHCID 5 255 10
21050 NSEC3 5 255 41
21151 NSEC3PARAM 5 255 42
21255 HIP 5 255 19
21399 SPF 5 255 54
214100 UINFO 5 255 62
215101 UID 5 255 61
216102 GID 5 255 16
217103 UNSPEC 5 255 63
218249 TKEY 5 255 58
219250 TSIG 5 255 59
220251 IXFR 5 255 22
221252 AXFR 5 255 7
222253 MAILB 5 255 27
223254 MAILA 5 255 26
22432768 TA 5 255 57
22532769 DLV 5 255 11
226\.
227
228-- Custom types (ab)using the "Private use" range from 65280 to 65534
229COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
23065280 A+PTR 2 2 2
23165281 AAAA+PTR 2 4 4
23265282 PTR template 3 6 2
23365283 A+PTR template 2 7 2
23465284 AAAA+PTR template 8 13 2
23565285 Delegation 2 8 2
236\.
237
238CREATE TABLE users (
239 user_id serial NOT NULL,
240 group_id integer DEFAULT 1 NOT NULL,
241 username character varying(60) NOT NULL,
242 "password" character varying(34) NOT NULL,
243 firstname character varying(60),
244 lastname character varying(60),
245 phone character varying(15),
246 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
247 status integer DEFAULT 1 NOT NULL,
248 permission_id integer DEFAULT 1 NOT NULL,
249 inherit_perm boolean DEFAULT true NOT NULL
250);
251
252-- create initial default user? may be better to create an "initialize" script or something
253COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2541 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
255\.
256
257--
258-- contraints. add these here so initial data doesn't get added strangely.
259--
260
261-- primary keys
262ALTER TABLE ONLY permissions
263 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
264
265ALTER TABLE ONLY groups
266 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
267
268ALTER TABLE ONLY domains
269 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
270
271ALTER TABLE ONLY domains
272 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
273
274ALTER TABLE ONLY default_records
275 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
276
277ALTER TABLE ONLY records
278 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
279
280ALTER TABLE ONLY rectypes
281 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
282
283ALTER TABLE ONLY users
284 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
285
286ALTER TABLE ONLY users
287 ADD CONSTRAINT uidu UNIQUE (user_id);
288
289-- foreign keys
290-- fixme: permissions FK refs
291ALTER TABLE ONLY domains
292 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
293
294ALTER TABLE ONLY default_records
295 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
296
297ALTER TABLE ONLY users
298 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
299
300ALTER TABLE ONLY groups
301 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
302
303-- set starting sequence numbers, since we've inserted data before they're active
304SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
305SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
306SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
307SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
308SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
309SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
310SELECT pg_catalog.setval('records_record_id_seq', 1, false);
311SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.