-- these lines could be run as a superuser. alter database name, username, password, group as appropriate. -- make sure to alter dnsdb.conf to match -- CREATE GROUP dnsdb; -- CREATE USER dnsdb WITH UNENCRYPTED PASSWORD 'secret' IN GROUP dnsdb; -- CREATE DATABASE dnsdb OWNED BY dnsdb; -- SET SESSION AUTHORIZATION 'dnsdb'; -- tabledefs and preloaded data bits CREATE TABLE permissions ( permission_id SERIAL NOT NULL, admin boolean DEFAULT 'n' NOT NULL, self_edit boolean DEFAULT 'n' NOT NULL, group_create boolean DEFAULT 'n' NOT NULL, group_edit boolean DEFAULT 'n' NOT NULL, group_delete boolean DEFAULT 'n' NOT NULL, user_create boolean DEFAULT 'n' NOT NULL, user_edit boolean DEFAULT 'n' NOT NULL, user_delete boolean DEFAULT 'n' NOT NULL, domain_create boolean DEFAULT 'n' NOT NULL, domain_edit boolean DEFAULT 'n' NOT NULL, domain_delete boolean DEFAULT 'n' NOT NULL, record_create boolean DEFAULT 'n' NOT NULL, record_edit boolean DEFAULT 'n' NOT NULL, record_delete boolean DEFAULT 'n' NOT NULL ); -- Need *two* basic permissions; one for the initial group, one for the default admin user 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; 1 n n n n n n n n n n n n n n 2 y n n n n n n n n n n n n n \. CREATE TABLE groups ( group_id serial NOT NULL, parent_group_id integer DEFAULT 1 NOT NULL, permission_id integer DEFAULT 1 NOT NULL, group_name character varying(255) DEFAULT ''::character varying NOT NULL ); -- Provide a basic default group COPY groups (group_id, parent_group_id, group_name) FROM stdin; 1 1 1 default \. CREATE TABLE domains ( domain_id serial NOT NULL, "domain" character varying(80) NOT NULL, group_id integer DEFAULT 1 NOT NULL, description character varying(255) DEFAULT ''::character varying NOT NULL, status integer DEFAULT 1 NOT NULL, zserial integer, sertype character(1) DEFAULT 'D'::bpchar ); CREATE TABLE default_records ( record_id serial NOT NULL, group_id integer DEFAULT 1 NOT NULL, host character varying(100) DEFAULT ''::character varying NOT NULL, "type" integer DEFAULT 1 NOT NULL, val character varying(100) DEFAULT ''::character varying NOT NULL, distance integer DEFAULT 0 NOT NULL, weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, ttl integer DEFAULT 86400 NOT NULL, description character varying(255) ); -- default records for the default group COPY default_records (record_id, group_id, host, "type", val, distance, weight, port, ttl, description) FROM stdin; 1 1 ns1.example.com:hostmaster.DOMAIN 6 10800:3600:604800:5400 0 0 0 86400 \N 2 1 DOMAIN 2 ns2.example.com 0 0 0 7200 \N 3 1 DOMAIN 2 ns1.example.com 0 0 0 7200 \N 4 1 DOMAIN 1 10.0.0.4 0 0 0 7200 \N 5 1 DOMAIN 15 mx1.example.com 10 0 0 7200 \N 6 1 www.DOMAIN 5 DOMAIN 0 0 0 10800 \N 7 1 DOMAIN 16 "v=spf1 a mx -all" 0 0 0 10800 \N \. CREATE TABLE records ( domain_id integer NOT NULL, record_id serial NOT NULL, host character varying(100) DEFAULT ''::character varying NOT NULL, "type" integer DEFAULT 1 NOT NULL, val character varying(100) DEFAULT ''::character varying NOT NULL, distance integer DEFAULT 0 NOT NULL, weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, ttl integer DEFAULT 86400 NOT NULL, description character varying(255) ); CREATE TABLE rectypes ( val integer NOT NULL, name character varying(12) NOT NULL, stdflag integer DEFAULT 1 NOT NULL, listorder integer DEFAULT 255 NOT NULL ); -- Types are required. NB: these are vaguely read-only too COPY rectypes (val, name, stdflag, listorder) FROM stdin; 1 A 1 1 2 NS 1 2 3 MD 2 255 4 MF 2 255 5 CNAME 1 6 6 SOA 0 8 7 MB 3 255 8 MG 3 255 9 MR 3 255 10 NULL 3 255 11 WKS 3 255 12 PTR 2 4 13 HINFO 3 255 14 MINFO 3 255 15 MX 1 3 16 TXT 1 5 17 RP 2 255 18 AFSDB 3 255 19 X25 3 255 20 ISDN 3 255 21 RT 3 255 22 NSAP 3 255 23 NSAP-PTR 3 255 24 SIG 3 255 25 KEY 3 255 26 PX 3 255 27 GPOS 3 255 28 AAAA 2 2 29 LOC 3 255 30 NXT 3 255 31 EID 3 255 32 NIMLOC 3 255 33 SRV 1 7 34 ATMA 3 255 35 NAPTR 3 255 36 KX 3 255 37 CERT 3 255 38 A6 3 3 39 DNAME 3 255 40 SINK 3 255 41 OPT 3 255 42 APL 3 255 43 DS 3 255 44 SSHFP 3 255 45 IPSECKEY 3 255 46 RRSIG 3 255 47 NSEC 3 255 48 DNSKEY 3 255 49 DHCID 3 255 50 NSEC3 3 255 51 NSEC3PARAM 3 255 55 HIP 3 255 99 SPF 3 255 100 UINFO 3 255 101 UID 3 255 102 GID 3 255 103 UNSPEC 3 255 249 TKEY 3 255 250 TSIG 3 255 251 IXFR 3 255 252 AXFR 3 255 253 MAILB 3 255 254 MAILA 3 255 32768 TA 3 255 32769 DLV 3 255 \. CREATE TABLE users ( user_id serial NOT NULL, group_id integer DEFAULT 1 NOT NULL, username character varying(60) NOT NULL, "password" character varying(34) NOT NULL, firstname character varying(30), lastname character varying(30), phone character varying(15), "type" character(1) DEFAULT 'S'::bpchar NOT NULL, status integer DEFAULT 1 NOT NULL, acl character varying(40) DEFAULT 'b'::character varying NOT NULL, permission_id DEFAULT 1 NOT NULL, ); -- create initial default user? may be better to create an "initialize" script or something COPY users (user_id, group_id, username, "password", firstname, lastname, phone, "type", status, acl) FROM stdin; 1 1 test@test $1$BByge8u2$48AaGX3YeHplfErX5Tlqa1 \N \N \N S 1 A 2 \. CREATE TABLE log ( domain_id integer, user_id integer, group_id integer, email character varying(60), name character varying(60), entry character varying(200), stamp timestamp with time zone ); -- -- contraints. add these here so initial data doesn't get added strangely. -- -- primary keys ALTER TABLE ONLY permissions ADD CONSTRAINT permissions_permission_id_key UNIQUE (permission_id); ALTER TABLE ONLY groups ADD CONSTRAINT groups_group_id_key UNIQUE (group_id); ALTER TABLE ONLY domains ADD CONSTRAINT domains_pkey PRIMARY KEY ("domain"); ALTER TABLE ONLY domains ADD CONSTRAINT domains_domain_id_key UNIQUE (domain_id); ALTER TABLE ONLY default_records ADD CONSTRAINT default_records_pkey PRIMARY KEY (record_id); ALTER TABLE ONLY records ADD CONSTRAINT records_pkey PRIMARY KEY (record_id); ALTER TABLE ONLY rectypes ADD CONSTRAINT rectypes_pkey PRIMARY KEY (val, name); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (username); ALTER TABLE ONLY users ADD CONSTRAINT uidu UNIQUE (user_id); -- foreign keys -- fixme: permissions FK refs ALTER TABLE ONLY domains ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); ALTER TABLE ONLY default_records ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); ALTER TABLE ONLY records ADD CONSTRAINT "$1" FOREIGN KEY (domain_id) REFERENCES domains(domain_id); ALTER TABLE ONLY users ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id); ALTER TABLE ONLY groups ADD CONSTRAINT group_parent FOREIGN KEY (parent_group_id) REFERENCES groups(group_id); -- set sequence start values - make sure we don't screw up adding -- records to tables that already have a few entries SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true); SELECT pg_catalog.setval('groups_group_id_seq', 52, true); SELECT pg_catalog.setval('domains_domain_id_seq', 953, true); SELECT pg_catalog.setval('default_records_record_id_seq', 320, true); SELECT pg_catalog.setval('records_record_id_seq', 660, true); SELECT pg_catalog.setval('users_user_id_seq', 37, true);