Context Navigation

source: trunk/dns.sql@ 287

Last change on this file since 287 was 249, checked in by Kris Deugau, 13 years ago

/trunk

Fix lurking bug in SQL tabledef from early idea for default rdns
records
Fix logic bugs in A+PTR creation in default records:

we should NOT blindly prepend 'ZONE.' if it's present in the value/IP
we should not blindly append $config{domain} if ADMINDOMAIN is in the hostname
we need to check for "ZONE.1", "ZONE,1", and "ZONE::1" in the "does this PTR exist?" check because otherwise we'll silently end up with duplicates

Minor tweak to call to addRec() so that changes from validation
get propagated all the way back up the call chain.
See #26

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

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

Download in other formats: