PGSandbox

PGSandbox is a local-first Rust CLI and MCP stdio server that lets coding agents create, inspect, use, clone, and delete disposable PostgreSQL databases. It is for engineers who want agents to validate migrations, SQL, seed data, and backend bug reproductions against a real isolated database without touching a shared developer database.

Key Features

• Creates one tracked PostgreSQL database and one scoped login role per task.

• Starts and reuses a managed local Postgres cluster under ~/.pgsandbox/ by default, without Docker and without binding localhost:5432.

• Supports explicit external Postgres admin profiles when you intentionally opt in through environment variables or a JSON config file.

• Enforces positive TTLs, max TTL caps, metadata-backed deletion, and optional per-owner active sandbox quotas.

• Runs user SQL through sandbox role credentials, not the admin connection.

• Returns bounded, typed SQL result sets so agents do not dump unbounded rows.

• Describes schemas, computes schema digests, diffs schemas, creates named schema snapshots, and returns EXPLAIN (FORMAT JSON) plans.

• Runs repo migration, seed, and smoke commands with sandbox credentials injected through environment variables or validated app-specific aliases, plus bounded stdin and output controls instead of temporary repo files.

• Returns host-local and Docker-friendly connection variants so containerized local apps can reach a host-managed sandbox without guessing the right host.

• Creates reusable local template artifacts from PGSandbox-owned sandboxes.

• Writes MCP client config for Codex, Cursor, VS Code, and Claude Desktop.

• Masks admin URLs and sandbox credentials in diagnostics and safe summaries.

Related MCP server: pg-mcp

Table Of Contents

• Tech Stack

• Prerequisites

• Getting Started

• Development From This Repo

• Architecture

• Environment Variables

• Available Scripts

• Testing

• Deployment

• Troubleshooting

• Contributing

• License

Tech Stack

• Language: Rust 2021 edition

• Runtime: Native CLI binary named pgsandbox

• MCP Framework: rmcp stdio server

• Async Runtime: Tokio

• Database Client: tokio-postgres

• TLS: native-tls and postgres-native-tls for Postgres URLs that use TLS options such as sslmode=require

• Configuration: Serde, JSON, TOML, and environment variables

• Serialization: serde, serde_json, serde_yaml_ng, and schemars

• Security Helpers: aes-gcm for encrypted sandbox role passwords, sha2 for digests/checksums, uuid for ids

• Diagnostics and HTTP: reqwest for telemetry delivery, url for connection-string parsing

• Website: Astro 6, TypeScript, Node.js, and npm under site/

• Testing: Cargo unit tests plus opt-in live Postgres integration tests

• Packaging: GitHub release archives, Homebrew tap packaging, and a hosted install script

• Deployment: Native binary distribution for the MCP server; CapRover deployment for the separate static Astro site

Prerequisites

For normal end-user installation:

• macOS or Linux on x86_64 or aarch64

• Local PostgreSQL server binaries for the managed local runtime. setup checks for them, installs PostgreSQL through a supported package manager when available, and starts the managed local cluster.

• Optional PostgreSQL dump tools for clone/template workflows: pg_dump and pg_restore

• One MCP client: Codex, Cursor, VS Code, Claude Desktop, or another client that can launch stdio MCP servers

• Homebrew, curl, or wget if installing a released binary

For repository development:

• Git

• Rust toolchain compatible with the repo. CI uses Rust 1.91.1.

• Node.js 22 or newer. CI uses Node 22; mise.toml pins Node 24.15.0 for local toolchain management.

• npm

• PostgreSQL server binaries available on PATH, in a common package-manager location, or through PGSANDBOX_POSTGRES_BIN_DIR

PGSandbox checks PATH, common package-manager locations such as /opt/homebrew/opt/postgresql/bin, /usr/lib/postgresql/<major>/bin, versioned Homebrew kegs from postgresql@18 through postgresql@13, Postgres.app locations, and explicit bin dir environment variables. Homebrew kegs do not need to be linked globally if PGSandbox can discover the opt path.

Docker is not required. docker-compose.example.yml is only a demo helper for users who intentionally want an external local Postgres profile.

Getting Started

These steps are for a normal user who wants to install the released pgsandbox binary and use it from an MCP client. You do not need to clone this repository, install Rust, or run Cargo for the standard setup.

1. Install PGSandbox

Homebrew is the recommended install path:

brew install LVTD-LLC/tap/pgsandbox

If you do not use Homebrew, install the latest GitHub release binary with the hosted installer:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh

If the installer uses ~/.local/bin, make sure that directory is on your PATH before continuing.

Verify the installed binary:

pgsandbox --version

2. Run Setup

Pick the client you use:

pgsandbox setup --client codex
pgsandbox setup --client cursor
pgsandbox setup --client vscode
pgsandbox setup --client claude-desktop

setup does the normal local setup work for you:

• checks for the PostgreSQL server binaries the managed runtime needs

• installs PostgreSQL with a supported package manager when those binaries are missing and one is available

• initializes and starts the managed local Postgres cluster under ~/.pgsandbox/

• writes the MCP client config

For Cursor or VS Code project-local config:

pgsandbox setup --client cursor --scope project
pgsandbox setup --client vscode --scope project

By default, setup does not write PGSANDBOX_ADMIN_DATABASE_URL. That is intentional: the MCP server will use the managed local Postgres cluster under ~/.pgsandbox/.

Only pass --admin-url when you intentionally want to use an explicit external local/private Postgres admin profile:

pgsandbox setup --client codex --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"

3. Restart Your MCP Client

Restart Codex, Cursor, VS Code, or Claude Desktop after setup. MCP clients cache server metadata and usually do not notice a newly configured server until restart.

In Codex, run:

/mcp

Verify that the pgsandbox server appears.

4. Optional Verification

Run diagnostics:

pgsandbox doctor

Run the disposable end-to-end check:

pgsandbox smoke-test

The smoke test creates a sandbox, runs SQL, validates serialization behavior, and deletes the sandbox before exiting. Its default output is a compact list of pass/fail results. Use pgsandbox smoke-test --verbose to include the full structured SQL result dictionaries for diagnostics.

You can also inspect the managed local runtime directly:

pgsandbox local status
pgsandbox local start

The runtime starts at port 65432 and scans upward for a free port. It should not collide with Docker or another developer Postgres already using 5432.

5. Use A Sandbox

Ask your agent to create a disposable Postgres sandbox for the task. A typical MCP workflow is:

1. Create a sandbox with create_database.

2. Run SQL with run_sql or run a repo command with run_repo_command.

3. Inspect schema with describe_schema or schema_digest.

4. Delete the sandbox with delete_database, or let TTL cleanup remove it later.

Agents that prefer shell commands can run the same tool contracts directly through the CLI:

pgsandbox create-database --name-hint "migration check" --ttl-minutes 30
pgsandbox run-sql --database-id "$DATABASE_ID" --sql "select 1" --readonly
pgsandbox delete-database --database-id "$DATABASE_ID"

Every MCP tool has a CLI command. Use hyphenated command names for normal shell usage, or call the exact MCP tool name through tool:

pgsandbox schema-digest --database-id "$DATABASE_ID"
pgsandbox tool schema_digest --input "{\"databaseId\":\"$DATABASE_ID\"}"
pgsandbox run-repo-command --database-id "$DATABASE_ID" --repo-path "$PWD" -- npm run migrate

--input and --input-file accept the same camelCase JSON objects documented for MCP tools, so the CLI can reach the full tool surface without adding a custom flag for every field.

If your application runs inside Docker while PGSandbox runs on the host, call get_connection_string with includeCredentials: true and pass connectionStrings.localContainer to the app service as DATABASE_URL. Keep the raw value out of chat, logs, issues, PR comments, and task trackers. Docker Desktop supports the returned host.docker.internal host automatically. On Linux Docker, add this to the service:

extra_hosts:
  - "host.docker.internal:host-gateway"

For run_repo_command, set connectionMode: "localContainer" and add app aliases such as databaseUrlEnvNames: ["DATABASE_URI"]; PGSandbox injects the Docker-friendly URL without returning the raw value in command metadata.

For direct CLI troubleshooting, this command starts the MCP server over stdio:

pgsandbox mcp

You normally do not run it yourself; your MCP client launches it.

Development From This Repo

Use this section when contributing to PGSandbox, testing unreleased changes, or pointing an MCP client at a local development build. Normal users should use the packaged setup in Getting Started.

1. Clone The Repository

git clone https://github.com/LVTD-LLC/pgsandbox.git
cd pgsandbox

2. Install Toolchains

If you use mise, the repo already declares tool versions:

mise install

Without mise, install Rust and Node manually:

rustup toolchain install stable
node --version
npm --version

The site requires Node >=22.12.0.

3. Install JavaScript Dependencies

The root package is mostly a command runner for Cargo and packaging scripts. The website has its own package under site/.

npm ci
npm --prefix site ci --include=dev

4. Build And Check The CLI

cargo build
cargo run -- doctor
cargo run -- smoke-test

The development binary is created at:

target/debug/pgsandbox

For an optimized release build:

cargo build --release

5. Configure An MCP Client For Local Development

For development, point the MCP client at the binary in this checkout so it does not accidentally launch a separately installed release:

cargo build
cargo run -- setup --client codex --command "$(pwd)/target/debug/pgsandbox"

Other supported clients:

cargo run -- setup --client cursor --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client vscode --scope project --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client claude-desktop --command "$(pwd)/target/debug/pgsandbox"
cargo run -- setup --client all --command "$(pwd)/target/debug/pgsandbox"

Use --dry-run to inspect the config without writing it:

cargo run -- setup --client codex --dry-run --command "$(pwd)/target/debug/pgsandbox"

Restart the MCP client after setup. MCP clients cache tool metadata.

6. Verify MCP Server Startup Manually

The binary starts the stdio server by default:

cargo run

Equivalent explicit form:

cargo run -- mcp

You usually do not run this command directly because the MCP client owns the stdio transport. Use it only when checking startup failures.

7. Run The Documentation Site

The Astro website lives in site/.

npm --prefix site run dev

Build the site:

npm run site:build

No build-time environment variables are required for the site.

Architecture

Directory Structure

.
|-- rust-src/
|   |-- main.rs              Binary entrypoint
|   |-- cli.rs               CLI dispatch, setup, doctor, local runtime commands, smoke test
|   |-- mcp.rs               MCP server, public tool registration, response envelopes
|   |-- config.rs            Env and JSON config loading, profile validation
|   |-- local.rs             Managed local Postgres init/start/stop/status
|   |-- postgres.rs          Sandbox lifecycle, SQL, schema, repo workflow, templates
|   |-- names.rs             Identifier generation and SQL quoting helpers
|   |-- doctor.rs            Diagnostics and connection-string masking
|   |-- setup.rs             MCP client config writers
|   |-- telemetry.rs         Anonymous usage telemetry
|   `-- lib.rs               Library exports and package version
|-- docs/
|   |-- architecture.md      Resource model and backend notes
|   |-- mcp-tools.md         MCP tool contracts
|   |-- install.md           Install and setup guide
|   |-- homebrew.md          Homebrew packaging notes
|   |-- agent-workflows.md   Copyable agent workflow examples
|   `-- open-questions.md    Product and architecture questions
|-- tests/
|   |-- dogfood_reliability.rs
|   |-- extensions.rs
|   `-- run_sql_serialization.rs
|-- scripts/
|   |-- install.sh
|   |-- package-homebrew.sh
|   |-- package-release.sh
|   `-- update-homebrew-formula.sh
|-- site/                    Astro documentation and marketing site
|-- .github/workflows/       CI, site deploy, and Homebrew tap update workflows
|-- Cargo.toml               Rust package metadata
|-- package.json             Root npm command wrappers
|-- docker-compose.example.yml
|-- .env.example
`-- README.md

Runtime Shape

PGSandbox is one native binary with multiple command modes:

User or MCP client
        |
        v
pgsandbox CLI
        |
        +-- mcp stdio server
        +-- direct CLI tool commands
        +-- setup config writer
        +-- doctor diagnostics
        +-- local Postgres runtime manager
        `-- smoke-test verifier

The MCP server talks to one selected Postgres profile at a time unless the caller explicitly requests all-version listing or cleanup. The default profile is a managed local Postgres cluster. External profiles are opt-in.

Agent / MCP client
        |
        v
PGSandbox stdio MCP mode
        |
        v
Managed local cluster or explicit Postgres admin profile
        |
        v
Tracked sandbox databases and scoped sandbox roles

Entry Points

• rust-src/main.rs starts Tokio and calls pgsandbox::cli::run.

• rust-src/cli.rs defaults to MCP stdio when no command is provided.

• rust-src/mcp.rs exposes the public MCP tools.

• rust-src/postgres.rs owns lifecycle behavior and database interactions.

CLI commands:

pgsandbox
pgsandbox mcp
pgsandbox setup [options]
pgsandbox doctor [options]
pgsandbox create-database [options]
pgsandbox run-sql [options]
pgsandbox delete-database [options]
pgsandbox tool <mcp_tool_name> --input '{...}'
pgsandbox local init [options]
pgsandbox local start [options]
pgsandbox local stop [options]
pgsandbox local status [options]
pgsandbox smoke-test [options]
pgsandbox uninstall [options]
pgsandbox --version
pgsandbox --help

Managed Local Runtime

When neither PGSANDBOX_ADMIN_DATABASE_URL nor PGSANDBOX_CONFIG is set, PGSandbox initializes and starts a local Postgres cluster:

• root: ~/.pgsandbox by default

• default profile: local

• data directory: ~/.pgsandbox/postgres/data

• private runtime config: ~/.pgsandbox/local-postgres.json

• default port search start: 65432

• Unix socket directory on Unix: short PGSandbox-owned path under /tmp/pgsandbox-sockets/

• admin user: pgsandbox_admin

Versioned local profiles use separate state:

Postgres 18 profile: local-pg18
Config:              ~/.pgsandbox/local-postgres-18.json
Data:                ~/.pgsandbox/postgres/versions/18/data
Log:                 ~/.pgsandbox/postgres/versions/18/postgres.log

Start a specific installed version:

pgsandbox local start --postgres-version 18

PGSandbox probes common local install paths for installed Postgres 18, 17, 16, 15, 14, and 13 binaries. Explicit PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR settings can still target any numeric major version that reports matching server binaries.

In MCP tools, agents should usually omit profile and pass only postgresVersion:

{ "postgresVersion": "18" }

Supplying both profile and postgresVersion is reserved for exact profile targeting. A mismatch returns a structured version_mismatch error.

Profiles

A profile describes an admin connection PGSandbox may use for lifecycle and metadata operations. Profiles can be:

• managed local profiles created by PGSandbox

• explicit local Postgres URLs

• explicit private remote Postgres URLs, only with external-host opt-in

• versioned profiles carrying postgresVersion metadata

Admin connections are used for:

• metadata table setup

• role creation

• database creation

• database deletion

• cleanup

• audit events

User SQL and repo commands use sandbox role credentials generated for the specific database. Requested sandbox extensions are installed through the same sandbox role connection after the database is created, not through the admin connection.

Resource Model

Each sandbox gets:

• a UUID databaseId

• one database

• one login role

• one generated role password

• one owner string, if supplied

• one purpose/name hint, if supplied

• JSON labels, if supplied

• optional installed extensions requested at creation time

• a createdAt timestamp

• an expiresAt timestamp

• a deletedAt timestamp after deletion

Generated database and role names use the configured prefix, a slugified name hint, and a short random id while staying within Postgres's 63 byte identifier limit:

pgsandbox_<hint>_<short_id>
pgsandbox_<hint>_<short_id>_role

All generated SQL identifiers and literals go through helpers in rust-src/names.rs.

Metadata And Audit Tables

PGSandbox stores lifecycle metadata in the admin database for the selected profile.

pgsandbox_databases:

pgsandbox_events:

Destructive operations must find a live metadata row before dropping a database or role. PGSandbox does not drop arbitrary databases by name.

MCP Response Envelope

Every public MCP tool returns JSON text using a compact envelope:

{
  "ok": true,
  "summary": "Tool completed successfully.",
  "warnings": [],
  "errors": [],
  "detailHandles": [],
  "result": {}
}

Workflow tools can also include changedObjects. Tool failures use the same shape with ok: false, stable error code, a broad category, a human message, and a hint. Expected categories include:

• validation

• database_not_found

• version_mismatch

• restore_incompatible

• sql_analysis

• sql_syntax

• constraint_violation

• readonly_violation

• template_not_found

• timeout

Postgres errors include SQLSTATE when available.

Public MCP Tools

See docs/mcp-tools.md for full tool inputs and outputs.

Extension Discovery And Installation

Use list_extensions before creating or cloning when an app depends on Postgres extensions:

{ "postgresVersion": "18" }

For an existing sandbox, pass databaseId or databaseName to also return installed extension names and versions:

{ "databaseName": "pgsandbox_example_abc123" }

The CLI exposes the same discovery path and prints JSON:

pgsandbox list-extensions --postgres-version 18
pgsandbox list-extensions --database-name pgsandbox_example_abc123

create_database and clone_database accept an optional extensions list:

{
  "nameHint": "trigram search repro",
  "extensions": ["pg_trgm", "uuid-ossp"]
}

PGSandbox trims names, normalizes them to lowercase, deduplicates them, and allows only letters, numbers, underscores, and hyphens. The installed names are returned as installedExtensions.

Extension availability depends on the selected target profile's Postgres installation. PGSandbox checks pg_available_extensions inside the target sandbox before running CREATE EXTENSION IF NOT EXISTS; unavailable or invalid names return invalid_extensions and the new sandbox is rolled back. For clone_database, requested extensions are installed in the empty target sandbox before pg_restore runs.

For clone sources that include observability or environment-specific extensions that the sandbox role should not create, clone_database skips pg_stat_statements source extension entries by default. Pass excludeSourceExtensions to skip additional source extensions while still restoring the rest of the schema:

{
  "sourceDatabaseUrl": "postgres://...",
  "schemaOnly": true,
  "excludeSourceExtensions": ["auto_explain"]
}

Use extensions for target extensions that should exist in the sandbox, and excludeSourceExtensions for source extension entries that should be omitted from the restore archive.

Some extensions require server-level setup such as installed extension packages or shared_preload_libraries. Those failures return extension_setup_required; use profile setup docs or a recipe for that profile instead of adding a one-off MCP tool for the extension.

SQL Execution

run_sql resolves the selected sandbox, obtains its sandbox role connection string, and connects as that role. It does not execute user SQL through the admin connection.

Result behavior:

• default rowLimit: 100

• rowLimit: 0: valid zero-row preview

• hard row limit cap: 1000

• negative rowLimit values return invalid_row_limit

• returns returnedRowCount

• returns affectedRowCount for DML/DDL command tags when available

• reports totalRowCountKnown

• reports truncated

• returns ordered resultSets for multi-statement SQL with 1-based statementIndex values. Top-level rows and row metadata summarize the last row-returning statement, or the last statement when no statement returned rows. The row limit applies independently to each row-returning result set.

• preserves int8 and numeric values as JSON strings

• serializes json/jsonb as nested JSON

• serializes common Postgres arrays as JSON arrays

• returns unsupported non-null types as an object with the original type and a cast-to-text hint

• keeps SQL NULL as JSON null

With readonly: true, PGSandbox runs SQL in a read-only transaction, rejects transaction-control escape hatches, and rolls the transaction back after execution. Mutating statements are returned as structured readonly_violation errors; harmless settings that Postgres permits inside the transaction, such as SET search_path, may still run.

Repo Workflow Tools

Repo workflow tools are intentionally conservative:

• They require an explicit repoPath.

• They execute argv arrays directly without an implicit shell.

• They reject shell wrappers and indirect launchers such as bash -lc, sh -c, env, sudo, and nsenter.

• They inject sandbox credentials into the child process environment.

• They return bounded stdout/stderr with truncation flags.

• They redact the injected database URL and password from captured output.

• They do not permanently rewrite application configuration.

Injected database environment variables include:

DATABASE_URL
PGSANDBOX_DATABASE_URL
PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD

When databaseId is present, run_repo_command resolves the owning profile/Postgres version from sandbox metadata. Repo-inferred versions are not allowed to override that id; an explicitly supplied conflicting profile/version still fails normally.

Use stdin with a direct interpreter to run a multi-line smoke without writing a temporary file into the repo:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["python", "-"],
  "stdin": "print('run app smoke here')\n"
}

stdin accepts up to 65,536 UTF-8 bytes and rejects NUL characters. The normal no-shell command validation remains in force.

Dockerized apps and repos with nonstandard settings can select the connection variant and validated aliases directly:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["docker", "compose", "run", "--rm", "web", "python", "manage.py", "check"],
  "connectionMode": "localContainer",
  "databaseUrlEnvNames": ["DATABASE_URI"]
}

Noisy commands can request stripAnsi, stdoutLimit, stderrLimit, tailLines, and suppressDockerLifecycle. Limits remain bounded to 8,000 bytes per stream. run_repo_command reports changedObjects: null with changedObjectsUnsupportedReason because writes made by a child process cannot be observed reliably; use run_sql, schema_digest, or validate_schema_change when side effects must be measured.

Good command examples:

["npm", "run", "migrate"]

["psql", "-v", "ON_ERROR_STOP=1", "-f", "migrations/schema.sql"]

["./scripts/seed.sh"]

prepare_for_repo writes .pgsandbox/project.json without secrets. It can store migrationCommand, seedCommand, databaseUrlEnv, postgresVersion, and preparedAt. It can infer a Postgres major version from Compose files or a devcontainer image such as postgres:16, postgis/postgis:16-3.4, or timescale/timescaledb:pg16.

Schema Snapshots And Templates

Schema snapshots are JSON metadata artifacts under:

~/.pgsandbox/schema-snapshots/<profile>/<database-id>/<snapshot-name>.json

They store object counts, fingerprints, profile, sandbox id, owner/purpose, labels, Postgres version, digest version, notes, and creation time. They are manual checkpoints, not automatically refreshed truth.

Templates are dump plus metadata artifacts under:

~/.pgsandbox/templates/<profile>/<template-name>.dump
~/.pgsandbox/templates/<profile>/<template-name>.json

Templates can only be created from PGSandbox-owned sandboxes and restored into new PGSandbox-owned sandboxes. They are useful for local seeded-state loops, regression fixtures, and repeatable agent QA. They are not copy-on-write forks or a production-data import workflow.

Clone Workflow

clone_database uses a portable dump/restore path:

1. Preflight source and target Postgres major versions, and capture a best-effort source size estimate.

2. Create an empty tracked target sandbox.

3. Install any requested target extensions using the sandbox role.

4. Run pg_dump against the source database.

5. Skip source extension archive entries from pg_stat_statements and any requested excludeSourceExtensions.

6. Run pg_restore into the target sandbox using the sandbox role.

7. Delete the target sandbox if restore fails or times out.

Newer-to-older clone paths fail before target creation with restore_incompatible. The dump/restore phase defaults to a 240 second timeout so PGSandbox can return command_timeout with the created sandbox id, source size estimate when available, and cleanup status before common MCP clients exhaust their own call budget. Cloning and template tools require pg_dump and pg_restore; ordinary create/query/delete flows do not.

Telemetry

Telemetry is enabled by default and sends anonymous, personless usage events to PostHog. It records command/tool names, version, OS/architecture, success, elapsed time, and small booleans/counts.

Telemetry must not include:

• Postgres URLs

• connection strings

• database names or ids

• SQL text

• owner values

• label keys or values

• full local paths

• raw error messages

Telemetry never blocks CLI or MCP tool results. See Environment Variables for opt-out settings.

Safety Boundaries

PGSandbox is designed as local/private infrastructure:

• It installs PostgreSQL packages only during explicit setup, ensure-postgres, or local start --install-missing runs when a supported package manager is available.

• It does not require Docker.

• It does not stop Docker containers.

• It does not bind localhost:5432 by default.

• It does not silently configure external admin URLs.

• It does not expose a public network admin surface.

• It does not delete databases missing from pgsandbox_databases.

• It does not log full connection strings in diagnostics.

• It does not return raw sandbox credentials unless a caller explicitly asks for them.

Hosted database platform work is a future product direction, but it needs a deliberate auth, tenancy, quota, billing, and security design before a public network admin surface is added.

Website

The site/ directory is an Astro site. It contains docs pages, changelog rendering, blog content, sitemap/robots routes, and a CapRover deployment workflow. It is separate from the MCP runtime and does not run the MCP server.

Environment Variables

Configuration Precedence

PGSandbox loads runtime configuration in this order:

1. If PGSANDBOX_CONFIG is set, load the JSON config file it points to.

2. Else if PGSANDBOX_ADMIN_DATABASE_URL is set, create a single explicit profile from environment variables.

3. Else start or reuse the managed local profile under PGSANDBOX_HOME or ~/.pgsandbox.

Telemetry opt-out environment variables are applied after config loading.

Core Runtime Variables

Local Postgres Binary Discovery

Discovery order favors explicit version-specific settings, then other configured bin dirs, common package-manager locations, local PATH entries, and finally direct PATH command resolution. Common-path version discovery includes installed Postgres 18, 17, 16, 15, 14, and 13 binaries.

External Admin URL Policy

By default, explicit profile admin URLs must be local:

• localhost

• 127.0.0.1

• ::1

• URLs without a host, such as Unix socket style libpq URLs

Remote/private hosts require opt-in.

Telemetry Variables

JSON config can also disable telemetry:

{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}

Install Script Variables

These variables are consumed by scripts/install.sh:

Pin the current manifest version explicitly:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.4.9 sh

Site Variables

The Astro site has no required build-time environment variables. Production deployment uses GitHub Actions secrets:

Example .env

The root .env.example documents a simple local/default setup:

# By default, PGSandbox uses a managed local cluster under ~/.pgsandbox.
# Set this only when intentionally using an external Postgres admin profile.
# PGSANDBOX_ADMIN_DATABASE_URL=postgres://postgres:postgres@localhost:6543/postgres
# PGSANDBOX_HOME=/path/to/pgsandbox-home
PGSANDBOX_DATABASE_PREFIX=pgsandbox
PGSANDBOX_DEFAULT_TTL_MINUTES=240
PGSANDBOX_MAX_TTL_MINUTES=1440

# Optional alternative to single-profile env configuration.
# PGSANDBOX_CONFIG=./pgsandbox.config.json

# Optional telemetry opt-out.
# PGSANDBOX_TELEMETRY=false

Multi-Profile JSON Config

Use PGSANDBOX_CONFIG for multiple external profiles or version-specific profiles:

{
  "defaultProfile": "external-pg17",
  "profiles": [
    {
      "name": "external-pg17",
      "adminUrl": "postgres://postgres:postgres@localhost:6543/postgres",
      "postgresVersion": "17",
      "databasePrefix": "pgsandbox",
      "defaultTtlMinutes": 240,
      "maxTtlMinutes": 1440,
      "maxActiveDatabasesPerOwner": 3
    },
    {
      "name": "external-pg16",
      "adminUrl": "postgres://postgres:postgres@localhost:6544/postgres",
      "postgresVersion": "16"
    }
  ],
  "telemetry": {
    "enabled": false
  }
}

Use it:

export PGSANDBOX_CONFIG="$PWD/pgsandbox.config.json"
pgsandbox doctor

For a private remote host, opt in explicitly:

{
  "defaultProfile": "private-dev",
  "profiles": [
    {
      "name": "private-dev",
      "adminUrl": "postgres://postgres:postgres@db.internal.example/postgres?sslmode=require",
      "postgresVersion": "17",
      "allowedAdminHosts": ["db.internal.example"]
    }
  ]
}

Available Scripts

Run root scripts from the repository root.

Direct Cargo commands:

CLI commands after installation:

Site commands:

Testing

Standard Test Suite

Run the expected local checks:

npm run check
npm test
npm run build

For full CI parity:

cargo fmt -- --check
cargo clippy --all-targets -- -D warnings
npm run check
npm test
npm run build
npm run site:check-changelog
npm run site:check-blog-content
npm --prefix site ci --include=dev
npm run site:build
npm run site:check-blog-tables

Test Layout

Rust unit tests live beside the code they cover:

rust-src/config.rs     Config loading, env handling, profile validation
rust-src/local.rs      Managed local runtime paths, ports, binary discovery
rust-src/mcp.rs        Envelope normalization and tool error shaping
rust-src/names.rs      Identifier generation and SQL quoting
rust-src/postgres.rs   SQL execution, schema digest/diff, workflow behavior
rust-src/setup.rs      MCP client config writing

Integration-style tests live under tests/:

tests/dogfood_reliability.rs
tests/extensions.rs
tests/run_sql_serialization.rs

These tests are compiled by cargo test, but live database scenarios are skipped unless the relevant environment variable is set.

Live Postgres Tests

Run the dogfood reliability suite against real disposable sandboxes:

PGSANDBOX_DOGFOOD_E2E=1 cargo test --test dogfood_reliability -- --nocapture

Run the PG18 schema snapshot regression when Postgres 18 binaries are installed:

PGSANDBOX_DOGFOOD_PG18_E2E=1 \
  cargo test --test dogfood_reliability \
  pg18_schema_snapshot_minimal_schema_returns_without_timeout_when_enabled \
  -- --nocapture

Run the SQL serialization E2E test:

PGSANDBOX_RUN_SQL_SERIALIZATION_E2E=1 \
  cargo test --test run_sql_serialization -- --nocapture

Run the readonly transaction contract E2E test:

PGSANDBOX_RUN_SQL_READONLY_E2E=1 \
  cargo test --test run_sql_serialization \
  run_sql_readonly_contract_matches_postgres_transaction_when_enabled \
  -- --nocapture

Run the extension discovery and installation E2E test. It uses pg_trgm by default; override the extension name when a target profile exposes a different extension package:

PGSANDBOX_EXTENSION_E2E=1 \
  cargo test --test extensions -- --nocapture

PGSANDBOX_EXTENSION_E2E=1 PGSANDBOX_EXTENSION_E2E_NAME=citext \
  cargo test --test extensions -- --nocapture

Each live test creates a sandbox and attempts cleanup at the end. If cleanup fails, the test prints the failure so you can remove the sandbox with the MCP tool or with pgsandbox smoke-test/manual diagnostics. Live tests use the normal PGSandbox config path: the managed local runtime when no explicit config is set, or the configured PGSANDBOX_ADMIN_DATABASE_URL/PGSANDBOX_CONFIG profile when present.

Manual Runtime Checks

Useful local runtime checks:

pgsandbox local status
pgsandbox doctor
pgsandbox smoke-test

For a versioned local runtime:

pgsandbox local status --postgres-version 18
pgsandbox doctor --postgres-version 18
pgsandbox smoke-test --postgres-version 18

Packaging Checks

npm run package:homebrew
npm run package:release

Generated release archives and checksum files are build artifacts. Do not edit them by hand.

Deployment

PGSandbox has two separate deployable artifacts:

1. The native pgsandbox CLI/MCP binary.

2. The static Astro website under site/.

The MCP server is intended to run locally or on a private trusted machine as a stdio process launched by an MCP client. It is not a public web service in this repository.

Install Released Binary With Homebrew

This is the same recommended packaged path shown in Getting Started.

Recommended user flow:

brew install LVTD-LLC/tap/pgsandbox
pgsandbox setup --client codex
pgsandbox doctor
pgsandbox smoke-test

The formula lives in LVTD-LLC/homebrew-tap, which Homebrew addresses as LVTD-LLC/tap.

Restart the MCP client after setup. In Codex, run /mcp after restart to verify that the pgsandbox server is available.

Install Released Binary With The Script

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox setup --client codex
pgsandbox doctor

The script downloads a platform-specific release archive, verifies checksums when the release includes pgsandbox-<version>-checksums.txt, and installs to ~/.local/bin by default.

Install the current manifest version explicitly:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_VERSION=0.4.9 sh

Install to a custom directory:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh \
  | PGSANDBOX_INSTALL_DIR=/usr/local/bin sh
pgsandbox setup --client codex --command /usr/local/bin/pgsandbox

Install From Source For Development

Use source installs when contributing, testing a local checkout, or validating an unreleased tag. Normal users should prefer Homebrew or the GitHub release installer.

From a checkout:

cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor

From GitHub:

cargo install --git https://github.com/LVTD-LLC/pgsandbox --tag v0.4.9 --force
pgsandbox setup --client codex
pgsandbox doctor

Update An Existing Installation

For Homebrew and GitHub install-script installs, the shortest path is:

pgsandbox upgrade

upgrade updates the installed binary, reruns setup --client all, runs doctor, and reminds you to restart MCP clients. It supports the same release targets as the GitHub installer: macOS and Linux on x86_64 or aarch64. Homebrew installs are upgraded through Homebrew. GitHub install-script installs rerun the hosted installer into the current binary directory. --version is only supported for GitHub install-script installs because Homebrew upgrades use the tap formula version.

To update only one client config, skip post-upgrade steps, or pin a GitHub installer release:

pgsandbox upgrade --setup codex
pgsandbox upgrade --no-setup
pgsandbox upgrade --no-doctor
pgsandbox upgrade --version 0.4.9

Homebrew:

brew update
brew upgrade LVTD-LLC/tap/pgsandbox
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor

Install script:

curl -fsSL https://raw.githubusercontent.com/LVTD-LLC/pgsandbox/main/scripts/install.sh | sh
pgsandbox --version
pgsandbox setup --client codex
pgsandbox doctor

Source install for development:

cargo install --path . --force
pgsandbox setup --client codex
pgsandbox doctor

Rerun setup when the binary path, explicit admin URL, selected client, or scope changes. Restart the MCP client after updating.

MCP Client Rollout

pgsandbox setup writes config while preserving unrelated servers.

Codex user config:

pgsandbox setup --client codex

Cursor project config:

pgsandbox setup --client cursor --scope project

VS Code project config:

pgsandbox setup --client vscode --scope project

Claude Desktop user config:

pgsandbox setup --client claude-desktop

A generated Codex config entry looks like:

[mcp_servers.pgsandbox]
command = "pgsandbox"
args = ["mcp"]

If you intentionally configure an external Postgres admin URL, setup writes it into the client config environment so desktop clients do not depend on shell startup files:

pgsandbox setup \
  --client codex \
  --admin-url "$PGSANDBOX_ADMIN_DATABASE_URL"

Do not use --admin-url for the managed local default.

Release Packaging For Maintainers

1. Update versions in Cargo.toml and package.json.

2. Run the full test suite.

3. Build release archives.

4. Publish a GitHub release with the generated artifacts.

5. Let the Homebrew tap workflow open a PR.

6. Merge the tap PR before telling Homebrew users to upgrade.

Commands:

cargo test
npm run package:homebrew
npm run package:release

npm run package:homebrew creates:

dist/pgsandbox-<version>.tar.gz

npm run package:release creates:

dist/pgsandbox-<version>-<target>.tar.gz
dist/pgsandbox-<version>-checksums.txt

When a GitHub release is published, .github/workflows/update-homebrew-tap.yml downloads or builds the Homebrew archive, computes SHA-256, checks out LVTD-LLC/homebrew-tap, updates Formula/pgsandbox.rb, and opens or updates a PR.

The workflow requires the HOMEBREW_TAP_PAT repository secret with write access to the tap repository.

Static Site Deployment

The site deploy workflow runs on pushes to main that affect site files, CHANGELOG.md, the changelog checker, or the deploy workflow itself. It can also be run manually with workflow_dispatch.

Workflow:

1. Install site dependencies with Node 22.

2. Run the changelog fallback check.

3. Build the Astro site.

4. Package site/ without node_modules.

5. Deploy the archive to CapRover.

Local site build:

npm --prefix site ci --include=dev
npm --prefix site run build

Production deployment requires these GitHub secrets:

CAPROVER_PGSANDBOX_SITE_URL
CAPROVER_PGSANDBOX_SITE_APP
CAPROVER_PGSANDBOX_SITE_TOKEN

Docker Demo Postgres

docker-compose.example.yml starts a normal Postgres service on 5432 for users who want to test explicit external profile mode:

docker compose -f docker-compose.example.yml up -d
export PGSANDBOX_ADMIN_DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres"
pgsandbox doctor
pgsandbox smoke-test

This is optional. Runtime code must not require Docker.

Troubleshooting

could not find local Postgres binaries

Rerun setup first. It checks the local runtime, installs PostgreSQL through a supported package manager when available, starts the managed local cluster, and writes MCP config:

pgsandbox setup --client codex

If setup cannot install PostgreSQL automatically, install the server binaries with your system package manager and point PGSandbox at their bin directory:

export PGSANDBOX_POSTGRES_BIN_DIR="/path/to/postgres/bin"
pgsandbox setup --client codex
pgsandbox doctor

For a requested major version, let PGSandbox install the package when your system package manager still provides it:

pgsandbox ensure-postgres --postgres-version 14
pgsandbox setup --client codex --postgres-version 14

For an older major such as Postgres 13:

pgsandbox ensure-postgres --postgres-version 13
pgsandbox setup --client codex --postgres-version 13

From MCP, agents can call ensure_postgres before creating a sandbox:

{ "postgresVersion": "13", "installMissing": true }

Automatic PostgreSQL package installs use Homebrew on macOS; apt-get, dnf, yum, zypper, or pacman on Linux; and WinGet or Chocolatey on Windows. Versioned packages still depend on what that package manager currently publishes. If an automatic install fails, the MCP error includes bounded installer output and the next action: install manually and set the matching PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR, or choose a newer clone target when a matching older target is unavailable.

If your package manager does not provide that major version, point PGSandbox at existing server binaries:

export PGSANDBOX_POSTGRES_13_BIN_DIR="/opt/homebrew/opt/postgresql@13/bin"
pgsandbox setup --client codex --postgres-version 13

Requested Postgres Version Is Missing

List discovered local versions:

pgsandbox doctor

From MCP, call list_profiles with:

{ "includeDiscoveredLocal": true }

Then install the missing version, set PGSANDBOX_POSTGRES_<MAJOR>_BIN_DIR, or choose an available version.

Existing Postgres On Port 5432

This is expected and should not be a problem. The managed local runtime starts at 65432 and scans upward.

pgsandbox local start
pgsandbox local status

If you intentionally want to use the service on 5432, set an explicit admin URL or JSON profile.

Stale MCP Config Uses An Old Admin URL

If doctor reports that it is using an admin URL from an MCP config but you want managed local, rerun setup without --admin-url:

pgsandbox setup --client codex

Restart the MCP client and rerun:

pgsandbox doctor

pgsandbox --version Shows A Node.js Stack Trace

An old npm/global install may be earlier in PATH.

which -a pgsandbox
/opt/homebrew/bin/pgsandbox --version

Remove the stale install or point the MCP client at the native binary:

npm uninstall -g pgsandbox
hash -r 2>/dev/null || rehash
pgsandbox setup --client codex --command /opt/homebrew/bin/pgsandbox

clone_database Or Template Restore Fails

Install pg_dump and pg_restore from PostgreSQL client tools. Ordinary create/query/delete flows do not require them.

pg_dump --version
pg_restore --version

Also check source and target Postgres major versions. Newer-to-older clone paths are rejected before creating the target sandbox.

Large clone operations time out inside PGSandbox before common MCP client call budgets expire. The structured timeout includes the created sandbox id/name, best-effort source size estimate, and whether cleanup already deleted the sandbox. Retry with a larger timeoutSeconds, schemaOnly: true, or a smaller source/template path.

If clone restore fails on a source-only extension such as an observability extension, retry with excludeSourceExtensions. pg_stat_statements is skipped by default because sandbox roles commonly cannot create it.

run_repo_command Rejects A Command

Repo commands are executed directly and cannot invoke shells or launchers. Change this:

["bash", "-lc", "npm run migrate && npm run seed"]

To direct commands or an executable repo script:

["npm", "run", "migrate"]

["./scripts/seed.sh"]

For a one-off multi-line Python script, keep the direct-command boundary and send the script over stdin:

{
  "command": ["python", "-"],
  "stdin": "print('smoke')\n"
}

Repo Workflow Has No Migration Command

Pass a command directly:

{
  "repoPath": "/absolute/path/to/repo",
  "databaseId": "sandbox-id",
  "command": ["npm", "run", "migrate"]
}

Or store a secret-free default:

{
  "repoPath": "/absolute/path/to/repo",
  "migrationCommand": ["npm", "run", "migrate"]
}

prepare_for_repo writes .pgsandbox/project.json.

SQL Results Are Truncated

run_sql defaults to 100 rows, accepts rowLimit: 0 for a zero-row preview, rejects negative rowLimit values with invalid_row_limit, and caps rowLimit at 1000.

{
  "databaseId": "sandbox-id",
  "sql": "select * from large_table order by id",
  "readonly": true,
  "rowLimit": 1000
}

Use SQL filters, aggregates, or pagination for larger inspection tasks.

Readonly SQL Fails

With readonly: true, PGSandbox runs SQL in a read-only transaction and rolls it back after execution. Mutating statements such as INSERT or CREATE TEMP TABLE fail with readonly_violation; harmless settings that Postgres permits inside the transaction, such as SET search_path, may still run. If mutation is intentional, omit readonly or set it to false.

Sandbox Was Not Found

Unscoped databaseId lookup searches configured profiles and running managed local profiles. If the sandbox cannot be resolved:

1. Call list_databases with includeAllVersions: true.

2. Retry the operation with the returned profile or postgresVersion.

3. Check whether the sandbox expired or was deleted.

Expired Sandboxes Remain

Cleanup is explicit unless you run it from a scheduler or call the MCP tool. list_databases excludes expired sandboxes by default. Pass includeExpired: true to inspect them; every returned database includes expired, ttlStatus, and signed expiresInSeconds fields.

Profile-scoped dry run:

{ "dryRun": true }

All running versions:

{ "includeAllVersions": true, "dryRun": true }

Only sandboxes matching the current owner and task labels:

{
  "includeAllVersions": true,
  "dryRun": true,
  "owner": "codex-run-1",
  "labels": { "task": "PGS-043" }
}

Then run without dryRun to delete selected expired sandboxes.

Local State Permissions

If PGSandbox cannot write under ~/.pgsandbox, fix ownership or use another state root:

export PGSANDBOX_HOME="$HOME/.local/state/pgsandbox"
pgsandbox local start

Old Unix Socket Path After Upgrade

TCP connections continue to work. If a local Unix-socket consumer needs the new short socket path under /tmp/pgsandbox-sockets/, restart the local runtime:

pgsandbox local stop
pgsandbox local start

External Admin URL Is Refused

Non-local admin URLs require explicit opt-in:

export PGSANDBOX_ALLOW_EXTERNAL_ADMIN_URL=true

Or use a host allowlist:

export PGSANDBOX_ALLOWED_ADMIN_HOSTS="db.internal.example"

For JSON config, use allowExternalAdminUrl or allowedAdminHosts.

Site Build Fails

Install site dependencies and run Astro checks directly:

npm --prefix site ci --include=dev
npm --prefix site run check
npm --prefix site run build

If blog table checks fail, build first and then run:

npm run site:check-blog-tables

Contributing

Before editing, check the worktree:

git status --short

Keep changes scoped to the relevant module:

• Config loading changes belong in rust-src/config.rs with tests there.

• Identifier generation and quoting changes belong in rust-src/names.rs.

• SQL execution, cleanup, TTL, schema, template, and response-shape changes belong in rust-src/postgres.rs.

• MCP tool surface changes belong in rust-src/mcp.rs and should be reflected in docs/mcp-tools.md.

• MCP client config writing changes belong in rust-src/setup.rs.

• Managed local runtime changes belong in rust-src/local.rs.

• User-facing setup, config, command, and packaging changes should update this README and any relevant files in docs/.

Run at least:

npm run check
npm test
npm run build

For behavior that needs live Postgres, add the smallest practical integration path and document whether it uses the managed local runtime or an explicit PGSANDBOX_ADMIN_DATABASE_URL.

License

MIT. See LICENSE.