[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
|
---|
| 9 | CREATE TABLE misc (
|
---|
| 10 | misc_id serial NOT NULL,
|
---|
| 11 | key text DEFAULT '' NOT NULL,
|
---|
[215] | 12 | value text DEFAULT '' NOT NULL
|
---|
[212] | 13 | );
|
---|
| 14 |
|
---|
| 15 | COPY misc (misc_id, key, value) FROM stdin;
|
---|
[347] | 16 | 1 dbversion 1.2
|
---|
[212] | 17 | \.
|
---|
| 18 |
|
---|
[370] | 19 | CREATE TABLE locations (
|
---|
| 20 | location character varying (4) PRIMARY KEY,
|
---|
[375] | 21 | loc_id serial UNIQUE,
|
---|
[370] | 22 | group_id integer NOT NULL DEFAULT 1,
|
---|
| 23 | iplist text NOT NULL DEFAULT '',
|
---|
[375] | 24 | description character varying(40) NOT NULL DEFAULT '',
|
---|
| 25 | comments text NOT NULL DEFAULT ''
|
---|
[370] | 26 | );
|
---|
| 27 |
|
---|
[50] | 28 | CREATE TABLE default_records (
|
---|
| 29 | record_id serial NOT NULL,
|
---|
| 30 | group_id integer DEFAULT 1 NOT NULL,
|
---|
[130] | 31 | host text DEFAULT '' NOT NULL,
|
---|
[50] | 32 | "type" integer DEFAULT 1 NOT NULL,
|
---|
[130] | 33 | val text DEFAULT '' NOT NULL,
|
---|
[50] | 34 | distance integer DEFAULT 0 NOT NULL,
|
---|
| 35 | weight integer DEFAULT 0 NOT NULL,
|
---|
| 36 | port integer DEFAULT 0 NOT NULL,
|
---|
| 37 | ttl integer DEFAULT 86400 NOT NULL,
|
---|
[130] | 38 | description text
|
---|
[50] | 39 | );
|
---|
| 40 |
|
---|
| 41 | -- default records for the default group
|
---|
| 42 | COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
|
---|
| 43 | 1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
|
---|
| 44 | 2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
|
---|
| 45 | 3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
|
---|
| 46 | 4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
|
---|
| 47 | 5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
|
---|
| 48 | 6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
|
---|
| 49 | 7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
|
---|
| 50 | \.
|
---|
| 51 |
|
---|
[224] | 52 | CREATE TABLE default_rev_records (
|
---|
| 53 | record_id serial NOT NULL,
|
---|
| 54 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 55 | host text DEFAULT '' NOT NULL,
|
---|
| 56 | "type" integer DEFAULT 1 NOT NULL,
|
---|
| 57 | val text DEFAULT '' NOT NULL,
|
---|
| 58 | ttl integer DEFAULT 86400 NOT NULL,
|
---|
| 59 | description text
|
---|
| 60 | );
|
---|
| 61 |
|
---|
[249] | 62 | COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
|
---|
[224] | 63 | 1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
|
---|
| 64 | 2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
|
---|
[328] | 65 | 3 1 ns2.example.com 2 ZONE 7200 \N
|
---|
| 66 | 4 1 ns1.example.com 2 ZONE 7200 \N
|
---|
[224] | 67 | \.
|
---|
| 68 |
|
---|
[85] | 69 | CREATE TABLE domains (
|
---|
| 70 | domain_id serial NOT NULL,
|
---|
[347] | 71 | "domain" character varying(80) NOT NULL PRIMARY KEY,
|
---|
[85] | 72 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 73 | description character varying(255) DEFAULT ''::character varying NOT NULL,
|
---|
| 74 | status integer DEFAULT 1 NOT NULL,
|
---|
| 75 | zserial integer,
|
---|
[366] | 76 | sertype character(1) DEFAULT 'D'::bpchar,
|
---|
[370] | 77 | changed boolean DEFAULT true NOT NULL,
|
---|
| 78 | default_location character varying (4) DEFAULT '' NOT NULL
|
---|
[85] | 79 | );
|
---|
| 80 |
|
---|
[224] | 81 | CREATE TABLE revzones (
|
---|
| 82 | rdns_id serial NOT NULL,
|
---|
[347] | 83 | revnet cidr NOT NULL PRIMARY KEY,
|
---|
[224] | 84 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 85 | description character varying(255) DEFAULT ''::character varying NOT NULL,
|
---|
| 86 | status integer DEFAULT 1 NOT NULL,
|
---|
| 87 | zserial integer,
|
---|
[366] | 88 | sertype character(1) DEFAULT 'D'::bpchar,
|
---|
[370] | 89 | changed boolean DEFAULT true NOT NULL,
|
---|
| 90 | default_location character varying (4) DEFAULT '' NOT NULL
|
---|
[224] | 91 | );
|
---|
| 92 |
|
---|
[85] | 93 | CREATE TABLE groups (
|
---|
| 94 | group_id serial NOT NULL,
|
---|
| 95 | parent_group_id integer DEFAULT 1 NOT NULL,
|
---|
| 96 | group_name character varying(255) DEFAULT ''::character varying NOT NULL,
|
---|
| 97 | permission_id integer DEFAULT 1 NOT NULL,
|
---|
| 98 | inherit_perm boolean DEFAULT true NOT NULL
|
---|
| 99 | );
|
---|
| 100 |
|
---|
| 101 | -- Provide a basic default group
|
---|
[86] | 102 | COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
|
---|
[85] | 103 | 1 1 1 default
|
---|
| 104 | \.
|
---|
| 105 |
|
---|
[91] | 106 | -- entry is text due to possible long entries from AXFR - a domain with "many"
|
---|
| 107 | -- odd records will overflow varchar(200)
|
---|
[85] | 108 | CREATE TABLE log (
|
---|
[89] | 109 | log_id serial NOT NULL,
|
---|
[85] | 110 | domain_id integer,
|
---|
[224] | 111 | rdns_id integer,
|
---|
[85] | 112 | user_id integer,
|
---|
| 113 | group_id integer,
|
---|
| 114 | email character varying(60),
|
---|
| 115 | name character varying(60),
|
---|
[91] | 116 | entry text,
|
---|
[85] | 117 | stamp timestamp with time zone DEFAULT now()
|
---|
| 118 | );
|
---|
| 119 |
|
---|
| 120 | CREATE TABLE permissions (
|
---|
| 121 | permission_id serial NOT NULL,
|
---|
| 122 | "admin" boolean DEFAULT false NOT NULL,
|
---|
| 123 | self_edit boolean DEFAULT false NOT NULL,
|
---|
| 124 | group_create boolean DEFAULT false NOT NULL,
|
---|
| 125 | group_edit boolean DEFAULT false NOT NULL,
|
---|
| 126 | group_delete boolean DEFAULT false NOT NULL,
|
---|
| 127 | user_create boolean DEFAULT false NOT NULL,
|
---|
| 128 | user_edit boolean DEFAULT false NOT NULL,
|
---|
| 129 | user_delete boolean DEFAULT false NOT NULL,
|
---|
| 130 | domain_create boolean DEFAULT false NOT NULL,
|
---|
| 131 | domain_edit boolean DEFAULT false NOT NULL,
|
---|
| 132 | domain_delete boolean DEFAULT false NOT NULL,
|
---|
| 133 | record_create boolean DEFAULT false NOT NULL,
|
---|
| 134 | record_edit boolean DEFAULT false NOT NULL,
|
---|
| 135 | record_delete boolean DEFAULT false NOT NULL,
|
---|
[387] | 136 | record_locchg boolean DEFAULT false NOT NULL,
|
---|
[370] | 137 | location_create boolean DEFAULT false NOT NULL,
|
---|
| 138 | location_edit boolean DEFAULT false NOT NULL,
|
---|
| 139 | location_delete boolean DEFAULT false NOT NULL,
|
---|
| 140 | location_view boolean DEFAULT false NOT NULL,
|
---|
[86] | 141 | user_id integer UNIQUE,
|
---|
| 142 | group_id integer UNIQUE
|
---|
[85] | 143 | );
|
---|
| 144 |
|
---|
| 145 | -- Need *two* basic permissions; one for the initial group, one for the default admin user
|
---|
[393] | 146 | COPY 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, record_locchg, location_create, location_edit, location_delete, location_view, user_id, group_id) FROM stdin;
|
---|
| 147 | 1 f f f f f f f f t t t t t t f f f f f \N 1
|
---|
| 148 | 2 t f f f f f f f f f f f f f f f f f f 1 \N
|
---|
[85] | 149 | \.
|
---|
| 150 |
|
---|
[224] | 151 | -- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
|
---|
[50] | 152 | CREATE TABLE records (
|
---|
[224] | 153 | domain_id integer NOT NULL DEFAULT 0,
|
---|
| 154 | rdns_id integer NOT NULL DEFAULT 0,
|
---|
[50] | 155 | record_id serial NOT NULL,
|
---|
[130] | 156 | host text DEFAULT '' NOT NULL,
|
---|
[50] | 157 | "type" integer DEFAULT 1 NOT NULL,
|
---|
[130] | 158 | val text DEFAULT '' NOT NULL,
|
---|
[50] | 159 | distance integer DEFAULT 0 NOT NULL,
|
---|
| 160 | weight integer DEFAULT 0 NOT NULL,
|
---|
| 161 | port integer DEFAULT 0 NOT NULL,
|
---|
[85] | 162 | ttl integer DEFAULT 7200 NOT NULL,
|
---|
[370] | 163 | description text,
|
---|
[395] | 164 | location character varying (4) DEFAULT '' NOT NULL
|
---|
[50] | 165 | );
|
---|
| 166 |
|
---|
| 167 | CREATE TABLE rectypes (
|
---|
| 168 | val integer NOT NULL,
|
---|
[224] | 169 | name character varying(20) NOT NULL,
|
---|
[50] | 170 | stdflag integer DEFAULT 1 NOT NULL,
|
---|
[102] | 171 | listorder integer DEFAULT 255 NOT NULL,
|
---|
| 172 | alphaorder integer DEFAULT 32768 NOT NULL
|
---|
[50] | 173 | );
|
---|
| 174 |
|
---|
| 175 | -- Types are required. NB: these are vaguely read-only too
|
---|
[85] | 176 | -- data from http://www.iana.org/assignments/dns-parameters
|
---|
[102] | 177 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
| 178 | 1 A 1 1 1
|
---|
[300] | 179 | 2 NS 2 9 37
|
---|
[224] | 180 | 3 MD 5 255 29
|
---|
| 181 | 4 MF 5 255 30
|
---|
[300] | 182 | 5 CNAME 2 11 9
|
---|
[224] | 183 | 6 SOA 0 0 53
|
---|
| 184 | 7 MB 5 255 28
|
---|
| 185 | 8 MG 5 255 31
|
---|
| 186 | 9 MR 5 255 33
|
---|
| 187 | 10 NULL 5 255 43
|
---|
| 188 | 11 WKS 5 255 64
|
---|
[300] | 189 | 12 PTR 3 5 46
|
---|
[224] | 190 | 13 HINFO 5 255 18
|
---|
| 191 | 14 MINFO 5 255 32
|
---|
[300] | 192 | 15 MX 1 10 34
|
---|
[351] | 193 | 16 TXT 2 12 60
|
---|
[224] | 194 | 17 RP 4 255 48
|
---|
| 195 | 18 AFSDB 5 255 4
|
---|
| 196 | 19 X25 5 255 65
|
---|
| 197 | 20 ISDN 5 255 21
|
---|
| 198 | 21 RT 5 255 50
|
---|
| 199 | 22 NSAP 5 255 38
|
---|
| 200 | 23 NSAP-PTR 5 255 39
|
---|
| 201 | 24 SIG 5 255 51
|
---|
| 202 | 25 KEY 5 255 23
|
---|
| 203 | 26 PX 5 255 47
|
---|
| 204 | 27 GPOS 5 255 17
|
---|
| 205 | 28 AAAA 1 3 3
|
---|
| 206 | 29 LOC 5 255 25
|
---|
| 207 | 30 NXT 5 255 44
|
---|
| 208 | 31 EID 5 255 15
|
---|
| 209 | 32 NIMLOC 5 255 36
|
---|
[300] | 210 | 33 SRV 1 13 55
|
---|
[224] | 211 | 34 ATMA 5 255 6
|
---|
| 212 | 35 NAPTR 5 255 35
|
---|
| 213 | 36 KX 5 255 24
|
---|
| 214 | 37 CERT 5 255 8
|
---|
| 215 | 38 A6 5 3 2
|
---|
| 216 | 39 DNAME 5 255 12
|
---|
| 217 | 40 SINK 5 255 52
|
---|
| 218 | 41 OPT 5 255 45
|
---|
| 219 | 42 APL 5 255 5
|
---|
| 220 | 43 DS 5 255 14
|
---|
| 221 | 44 SSHFP 5 255 56
|
---|
| 222 | 45 IPSECKEY 5 255 20
|
---|
| 223 | 46 RRSIG 5 255 49
|
---|
| 224 | 47 NSEC 5 255 40
|
---|
| 225 | 48 DNSKEY 5 255 13
|
---|
| 226 | 49 DHCID 5 255 10
|
---|
| 227 | 50 NSEC3 5 255 41
|
---|
| 228 | 51 NSEC3PARAM 5 255 42
|
---|
| 229 | 55 HIP 5 255 19
|
---|
| 230 | 99 SPF 5 255 54
|
---|
| 231 | 100 UINFO 5 255 62
|
---|
| 232 | 101 UID 5 255 61
|
---|
| 233 | 102 GID 5 255 16
|
---|
| 234 | 103 UNSPEC 5 255 63
|
---|
| 235 | 249 TKEY 5 255 58
|
---|
| 236 | 250 TSIG 5 255 59
|
---|
| 237 | 251 IXFR 5 255 22
|
---|
| 238 | 252 AXFR 5 255 7
|
---|
| 239 | 253 MAILB 5 255 27
|
---|
| 240 | 254 MAILA 5 255 26
|
---|
| 241 | 32768 TA 5 255 57
|
---|
| 242 | 32769 DLV 5 255 11
|
---|
[50] | 243 | \.
|
---|
| 244 |
|
---|
[224] | 245 | -- Custom types (ab)using the "Private use" range from 65280 to 65534
|
---|
| 246 | COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
|
---|
| 247 | 65280 A+PTR 2 2 2
|
---|
| 248 | 65281 AAAA+PTR 2 4 4
|
---|
[300] | 249 | 65282 PTR template 3 6 2
|
---|
[342] | 250 | 65283 A+PTR template 2 7 2
|
---|
[300] | 251 | 65284 AAAA+PTR template 8 13 2
|
---|
[342] | 252 | 65285 Delegation 2 8 2
|
---|
[224] | 253 | \.
|
---|
| 254 |
|
---|
[50] | 255 | CREATE TABLE users (
|
---|
| 256 | user_id serial NOT NULL,
|
---|
| 257 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 258 | username character varying(60) NOT NULL,
|
---|
| 259 | "password" character varying(34) NOT NULL,
|
---|
[85] | 260 | firstname character varying(60),
|
---|
| 261 | lastname character varying(60),
|
---|
[50] | 262 | phone character varying(15),
|
---|
| 263 | "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
|
---|
| 264 | status integer DEFAULT 1 NOT NULL,
|
---|
[85] | 265 | permission_id integer DEFAULT 1 NOT NULL,
|
---|
| 266 | inherit_perm boolean DEFAULT true NOT NULL
|
---|
[50] | 267 | );
|
---|
| 268 |
|
---|
| 269 | -- create initial default user? may be better to create an "initialize" script or something
|
---|
[86] | 270 | COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
|
---|
[89] | 271 | 1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
|
---|
[50] | 272 | \.
|
---|
| 273 |
|
---|
| 274 | --
|
---|
| 275 | -- contraints. add these here so initial data doesn't get added strangely.
|
---|
| 276 | --
|
---|
| 277 |
|
---|
| 278 | -- primary keys
|
---|
[65] | 279 | ALTER TABLE ONLY permissions
|
---|
| 280 | ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
|
---|
| 281 |
|
---|
[50] | 282 | ALTER TABLE ONLY groups
|
---|
| 283 | ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
|
---|
| 284 |
|
---|
| 285 | ALTER TABLE ONLY domains
|
---|
| 286 | ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
|
---|
| 287 |
|
---|
| 288 | ALTER TABLE ONLY default_records
|
---|
| 289 | ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
|
---|
| 290 |
|
---|
| 291 | ALTER TABLE ONLY records
|
---|
| 292 | ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
|
---|
| 293 |
|
---|
| 294 | ALTER TABLE ONLY rectypes
|
---|
| 295 | ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
|
---|
| 296 |
|
---|
| 297 | ALTER TABLE ONLY users
|
---|
| 298 | ADD CONSTRAINT users_pkey PRIMARY KEY (username);
|
---|
| 299 |
|
---|
| 300 | ALTER TABLE ONLY users
|
---|
| 301 | ADD CONSTRAINT uidu UNIQUE (user_id);
|
---|
| 302 |
|
---|
| 303 | -- foreign keys
|
---|
[65] | 304 | -- fixme: permissions FK refs
|
---|
[370] | 305 | ALTER TABLE ONLY locations
|
---|
| 306 | ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 307 |
|
---|
[50] | 308 | ALTER TABLE ONLY domains
|
---|
| 309 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 310 |
|
---|
[369] | 311 | ALTER TABLE ONLY revzones
|
---|
| 312 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 313 |
|
---|
[50] | 314 | ALTER TABLE ONLY default_records
|
---|
| 315 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 316 |
|
---|
| 317 | ALTER TABLE ONLY users
|
---|
| 318 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 319 |
|
---|
| 320 | ALTER TABLE ONLY groups
|
---|
| 321 | ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
|
---|
[86] | 322 |
|
---|
| 323 | -- set starting sequence numbers, since we've inserted data before they're active
|
---|
[224] | 324 | SELECT pg_catalog.setval('misc_misc_id_seq', 2, false);
|
---|
| 325 | SELECT pg_catalog.setval('default_records_record_id_seq', 8, false);
|
---|
[300] | 326 | SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
|
---|
[86] | 327 | SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
|
---|
[224] | 328 | SELECT pg_catalog.setval('groups_group_id_seq', 2, false);
|
---|
| 329 | SELECT pg_catalog.setval('permissions_permission_id_seq', 3, false);
|
---|
[86] | 330 | SELECT pg_catalog.setval('records_record_id_seq', 1, false);
|
---|
| 331 | SELECT pg_catalog.setval('users_user_id_seq', 2, false);
|
---|