summaryrefslogtreecommitdiff
path: root/hosts/surtr/postgresql.nix
blob: e4a83e63179995c121054d3dbee1a2fb2a991d3b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
{ pkgs, sources, ... }:
let
  versioning = sources.psql-versioning.src;
in {
  config = {
    services.postgresql = {
      enable = true;
      package = pkgs.postgresql_14;
      initialScript = pkgs.writeText "schema.sql" ''
        CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
        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";

        CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
        CREATE USER "postfix";
        GRANT CONNECT ON DATABASE "email" TO "postfix";
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix";
        CREATE USER "dovecot2";
        GRANT CONNECT ON DATABASE "email" TO "dovecot2";
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO "dovecot2";
      '';
    };

    systemd.services.postgresql = {
      postStart = ''
        psql email postgres -eXf ${pkgs.writeText "email.sql" ''
          \i ${versioning + "/install.versioning.sql"}

          BEGIN;
          select _v.register_patch('000-base', null, null);

          create table virtual_mailbox (
            id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
            local text,
            CHECK (local IS DISTINCT FROM ''''),
            domain text NOT NULL,
            CHECK (domain <> ''''),
            mailbox text NOT NULL,
            CHECK (mailbox <> ''''),
            UNIQUE(COALESCE(local, ''''), domain)
          );
          COMMIT;
        ''}
      '';
    };
  };
}