nixos postgresql monstrosities

datomic-console.module.nix

{
    services.postgresql = {
      enable = true;
      ensureDatabases = [ "datomic" ];
      ensureUsers = [{
        name = "datomic";
        ensurePermissions = {
          "DATABASE \"datomic\"" = "ALL PRIVILEGES";
        };
      } {
        name = config.hdirect.user;
        ensurePermissions = {
          "DATABASE \"datomic\"" = "CONNECT";
          ## this would go to wrong db, see https://discourse.nixos.org/t/postgresql-user-permission-setup-for-database-tables-access/5897
          # "ALL TABLES IN SCHEMA public" = "SELECT";
          # "ALL SEQUENCES IN SCHEMA public" = "SELECT";
        };
      } {
        name = "datomic-console";
        ensurePermissions = {
          "DATABASE \"datomic\"" = "CONNECT";
          # "ALL TABLES IN SCHEMA public" = "SELECT";
          # "ALL SEQUENCES IN SCHEMA public" = "SELECT";
        };
      }];
    };
    systemd.services.postgresql.postStart = lib.mkAfter ''
      $PSQL datomic -tAc 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${config.hdirect.user}'
      $PSQL datomic -tAc 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO ${config.hdirect.user}'
      $PSQL datomic -tAc 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "datomic-console"'
      $PSQL datomic -tAc 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "datomic-console"'
    '';
}

matrix.module.nix

{
  ## simulate "if not exists" for create role and create database
  ## https://stackoverflow.com/questions/52589849/create-database-if-not-exists-in-postgres
  ## https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
  hst.postgres.initSql = ''
    DO $$
      BEGIN
        CREATE ROLE "matrix-synapse" WITH LOGIN;
        EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
      END
    $$;

    CREATE EXTENSION IF NOT EXISTS dblink;
    DO $$
      BEGIN
        IF EXISTS (SELECT FROM pg_database WHERE datname = 'matrix-synapse') THEN
          RAISE NOTICE 'Database "matrix-synapse" already exists';
        ELSE
          PERFORM dblink_exec('dbname=' || current_database(), '
            CREATE DATABASE "matrix-synapse" WITH OWNER "matrix-synapse"
              TEMPLATE template0
              LC_COLLATE = "C"
              LC_CTYPE = "C"
          ');
        END IF;
      END
    $$;
  '';
}