From ba86ae504d8ea9796e43c1b061aa070761cd1323 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Mon, 21 Nov 2022 18:58:56 +0100 Subject: pgbackrest --- hosts/surtr/postgresql.nix | 172 --------------------------------------------- 1 file changed, 172 deletions(-) delete mode 100644 hosts/surtr/postgresql.nix (limited to 'hosts/surtr/postgresql.nix') diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix deleted file mode 100644 index c10c5084..00000000 --- a/hosts/surtr/postgresql.nix +++ /dev/null @@ -1,172 +0,0 @@ -{ pkgs, sources, config, ... }: -let - versioning = sources.psql-versioning.src; -in { - config = { - services.postgresql = { - enable = true; - package = pkgs.postgresql_14; - }; - - systemd.services.migrate-postgresql = { - after = [ "postgresql.service" ]; - bindsTo = [ "postgresql.service" ]; - wantedBy = [ "postgresql.service" ]; - - serviceConfig = { - Type = "oneshot"; - inherit (config.systemd.services.postgresql.serviceConfig) User Group; - RemainAfterExit = true; - }; - - path = [ config.services.postgresql.package ]; - script = '' - psql postgres postgres -eXf ${pkgs.writeText "schema.sql" '' - CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - CREATE DATABASE "etebase" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - ''} - - psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-matrix-users', null, null); - - CREATE USER "matrix-synapse"; - GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse"; - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse"; - COMMIT; - ''} - - psql email postgres -eXf ${pkgs.writeText "email.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-users', null, null); - - CREATE USER "postfix"; - GRANT CONNECT ON DATABASE "email" TO "postfix"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix"; - CREATE USER "dovecot2"; - GRANT CONNECT ON DATABASE "email" TO "dovecot2"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('001-spm', null, null); - - CREATE USER "spm"; - GRANT CONNECT ON DATABASE "email" TO "spm"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('000-base', null, null); - - CREATE TABLE mailbox ( - id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), - mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''), - quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END), - CONSTRAINT mailbox_unique UNIQUE (mailbox) - ); - CREATE TABLE mailbox_mapping ( - id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), - local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''), - domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''), - mailbox uuid REFERENCES mailbox(id), - CONSTRAINT local_domain_unique UNIQUE (local, domain) - ); - CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL; - - CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; - CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox; - - CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null); - - CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('002-citext', ARRAY['000-base'], null); - - DROP VIEW virtual_mailbox_domain; - DROP VIEW virtual_mailbox_mapping; - DROP VIEW imap_user; - DROP VIEW lmtp_mapping; - - CREATE EXTENSION citext; - - ALTER TABLE mailbox ALTER mailbox TYPE citext; - ALTER TABLE mailbox_mapping ALTER local TYPE citext; - ALTER TABLE mailbox_mapping ALTER domain TYPE citext; - - CREATE VIEW mailbox_quota_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; - - CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; - CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping; - CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule; - CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null); - - ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END); - - DROP VIEW virtual_mailbox_mapping; - DROP VIEW lmtp_mapping; - - CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup FROM mailbox_mapping WHERE mailbox IS NOT NULL; - CREATE VIEW virtual_mailbox_access (lookup, action) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup, CASE WHEN mailbox IS NULL THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping; - CREATE VIEW lmtp_mapping ("user", quota_rule, local, extension, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, extension, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null); - - ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey; - ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT; - COMMIT; - - BEGIN; - SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null); - - GRANT INSERT ON "mailbox_mapping" TO "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null); - - GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('007-ccert-sender-policy', ARRAY['000-base'], null); - - CREATE USER "postfix-ccert-sender-policy"; - GRANT CONNECT ON DATABASE "email" TO "postfix-ccert-sender-policy"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy"; - GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy"; - COMMIT; - ''} - - psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-user', null, null); - - CREATE USER "etebase"; - GRANT ALL PRIVILEGES ON DATABASE "etebase" TO "etebase"; - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "etebase"; - COMMIT; - ''} - ''; - }; - }; -} -- cgit v1.2.3