-- 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'; -- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB CREATE TABLE misc ( misc_id serial NOT NULL, key text DEFAULT '' NOT NULL, value test DEFAULT '' NOT NULL ); COPY misc (misc_id, key, value) FROM stdin; 1 dbversion 1.0 \. CREATE TABLE default_records ( record_id serial NOT NULL, group_id integer DEFAULT 1 NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' 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 text ); -- 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 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 groups ( group_id serial NOT NULL, parent_group_id integer DEFAULT 1 NOT NULL, group_name character varying(255) DEFAULT ''::character varying NOT NULL, permission_id integer DEFAULT 1 NOT NULL, inherit_perm boolean DEFAULT true NOT NULL ); -- Provide a basic default group COPY groups (group_id, parent_group_id, permission_id, group_name) FROM stdin; 1 1 1 default \. -- entry is text due to possible long entries from AXFR - a domain with "many" -- odd records will overflow varchar(200) CREATE TABLE log ( log_id serial NOT NULL, domain_id integer, user_id integer, group_id integer, email character varying(60), name character varying(60), entry text, stamp timestamp with time zone DEFAULT now() ); CREATE TABLE permissions ( permission_id serial NOT NULL, "admin" boolean DEFAULT false NOT NULL, self_edit boolean DEFAULT false NOT NULL, group_create boolean DEFAULT false NOT NULL, group_edit boolean DEFAULT false NOT NULL, group_delete boolean DEFAULT false NOT NULL, user_create boolean DEFAULT false NOT NULL, user_edit boolean DEFAULT false NOT NULL, user_delete boolean DEFAULT false NOT NULL, domain_create boolean DEFAULT false NOT NULL, domain_edit boolean DEFAULT false NOT NULL, domain_delete boolean DEFAULT false NOT NULL, record_create boolean DEFAULT false NOT NULL, record_edit boolean DEFAULT false NOT NULL, record_delete boolean DEFAULT false NOT NULL, user_id integer UNIQUE, group_id integer UNIQUE ); -- 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, user_id, group_id) FROM stdin; 1 f f f f f f f f t t t t t t \N 1 2 t f f f f f f f f f f f f f 1 \N \. -- fixme: need to handle looooong records (eg, SPF) CREATE TABLE records ( domain_id integer NOT NULL, record_id serial NOT NULL, host text DEFAULT '' NOT NULL, "type" integer DEFAULT 1 NOT NULL, val text DEFAULT '' NOT NULL, distance integer DEFAULT 0 NOT NULL, weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, ttl integer DEFAULT 7200 NOT NULL, description text ); 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, alphaorder integer DEFAULT 32768 NOT NULL ); -- Types are required. NB: these are vaguely read-only too -- data from http://www.iana.org/assignments/dns-parameters COPY rectypes (val, name, stdflag, listorder, alphaorder) FROM stdin; 1 A 1 1 1 2 NS 1 2 37 3 MD 2 255 29 4 MF 2 255 30 5 CNAME 1 6 9 6 SOA 0 8 53 7 MB 3 255 28 8 MG 3 255 31 9 MR 3 255 33 10 NULL 3 255 43 11 WKS 3 255 64 12 PTR 2 4 46 13 HINFO 3 255 18 14 MINFO 3 255 32 15 MX 1 3 34 16 TXT 1 5 60 17 RP 2 255 48 18 AFSDB 3 255 4 19 X25 3 255 65 20 ISDN 3 255 21 21 RT 3 255 50 22 NSAP 3 255 38 23 NSAP-PTR 3 255 39 24 SIG 3 255 51 25 KEY 3 255 23 26 PX 3 255 47 27 GPOS 3 255 17 28 AAAA 1 2 3 29 LOC 3 255 25 30 NXT 3 255 44 31 EID 3 255 15 32 NIMLOC 3 255 36 33 SRV 1 7 55 34 ATMA 3 255 6 35 NAPTR 3 255 35 36 KX 3 255 24 37 CERT 3 255 8 38 A6 3 3 2 39 DNAME 3 255 12 40 SINK 3 255 52 41 OPT 3 255 45 42 APL 3 255 5 43 DS 3 255 14 44 SSHFP 3 255 56 45 IPSECKEY 3 255 20 46 RRSIG 3 255 49 47 NSEC 3 255 40 48 DNSKEY 3 255 13 49 DHCID 3 255 10 50 NSEC3 3 255 41 51 NSEC3PARAM 3 255 42 55 HIP 3 255 19 99 SPF 3 255 54 100 UINFO 3 255 62 101 UID 3 255 61 102 GID 3 255 16 103 UNSPEC 3 255 63 249 TKEY 3 255 58 250 TSIG 3 255 59 251 IXFR 3 255 22 252 AXFR 3 255 7 253 MAILB 3 255 27 254 MAILA 3 255 26 32768 TA 3 255 57 32769 DLV 3 255 11 \. 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(60), lastname character varying(60), phone character varying(15), "type" character(1) DEFAULT 'S'::bpchar NOT NULL, status integer DEFAULT 1 NOT NULL, permission_id integer DEFAULT 1 NOT NULL, inherit_perm boolean DEFAULT true 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, permission_id, inherit_perm) FROM stdin; 1 1 admin $1$PfEBUv9d$wV2/UG4gmKk08DLmdE8/d. Initial User \N S 1 2 f \. -- -- 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 starting sequence numbers, since we've inserted data before they're active SELECT pg_catalog.setval('misc_misc_id_seq', 1, true); SELECT pg_catalog.setval('default_records_record_id_seq', 8, true); SELECT pg_catalog.setval('domains_domain_id_seq', 1, false); SELECT pg_catalog.setval('groups_group_id_seq', 1, true); SELECT pg_catalog.setval('permissions_permission_id_seq', 2, true); SELECT pg_catalog.setval('records_record_id_seq', 1, false); SELECT pg_catalog.setval('users_user_id_seq', 2, false);