Context Navigation

source: trunk/dns.sql@ 87

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

/trunk

Checkpoint. Permissions manipulation should be complete.
Still need to set up permissions *checking*.

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

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

Download in other formats: