Context Navigation

source: trunk/dns.sql@ 154

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

/trunk

Duhhhhh... "text" fields don't have any performance penalty
over varchar or char. Rip out the longrecs business in favour
of much simpler record tables. Reverts addition of same from
r90.

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

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

Download in other formats: