Context Navigation

source: trunk/dns.sql@ 210

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

/trunk

Set AAAA records to be available in initial database load

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
[206]	146	28 AAAA 1 2 3
[102]	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: