id SERIAL,
name character varying(100) NOT NULL,
sys_user character varying(10) NOT NULL,
- sys_edit timestamp without time zone NOT NULL
+ sys_edit timestamp without time zone NOT NULL,
+ UNIQUE(name)
);
CREATE UNIQUE INDEX vmail_domain_id ON vmail_domain USING btree (id);
vmail_domain_id integer NOT NULL,
username character varying(50) NOT NULL,
password character varying(50),
- forward character varying(150),
active integer DEFAULT 0 NOT NULL,
sys_user character varying(10) NOT NULL,
- sys_edit timestamp without time zone NOT NULL
+ sys_edit timestamp without time zone NOT NULL,
+ UNIQUE(vmail_domain_id,username)
);
CREATE UNIQUE INDEX vmail_user_id ON vmail_user USING btree (id);
CREATE INDEX vmail_user_vmail_domain_id ON vmail_user USING btree (vmail_domain_id);
+-- ALTER TABLE ONLY vmail_user
+-- ADD CONSTRAINT vmail_user_vmail_domain_id_username_key UNIQUE (vmail_domain_id, username);
+
+CREATE TABLE vmail_alias (
+ id SERIAL,
+ vmail_domain_id integer NOT NULL,
+ username character varying(50) NOT NULL,
+ destination TEXT DEFAULT NULL,
+ active integer DEFAULT 0 NOT NULL,
+ sys_user character varying(10) NOT NULL,
+ sys_edit timestamp without time zone NOT NULL,
+ UNIQUE(vmail_domain_id,username)
+);
+
+CREATE UNIQUE INDEX vmail_alias_id ON vmail_alias USING btree (id);
+CREATE INDEX vmail_alias_vmail_domain_id ON vmail_alias USING btree (vmail_domain_id);
+
REVOKE ALL ON TABLE vmail_user FROM PUBLIC;
GRANT SELECT ON TABLE vmail_user TO "vmail";
GRANT ALL ON TABLE vmail_user TO "hallinta";
REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_user_id_seq TO "hallinta";
+REVOKE ALL ON TABLE vmail_alias FROM PUBLIC;
+GRANT SELECT ON TABLE vmail_alias TO "vmail";
+GRANT ALL ON TABLE vmail_alias TO "hallinta";
+
+REVOKE ALL ON SEQUENCE vmail_alias_id_seq FROM PUBLIC;
+GRANT ALL ON SEQUENCE vmail_alias_id_seq TO "hallinta";
+
REVOKE ALL ON TABLE vmail_domain FROM PUBLIC;
GRANT SELECT ON TABLE vmail_domain TO "vmail";
GRANT ALL ON TABLE vmail_domain TO "hallinta";