Context Navigation

source: trunk/dns.sql@ 214

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

/trunk

Begin setting up the barest sketch of what will eventually become
a complete automagical DB init/upgrade feature. See #34.

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

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

Download in other formats: