source: trunk/dns.sql@ 117

Last change on this file since 117 was 102, checked in by Kris Deugau, 13 years ago

/trunk

Add alphaorder column in rectypes table; this fixes the data source

for "properly" sorting by type (alphabetically vs by value)

File size: 8.2 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
8CREATE TABLE default_records (
9 record_id serial NOT NULL,
10 longrec_id integer,
11 group_id integer DEFAULT 1 NOT NULL,
12 host character varying(100) DEFAULT ''::character varying NOT NULL,
13 "type" integer DEFAULT 1 NOT NULL,
14 val character varying(100) DEFAULT ''::character varying NOT NULL,
15 distance integer DEFAULT 0 NOT NULL,
16 weight integer DEFAULT 0 NOT NULL,
17 port integer DEFAULT 0 NOT NULL,
18 ttl integer DEFAULT 86400 NOT NULL,
19 description character varying(255)
20);
21
22-- default records for the default group
23COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
241 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
252 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
263 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
274 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
285 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
296 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
307 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
31\.
32
33CREATE TABLE domains (
34 domain_id serial NOT NULL,
35 "domain" character varying(80) NOT NULL,
36 group_id integer DEFAULT 1 NOT NULL,
37 description character varying(255) DEFAULT ''::character varying NOT NULL,
38 status integer DEFAULT 1 NOT NULL,
39 zserial integer,
40 sertype character(1) DEFAULT 'D'::bpchar
41);
42
43CREATE TABLE groups (
44 group_id serial NOT NULL,
45 parent_group_id integer DEFAULT 1 NOT NULL,
46 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
47 permission_id integer DEFAULT 1 NOT NULL,
48 inherit_perm boolean DEFAULT true NOT NULL
49);
50
51-- Provide a basic default group
52COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
531 1 1 default
54\.
55
56-- entry is text due to possible long entries from AXFR - a domain with "many"
57-- odd records will overflow varchar(200)
58CREATE TABLE log (
59 log_id serial NOT NULL,
60 domain_id integer,
61 user_id integer,
62 group_id integer,
63 email character varying(60),
64 name character varying(60),
65 entry text,
66 stamp timestamp with time zone DEFAULT now()
67);
68
69CREATE TABLE permissions (
70 permission_id serial NOT NULL,
71 "admin" boolean DEFAULT false NOT NULL,
72 self_edit boolean DEFAULT false NOT NULL,
73 group_create boolean DEFAULT false NOT NULL,
74 group_edit boolean DEFAULT false NOT NULL,
75 group_delete boolean DEFAULT false NOT NULL,
76 user_create boolean DEFAULT false NOT NULL,
77 user_edit boolean DEFAULT false NOT NULL,
78 user_delete boolean DEFAULT false NOT NULL,
79 domain_create boolean DEFAULT false NOT NULL,
80 domain_edit boolean DEFAULT false NOT NULL,
81 domain_delete boolean DEFAULT false NOT NULL,
82 record_create boolean DEFAULT false NOT NULL,
83 record_edit boolean DEFAULT false NOT NULL,
84 record_delete boolean DEFAULT false NOT NULL,
85 user_id integer UNIQUE,
86 group_id integer UNIQUE
87);
88
89-- Need *two* basic permissions; one for the initial group, one for the default admin user
90COPY 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;
911 f f f f f f f f t t t t t t \N 1
922 t f f f f f f f f f f f f f 1 \N
93\.
94
95-- fixme: need to handle looooong records (eg, SPF)
96CREATE TABLE records (
97 domain_id integer NOT NULL,
98 record_id serial NOT NULL,
99 longrec_id integer,
100 host character varying(100) DEFAULT ''::character varying NOT NULL,
101 "type" integer DEFAULT 1 NOT NULL,
102 val character varying(100) DEFAULT ''::character varying NOT NULL,
103 distance integer DEFAULT 0 NOT NULL,
104 weight integer DEFAULT 0 NOT NULL,
105 port integer DEFAULT 0 NOT NULL,
106 ttl integer DEFAULT 7200 NOT NULL,
107 description character varying(255)
108);
109
110CREATE TABLE longrecs (
111 longrec_id serial NOT NULL,
112 recdata text
113);
114
115CREATE TABLE rectypes (
116 val integer NOT NULL,
117 name character varying(12) NOT NULL,
118 stdflag integer DEFAULT 1 NOT NULL,
119 listorder integer DEFAULT 255 NOT NULL,
120 alphaorder integer DEFAULT 32768 NOT NULL
121);
122
123-- Types are required. NB: these are vaguely read-only too
124-- data from http://www.iana.org/assignments/dns-parameters
125COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1261 A 1 1 1
1272 NS 1 2 37
1283 MD 2 255 29
1294 MF 2 255 30
1305 CNAME 1 6 9
1316 SOA 0 8 53
1327 MB 3 255 28
1338 MG 3 255 31
1349 MR 3 255 33
13510 NULL 3 255 43
13611 WKS 3 255 64
13712 PTR 2 4 46
13813 HINFO 3 255 18
13914 MINFO 3 255 32
14015 MX 1 3 34
14116 TXT 1 5 60
14217 RP 2 255 48
14318 AFSDB 3 255 4
14419 X25 3 255 65
14520 ISDN 3 255 21
14621 RT 3 255 50
14722 NSAP 3 255 38
14823 NSAP-PTR 3 255 39
14924 SIG 3 255 51
15025 KEY 3 255 23
15126 PX 3 255 47
15227 GPOS 3 255 17
15328 AAAA 2 2 3
15429 LOC 3 255 25
15530 NXT 3 255 44
15631 EID 3 255 15
15732 NIMLOC 3 255 36
15833 SRV 1 7 55
15934 ATMA 3 255 6
16035 NAPTR 3 255 35
16136 KX 3 255 24
16237 CERT 3 255 8
16338 A6 3 3 2
16439 DNAME 3 255 12
16540 SINK 3 255 52
16641 OPT 3 255 45
16742 APL 3 255 5
16843 DS 3 255 14
16944 SSHFP 3 255 56
17045 IPSECKEY 3 255 20
17146 RRSIG 3 255 49
17247 NSEC 3 255 40
17348 DNSKEY 3 255 13
17449 DHCID 3 255 10
17550 NSEC3 3 255 41
17651 NSEC3PARAM 3 255 42
17755 HIP 3 255 19
17899 SPF 3 255 54
179100 UINFO 3 255 62
180101 UID 3 255 61
181102 GID 3 255 16
182103 UNSPEC 3 255 63
183249 TKEY 3 255 58
184250 TSIG 3 255 59
185251 IXFR 3 255 22
186252 AXFR 3 255 7
187253 MAILB 3 255 27
188254 MAILA 3 255 26
18932768 TA 3 255 57
19032769 DLV 3 255 11
191\.
192
193CREATE TABLE users (
194 user_id serial NOT NULL,
195 group_id integer DEFAULT 1 NOT NULL,
196 username character varying(60) NOT NULL,
197 "password" character varying(34) NOT NULL,
198 firstname character varying(60),
199 lastname character varying(60),
200 phone character varying(15),
201 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
202 status integer DEFAULT 1 NOT NULL,
203 permission_id integer DEFAULT 1 NOT NULL,
204 inherit_perm boolean DEFAULT true NOT NULL
205);
206
207-- create initial default user? may be better to create an "initialize" script or something
208COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2091 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
210\.
211
212--
213-- contraints. add these here so initial data doesn't get added strangely.
214--
215
216-- primary keys
217ALTER TABLE ONLY permissions
218 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
219
220ALTER TABLE ONLY groups
221 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
222
223ALTER TABLE ONLY domains
224 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
225
226ALTER TABLE ONLY domains
227 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
228
229ALTER TABLE ONLY default_records
230 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
231
232ALTER TABLE ONLY records
233 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
234
235ALTER TABLE ONLY rectypes
236 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
237
238ALTER TABLE ONLY users
239 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
240
241ALTER TABLE ONLY users
242 ADD CONSTRAINT uidu UNIQUE (user_id);
243
244-- foreign keys
245-- fixme: permissions FK refs
246ALTER TABLE ONLY domains
247 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
248
249ALTER TABLE ONLY default_records
250 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
251
252ALTER TABLE ONLY records
253 ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
254
255ALTER TABLE ONLY users
256 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
257
258ALTER TABLE ONLY groups
259 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
260
261-- set starting sequence numbers, since we've inserted data before they're active
262SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
263SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
264SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
265SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
266SELECT pg_catalog.setval('records_record_id_seq', 1, false);
267SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.