Context Navigation

source: trunk/dns.sql@ 302

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

/trunk

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

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

Download in other formats: