| 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 | -- tabledefs and preloaded data bits
|
|---|
| 9 | CREATE TABLE permissions (
|
|---|
| 10 | permission_id SERIAL NOT NULL,
|
|---|
| 11 | admin boolean DEFAULT 'n' NOT NULL,
|
|---|
| 12 | self_edit boolean DEFAULT 'n' NOT NULL,
|
|---|
| 13 | group_create boolean DEFAULT 'n' NOT NULL,
|
|---|
| 14 | group_edit boolean DEFAULT 'n' NOT NULL,
|
|---|
| 15 | group_delete boolean DEFAULT 'n' NOT NULL,
|
|---|
| 16 | user_create boolean DEFAULT 'n' NOT NULL,
|
|---|
| 17 | user_edit boolean DEFAULT 'n' NOT NULL,
|
|---|
| 18 | user_delete boolean DEFAULT 'n' NOT NULL,
|
|---|
| 19 | domain_create boolean DEFAULT 'n' NOT NULL,
|
|---|
| 20 | domain_edit boolean DEFAULT 'n' NOT NULL,
|
|---|
| 21 | domain_delete boolean DEFAULT 'n' NOT NULL,
|
|---|
| 22 | record_create boolean DEFAULT 'n' NOT NULL,
|
|---|
| 23 | record_edit boolean DEFAULT 'n' NOT NULL,
|
|---|
| 24 | record_delete boolean DEFAULT 'n' NOT NULL
|
|---|
| 25 | );
|
|---|
| 26 |
|
|---|
| 27 | -- Need *two* basic permissions; one for the initial group, one for the default admin user
|
|---|
| 28 | 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) FROM stdin;
|
|---|
| 29 | 1 n n n n n n n n n n n n n n
|
|---|
| 30 | 2 y n n n n n n n n n n n n n
|
|---|
| 31 | \.
|
|---|
| 32 |
|
|---|
| 33 | CREATE TABLE groups (
|
|---|
| 34 | group_id serial NOT NULL,
|
|---|
| 35 | parent_group_id integer DEFAULT 1 NOT NULL,
|
|---|
| 36 | permission_id integer DEFAULT 1 NOT NULL,
|
|---|
| 37 | group_name character varying(255) DEFAULT ''::character varying NOT NULL
|
|---|
| 38 | );
|
|---|
| 39 |
|
|---|
| 40 | -- Provide a basic default group
|
|---|
| 41 | COPY groups (group_id, parent_group_id, group_name) FROM stdin;
|
|---|
| 42 | 1 1 1 default
|
|---|
| 43 | \.
|
|---|
| 44 |
|
|---|
| 45 | CREATE TABLE domains (
|
|---|
| 46 | domain_id serial NOT NULL,
|
|---|
| 47 | "domain" character varying(80) NOT NULL,
|
|---|
| 48 | group_id integer DEFAULT 1 NOT NULL,
|
|---|
| 49 | description character varying(255) DEFAULT ''::character varying NOT NULL,
|
|---|
| 50 | status integer DEFAULT 1 NOT NULL,
|
|---|
| 51 | zserial integer,
|
|---|
| 52 | sertype character(1) DEFAULT 'D'::bpchar
|
|---|
| 53 | );
|
|---|
| 54 |
|
|---|
| 55 | CREATE TABLE default_records (
|
|---|
| 56 | record_id serial NOT NULL,
|
|---|
| 57 | group_id integer DEFAULT 1 NOT NULL,
|
|---|
| 58 | host character varying(100) DEFAULT ''::character varying NOT NULL,
|
|---|
| 59 | "type" integer DEFAULT 1 NOT NULL,
|
|---|
| 60 | val character varying(100) DEFAULT ''::character varying NOT NULL,
|
|---|
| 61 | distance integer DEFAULT 0 NOT NULL,
|
|---|
| 62 | weight integer DEFAULT 0 NOT NULL,
|
|---|
| 63 | port integer DEFAULT 0 NOT NULL,
|
|---|
| 64 | ttl integer DEFAULT 86400 NOT NULL,
|
|---|
| 65 | description character varying(255)
|
|---|
| 66 | );
|
|---|
| 67 |
|
|---|
| 68 | -- default records for the default group
|
|---|
| 69 | COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin;
|
|---|
| 70 | 1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N
|
|---|
| 71 | 2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N
|
|---|
| 72 | 3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N
|
|---|
| 73 | 4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N
|
|---|
| 74 | 5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N
|
|---|
| 75 | 6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N
|
|---|
| 76 | 7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N
|
|---|
| 77 | \.
|
|---|
| 78 |
|
|---|
| 79 | CREATE TABLE records (
|
|---|
| 80 | domain_id integer NOT NULL,
|
|---|
| 81 | record_id serial NOT NULL,
|
|---|
| 82 | host character varying(100) DEFAULT ''::character varying NOT NULL,
|
|---|
| 83 | "type" integer DEFAULT 1 NOT NULL,
|
|---|
| 84 | val character varying(100) DEFAULT ''::character varying NOT NULL,
|
|---|
| 85 | distance integer DEFAULT 0 NOT NULL,
|
|---|
| 86 | weight integer DEFAULT 0 NOT NULL,
|
|---|
| 87 | port integer DEFAULT 0 NOT NULL,
|
|---|
| 88 | ttl integer DEFAULT 86400 NOT NULL,
|
|---|
| 89 | description character varying(255)
|
|---|
| 90 | );
|
|---|
| 91 |
|
|---|
| 92 | CREATE TABLE rectypes (
|
|---|
| 93 | val integer NOT NULL,
|
|---|
| 94 | name character varying(12) NOT NULL,
|
|---|
| 95 | stdflag integer DEFAULT 1 NOT NULL,
|
|---|
| 96 | listorder integer DEFAULT 255 NOT NULL
|
|---|
| 97 | );
|
|---|
| 98 |
|
|---|
| 99 | -- Types are required. NB: these are vaguely read-only too
|
|---|
| 100 | COPY rectypes (val, name, stdflag, listorder) FROM stdin;
|
|---|
| 101 | 1 A 1 1
|
|---|
| 102 | 2 NS 1 2
|
|---|
| 103 | 3 MD 2 255
|
|---|
| 104 | 4 MF 2 255
|
|---|
| 105 | 5 CNAME 1 6
|
|---|
| 106 | 6 SOA 0 8
|
|---|
| 107 | 7 MB 3 255
|
|---|
| 108 | 8 MG 3 255
|
|---|
| 109 | 9 MR 3 255
|
|---|
| 110 | 10 NULL 3 255
|
|---|
| 111 | 11 WKS 3 255
|
|---|
| 112 | 12 PTR 2 4
|
|---|
| 113 | 13 HINFO 3 255
|
|---|
| 114 | 14 MINFO 3 255
|
|---|
| 115 | 15 MX 1 3
|
|---|
| 116 | 16 TXT 1 5
|
|---|
| 117 | 17 RP 2 255
|
|---|
| 118 | 18 AFSDB 3 255
|
|---|
| 119 | 19 X25 3 255
|
|---|
| 120 | 20 ISDN 3 255
|
|---|
| 121 | 21 RT 3 255
|
|---|
| 122 | 22 NSAP 3 255
|
|---|
| 123 | 23 NSAP-PTR 3 255
|
|---|
| 124 | 24 SIG 3 255
|
|---|
| 125 | 25 KEY 3 255
|
|---|
| 126 | 26 PX 3 255
|
|---|
| 127 | 27 GPOS 3 255
|
|---|
| 128 | 28 AAAA 2 2
|
|---|
| 129 | 29 LOC 3 255
|
|---|
| 130 | 30 NXT 3 255
|
|---|
| 131 | 31 EID 3 255
|
|---|
| 132 | 32 NIMLOC 3 255
|
|---|
| 133 | 33 SRV 1 7
|
|---|
| 134 | 34 ATMA 3 255
|
|---|
| 135 | 35 NAPTR 3 255
|
|---|
| 136 | 36 KX 3 255
|
|---|
| 137 | 37 CERT 3 255
|
|---|
| 138 | 38 A6 3 3
|
|---|
| 139 | 39 DNAME 3 255
|
|---|
| 140 | 40 SINK 3 255
|
|---|
| 141 | 41 OPT 3 255
|
|---|
| 142 | 42 APL 3 255
|
|---|
| 143 | 43 DS 3 255
|
|---|
| 144 | 44 SSHFP 3 255
|
|---|
| 145 | 45 IPSECKEY 3 255
|
|---|
| 146 | 46 RRSIG 3 255
|
|---|
| 147 | 47 NSEC 3 255
|
|---|
| 148 | 48 DNSKEY 3 255
|
|---|
| 149 | 49 DHCID 3 255
|
|---|
| 150 | 50 NSEC3 3 255
|
|---|
| 151 | 51 NSEC3PARAM 3 255
|
|---|
| 152 | 55 HIP 3 255
|
|---|
| 153 | 99 SPF 3 255
|
|---|
| 154 | 100 UINFO 3 255
|
|---|
| 155 | 101 UID 3 255
|
|---|
| 156 | 102 GID 3 255
|
|---|
| 157 | 103 UNSPEC 3 255
|
|---|
| 158 | 249 TKEY 3 255
|
|---|
| 159 | 250 TSIG 3 255
|
|---|
| 160 | 251 IXFR 3 255
|
|---|
| 161 | 252 AXFR 3 255
|
|---|
| 162 | 253 MAILB 3 255
|
|---|
| 163 | 254 MAILA 3 255
|
|---|
| 164 | 32768 TA 3 255
|
|---|
| 165 | 32769 DLV 3 255
|
|---|
| 166 | \.
|
|---|
| 167 |
|
|---|
| 168 | CREATE TABLE users (
|
|---|
| 169 | user_id serial NOT NULL,
|
|---|
| 170 | group_id integer DEFAULT 1 NOT NULL,
|
|---|
| 171 | username character varying(60) NOT NULL,
|
|---|
| 172 | "password" character varying(34) NOT NULL,
|
|---|
| 173 | firstname character varying(30),
|
|---|
| 174 | lastname character varying(30),
|
|---|
| 175 | phone character varying(15),
|
|---|
| 176 | "type" character(1) DEFAULT 'S'::bpchar NOT NULL,
|
|---|
| 177 | status integer DEFAULT 1 NOT NULL,
|
|---|
| 178 | acl character varying(40) DEFAULT 'b'::character varying NOT NULL,
|
|---|
| 179 | permission_id DEFAULT 1 NOT NULL,
|
|---|
| 180 | );
|
|---|
| 181 |
|
|---|
| 182 | -- create initial default user? may be better to create an "initialize" script or something
|
|---|
| 183 | COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, acl) FROM stdin;
|
|---|
| 184 | 1 1 test@test $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1 \N \N \N S 1 A 2
|
|---|
| 185 | \.
|
|---|
| 186 |
|
|---|
| 187 | CREATE TABLE log (
|
|---|
| 188 | domain_id integer,
|
|---|
| 189 | user_id integer,
|
|---|
| 190 | group_id integer,
|
|---|
| 191 | email character varying(60),
|
|---|
| 192 | name character varying(60),
|
|---|
| 193 | entry character varying(200),
|
|---|
| 194 | stamp timestamp with time zone
|
|---|
| 195 | );
|
|---|
| 196 |
|
|---|
| 197 | --
|
|---|
| 198 | -- contraints. add these here so initial data doesn't get added strangely.
|
|---|
| 199 | --
|
|---|
| 200 |
|
|---|
| 201 | -- primary keys
|
|---|
| 202 | ALTER TABLE ONLY permissions
|
|---|
| 203 | ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
|
|---|
| 204 |
|
|---|
| 205 | ALTER TABLE ONLY groups
|
|---|
| 206 | ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
|
|---|
| 207 |
|
|---|
| 208 | ALTER TABLE ONLY domains
|
|---|
| 209 | ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain");
|
|---|
| 210 |
|
|---|
| 211 | ALTER TABLE ONLY domains
|
|---|
| 212 | ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
|
|---|
| 213 |
|
|---|
| 214 | ALTER TABLE ONLY default_records
|
|---|
| 215 | ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
|
|---|
| 216 |
|
|---|
| 217 | ALTER TABLE ONLY records
|
|---|
| 218 | ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
|
|---|
| 219 |
|
|---|
| 220 | ALTER TABLE ONLY rectypes
|
|---|
| 221 | ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
|
|---|
| 222 |
|
|---|
| 223 | ALTER TABLE ONLY users
|
|---|
| 224 | ADD CONSTRAINT users_pkey PRIMARY KEY (username);
|
|---|
| 225 |
|
|---|
| 226 | ALTER TABLE ONLY users
|
|---|
| 227 | ADD CONSTRAINT uidu UNIQUE (user_id);
|
|---|
| 228 |
|
|---|
| 229 | -- foreign keys
|
|---|
| 230 | -- fixme: permissions FK refs
|
|---|
| 231 | ALTER TABLE ONLY domains
|
|---|
| 232 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
|---|
| 233 |
|
|---|
| 234 | ALTER TABLE ONLY default_records
|
|---|
| 235 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
|---|
| 236 |
|
|---|
| 237 | ALTER TABLE ONLY records
|
|---|
| 238 | ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id);
|
|---|
| 239 |
|
|---|
| 240 | ALTER TABLE ONLY users
|
|---|
| 241 | ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
|
|---|
| 242 |
|
|---|
| 243 | ALTER TABLE ONLY groups
|
|---|
| 244 | ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
|
|---|
| 245 |
|
|---|
| 246 | -- set sequence start values - make sure we don't screw up adding
|
|---|
| 247 | -- records to tables that already have a few entries
|
|---|
| 248 | SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
|
|---|
| 249 |
|
|---|
| 250 | SELECT pg_catalog.setval('groups_group_id_seq', 52, true);
|
|---|
| 251 |
|
|---|
| 252 | SELECT pg_catalog.setval('domains_domain_id_seq', 953, true);
|
|---|
| 253 |
|
|---|
| 254 | SELECT pg_catalog.setval('default_records_record_id_seq', 320, true);
|
|---|
| 255 |
|
|---|
| 256 | SELECT pg_catalog.setval('records_record_id_seq', 660, true);
|
|---|
| 257 |
|
|---|
| 258 | SELECT pg_catalog.setval('users_user_id_seq', 37, true);
|
|---|
![[ DNS Administrator ]](/fx/dnsadmin-logo.png){kind=logo}
