source: trunk/dns.sql@ 363

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

/trunk

Forgot to include the initial tabledef/record type change to allow
TXT records in reverse zones in r350.

See #26.

File size: 9.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 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);
69
70CREATE TABLE revzones (
71 rdns_id serial NOT NULL,
72 revnet cidr NOT NULL PRIMARY KEY,
73 group_id integer DEFAULT 1 NOT NULL,
74 description character varying(255) DEFAULT ''::character varying NOT NULL,
75 status integer DEFAULT 1 NOT NULL,
76 zserial integer,
77 sertype character(1) DEFAULT 'D'::bpchar
78);
79
80CREATE TABLE groups (
81 group_id serial NOT NULL,
82 parent_group_id integer DEFAULT 1 NOT NULL,
83 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
84 permission_id integer DEFAULT 1 NOT NULL,
85 inherit_perm boolean DEFAULT true NOT NULL
86);
87
88-- Provide a basic default group
89COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
901 1 1 default
91\.
92
93-- entry is text due to possible long entries from AXFR - a domain with "many"
94-- odd records will overflow varchar(200)
95CREATE TABLE log (
96 log_id serial NOT NULL,
97 domain_id integer,
98 rdns_id integer,
99 user_id integer,
100 group_id integer,
101 email character varying(60),
102 name character varying(60),
103 entry text,
104 stamp timestamp with time zone DEFAULT now()
105);
106
107CREATE TABLE permissions (
108 permission_id serial NOT NULL,
109 "admin" boolean DEFAULT false NOT NULL,
110 self_edit boolean DEFAULT false NOT NULL,
111 group_create boolean DEFAULT false NOT NULL,
112 group_edit boolean DEFAULT false NOT NULL,
113 group_delete boolean DEFAULT false NOT NULL,
114 user_create boolean DEFAULT false NOT NULL,
115 user_edit boolean DEFAULT false NOT NULL,
116 user_delete boolean DEFAULT false NOT NULL,
117 domain_create boolean DEFAULT false NOT NULL,
118 domain_edit boolean DEFAULT false NOT NULL,
119 domain_delete boolean DEFAULT false NOT NULL,
120 record_create boolean DEFAULT false NOT NULL,
121 record_edit boolean DEFAULT false NOT NULL,
122 record_delete boolean DEFAULT false NOT NULL,
123 user_id integer UNIQUE,
124 group_id integer UNIQUE
125);
126
127-- Need *two* basic permissions; one for the initial group, one for the default admin user
128COPY 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;
1291 f f f f f f f f t t t t t t \N 1
1302 t f f f f f f f f f f f f f 1 \N
131\.
132
133-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
134CREATE TABLE records (
135 domain_id integer NOT NULL DEFAULT 0,
136 rdns_id integer NOT NULL DEFAULT 0,
137 record_id serial NOT NULL,
138 host text DEFAULT '' NOT NULL,
139 "type" integer DEFAULT 1 NOT NULL,
140 val text DEFAULT '' NOT NULL,
141 distance integer DEFAULT 0 NOT NULL,
142 weight integer DEFAULT 0 NOT NULL,
143 port integer DEFAULT 0 NOT NULL,
144 ttl integer DEFAULT 7200 NOT NULL,
145 description text
146);
147
148CREATE TABLE rectypes (
149 val integer NOT NULL,
150 name character varying(20) NOT NULL,
151 stdflag integer DEFAULT 1 NOT NULL,
152 listorder integer DEFAULT 255 NOT NULL,
153 alphaorder integer DEFAULT 32768 NOT NULL
154);
155
156-- Types are required. NB: these are vaguely read-only too
157-- data from http://www.iana.org/assignments/dns-parameters
158COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1591 A 1 1 1
1602 NS 2 9 37
1613 MD 5 255 29
1624 MF 5 255 30
1635 CNAME 2 11 9
1646 SOA 0 0 53
1657 MB 5 255 28
1668 MG 5 255 31
1679 MR 5 255 33
16810 NULL 5 255 43
16911 WKS 5 255 64
17012 PTR 3 5 46
17113 HINFO 5 255 18
17214 MINFO 5 255 32
17315 MX 1 10 34
17416 TXT 2 12 60
17517 RP 4 255 48
17618 AFSDB 5 255 4
17719 X25 5 255 65
17820 ISDN 5 255 21
17921 RT 5 255 50
18022 NSAP 5 255 38
18123 NSAP-PTR 5 255 39
18224 SIG 5 255 51
18325 KEY 5 255 23
18426 PX 5 255 47
18527 GPOS 5 255 17
18628 AAAA 1 3 3
18729 LOC 5 255 25
18830 NXT 5 255 44
18931 EID 5 255 15
19032 NIMLOC 5 255 36
19133 SRV 1 13 55
19234 ATMA 5 255 6
19335 NAPTR 5 255 35
19436 KX 5 255 24
19537 CERT 5 255 8
19638 A6 5 3 2
19739 DNAME 5 255 12
19840 SINK 5 255 52
19941 OPT 5 255 45
20042 APL 5 255 5
20143 DS 5 255 14
20244 SSHFP 5 255 56
20345 IPSECKEY 5 255 20
20446 RRSIG 5 255 49
20547 NSEC 5 255 40
20648 DNSKEY 5 255 13
20749 DHCID 5 255 10
20850 NSEC3 5 255 41
20951 NSEC3PARAM 5 255 42
21055 HIP 5 255 19
21199 SPF 5 255 54
212100 UINFO 5 255 62
213101 UID 5 255 61
214102 GID 5 255 16
215103 UNSPEC 5 255 63
216249 TKEY 5 255 58
217250 TSIG 5 255 59
218251 IXFR 5 255 22
219252 AXFR 5 255 7
220253 MAILB 5 255 27
221254 MAILA 5 255 26
22232768 TA 5 255 57
22332769 DLV 5 255 11
224\.
225
226-- Custom types (ab)using the "Private use" range from 65280 to 65534
227COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
22865280 A+PTR 2 2 2
22965281 AAAA+PTR 2 4 4
23065282 PTR template 3 6 2
23165283 A+PTR template 2 7 2
23265284 AAAA+PTR template 8 13 2
23365285 Delegation 2 8 2
234\.
235
236CREATE TABLE users (
237 user_id serial NOT NULL,
238 group_id integer DEFAULT 1 NOT NULL,
239 username character varying(60) NOT NULL,
240 "password" character varying(34) NOT NULL,
241 firstname character varying(60),
242 lastname character varying(60),
243 phone character varying(15),
244 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
245 status integer DEFAULT 1 NOT NULL,
246 permission_id integer DEFAULT 1 NOT NULL,
247 inherit_perm boolean DEFAULT true NOT NULL
248);
249
250-- create initial default user? may be better to create an "initialize" script or something
251COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2521 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
253\.
254
255--
256-- contraints. add these here so initial data doesn't get added strangely.
257--
258
259-- primary keys
260ALTER TABLE ONLY permissions
261 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
262
263ALTER TABLE ONLY groups
264 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
265
266ALTER TABLE ONLY domains
267 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
268
269ALTER TABLE ONLY domains
270 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
271
272ALTER TABLE ONLY default_records
273 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
274
275ALTER TABLE ONLY records
276 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
277
278ALTER TABLE ONLY rectypes
279 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
280
281ALTER TABLE ONLY users
282 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
283
284ALTER TABLE ONLY users
285 ADD CONSTRAINT uidu UNIQUE (user_id);
286
287-- foreign keys
288-- fixme: permissions FK refs
289ALTER TABLE ONLY domains
290 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
291
292ALTER TABLE ONLY default_records
293 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
294
295ALTER TABLE ONLY users
296 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
297
298ALTER TABLE ONLY groups
299 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
300
301-- set starting sequence numbers, since we've inserted data before they're active
302SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
303SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
304SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
305SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
306SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
307SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
308SELECT pg_catalog.setval('records_record_id_seq', 1, false);
309SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.