Context Navigation

source: trunk/dns.sql@ 514

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

/trunk

Re-re-re-review and tweak the new-database initialization dns.sql script
and the dns-1.0-1.2.sql upgrade script to ensure the resulting structure
is as close as possible to identical to minimize any possible breakage.

Creating a database with the 1.0 SQL init, then applying the 1.0-1.2
upgrade script, will result in pg_dump output with only one difference;
the setval for the sequence on user_id in the users table. Sematically,
there is no difference in the database behaviour (the first new user
created will have ID 2), but the changes make it easier to copy-paste
pg_dump schema fragments if further updates to the default schema are
required.

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

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

Download in other formats: