Context Navigation

source: trunk/dns.sql@ 743

Last change on this file since 743 was 739, checked in by Kris Deugau, 8 years ago

/trunk

Update initial tabledef and update SQL for log view tweaks

File size: 12.0 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,
	193	stampactive boolean DEFAULT 'n' NOT NULL
[50]	194	);
[507]	195	CREATE INDEX rec_domain_index ON records USING btree (domain_id);
	196	CREATE INDEX rec_revzone_index ON records USING btree (rdns_id);
	197	CREATE INDEX rec_types_index ON records USING btree ("type");
[50]	198
	199	CREATE TABLE rectypes (
	200	val integer NOT NULL,
[224]	201	name character varying(20) NOT NULL,
[50]	202	stdflag integer DEFAULT 1 NOT NULL,
[102]	203	listorder integer DEFAULT 255 NOT NULL,
	204	alphaorder integer DEFAULT 32768 NOT NULL
[50]	205	);
	206
	207	-- Types are required. NB: these are vaguely read-only too
[85]	208	-- data from http://www.iana.org/assignments/dns-parameters
[102]	209	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	210	1 A 1 1 1
[456]	211	2 NS 2 10 37
[224]	212	3 MD 5 255 29
	213	4 MF 5 255 30
[456]	214	5 CNAME 2 12 9
[224]	215	6 SOA 0 0 53
	216	7 MB 5 255 28
	217	8 MG 5 255 31
	218	9 MR 5 255 33
	219	10 NULL 5 255 43
	220	11 WKS 5 255 64
[300]	221	12 PTR 3 5 46
[224]	222	13 HINFO 5 255 18
	223	14 MINFO 5 255 32
[456]	224	15 MX 1 11 34
	225	16 TXT 2 13 60
[224]	226	17 RP 4 255 48
	227	18 AFSDB 5 255 4
	228	19 X25 5 255 65
	229	20 ISDN 5 255 21
	230	21 RT 5 255 50
	231	22 NSAP 5 255 38
	232	23 NSAP-PTR 5 255 39
	233	24 SIG 5 255 51
	234	25 KEY 5 255 23
	235	26 PX 5 255 47
	236	27 GPOS 5 255 17
	237	28 AAAA 1 3 3
	238	29 LOC 5 255 25
	239	30 NXT 5 255 44
	240	31 EID 5 255 15
	241	32 NIMLOC 5 255 36
[456]	242	33 SRV 1 14 55
[224]	243	34 ATMA 5 255 6
	244	35 NAPTR 5 255 35
	245	36 KX 5 255 24
	246	37 CERT 5 255 8
	247	38 A6 5 3 2
	248	39 DNAME 5 255 12
	249	40 SINK 5 255 52
	250	41 OPT 5 255 45
	251	42 APL 5 255 5
	252	43 DS 5 255 14
	253	44 SSHFP 5 255 56
	254	45 IPSECKEY 5 255 20
	255	46 RRSIG 5 255 49
	256	47 NSEC 5 255 40
	257	48 DNSKEY 5 255 13
	258	49 DHCID 5 255 10
	259	50 NSEC3 5 255 41
	260	51 NSEC3PARAM 5 255 42
	261	55 HIP 5 255 19
	262	99 SPF 5 255 54
	263	100 UINFO 5 255 62
	264	101 UID 5 255 61
	265	102 GID 5 255 16
	266	103 UNSPEC 5 255 63
	267	249 TKEY 5 255 58
	268	250 TSIG 5 255 59
	269	251 IXFR 5 255 22
	270	252 AXFR 5 255 7
	271	253 MAILB 5 255 27
	272	254 MAILA 5 255 26
	273	32768 TA 5 255 57
	274	32769 DLV 5 255 11
[50]	275	\.
	276
[224]	277	-- Custom types (ab)using the "Private use" range from 65280 to 65534
	278	COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin;
	279	65280 A+PTR 2 2 2
	280	65281 AAAA+PTR 2 4 4
[300]	281	65282 PTR template 3 6 2
[342]	282	65283 A+PTR template 2 7 2
[456]	283	65284 AAAA+PTR template 2 8 2
	284	65285 Delegation 2 9 2
[224]	285	\.
	286
[50]	287	CREATE TABLE users (
	288	user_id serial NOT NULL,
	289	group_id integer DEFAULT 1 NOT NULL,
	290	username character varying(60) NOT NULL,
	291	"password" character varying(34) NOT NULL,
[85]	292	firstname character varying(60),
	293	lastname character varying(60),
[50]	294	phone character varying(15),
	295	"type" character(1) DEFAULT 'S'::bpchar NOT NULL,
	296	status integer DEFAULT 1 NOT NULL,
[85]	297	permission_id integer DEFAULT 1 NOT NULL,
	298	inherit_perm boolean DEFAULT true NOT NULL
[50]	299	);
	300
	301	-- create initial default user? may be better to create an "initialize" script or something
[86]	302	COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, permission_id, inherit_perm) FROM stdin;
[89]	303	1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f
[50]	304	\.
	305
	306	--
	307	-- contraints. add these here so initial data doesn't get added strangely.
	308	--
	309
	310	-- primary keys
[65]	311	ALTER TABLE ONLY permissions
	312	ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id);
	313
[50]	314	ALTER TABLE ONLY groups
	315	ADD CONSTRAINT groups_group_id_key UNIQUE (group_id);
	316
	317	ALTER TABLE ONLY domains
	318	ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id);
	319
[718]	320	ALTER TABLE ONLY domains
	321	ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain", default_location);
	322
[50]	323	ALTER TABLE ONLY default_records
	324	ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id);
	325
	326	ALTER TABLE ONLY records
	327	ADD CONSTRAINT records_pkey PRIMARY KEY (record_id);
	328
	329	ALTER TABLE ONLY rectypes
	330	ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name);
	331
[718]	332	ALTER TABLE ONLY revzones
	333	ADD CONSTRAINT revzones_rdns_id_key UNIQUE (rdns_id);
	334
	335	ALTER TABLE ONLY revzones
	336	ADD CONSTRAINT revzones_pkey PRIMARY KEY (revnet, default_location);
	337
[50]	338	ALTER TABLE ONLY users
	339	ADD CONSTRAINT users_pkey PRIMARY KEY (username);
	340
	341	ALTER TABLE ONLY users
	342	ADD CONSTRAINT uidu UNIQUE (user_id);
	343
	344	-- foreign keys
[65]	345	-- fixme: permissions FK refs
[370]	346	ALTER TABLE ONLY locations
	347	ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	348
[50]	349	ALTER TABLE ONLY domains
	350	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	351
	352	ALTER TABLE ONLY default_records
	353	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	354
	355	ALTER TABLE ONLY users
	356	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	357
[507]	358	ALTER TABLE ONLY revzones
	359	ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);
	360
[50]	361	ALTER TABLE ONLY groups
	362	ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id);
[86]	363
	364	-- set starting sequence numbers, since we've inserted data before they're active
[507]	365	-- only set the ones that have data loaded with \copy, and obey the convention
	366	-- that comes out of pg_dump
	367	SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
	368	SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
	369	SELECT pg_catalog.setval('default_rev_records_record_id_seq', 4, true);
	370	SELECT pg_catalog.setval('groups_group_id_seq', 1, true);
	371	SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true);
	372	SELECT pg_catalog.setval('users_user_id_seq', 1, true);

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

Download in other formats: