Context Navigation

source: trunk/dns.sql@ 351

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

/trunk

Forgot to include the initial tabledef/record type change to allow
TXT records in reverse zones in r350.

See #26.

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

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

Download in other formats: