Context Navigation

source: trunk/dns.sql@ 368

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

/trunk

Update initial tabledef and 1.0 -> 1.2 conversion SQL to add "changed"
flag intended to help speed up export of large datasets by not actually
actively exporting all zones each time. Note for tinydns this will
require a static cache directory for a workspace so that unchanged
zones can have their data streamed into the main tinydns data file.

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

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

Download in other formats: