Context Navigation

source: trunk/dns.sql@ 91

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

/trunk

SQL tabledef update:

Tweak log table definition to cope with looooong entries (mainly from AXFR warnings)

DNSDB.pm:

Add domainID and getRecCount subs in DNSDB
Return more user-friendly error on attempting to add a domain with no name
Nitpick-tweak DB calls and SQL formatting in domainName()
Replace placeholderish OK message in importAXFR()

dns.cgi:

Trim some more direct SQL out in reclist
Uninitialized-variable cleanup in AXFR import
Add logging to AXFR import
Don't complain about uninitialized variables in record-display

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

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

Download in other formats: