{pkgs, lib, config, ...}:
let inherit (lib) types;
    inherit (pkgs.lib) unlines unlinesAttrs;
    inherit (config) networking users;
    inherit (config.services) postgresql;
    psql = "${pkgs.postgresql}/bin/psql --set ON_ERROR_STOP=1 -U ${postgresql.superUser}";
    psqlRun = args: sql: "${psql} ${args} -f - <<EOF\n" + sql + "\nEOF\n";
    initLanguages = initLanguagePLPGSQL;
    initLanguagePLPGSQL =
      psqlRun "template1" ''
        CREATE OR REPLACE FUNCTION create_language_plpgsql()
          RETURNS BOOLEAN AS \$\$
            CREATE LANGUAGE plpgsql;
            SELECT TRUE;
          \$\$ LANGUAGE SQL;
        SELECT CASE WHEN NOT (
          SELECT  TRUE AS exists
          FROM    pg_language
          WHERE   lanname = 'plpgsql'
          UNION
          SELECT  FALSE AS exists
          ORDER BY exists DESC
          LIMIT 1
         )
        THEN
          create_language_plpgsql()
        ELSE
          FALSE
        END AS plpgsql_created;
        DROP FUNCTION create_language_plpgsql();
      '';
    initSchemas =
      initSchemaPublic +
      psqlRun "" (unlinesAttrs (schema: {owner, extraConfig, ...}: ''
        DO LANGUAGE plpgsql \$\$
        BEGIN
          IF NOT EXISTS (SELECT * FROM pg_catalog.pg_namespace WHERE nspname = "${schema}" LIMIT 1) THEN
            CREATE SCHEMA ${schema}
             AUTHORIZATION ${owner};
          END IF;
        END;
        \$\$;
        ${extraConfig}
      '') postgresql.schemas);
    initSchemaPublic =
      psqlRun "template1" ''
        -- NOTE: deny access to public schema,
        --       so that users do not see others' databases
        REVOKE ALL ON DATABASE template1 FROM public;
        REVOKE ALL ON SCHEMA   public    FROM public;
        GRANT  ALL ON SCHEMA   public    TO   ${postgresql.superUser};
      '' +
      psqlRun "template1" ''
        -- NOTE: deny public access to all tables
        REVOKE ALL ON ALL TABLES IN SCHEMA pg_catalog FROM public;
        REVOKE ALL ON               SCHEMA pg_catalog FROM public;
      '';
    initUsers = psqlRun ""
      (unlinesAttrs (user: {auth, extraConfig, ...}: ''
        DO LANGUAGE plpgsql \$\$
        BEGIN
          IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = '${user}' LIMIT 1) THEN
            CREATE ROLE ${user}
             LOGIN
             NOCREATEDB
             NOCREATEROLE
             NOINHERIT
             NOSUPERUSER;
          END IF;
        END;
        \$\$;
        GRANT USAGE ON SCHEMA public TO ${user};
        ${extraConfig}
      '') postgresql.users);
    initRoles = psqlRun ""
      (unlinesAttrs (role: {...}: ''
        DO LANGUAGE plpgsql \$\$
        BEGIN
          IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = "${role}" LIMIT 1) THEN
            CREATE ROLE ${role}
             NOLOGIN
             NOCREATEDB
             NOCREATEROLE
             NOINHERIT
             NOSUPERUSER;
          END IF;
        END;
        \$\$;
      '') postgresql.roles);
    initDatabases =
      unlinesAttrs (db: {owner ? db, users, extraConfig, ...}: ''
        # NOTE: CREATE DATABASE cannot be done inside a function or multi-lines input.
        exists="$(${psql} template1 -t -c "SELECT datname FROM pg_catalog.pg_database WHERE datname = '${db}' LIMIT 1")"
        test -n "$exists" ||
        ${psql} template1 -c "CREATE DATABASE ${db} WITH OWNER=${owner};"
        '' + psqlRun "template1" ''
          REVOKE ALL ON DATABASE ${db} FROM public;
          ${unlines (map (user: "GRANT CONNECT,TEMPORARY ON DATABASE ${db} TO ${user};") users)}
        '' +
        psqlRun db ''
          GRANT ALL ON SCHEMA public TO ${owner} WITH GRANT OPTION;
          ${extraConfig}
        ''
      ) postgresql.databases;
in
{
  options = {
    services.postgresql.databases = lib.mkOption {
      default = {};
      type = types.attrsOf (types.submodule ({name, options, config, ...}: {
        options = {
          data = lib.mkOption {
            type = types.lines;
            description = "The database's data in SQL.";
          };
          owner = lib.mkOption {
            type = types.str;
            description = "The database's owner.";
            default = name;
          };
          users = lib.mkOption {
            type = types.listOf types.str;
            description = "Databases' users.";
            default = [];
          };
          resetData = lib.mkOption {
            type = types.bool;
            description = "Whether to reset the data at each start of the postgresql service.";
            default = false;
          };
          extraConfig = lib.mkOption {
            type = types.lines;
            description = "Extra SQL config code (run on the database).";
            default = "";
          };
        };
      }));
    };
    services.postgresql.schemas = lib.mkOption {
      default = {};
      type = types.attrsOf (types.submodule ({name, options, config, ...}: {
        options = {
          data = lib.mkOption {
            type = types.lines;
            description = "The schema's data in SQL.";
          };
          resetData = lib.mkOption {
            type = types.bool;
            description = "Whether to reset the data at each start of the postgresql service.";
            default = false;
          };
          extraConfig = lib.mkOption {
            type = types.lines;
            description = "Extra SQL config code.";
            default = "";
          };
        };
      }));
    };
    services.postgresql.users = lib.mkOption {
      default = {};
      type = types.attrsOf (types.submodule ({name, options, config, ...}: {
        options = {
          auth = lib.mkOption {
            type = types.enum [ "unix" "password" ];
            description = "Authentification method.";
            default = "unix";
          };
          extraConfig = lib.mkOption {
            type = types.lines;
            description = "Extra SQL config code.";
            default = "";
          };
        };
      }));
    };
    services.postgresql.roles = lib.mkOption {
      default = {};
      type = types.attrsOf (types.submodule ({name, options, config, ...}: {
        options = {
        };
      }));
    };
  };
  config = {
    users.users = lib.mapAttrs (user: {auth, ...}:
      { extraGroups = lib.optionals (auth == "unix")
          [ users.users.postgres.group ];
      }
    ) postgresql.users;
    systemd.services.postgresql = {
      postStart = unlines [
        "set -x"
        initLanguages
        initSchemas
        initUsers
        initRoles
        initDatabases
      ];
      #serviceConfig = {
      #  RuntimeDirectory     = [ "postgresql" ];
      #  RuntimeDirectoryMode = "0775";
      #};
    };
    services = {
      postgresql = {
        enable = true;
        extraConfig = ''
          #unix_socket_directories = '/run/postgresql'
          #unix_socket_group = '${postgresql.superUser}-data'
          unix_socket_permissions = 0770 # begin with 0 to use octal notation
        '';
        identMap = lib.mkForce ''
          # MAPNAME  SYSTEM-USERNAME          PG-USERNAME
          admin      ${postgresql.superUser}  ${postgresql.superUser}
          admin      root                     ${postgresql.superUser}
          ${unlinesAttrs (user: {...}: ''user  root  ${user}'') postgresql.users}
          user       /^(.*)$                  \1
        '';
        authentication = lib.mkForce ''
          # CONNECTION  DATABASE  USER      AUTH  OPTIONS
          local         all       postgres  peer  map=admin
          local         all       backup    peer
          local         sameuser  all       peer  map=user
          local         samerole  all       peer  map=role
        '';
      };
      postgresqlBackup = {
        enable = true;
      };
    };
    #users.groups."${postgresql.superUser}-data" = {
    #  name = "${postgresql.superUser}-data";
    #};
  };
}