Context Navigation

source: trunk/dns.sql@ 77

Last change on this file since 77 was 65, checked in by Kris Deugau, 14 years ago

/trunk

checkpoint, adding permissions/ACL support

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

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

Download in other formats: