source: trunk/dns.sql@ 368

Last change on this file since 368 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
RevLine 
[50]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
[212]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,
[215]12 value text DEFAULT '' NOT NULL
[212]13);
14
15COPY misc (misc_id, key, value) FROM stdin;
[347]161 dbversion 1.2
[212]17\.
18
[50]19CREATE TABLE default_records (
20 record_id serial NOT NULL,
21 group_id integer DEFAULT 1 NOT NULL,
[130]22 host text DEFAULT '' NOT NULL,
[50]23 "type" integer DEFAULT 1 NOT NULL,
[130]24 val text DEFAULT '' NOT NULL,
[50]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,
[130]29 description text
[50]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
[224]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
[249]53COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
[224]541 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
552 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
[328]563 1 ns2.example.com 2 ZONE 7200 \N
574 1 ns1.example.com 2 ZONE 7200 \N
[224]58\.
59
[85]60CREATE TABLE domains (
61 domain_id serial NOT NULL,
[347]62 "domain" character varying(80) NOT NULL PRIMARY KEY,
[85]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,
[366]67 sertype character(1) DEFAULT 'D'::bpchar,
68 changed boolean DEFAULT true NOT NULL
[85]69);
70
[224]71CREATE TABLE revzones (
72 rdns_id serial NOT NULL,
[347]73 revnet cidr NOT NULL PRIMARY KEY,
[224]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,
[366]78 sertype character(1) DEFAULT 'D'::bpchar,
79 changed boolean DEFAULT true NOT NULL
[224]80);
81
[85]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
[86]91COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
[85]921 1 1 default
93\.
94
[91]95-- entry is text due to possible long entries from AXFR - a domain with "many"
96-- odd records will overflow varchar(200)
[85]97CREATE TABLE log (
[89]98 log_id serial NOT NULL,
[85]99 domain_id integer,
[224]100 rdns_id integer,
[85]101 user_id integer,
102 group_id integer,
103 email character varying(60),
104 name character varying(60),
[91]105 entry text,
[85]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,
[86]125 user_id integer UNIQUE,
126 group_id integer UNIQUE
[85]127);
128
129-- Need *two* basic permissions; one for the initial group, one for the default admin user
[86]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;
[89]1311 f f f f f f f f t t t t t t \N 1
[86]1322 t f f f f f f f f f f f f f 1 \N
[85]133\.
134
[224]135-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
[50]136CREATE TABLE records (
[224]137 domain_id integer NOT NULL DEFAULT 0,
138 rdns_id integer NOT NULL DEFAULT 0,
[50]139 record_id serial NOT NULL,
[130]140 host text DEFAULT '' NOT NULL,
[50]141 "type" integer DEFAULT 1 NOT NULL,
[130]142 val text DEFAULT '' NOT NULL,
[50]143 distance integer DEFAULT 0 NOT NULL,
144 weight integer DEFAULT 0 NOT NULL,
145 port integer DEFAULT 0 NOT NULL,
[85]146 ttl integer DEFAULT 7200 NOT NULL,
[130]147 description text
[50]148);
149
150CREATE TABLE rectypes (
151 val integer NOT NULL,
[224]152 name character varying(20) NOT NULL,
[50]153 stdflag integer DEFAULT 1 NOT NULL,
[102]154 listorder integer DEFAULT 255 NOT NULL,
155 alphaorder integer DEFAULT 32768 NOT NULL
[50]156);
157
158-- Types are required. NB: these are vaguely read-only too
[85]159-- data from http://www.iana.org/assignments/dns-parameters
[102]160COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1611 A 1 1 1
[300]1622 NS 2 9 37
[224]1633 MD 5 255 29
1644 MF 5 255 30
[300]1655 CNAME 2 11 9
[224]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
[300]17212 PTR 3 5 46
[224]17313 HINFO 5 255 18
17414 MINFO 5 255 32
[300]17515 MX 1 10 34
[351]17616 TXT 2 12 60
[224]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
[300]19333 SRV 1 13 55
[224]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
[50]226\.
227
[224]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
[300]23265282 PTR template 3 6 2
[342]23365283 A+PTR template 2 7 2
[300]23465284 AAAA+PTR template 8 13 2
[342]23565285 Delegation 2 8 2
[224]236\.
237
[50]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,
[85]243 firstname character varying(60),
244 lastname character varying(60),
[50]245 phone character varying(15),
246 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
247 status integer DEFAULT 1 NOT NULL,
[85]248 permission_id integer DEFAULT 1 NOT NULL,
249 inherit_perm boolean DEFAULT true NOT NULL
[50]250);
251
252-- create initial default user? may be better to create an "initialize" script or something
[86]253COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
[89]2541 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
[50]255\.
256
257--
258-- contraints. add these here so initial data doesn't get added strangely.
259--
260
261-- primary keys
[65]262ALTER TABLE ONLY permissions
263 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
264
[50]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
[65]290-- fixme: permissions FK refs
[50]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);
[86]302
303-- set starting sequence numbers, since we've inserted data before they're active
[224]304SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
305SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
[300]306SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
[86]307SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
[224]308SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
309SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
[86]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.