Context Navigation

source: trunk/dns.sql@ 471

Last change on this file since 471 was 456, checked in by Kris Deugau, 12 years ago

/trunk

Refiddle/retweak the available record types in the initial tabledef
and 1.0->1.2 upgrade SQL. AAAA+PTR template should be available
but is effectively be ignored on export until DNS server software
has a native way to represent the data in a single entry.

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

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

Download in other formats: