Context Navigation

source: trunk/dns.sql@ 229

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

/trunk

Start adding rDNS (See #26)

Initial SQL tabledef, default records, and record types (we'll abuse the reserved range of DNS record types from 65280 to 65534 for some extra custom types)
Start passing $webvar{revrec} around similar to $webvar{defrec}
Adjust record list page to handle reverse records. Tested on group-default reverse records but should work fine for live zones
Add "Reverse Zones" and "Default Reverse Records" to menu
While we're rearranging it, add a little more paranoia on some of the SQL

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
	53	COPY default_rev_records (record_id, group_id, host, "type", ip, ttl, description) FROM stdin;
	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: