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