source: branches/stable/dns.sql@ 447

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

/trunk

Fix typo in initial database tableset

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
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.0
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 domains (
44 domain_id serial NOT NULL,
45 "domain" character varying(80) NOT NULL,
46 group_id integer DEFAULT 1 NOT NULL,
47 description character varying(255) DEFAULT ''::character varying NOT NULL,
48 status integer DEFAULT 1 NOT NULL,
49 zserial integer,
50 sertype character(1) DEFAULT 'D'::bpchar
51);
52
53CREATE TABLE groups (
54 group_id serial NOT NULL,
55 parent_group_id integer DEFAULT 1 NOT NULL,
56 group_name character varying(255) DEFAULT ''::character varying NOT NULL,
57 permission_id integer DEFAULT 1 NOT NULL,
58 inherit_perm boolean DEFAULT true NOT NULL
59);
60
61-- Provide a basic default group
62COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
631 1 1 default
64\.
65
66-- entry is text due to possible long entries from AXFR - a domain with "many"
67-- odd records will overflow varchar(200)
68CREATE TABLE log (
69 log_id serial NOT NULL,
70 domain_id integer,
71 user_id integer,
72 group_id integer,
73 email character varying(60),
74 name character varying(60),
75 entry text,
76 stamp timestamp with time zone DEFAULT now()
77);
78
79CREATE TABLE permissions (
80 permission_id serial NOT NULL,
81 "admin" boolean DEFAULT false NOT NULL,
82 self_edit boolean DEFAULT false NOT NULL,
83 group_create boolean DEFAULT false NOT NULL,
84 group_edit boolean DEFAULT false NOT NULL,
85 group_delete boolean DEFAULT false NOT NULL,
86 user_create boolean DEFAULT false NOT NULL,
87 user_edit boolean DEFAULT false NOT NULL,
88 user_delete boolean DEFAULT false NOT NULL,
89 domain_create boolean DEFAULT false NOT NULL,
90 domain_edit boolean DEFAULT false NOT NULL,
91 domain_delete boolean DEFAULT false NOT NULL,
92 record_create boolean DEFAULT false NOT NULL,
93 record_edit boolean DEFAULT false NOT NULL,
94 record_delete boolean DEFAULT false NOT NULL,
95 user_id integer UNIQUE,
96 group_id integer UNIQUE
97);
98
99-- Need *two* basic permissions; one for the initial group, one for the default admin user
100COPY 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;
1011 f f f f f f f f t t t t t t \N 1
1022 t f f f f f f f f f f f f f 1 \N
103\.
104
105-- fixme: need to handle looooong records (eg, SPF)
106CREATE TABLE records (
107 domain_id integer NOT NULL,
108 record_id serial NOT NULL,
109 host text DEFAULT '' NOT NULL,
110 "type" integer DEFAULT 1 NOT NULL,
111 val text DEFAULT '' NOT NULL,
112 distance integer DEFAULT 0 NOT NULL,
113 weight integer DEFAULT 0 NOT NULL,
114 port integer DEFAULT 0 NOT NULL,
115 ttl integer DEFAULT 7200 NOT NULL,
116 description text
117);
118
119CREATE TABLE rectypes (
120 val integer NOT NULL,
121 name character varying(12) NOT NULL,
122 stdflag integer DEFAULT 1 NOT NULL,
123 listorder integer DEFAULT 255 NOT NULL,
124 alphaorder integer DEFAULT 32768 NOT NULL
125);
126
127-- Types are required. NB: these are vaguely read-only too
128-- data from http://www.iana.org/assignments/dns-parameters
129COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
1301 A 1 1 1
1312 NS 1 2 37
1323 MD 2 255 29
1334 MF 2 255 30
1345 CNAME 1 6 9
1356 SOA 0 8 53
1367 MB 3 255 28
1378 MG 3 255 31
1389 MR 3 255 33
13910 NULL 3 255 43
14011 WKS 3 255 64
14112 PTR 2 4 46
14213 HINFO 3 255 18
14314 MINFO 3 255 32
14415 MX 1 3 34
14516 TXT 1 5 60
14617 RP 2 255 48
14718 AFSDB 3 255 4
14819 X25 3 255 65
14920 ISDN 3 255 21
15021 RT 3 255 50
15122 NSAP 3 255 38
15223 NSAP-PTR 3 255 39
15324 SIG 3 255 51
15425 KEY 3 255 23
15526 PX 3 255 47
15627 GPOS 3 255 17
15728 AAAA 1 2 3
15829 LOC 3 255 25
15930 NXT 3 255 44
16031 EID 3 255 15
16132 NIMLOC 3 255 36
16233 SRV 1 7 55
16334 ATMA 3 255 6
16435 NAPTR 3 255 35
16536 KX 3 255 24
16637 CERT 3 255 8
16738 A6 3 3 2
16839 DNAME 3 255 12
16940 SINK 3 255 52
17041 OPT 3 255 45
17142 APL 3 255 5
17243 DS 3 255 14
17344 SSHFP 3 255 56
17445 IPSECKEY 3 255 20
17546 RRSIG 3 255 49
17647 NSEC 3 255 40
17748 DNSKEY 3 255 13
17849 DHCID 3 255 10
17950 NSEC3 3 255 41
18051 NSEC3PARAM 3 255 42
18155 HIP 3 255 19
18299 SPF 3 255 54
183100 UINFO 3 255 62
184101 UID 3 255 61
185102 GID 3 255 16
186103 UNSPEC 3 255 63
187249 TKEY 3 255 58
188250 TSIG 3 255 59
189251 IXFR 3 255 22
190252 AXFR 3 255 7
191253 MAILB 3 255 27
192254 MAILA 3 255 26
19332768 TA 3 255 57
19432769 DLV 3 255 11
195\.
196
197CREATE TABLE users (
198 user_id serial NOT NULL,
199 group_id integer DEFAULT 1 NOT NULL,
200 username character varying(60) NOT NULL,
201 "password" character varying(34) NOT NULL,
202 firstname character varying(60),
203 lastname character varying(60),
204 phone character varying(15),
205 "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
206 status integer DEFAULT 1 NOT NULL,
207 permission_id integer DEFAULT 1 NOT NULL,
208 inherit_perm boolean DEFAULT true NOT NULL
209);
210
211-- create initial default user? may be better to create an "initialize" script or something
212COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
2131 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
214\.
215
216--
217-- contraints. add these here so initial data doesn't get added strangely.
218--
219
220-- primary keys
221ALTER TABLE ONLY permissions
222 ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
223
224ALTER TABLE ONLY groups
225 ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
226
227ALTER TABLE ONLY domains
228 ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
229
230ALTER TABLE ONLY domains
231 ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
232
233ALTER TABLE ONLY default_records
234 ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
235
236ALTER TABLE ONLY records
237 ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
238
239ALTER TABLE ONLY rectypes
240 ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
241
242ALTER TABLE ONLY users
243 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
244
245ALTER TABLE ONLY users
246 ADD CONSTRAINT uidu UNIQUE (user_id);
247
248-- foreign keys
249-- fixme: permissions FK refs
250ALTER TABLE ONLY domains
251 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
252
253ALTER TABLE ONLY default_records
254 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
255
256ALTER TABLE ONLY records
257 ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
258
259ALTER TABLE ONLY users
260 ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
261
262ALTER TABLE ONLY groups
263 ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
264
265-- set starting sequence numbers, since we've inserted data before they're active
266SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
267SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
268SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
269SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
270SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
271SELECT pg_catalog.setval('records_record_id_seq', 1, false);
272SELECT pg_catalog.setval('users_user_id_seq', 2, false);
Note: See TracBrowser for help on using the repository browser.