Untitled

DROP DATABASE admin;
DROP ROLE dummyuser;

CREATE DATABASE admin;

CREATE ROLE dummyuser WITH PASSWORD 'dummypassword' NOCREATEDB NOCREATEUSER;
ALTER USER dummyuser WITH LOGIN;
GRANT ALL ON DATABASE admin TO dummyuser;

\c admin;

---------------------
-- SCHEMA accounts
---------------------
CREATE SCHEMA accounts;

CREATE TABLE accounts.users (
                            username VARCHAR(80) PRIMARY KEY,
                            password VARCHAR(40) NOT NULL,
                            active BOOLEAN NOT NULL DEFAULT TRUE
                            );

CREATE TABLE accounts.userdetails (
                                username VARCHAR(80) PRIMARY KEY REFERENCES accounts.users,
                                firstname VARCHAR(128) NULL,
                                admin BOOLEAN NOT NULL DEFAULT FALSE,
                                name VARCHAR(128) NULL,
                                gender VARCHAR(10) NULL,
                                password_before1 VARCHAR(40) NULL,
                                password_before2 VARCHAR(40) NULL,
                                password_before3 VARCHAR(40) NULL,
                                street VARCHAR(128) NULL,
                                house VARCHAR(10) NULL,
                                city VARCHAR(128) NULL,
                                zip VARCHAR(12) NULL,
                                phone VARCHAR(128) NULL,
                                mobile VARCHAR(128) NULL,
                                fax VARCHAR(128) NULL,
                                email VARCHAR(128) NOT NULL,
                                email2 VARCHAR(128) NULL,
                                birthday VARCHAR(128) NULL,
                                last_login TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                                changed_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                                added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                                );

CREATE TABLE accounts.permission (
                                name VARCHAR(80) PRIMARY KEY,
                                menu_order INTEGER NOT NULL DEFAULT 0
                                );

CREATE TABLE accounts.permissions (
                                username VARCHAR(80) REFERENCES accounts.users,
                                permission VARCHAR(80) REFERENCES accounts.permission,
                                added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                                );

GRANT ALL ON SCHEMA accounts TO dummyuser;
GRANT ALL ON TABLE accounts.users TO dummyuser;
GRANT ALL ON TABLE accounts.userdetails TO dummyuser;
GRANT ALL ON TABLE accounts.permission TO dummyuser;
GRANT ALL ON TABLE accounts.permissions TO dummyuser;

---------------------
-- SCHEMA domains
---------------------
CREATE SCHEMA domains;

CREATE TABLE domains.domains (
                            DOMAIN VARCHAR(128) PRIMARY KEY,
                            active BOOLEAN NOT NULL DEFAULT TRUE,
                            public_vhost BOOLEAN NOT NULL DEFAULT FALSE,
                            public_mail BOOLEAN NOT NULL DEFAULT FALSE,
                            changed_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                            owned_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                            );

CREATE TABLE domains.subdomains (
                            DOMAIN VARCHAR(128) PRIMARY KEY,
                            active BOOLEAN NOT NULL DEFAULT TRUE,
                            rootdomain VARCHAR(128) REFERENCES domains.domains NOT NULL,
                            changed_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                            owned_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                            );

GRANT ALL ON SCHEMA domains TO dummyuser;
GRANT ALL ON TABLE domains.domains TO dummyuser;
GRANT ALL ON TABLE domains.subdomains TO dummyuser;

GRANT USAGE ON SCHEMA domains TO postfix;
GRANT SELECT ON TABLE domains.domains TO postfix;
GRANT SELECT ON TABLE domains.subdomains TO postfix;

---------------------
-- SCHEMA email
---------------------
CREATE SCHEMA email;

CREATE TABLE email.mailboxes (
                            address VARCHAR(80) PRIMARY KEY,
                            password VARCHAR(40) NOT NULL,
                            active BOOLEAN NOT NULL DEFAULT TRUE,
                            maildir VARCHAR(255) NOT NULL,
                            quota INTEGER NOT NULL DEFAULT 0,
                            last_changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                            owned_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                            );

CREATE TABLE email.forwardings (
                                SOURCE VARCHAR(128),
                                target VARCHAR(128),
                                active BOOLEAN NOT NULL DEFAULT TRUE,
                                changed_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                                owned_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                                added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                            );

GRANT ALL ON SCHEMA email TO dummyuser;
GRANT ALL ON TABLE email.mailboxes TO dummyuser;
GRANT ALL ON TABLE email.forwardings TO dummyuser;

GRANT USAGE ON SCHEMA email TO postfix;
GRANT SELECT ON TABLE email.mailboxes TO postfix;
GRANT SELECT ON TABLE email.forwardings TO postfix;

---------------------
-- SCHEMA server
---------------------
CREATE SCHEMA server;

CREATE TABLE server.webconfig (
                            servername VARCHAR(128),
                            DOMAIN VARCHAR(128),
                            active BOOLEAN NOT NULL DEFAULT TRUE,
                            administrator VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            changed_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                            changed TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                            );

CREATE TABLE server.news (
                        id serial PRIMARY KEY,
                        headline VARCHAR(128) NOT NULL,
                        content text NOT NULL,
                        added_by VARCHAR(80) REFERENCES accounts.users NOT NULL DEFAULT 'system',
                        added TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
                        );

GRANT ALL ON SCHEMA server TO dummyuser;
GRANT ALL ON TABLE server.webconfig TO dummyuser;
GRANT ALL ON TABLE server.news TO dummyuser;
GRANT ALL ON server.news_id_seq TO dummyuser;


-- FILL UP --
INSERT INTO accounts.users (username,password) VALUES ('system','50dbc4eXXXXXXXXXXXXXXXXXXXXXXXXXXXX4fc7d');
INSERT INTO accounts.userdetails (username,email,admin) VALUES ('system','simon.codingmonkey@googlemail.com',TRUE);
INSERT INTO server.webconfig (servername,DOMAIN) VALUES ('psaux','psaux.de');

INSERT INTO accounts.permission VALUES ('news',0);
INSERT INTO accounts.permission VALUES ('email',1);
INSERT INTO accounts.permission VALUES ('webserver',2);
INSERT INTO accounts.permission VALUES ('fileserver',10);
INSERT INTO accounts.permission VALUES ('vpn',20);
INSERT INTO accounts.permission VALUES ('settings',98);
INSERT INTO accounts.permission VALUES ('admin',99);
INSERT INTO accounts.permission VALUES ('logout',100);

INSERT INTO accounts.users (username,password) VALUES ('simon','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d');
INSERT INTO accounts.userdetails (username,email) VALUES ('simon','simon.codingmonkey@googlemail.com');
INSERT INTO accounts.users (username,password) VALUES ('sebbo','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d');
INSERT INTO accounts.userdetails (username,email) VALUES ('sebbo','sebbo@codingmonkey.de');
INSERT INTO accounts.users (username,password) VALUES ('mutti','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d');
INSERT INTO accounts.userdetails (username,email) VALUES ('mutti','barbara@psaux.de');
INSERT INTO accounts.users (username,password) VALUES ('fly','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d');
INSERT INTO accounts.userdetails (username,email) VALUES ('fly','fly@psaux.de');
INSERT INTO accounts.users (username,password) VALUES ('exi','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d');
INSERT INTO accounts.userdetails (username,email) VALUES ('exi','exi@psaux.de');

INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('psaux.de',TRUE,TRUE,'simon');
INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('geekhome.org',TRUE,TRUE,'simon');
INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('dance-different.de',TRUE,TRUE,'simon');
INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('mvs-verlag.de',FALSE,FALSE,'simon');
INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('twitsurvey.de',TRUE,TRUE,'simon');
INSERT INTO domains.domains (DOMAIN,public_mail,public_vhost,owned_by) VALUES ('static.145.102.40.188.clients.your-server.de',FALSE,FALSE,'simon');

INSERT INTO domains.subdomains (DOMAIN,rootdomain,owned_by) VALUES ('admin.psaux.de','psaux.de','simon');
INSERT INTO domains.subdomains (DOMAIN,rootdomain,owned_by) VALUES ('simon.psaux.de','psaux.de','simon');
INSERT INTO domains.subdomains (DOMAIN,rootdomain,owned_by) VALUES ('mail.psaux.de','psaux.de','simon');
INSERT INTO domains.subdomains (DOMAIN,rootdomain,owned_by) VALUES ('git.psaux.de','psaux.de','simon');

INSERT INTO email.mailboxes (address,password,maildir,owned_by) VALUES ('simon@psaux.de','50dbc4e57736a20c20547ccc72b815f6f4d4fc7d','simon@psaux.de/','simon');

INSERT INTO accounts.permissions VALUES ('simon','news');
INSERT INTO accounts.permissions VALUES ('simon','email');
INSERT INTO accounts.permissions VALUES ('simon','vpn');
INSERT INTO accounts.permissions VALUES ('simon','settings');
INSERT INTO accounts.permissions VALUES ('simon','admin');
INSERT INTO accounts.permissions VALUES ('simon','logout');

INSERT INTO server.news (content,headline,added_by) VALUES ('Hallo liebe user, ich hoffe ihr fühlt euch auf meinem Server wohl. Damit es noch ein bisschen flauschiger wird, habe ich endlich mal ein webfrontend gestrickt.<br/><br/>gruß simon','First Post!','simon');
INSERT INTO server.news (content,headline,added_by) VALUES ('Hi,<br/>Ich habe mal ein paar neue Services aufgesetzt. Guckt einfach mal auf der "Services" Seite vorbei.<br/><br/>gruß simon','New Services!','simon');