[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 |
|
---|
| 8 | CREATE TABLE default_records (
|
---|
| 9 | record_id serial NOT NULL,
|
---|
[89] | 10 | longrec_id integer,
|
---|
[50] | 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
|
---|
| 23 | COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
|
---|
| 24 | 1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
|
---|
| 25 | 2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
|
---|
| 26 | 3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
|
---|
| 27 | 4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
|
---|
| 28 | 5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
|
---|
| 29 | 6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
|
---|
| 30 | 7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
|
---|
| 31 | \.
|
---|
| 32 |
|
---|
[85] | 33 | CREATE 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 |
|
---|
| 43 | CREATE 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
|
---|
[86] | 52 | COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
|
---|
[85] | 53 | 1 1 1 default
|
---|
| 54 | \.
|
---|
| 55 |
|
---|
[91] | 56 | -- entry is text due to possible long entries from AXFR - a domain with "many"
|
---|
| 57 | -- odd records will overflow varchar(200)
|
---|
[85] | 58 | CREATE TABLE log (
|
---|
[89] | 59 | log_id serial NOT NULL,
|
---|
[85] | 60 | domain_id integer,
|
---|
| 61 | user_id integer,
|
---|
| 62 | group_id integer,
|
---|
| 63 | email character varying(60),
|
---|
| 64 | name character varying(60),
|
---|
[91] | 65 | entry text,
|
---|
[85] | 66 | stamp timestamp with time zone DEFAULT now()
|
---|
| 67 | );
|
---|
| 68 |
|
---|
| 69 | CREATE 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,
|
---|
[86] | 85 | user_id integer UNIQUE,
|
---|
| 86 | group_id integer UNIQUE
|
---|
[85] | 87 | );
|
---|
| 88 |
|
---|
| 89 | -- Need *two* basic permissions; one for the initial group, one for the default admin user
|
---|
[86] | 90 | 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] | 91 | 1 f f f f f f f f t t t t t t \N 1
|
---|
[86] | 92 | 2 t f f f f f f f f f f f f f 1 \N
|
---|
[85] | 93 | \.
|
---|
| 94 |
|
---|
[89] | 95 | -- fixme: need to handle looooong records (eg, SPF)
|
---|
[50] | 96 | CREATE TABLE records (
|
---|
| 97 | domain_id integer NOT NULL,
|
---|
| 98 | record_id serial NOT NULL,
|
---|
[89] | 99 | longrec_id integer,
|
---|
[50] | 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,
|
---|
[85] | 106 | ttl integer DEFAULT 7200 NOT NULL,
|
---|
[50] | 107 | description character varying(255)
|
---|
| 108 | );
|
---|
| 109 |
|
---|
[89] | 110 | CREATE TABLE longrecs (
|
---|
| 111 | longrec_id serial NOT NULL,
|
---|
| 112 | recdata text
|
---|
| 113 | );
|
---|
| 114 |
|
---|
[50] | 115 | CREATE 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 | );
|
---|
| 121 |
|
---|
| 122 | -- Types are required. NB: these are vaguely read-only too
|
---|
[85] | 123 | -- data from http://www.iana.org/assignments/dns-parameters
|
---|
[50] | 124 | COPY rectypes (val, name, stdflag, listorder) FROM stdin;
|
---|
| 125 | 1 A 1 1
|
---|
| 126 | 2 NS 1 2
|
---|
| 127 | 3 MD 2 255
|
---|
| 128 | 4 MF 2 255
|
---|
| 129 | 5 CNAME 1 6
|
---|
| 130 | 6 SOA 0 8
|
---|
| 131 | 7 MB 3 255
|
---|
| 132 | 8 MG 3 255
|
---|
| 133 | 9 MR 3 255
|
---|
| 134 | 10 NULL 3 255
|
---|
| 135 | 11 WKS 3 255
|
---|
| 136 | 12 PTR 2 4
|
---|
| 137 | 13 HINFO 3 255
|
---|
| 138 | 14 MINFO 3 255
|
---|
| 139 | 15 MX 1 3
|
---|
| 140 | 16 TXT 1 5
|
---|
| 141 | 17 RP 2 255
|
---|
| 142 | 18 AFSDB 3 255
|
---|
| 143 | 19 X25 3 255
|
---|
| 144 | 20 ISDN 3 255
|
---|
| 145 | 21 RT 3 255
|
---|
| 146 | 22 NSAP 3 255
|
---|
| 147 | 23 NSAP-PTR 3 255
|
---|
| 148 | 24 SIG 3 255
|
---|
| 149 | 25 KEY 3 255
|
---|
| 150 | 26 PX 3 255
|
---|
| 151 | 27 GPOS 3 255
|
---|
| 152 | 28 AAAA 2 2
|
---|
| 153 | 29 LOC 3 255
|
---|
| 154 | 30 NXT 3 255
|
---|
| 155 | 31 EID 3 255
|
---|
| 156 | 32 NIMLOC 3 255
|
---|
| 157 | 33 SRV 1 7
|
---|
| 158 | 34 ATMA 3 255
|
---|
| 159 | 35 NAPTR 3 255
|
---|
| 160 | 36 KX 3 255
|
---|
| 161 | 37 CERT 3 255
|
---|
| 162 | 38 A6 3 3
|
---|
| 163 | 39 DNAME 3 255
|
---|
| 164 | 40 SINK 3 255
|
---|
| 165 | 41 OPT 3 255
|
---|
| 166 | 42 APL 3 255
|
---|
| 167 | 43 DS 3 255
|
---|
| 168 | 44 SSHFP 3 255
|
---|
| 169 | 45 IPSECKEY 3 255
|
---|
| 170 | 46 RRSIG 3 255
|
---|
| 171 | 47 NSEC 3 255
|
---|
| 172 | 48 DNSKEY 3 255
|
---|
| 173 | 49 DHCID 3 255
|
---|
| 174 | 50 NSEC3 3 255
|
---|
| 175 | 51 NSEC3PARAM 3 255
|
---|
| 176 | 55 HIP 3 255
|
---|
| 177 | 99 SPF 3 255
|
---|
| 178 | 100 UINFO 3 255
|
---|
| 179 | 101 UID 3 255
|
---|
| 180 | 102 GID 3 255
|
---|
| 181 | 103 UNSPEC 3 255
|
---|
| 182 | 249 TKEY 3 255
|
---|
| 183 | 250 TSIG 3 255
|
---|
| 184 | 251 IXFR 3 255
|
---|
| 185 | 252 AXFR 3 255
|
---|
| 186 | 253 MAILB 3 255
|
---|
| 187 | 254 MAILA 3 255
|
---|
| 188 | 32768 TA 3 255
|
---|
| 189 | 32769 DLV 3 255
|
---|
| 190 | \.
|
---|
| 191 |
|
---|
| 192 | CREATE TABLE users (
|
---|
| 193 | user_id serial NOT NULL,
|
---|
| 194 | group_id integer DEFAULT 1 NOT NULL,
|
---|
| 195 | username character varying(60) NOT NULL,
|
---|
| 196 | "password" character varying(34) NOT NULL,
|
---|
[85] | 197 | firstname character varying(60),
|
---|
| 198 | lastname character varying(60),
|
---|
[50] | 199 | phone character varying(15),
|
---|
| 200 | "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
|
---|
| 201 | status integer DEFAULT 1 NOT NULL,
|
---|
[85] | 202 | permission_id integer DEFAULT 1 NOT NULL,
|
---|
| 203 | inherit_perm boolean DEFAULT true NOT NULL
|
---|
[50] | 204 | );
|
---|
| 205 |
|
---|
| 206 | -- create initial default user? may be better to create an "initialize" script or something
|
---|
[86] | 207 | COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
|
---|
[89] | 208 | 1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
|
---|
[50] | 209 | \.
|
---|
| 210 |
|
---|
| 211 | --
|
---|
| 212 | -- contraints. add these here so initial data doesn't get added strangely.
|
---|
| 213 | --
|
---|
| 214 |
|
---|
| 215 | -- primary keys
|
---|
[65] | 216 | ALTER TABLE ONLY permissions
|
---|
| 217 | ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
|
---|
| 218 |
|
---|
[50] | 219 | ALTER TABLE ONLY groups
|
---|
| 220 | ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
|
---|
| 221 |
|
---|
| 222 | ALTER TABLE ONLY domains
|
---|
| 223 | ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
|
---|
| 224 |
|
---|
| 225 | ALTER TABLE ONLY domains
|
---|
| 226 | ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
|
---|
| 227 |
|
---|
| 228 | ALTER TABLE ONLY default_records
|
---|
| 229 | ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
|
---|
| 230 |
|
---|
| 231 | ALTER TABLE ONLY records
|
---|
| 232 | ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
|
---|
| 233 |
|
---|
| 234 | ALTER TABLE ONLY rectypes
|
---|
| 235 | ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
|
---|
| 236 |
|
---|
| 237 | ALTER TABLE ONLY users
|
---|
| 238 | ADD CONSTRAINT users_pkey PRIMARY KEY (username);
|
---|
| 239 |
|
---|
| 240 | ALTER TABLE ONLY users
|
---|
| 241 | ADD CONSTRAINT uidu UNIQUE (user_id);
|
---|
| 242 |
|
---|
| 243 | -- foreign keys
|
---|
[65] | 244 | -- fixme: permissions FK refs
|
---|
[50] | 245 | ALTER TABLE ONLY domains
|
---|
| 246 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 247 |
|
---|
| 248 | ALTER TABLE ONLY default_records
|
---|
| 249 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 250 |
|
---|
| 251 | ALTER TABLE ONLY records
|
---|
| 252 | ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
|
---|
| 253 |
|
---|
| 254 | ALTER TABLE ONLY users
|
---|
| 255 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
---|
| 256 |
|
---|
| 257 | ALTER TABLE ONLY groups
|
---|
| 258 | ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
|
---|
[86] | 259 |
|
---|
| 260 | -- set starting sequence numbers, since we've inserted data before they're active
|
---|
| 261 | SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
|
---|
| 262 | SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);
|
---|
[87] | 263 | SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
|
---|
| 264 | SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
|
---|
[86] | 265 | SELECT pg_catalog.setval('records_record_id_seq', 1, false);
|
---|
| 266 | SELECT pg_catalog.setval('users_user_id_seq', 2, false);
|
---|