Context Navigation

source: trunk/dns.sql@ 374

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

/trunk

Checkpoint; adding location/view support. See #10.

add location to menu
add table and link fields to initial tabledef and upgrade SQL
add listing subs and list page
update permissions list and subpage template with new permissions

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