Index: trunk/dns.sql =================================================================== --- trunk/dns.sql (revision 84) +++ trunk/dns.sql (revision 85) @@ -5,51 +5,4 @@ -- 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 ( @@ -77,4 +30,64 @@ \. +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, group_name) FROM stdin; +1 1 1 default +\. + +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 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 DEFAULT 0 NOT NULL, + group_id integer DEFAULT 0 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 +\. + +-- fixeme: need to handle looooong TXT records (eg, SPF) CREATE TABLE records ( domain_id integer NOT NULL, @@ -86,5 +99,5 @@ weight integer DEFAULT 0 NOT NULL, port integer DEFAULT 0 NOT NULL, - ttl integer DEFAULT 86400 NOT NULL, + ttl integer DEFAULT 7200 NOT NULL, description character varying(255) ); @@ -98,4 +111,5 @@ -- 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) FROM stdin; 1 A 1 1 @@ -171,11 +185,11 @@ username character varying(60) NOT NULL, "password" character varying(34) NOT NULL, - firstname character varying(30), - lastname character varying(30), + 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, - acl character varying(40) DEFAULT 'b'::character varying NOT NULL, - permission_id DEFAULT 1 NOT NULL, + permission_id integer DEFAULT 1 NOT NULL, + inherit_perm boolean DEFAULT true NOT NULL ); @@ -184,14 +198,4 @@ 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 -); -- @@ -243,16 +247,2 @@ 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);