PG Pilot

Lightweight PostgreSQL client for Visual Studio Code.

Features

• Sidebar connection manager: add, edit, test, duplicate, remove, and import/export connections
• Authentication: password, connection string, or AWS RDS IAM / SSO (short-lived token auth)
• Schema browser: databases, schemas, tables, views, columns, and functions
• Table shortcuts: Select Top 200 Rows and View Table Details (indexes & constraints) from the tree
• Function SQL file navigation: open <function>.sql files from function tree items
• Query execution: run full editor SQL, selected SQL, or one/many .sql files from the explorer
• Results panel: multi-statement tabs, filtering, resizable columns, JSON expand/copy, CSV/JSON export; display in a side editor or the bottom panel
• Server-side row cap: SELECT results are limited on the server, so querying a huge table never pulls it down in full. Statements that bound themselves, lock rows, or write (including SELECT … INTO) are left as written. The true total shows as Unknown total — click to count, and COUNT(*) runs only when you ask for it
• Query history: re-open previous queries
• Status bar: active connection and database context, including a — disconnected badge (click to reconnect) when a session drops
• Connection recovery: dropped sessions are detected, reported everywhere, and re-established automatically on next use
• Long-running queries: run without the statement timeout, with elapsed time and cancellation
• Guardrails: destructive query confirmation and a server-side LIMIT on SELECT reads
• AI tools: list connections, connect, list databases, get schema, describe table, run query, and query history — available in Copilot Chat and, via a local MCP server, the Claude CLI and other MCP clients

Keybindings

Notes:

• Query keybindings apply in SQL editors (resourceLangId == sql).
• .sql and .pgsql files are registered to SQL in this extension.

Settings

Authentication (password, connection string, or AWS RDS IAM)

Each connection can authenticate in one of these ways, chosen in the connection form:

• Password — a password stored in VS Code SecretStorage.
• Connection string — a full postgresql://… URL (also kept in SecretStorage).
• RDS IAM / SSO — no stored password. PG Pilot mints a short-lived AWS RDS IAM auth token per connection using the AWS SDK. Supply an AWS Region and, optionally, an AWS Profile; when no profile is given, credentials are resolved from the standard AWS provider chain (env vars, SSO cache, instance role, etc.). Use the optional TLS Servername field when tunnelling through a proxy/NLB so the token is signed against the real RDS hostname rather than the tunnel host.

Importing and exporting connections

Move connections between machines without re-typing every field:

• PG Pilot: Export All Connections — save every connection to a JSON file.
• Export Connection (connection right-click) — save a single connection.
• PG Pilot: Import Connections — load connections from a JSON file.

Exported JSON never includes passwords or connection strings — those stay in SecretStorage. After importing, re-enter the password (or reconnect via IAM) for each connection.

Production / sensitive connections

A connection is treated as sensitive when its "Sensitive / production database" checkbox is ticked, or (when unset) when its name/host/database matches pgPilot.sensitiveNamePattern (e.g. prod, production, live). Sensitive connections enable extra safeguards:

• Server-side read-only by default — the session runs with default_transaction_read_only = on, so PostgreSQL itself rejects writes/DDL. Toggle it with PG Pilot: Toggle Read-Only Mode (or the status-bar Read-only / Writes ON badge) when you genuinely need to write.
• Stronger write confirmations — any INSERT/UPDATE/DELETE/DDL statement (in any position of a multi-statement run) is confirmed, naming the connection and statements.
• Agents blocked — Copilot/MCP tools refuse to connect to or query sensitive connections unless pgPilot.allowAgentsOnSensitive is enabled.
• Confirm-on-connect and a red PROD: status-bar badge.
• Forced TLS verification (verify-full equivalent), overriding a weaker per-connection SSL mode.
• Idle auto-disconnect after pgPilot.sensitiveIdleDisconnectMinutes.

Transactions (pinned session)

Each connection uses a single dedicated backend (a "pinned session"), so transaction control spans separate runs like a psql session: run BEGIN; UPDATE …; in one execution and ROLLBACK;/COMMIT; in a later one and it affects the same transaction. An open transaction is shown by a TX OPEN status-bar badge (click to roll back), and idle_in_transaction_session_timeout is a server-side backstop so a forgotten transaction cannot hold locks forever. Use PG Pilot: Roll Back Open Transaction to discard one manually.

Dropped connections and reconnecting

A pinned session can die without warning — the machine sleeps, a VPN reconnects, the server restarts. TCP does not report that immediately, so the extension takes care to never claim a connection it does not have:

• Open connections are checked whenever the VS Code window regains focus, and periodically in the background (pgPilot.connectionHealthCheck). A session that does not answer is retired.
• A query that fails because the session died marks the connection disconnected everywhere — status bar, sidebar, and the agent tools — rather than only reporting a query error.
• The status bar shows <name> — disconnected; the tooltip gives the reason and clicking it reconnects. PG Pilot: Reconnect does the same from the palette or the tree context menu.
• Running a query on a dropped connection re-establishes it first (pgPilot.autoReconnect), keeping your database, schema tree and read-only choice. Sensitive/production connections re-arm their read-only default on reconnect.
• Schema introspection (sidebar tree, IntelliSense, Cmd+Click) takes the same path and is time-bounded, so it recovers with everything else instead of spinning forever.
• Disconnecting or switching database never hangs, even when the underlying socket is dead.

Long-running queries

pgPilot.queryTimeout (default 30s) applies a server-side statement_timeout to every run. For work that legitimately takes longer — large aggregates, backfills, index builds, VACUUM — run it with no timeout instead of raising the global setting:

• PG Pilot: Run Query (No Timeout) — from the command palette or the editor toolbar (clock icon).
• PG Pilot: Run Query (No Limit, No Timeout) — the same, also bypassing the row limit (command palette only).
• Agents can pass noTimeout: true to the pgPilot_runQuery tool.

The progress notification shows elapsed time and stays cancellable throughout (cancellation issues pg_cancel_backend on a separate connection). Because each connection uses one pinned session, a second query started while a long one is running is rejected with a clear message rather than queuing invisibly.

Using PG Pilot from the Claude CLI

The same tools PG Pilot exposes to Copilot Chat (list connections, connect, list databases, get schema, describe table, run queries, query history) are also served over a local MCP server so you can drive your database from the Claude CLI — no chat window required. (Sensitive/production connections are still blocked for agents unless pgPilot.allowAgentsOnSensitive is enabled.)

Because the server runs inside the extension, it shares the live connection state: whatever connection is active in the sidebar is the one the CLI queries.

1. Make sure the extension is running and a connection is active in the sidebar.
2. Run PG Pilot: Connect Claude CLI from the Command Palette. This runs claude mcp add --scope user --transport http pg-pilot http://127.0.0.1:7032/mcp for you (if the claude CLI isn't on your PATH, it offers to copy the command instead). User scope means the server is available from every directory you launch claude in.
3. Run claude in the same project folder and ask it to explore your database — e.g. "list my postgres connections and show the columns of the users table".

The server listens on 127.0.0.1 only and is never reachable off your machine. Disable it with pgPilot.mcpServer.enabled if you don't need it.

Security and Data Storage

• Connection metadata (name/host/port/user/database/SSL/sensitive/auth mode/AWS region/profile/TLS servername) is stored in extension globalState.
• Connection passwords and connection strings are stored in VS Code SecretStorage. RDS IAM connections store no password — a short-lived auth token is minted on demand.
• Existing plaintext credentials from older versions are migrated to SecretStorage on activation.
• Exported connection files exclude passwords and connection strings; only non-secret metadata is written.
• Query history is stored locally in extension globalState (note: full SQL text, including any literal values in WHERE clauses, is retained — mind PII when debugging production).
• No telemetry is emitted by this extension.

Getting Started

1. Open the PostgreSQL view in the Activity Bar.
2. Click Add Connection.
3. Enter credentials (or a connection string) and save.
4. Connect to the new connection from the tree.
5. Open a .sql file (or create a new query editor with Ctrl+Shift+Q on Windows/Linux or Cmd+Option+Q on macOS).
6. Run with Shift+Enter.

Development

pnpm install
pnpm run package    # bundle to dist/extension.js
pnpm run dev        # rebuild on change (watch mode)
pnpm run lint       # type-check with tsc --noEmit

Run in VS Code Extension Development Host with F5.

Packaging

pnpm run build:vsix   # → pg-pilot.vsix