pgadmin-container-host-socket

Modules

Run pgAdmin (or any bundled web DB admin) inside a private-network NixOS container that reaches the host's PostgreSQL over a bind-mounted /run/postgresql Unix socket — not TCP.

Problem

pgAdmin ships a whole web stack (Python + a bundled server). Running it directly on the host drags that stack into the host's network namespace and tempts you into opening a TCP port on PostgreSQL just so the admin UI can reach it. You want:

• pgAdmin isolated in its own namespace,
• the DB reachable with no TCP listener and no password on the wire for local connections,
• a clean reverse-proxy handoff to nginx for TLS.

A NixOS container with privateNetwork = true plus a bind-mounted socket directory gives you all three.

The load-bearing details (traps)

Four things look redundant but are each doing real work. Remove any one and it breaks in a way that's annoying to debug:

1. Bind-mount /run/postgresql read-write into the container. This is the whole trick: pgAdmin (and the optional role-setup step) connect to the host database over the Unix socket. No TCP, no network exposure.

2. Pin the container's services.postgresql.package to the host's. The container only needs the client libraries, but they must match the server version, or you get protocol/catalog mismatch errors that look like corruption. The module reads config.services.postgresql.package from the host and forces the same package inside.

3. DEFAULT_SERVER = "0.0.0.0". By default pgAdmin binds loopback only. Inside the container that means the host-side nginx proxy at localAddress:port gets connection-refused. Binding 0.0.0.0 makes it listen on the container's veth so the proxy can reach it.

4. Order. The container is after/requires postgresql.service so the socket exists before pgAdmin starts (otherwise it comes up unable to connect and you must restart it). The container→host firewall is kept minimal: only DNS to the host resolver is allowed on the veth (ve-pgadmin); the host-initiated nginx proxy → pgAdmin traffic is accepted as an established connection, so no blanket trustedInterfaces entry is needed.

Usage

Import the module and enable it:

{
  imports = [ ./modules/pgadmin-container-host-socket ];

  modules.services.pgadmin = {
    enable = true;
    domain = "pgadmin.example.com";        # nginx vhost + ACME cert name
    passwordFile = "/run/secrets/pgadmin-initial-password";
    operatorUser = "alice";                # optional human who may read dataDir
  };
}

Provision passwordFile with your secrets tooling (sops-nix, agenix, a systemd credential — anything that lands a file on disk). The module never writes secrets into the Nix store.

Creating a DB login role

Set createUser = true and provide pgadminPasswordFile to have the host run a one-shot that idempotently creates/updates a pgadmin PostgreSQL role (CREATEDB + CREATEROLE). It runs as the postgres user over the local socket, waits for pg_isready, and is ordered before the container.

modules.services.pgadmin = {
  enable = true;
  createUser = true;
  pgadminPasswordFile = "/run/secrets/pgadmin-db-password";
  # ...
};

The role password is never placed on the psql command line (argv is world-readable via /proc/<pid>/cmdline on a default host). The one-shot feeds all SQL to psql over stdin and has psql read the secret itself into a variable, which also expands as a properly escaped literal to avoid SQL injection.

Options

Option	Default	Purpose
enable	false	Turn the module on.
enableNginx	true	Publish through a local nginx reverse proxy.
domain	null	nginx virtual-host name (required if enableNginx).
acmeHost	domain	ACME cert name for TLS; null serves plain HTTP.
passwordFile	null	Initial pgAdmin web login password (required).
operatorUser	null	Human account added to the pgadmin group to read dataDir.
createUser	false	Provision a pgadmin login role in the host DB.
pgadminPasswordFile	null	Password for that role (required if createUser).
dataDir	/var/lib/pgadmin	Persistent state directory (mode 0700).
port	5050	Port pgAdmin listens on inside the container.
email	admin@example.com	Initial pgAdmin login email.
uid / gid	5050	Ownership of dataDir.
containerNetwork.hostAddress	192.168.202.1	Host side of the veth pair.
containerNetwork.localAddress	192.168.202.2	Container side of the veth pair.

Caveats

• The container reaches the DB as whatever role local socket auth grants. With the common peer/trust local setup, the container process can act as privileged DB roles — treat the container boundary as part of your DB trust model.
• dataDir is mode 0700; only operatorUser (if set) and the service user can read it.
• If you terminate TLS elsewhere, set acmeHost = null (or enableNginx = false) and point your own proxy at localAddress:port.
• The /run/postgresql bind mount assumes the host uses the default socket directory. Adjust both sides if yours differs.

Security notes

• Container→host firewall is intentionally minimal. The veth allows only DNS to the host resolver; pgAdmin is an internet-facing web app with a CVE history, so it is deliberately not granted blanket access to host-bound services. If pgAdmin genuinely needs to reach another host port, add a scoped rule under networking.firewall.interfaces."ve-pgadmin" rather than trusting the whole interface.
• DB role password is quoted safely. When createUser is set, the one-shot has psql read the secret into a variable and expand it via :'pw', which escapes it as a SQL string literal — a password containing quotes cannot break out or inject DDL. The secret also never appears on any process argv.

Source

modules/pgadmin-container-host-socket/default.nix

# pgAdmin in a private-network NixOS container that reaches the host
# PostgreSQL over a bind-mounted /run/postgresql Unix socket (no TCP).
#
# Import this file as a NixOS module, then set e.g.:
#
#   modules.services.pgadmin = {
#     enable       = true;
#     domain       = "pgadmin.example.com";
#     passwordFile = "/run/secrets/pgadmin-initial-password";
#     operatorUser = "alice";   # optional: human who may read dataDir
#   };
#
# The `passwordFile` should be provisioned out-of-band (sops-nix, agenix,
# a systemd credential, etc.) — this module never bakes secrets into the store.

{
  config,
  lib,
  pkgs,
  ...
}:
with lib;
let
  cfg = config.modules.services.pgadmin;

  # The container must speak the SAME PostgreSQL client version as the host
  # server, otherwise protocol / catalog mismatches surface as confusing
  # connection errors. Pin the container's package to whatever the host runs.
  hostPostgresPackage = config.services.postgresql.package;
in
{
  options.modules.services.pgadmin = {
    enable = mkEnableOption "pgAdmin in a host-socket container";

    createUser = mkEnableOption "creating a login role in the host database";

    enableNginx = mkOption {
      type = types.bool;
      default = true;
      description = "Publish pgAdmin through a local nginx reverse proxy.";
    };

    domain = mkOption {
      type = types.nullOr types.str;
      default = null;
      example = "pgadmin.example.com";
      description = "Virtual-host name for the nginx reverse proxy.";
    };

    acmeHost = mkOption {
      type = types.nullOr types.str;
      default = cfg.domain;
      example = "pgadmin.example.com";
      description = ''
        ACME certificate name to use for TLS. Defaults to `domain`.
        Set to null to serve plain HTTP (e.g. behind another TLS terminator).
      '';
    };

    operatorUser = mkOption {
      type = types.nullOr types.str;
      default = null;
      example = "alice";
      description = ''
        Optional human account to add to the `pgadmin` group so it can read
        the (mode 0700) data directory. Leave null to grant no extra access.
      '';
    };

    passwordFile = mkOption {
      type = types.nullOr types.path;
      default = null;
      description = ''
        Path to a file containing the initial pgAdmin web login password.
        Bind-mounted into the container. Provision it with your secrets tool.
      '';
    };

    pgadminPasswordFile = mkOption {
      type = types.nullOr types.path;
      default = null;
      description = ''
        Path to a file containing the password for the `pgadmin` PostgreSQL
        role. Only required when `createUser` is true.
      '';
    };

    dataDir = mkOption {
      type = types.str;
      default = "/var/lib/pgadmin";
      description = "Host directory holding pgAdmin's persistent state.";
    };

    port = mkOption {
      type = types.port;
      default = 5050;
      description = "TCP port pgAdmin listens on inside the container.";
    };

    email = mkOption {
      type = types.str;
      default = "admin@example.com";
      description = "Initial pgAdmin login email.";
    };

    uid = mkOption {
      type = types.int;
      default = 5050;
      description = "UID owning the data directory on the host.";
    };

    gid = mkOption {
      type = types.int;
      default = cfg.uid;
      description = "GID owning the data directory on the host.";
    };

    containerNetwork = {
      hostAddress = mkOption {
        type = types.str;
        default = "192.168.202.1";
        description = "Host side of the container veth pair.";
      };
      localAddress = mkOption {
        type = types.str;
        default = "192.168.202.2";
        description = "Container side of the container veth pair.";
      };
    };
  };

  config = mkIf cfg.enable (mkMerge [
    # --- Optional nginx reverse proxy ------------------------------------
    (mkIf (cfg.enableNginx && cfg.domain != null) {
      services.nginx.enable = true;
      services.nginx.virtualHosts.${cfg.domain} = mkMerge [
        {
          locations."/" = {
            proxyPass = "http://${cfg.containerNetwork.localAddress}:${toString cfg.port}/";
            proxyWebsockets = true;
            recommendedProxySettings = true;
          };
        }
        (mkIf (cfg.acmeHost != null) {
          forceSSL = true;
          useACMEHost = cfg.acmeHost;
        })
      ];
    })

    # --- Core: the container + socket wiring ------------------------------
    {
      assertions = [
        {
          assertion = cfg.passwordFile != null;
          message = "modules.services.pgadmin: passwordFile must be set when enabled.";
        }
        {
          assertion = !cfg.enableNginx || cfg.domain != null;
          message = "modules.services.pgadmin: domain must be set when enableNginx is true.";
        }
        {
          assertion = cfg.pgadminPasswordFile != null || !cfg.createUser;
          message = "modules.services.pgadmin: pgadminPasswordFile must be set when createUser is true.";
        }
      ];

      # The host socket dir must exist before the container starts, or pgAdmin
      # boots unable to connect. Order the container after PostgreSQL.
      systemd.services."container@pgadmin" = {
        after = [ "postgresql.service" ];
        requires = [ "postgresql.service" ];
      };

      containers.pgadmin = {
        autoStart = true;
        privateNetwork = true;
        inherit (cfg.containerNetwork) hostAddress localAddress;

        config = _: {
          services.pgadmin = {
            enable = true;
            initialEmail = cfg.email;
            initialPasswordFile = cfg.passwordFile;
            inherit (cfg) port;
            openFirewall = true;
            settings = {
              # Bind the veth, not just loopback — otherwise the host-side
              # nginx proxy at localAddress:port gets connection-refused.
              DEFAULT_SERVER = "0.0.0.0";
            };
          };

          # Match the host server's client libraries exactly.
          services.postgresql.package = hostPostgresPackage;

          system.stateVersion = "24.11";
          networking.nameservers = [ cfg.containerNetwork.hostAddress ];
          networking.firewall.allowedTCPPorts = [ cfg.port ];
        };

        bindMounts = {
          # Web login secret.
          "${cfg.passwordFile}" = {
            hostPath = cfg.passwordFile;
          };
          # Persistent pgAdmin state.
          "${cfg.dataDir}" = {
            hostPath = cfg.dataDir;
          };
          # The load-bearing bit: the host's PostgreSQL Unix socket dir.
          # pgAdmin connects over this socket instead of TCP.
          "/run/postgresql" = {
            hostPath = "/run/postgresql";
            isReadOnly = false;
          };
        };
      };

      # Do NOT blanket-trust the container's veth. The only traffic the
      # container legitimately originates toward the host is DNS to the host
      # resolver (see `networking.nameservers` above); allow just that. The
      # nginx proxy → pgAdmin path is host-initiated, so its return packets are
      # already accepted as an established connection and need no extra rule.
      # Narrowing this means a compromised pgAdmin cannot reach arbitrary
      # host-bound services (metrics, ssh, other admin ports) over the veth.
      networking.firewall.interfaces."ve-pgadmin" = {
        allowedUDPPorts = [ 53 ];
        allowedTCPPorts = [ 53 ];
      };

      users.users.pgadmin = {
        inherit (cfg) uid;
        group = "pgadmin";
        isSystemUser = true;
      };

      users.groups.pgadmin = {
        inherit (cfg) gid;
        members = optional (cfg.operatorUser != null) cfg.operatorUser ++ [ "pgadmin" ];
      };

      systemd.tmpfiles.rules = [
        "d ${cfg.dataDir} 0700 ${toString cfg.uid} ${toString cfg.gid} - -"
      ];

      # Optional: idempotently create/refresh a login role in the host DB.
      # Runs as `postgres` over the local socket, before the container starts,
      # once PostgreSQL is accepting connections.
      systemd.services.pgadmin-postgres-setup = mkIf cfg.createUser {
        description = "Provision PostgreSQL login role for pgAdmin";
        wantedBy = [ "multi-user.target" ];
        before = [ "container@pgadmin.service" ];
        after = [ "postgresql.service" ];
        requires = [ "postgresql.service" ];
        serviceConfig = {
          Type = "oneshot";
          RemainAfterExit = true;
          User = "postgres";
          Group = "postgres";
        };
        script = ''
          while ! ${pkgs.postgresql}/bin/pg_isready -q; do
            sleep 1
          done

          # Feed all SQL over stdin — NEVER put the password on the psql
          # command line (argv is world-readable via /proc/<pid>/cmdline on a
          # default NixOS host, so `-c "... PASSWORD '$PASSWORD' ..."` would
          # leak the DB credential to any local user during this oneshot).
          #
          # psql reads the secret itself: `\set pw ` + a backtick command
          # captures the file contents into a psql variable (its output never
          # appears on any process's argv). `:'pw'` then expands to a properly
          # escaped SQL string literal, which also prevents SQL injection if the
          # password contains quotes. Variable interpolation does not happen
          # inside dollar-quoted DO blocks, so we use a \gexec-driven create
          # plus a plain ALTER instead.
          ${pkgs.postgresql}/bin/psql -v ON_ERROR_STOP=1 --no-psqlrc postgres <<'SQL'
          \set pw `cat ${cfg.pgadminPasswordFile}`
          SELECT 'CREATE ROLE pgadmin LOGIN'
            WHERE NOT EXISTS (
              SELECT FROM pg_catalog.pg_roles WHERE rolname = 'pgadmin'
            )
          \gexec
          ALTER ROLE pgadmin WITH LOGIN PASSWORD :'pw' CREATEDB CREATEROLE;
          SQL
        '';
      };
    }
  ]);
}