Context Navigation

source: trunk/dns.sql@ 751

Last change on this file since 751 was 751, checked in by Kris Deugau, 7 years ago

/trunk

SQL definition updates in dns.sql and dns-upd-1.4.1.sql:

Update IANA RR type list
Add new ALIAS pseudotype mainly for "CNAME at domain root" support
Add new column on records table required by new pseudotype

File size: 12.4 KB

Rev	Line
[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
[599]	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
[212]	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,
[215]	29	value text DEFAULT '' NOT NULL
[212]	30	);
	31
	32	COPY misc (misc_id, key, value) FROM stdin;
[734]	33	1 dbversion 1.4.0
[212]	34	\.
	35
[370]	36	CREATE TABLE locations (
	37	location character varying (4) PRIMARY KEY,
[375]	38	loc_id serial UNIQUE,
[370]	39	group_id integer NOT NULL DEFAULT 1,
	40	iplist text NOT NULL DEFAULT '',
[375]	41	description character varying(40) NOT NULL DEFAULT '',
	42	comments text NOT NULL DEFAULT ''
[370]	43	);
	44
[50]	45	CREATE TABLE default_records (
	46	record_id serial NOT NULL,
	47	group_id integer DEFAULT 1 NOT NULL,
[130]	48	host text DEFAULT '' NOT NULL,
[50]	49	"type" integer DEFAULT 1 NOT NULL,
[130]	50	val text DEFAULT '' NOT NULL,
[50]	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,
[130]	55	description text
[50]	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
[224]	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
[249]	79	COPY default_rev_records (record_id, group_id, host, "type", val, ttl, description) FROM stdin;
[224]	80	1 1 hostmaster.ADMINDOMAIN:ns1.ADMINDOMAIN 6 3600:900:1048576:2560 3600
	81	2 1 unused-%r.ADMINDOMAIN 65283 ZONE 3600
[328]	82	3 1 ns2.example.com 2 ZONE 7200 \N
	83	4 1 ns1.example.com 2 ZONE 7200 \N
[224]	84	\.
	85
[85]	86	CREATE TABLE domains (
	87	domain_id serial NOT NULL,
[718]	88	"domain" character varying(80) NOT NULL,
[85]	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,
[366]	93	sertype character(1) DEFAULT 'D'::bpchar,
[370]	94	changed boolean DEFAULT true NOT NULL,
	95	default_location character varying (4) DEFAULT '' NOT NULL
[85]	96	);
[507]	97	-- ~2x performance boost iff most zones are fed to output from the cache
	98	CREATE INDEX dom_status_index ON domains (status);
[85]	99
[507]	100
[224]	101	CREATE TABLE revzones (
	102	rdns_id serial NOT NULL,
[718]	103	revnet cidr NOT NULL,
[224]	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,
[366]	108	sertype character(1) DEFAULT 'D'::bpchar,
[370]	109	changed boolean DEFAULT true NOT NULL,
[718]	110	default_location character varying(4) DEFAULT ''::character varying NOT NULL
[224]	111	);
[507]	112	CREATE INDEX rev_status_index ON revzones USING btree (status);
[224]	113
[85]	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
[86]	123	COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin;
[85]	124	1 1 1 default
	125	\.
	126
[91]	127	-- entry is text due to possible long entries from AXFR - a domain with "many"
	128	-- odd records will overflow varchar(200)
[85]	129	CREATE TABLE log (
[89]	130	log_id serial NOT NULL,
[85]	131	domain_id integer,
	132	user_id integer,
	133	group_id integer,
	134	email character varying(60),
	135	name character varying(60),
[91]	136	entry text,
[507]	137	stamp timestamp with time zone DEFAULT now(),
[739]	138	rdns_id integer,
	139	logparent integer NOT NULL DEFAULT 0
[85]	140	);
[734]	141	CREATE INDEX log_domain_id_index ON log(domain_id);
	142	CREATE INDEX log_user_id_index ON log(user_id);
	143	CREATE INDEX log_group_id_index ON log(group_id);
	144	CREATE INDEX log_rdns_id_index ON log(rdns_id);
[85]	145
	146	CREATE TABLE permissions (
	147	permission_id serial NOT NULL,
	148	"admin" boolean DEFAULT false NOT NULL,
	149	self_edit boolean DEFAULT false NOT NULL,
	150	group_create boolean DEFAULT false NOT NULL,
	151	group_edit boolean DEFAULT false NOT NULL,
	152	group_delete boolean DEFAULT false NOT NULL,
	153	user_create boolean DEFAULT false NOT NULL,
	154	user_edit boolean DEFAULT false NOT NULL,
	155	user_delete boolean DEFAULT false NOT NULL,
	156	domain_create boolean DEFAULT false NOT NULL,
	157	domain_edit boolean DEFAULT false NOT NULL,
	158	domain_delete boolean DEFAULT false NOT NULL,
	159	record_create boolean DEFAULT false NOT NULL,
	160	record_edit boolean DEFAULT false NOT NULL,
	161	record_delete boolean DEFAULT false NOT NULL,
[507]	162	user_id integer UNIQUE,
	163	group_id integer UNIQUE,
[387]	164	record_locchg boolean DEFAULT false NOT NULL,
[370]	165	location_create boolean DEFAULT false NOT NULL,
	166	location_edit boolean DEFAULT false NOT NULL,
	167	location_delete boolean DEFAULT false NOT NULL,
[507]	168	location_view boolean DEFAULT false NOT NULL
[85]	169	);
	170
	171	-- Need two basic permissions; one for the initial group, one for the default admin user
[507]	172	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;
	173	1 f f f f f f f f t t t t t t \N 1 f f f f f
	174	2 t f f f f f f f f f f f f f 1 \N f f f f f
[85]	175	\.
	176
[224]	177	-- rdns_id defaults to 0 since many records will not have an associated rDNS entry.
[50]	178	CREATE TABLE records (
[224]	179	domain_id integer NOT NULL DEFAULT 0,
[50]	180	record_id serial NOT NULL,
[130]	181	host text DEFAULT '' NOT NULL,
[50]	182	"type" integer DEFAULT 1 NOT NULL,
[130]	183	val text DEFAULT '' NOT NULL,
[50]	184	distance integer DEFAULT 0 NOT NULL,
	185	weight integer DEFAULT 0 NOT NULL,
	186	port integer DEFAULT 0 NOT NULL,
[85]	187	ttl integer DEFAULT 7200 NOT NULL,
[370]	188	description text,
[507]	189	rdns_id integer NOT NULL DEFAULT 0,
[543]	190	location character varying (4) DEFAULT '' NOT NULL,
	191	stamp TIMESTAMP WITH TIME ZONE DEFAULT 'epoch' NOT NULL,
	192	expires boolean DEFAULT 'n' NOT NULL,
[751]	193	stampactive boolean DEFAULT 'n' NOT NULL,
	194	auxdata text
[50]	195	);
[507]	196	CREATE INDEX rec_domain_index ON records USING btree (domain_id);
	197	CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
	198	CREATE INDEX rec_types_index ON records USING btree ("type");
[50]	199
	200	CREATE TABLE rectypes (
	201	val integer NOT NULL,
[224]	202	name character varying(20) NOT NULL,
[50]	203	stdflag integer DEFAULT 1 NOT NULL,
[102]	204	listorder integer DEFAULT 255 NOT NULL,
	205	alphaorder integer DEFAULT 32768 NOT NULL
[50]	206	);
	207
	208	-- Types are required. NB: these are vaguely read-only too
[751]	209	-- data from https://www.iana.org/assignments/dns-parameters
[102]	210	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	211	1 A 1 1 1
[456]	212	2 NS 2 10 37
[224]	213	3 MD 5 255 29
	214	4 MF 5 255 30
[456]	215	5 CNAME 2 12 9
[224]	216	6 SOA 0 0 53
	217	7 MB 5 255 28
	218	8 MG 5 255 31
	219	9 MR 5 255 33
	220	10 NULL 5 255 43
	221	11 WKS 5 255 64
[300]	222	12 PTR 3 5 46
[224]	223	13 HINFO 5 255 18
	224	14 MINFO 5 255 32
[456]	225	15 MX 1 11 34
	226	16 TXT 2 13 60
[224]	227	17 RP 4 255 48
	228	18 AFSDB 5 255 4
	229	19 X25 5 255 65
	230	20 ISDN 5 255 21
	231	21 RT 5 255 50
	232	22 NSAP 5 255 38
	233	23 NSAP-PTR 5 255 39
	234	24 SIG 5 255 51
	235	25 KEY 5 255 23
	236	26 PX 5 255 47
	237	27 GPOS 5 255 17
	238	28 AAAA 1 3 3
	239	29 LOC 5 255 25
	240	30 NXT 5 255 44
	241	31 EID 5 255 15
	242	32 NIMLOC 5 255 36
[456]	243	33 SRV 1 14 55
[224]	244	34 ATMA 5 255 6
	245	35 NAPTR 5 255 35
	246	36 KX 5 255 24
	247	37 CERT 5 255 8
	248	38 A6 5 3 2
	249	39 DNAME 5 255 12
	250	40 SINK 5 255 52
	251	41 OPT 5 255 45
	252	42 APL 5 255 5
	253	43 DS 5 255 14
	254	44 SSHFP 5 255 56
	255	45 IPSECKEY 5 255 20
	256	46 RRSIG 5 255 49
	257	47 NSEC 5 255 40
	258	48 DNSKEY 5 255 13
	259	49 DHCID 5 255 10
	260	50 NSEC3 5 255 41
	261	51 NSEC3PARAM 5 255 42
[751]	262	52 TLSA 5 255 255
	263	53 SMIMEA 5 255 255
[224]	264	55 HIP 5 255 19
[751]	265	56 NINFO 5 255 255
	266	57 RKEY 5 255 255
	267	58 TALINK 5 255 255
	268	59 CDS 5 255 255
	269	60 CDNSKEY 5 255 255
	270	61 OPENPGPKEY 5 255 255
	271	62 CSYNC 5 255 255
[224]	272	99 SPF 5 255 54
	273	100 UINFO 5 255 62
	274	101 UID 5 255 61
	275	102 GID 5 255 16
	276	103 UNSPEC 5 255 63
[751]	277	104 NID 5 255 255
	278	105 L32 5 255 255
	279	106 L64 5 255 255
	280	107 LP 5 255 255
	281	108 EUI48 5 255 255
	282	109 EUI64 5 255 255
[224]	283	249 TKEY 5 255 58
	284	250 TSIG 5 255 59
	285	251 IXFR 5 255 22
	286	252 AXFR 5 255 7
	287	253 MAILB 5 255 27
	288	254 MAILA 5 255 26
[751]	289	255 * 5 255 255
	290	256 URI 5 255 255
	291	257 CAA 5 255 255
	292	258 AVC 5 255 255
[224]	293	32768 TA 5 255 57
	294	32769 DLV 5 255 11
[50]	295	\.
	296
[224]	297	-- Custom types (ab)using the "Private use" range from 65280 to 65534
	298	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	299	65280 A+PTR 2 2 2
	300	65281 AAAA+PTR 2 4 4
[300]	301	65282 PTR template 3 6 2
[342]	302	65283 A+PTR template 2 7 2
[456]	303	65284 AAAA+PTR template 2 8 2
	304	65285 Delegation 2 9 2
[751]	305	65300 ALIAS 2 15 255
[224]	306	\.
	307
[50]	308	CREATE TABLE users (
	309	user_id serial NOT NULL,
	310	group_id integer DEFAULT 1 NOT NULL,
	311	username character varying(60) NOT NULL,
	312	"password" character varying(34) NOT NULL,
[85]	313	firstname character varying(60),
	314	lastname character varying(60),
[50]	315	phone character varying(15),
	316	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
	317	status integer DEFAULT 1 NOT NULL,
[85]	318	permission_id integer DEFAULT 1 NOT NULL,
	319	inherit_perm boolean DEFAULT true NOT NULL
[50]	320	);
	321
	322	-- create initial default user? may be better to create an "initialize" script or something
[86]	323	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
[89]	324	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
[50]	325	\.
	326
	327	--
	328	-- contraints. add these here so initial data doesn't get added strangely.
	329	--
	330
	331	-- primary keys
[65]	332	ALTER TABLE ONLY permissions
	333	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
	334
[50]	335	ALTER TABLE ONLY groups
	336	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
	337
	338	ALTER TABLE ONLY domains
	339	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
	340
[718]	341	ALTER TABLE ONLY domains
	342	ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);
	343
[50]	344	ALTER TABLE ONLY default_records
	345	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
	346
	347	ALTER TABLE ONLY records
	348	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
	349
	350	ALTER TABLE ONLY rectypes
	351	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
	352
[718]	353	ALTER TABLE ONLY revzones
	354	ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);
	355
	356	ALTER TABLE ONLY revzones
	357	ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);
	358
[50]	359	ALTER TABLE ONLY users
	360	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
	361
	362	ALTER TABLE ONLY users
	363	ADD CONSTRAINT uidu UNIQUE (user_id);
	364
	365	-- foreign keys
[65]	366	-- fixme: permissions FK refs
[370]	367	ALTER TABLE ONLY locations
	368	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	369
[50]	370	ALTER TABLE ONLY domains
	371	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	372
	373	ALTER TABLE ONLY default_records
	374	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	375
	376	ALTER TABLE ONLY users
	377	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	378
[507]	379	ALTER TABLE ONLY revzones
	380	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	381
[50]	382	ALTER TABLE ONLY groups
	383	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
[86]	384
	385	-- set starting sequence numbers, since we've inserted data before they're active
[507]	386	-- only set the ones that have data loaded with \copy, and obey the convention
	387	-- that comes out of pg_dump
	388	SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
	389	SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
	390	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
	391	SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
	392	SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
	393	SELECT pg_catalog.setval('users_user_id_seq', 1, true);

Note: See TracBrowser for help on using the repository browser.

Download in other formats: