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