Content
# mcp-pgs-tool
MCP Server (Model Context Protocol) in **TypeScript** for **PostgreSQL**.
The project provides tools for analyzing database schema and activity, searching for potentially "cold" tables/columns, checking index coverage, analyzing `pg_stat_statements`, and roughly searching for table and column usage in local code.
Transport: **stdio** (one line = one JSON-RPC packet).
---
## Table of Contents
- [Capabilities](#capabilities)
- [Requirements](#requirements)
- [Installation and Build](#installation-and-build)
- [Connecting MCP to Cursor](#connecting-mcp-to-cursor)
- [Connecting MCP to GigaCode CLI](#connecting-mcp-to-gigacode-cli)
- [Environment Variables](#environment-variables)
- [Tools](#tools)
- [Usage Safety](#usage-safety)
- [PostgreSQL Rights and Extensions](#postgresql-rights-and-extensions)
- [Smoke Test](#smoke-test)
- [Limitations and Result Interpretation](#limitations-and-result-interpretation)
- [Development](#development)
---
## Capabilities
- Retrieving schemas, tables, and columns from `information_schema`.
- Table activity statistics from `pg_stat_user_tables`.
- Heuristics for "suspicious/rarely used" columns based on `pg_stats`.
- Searching for columns not included in any index.
- Top SQL queries from `pg_stat_statements`.
- Scanning local repository for table/column mentions.
- Built-in protection:
- Only read-only SQL (at runtime policy level),
- Masking sensitive data in responses.
---
## Requirements
| Component | Version |
|-----------|--------|
| Node.js | **>= 20** |
| PostgreSQL | recommended **12+** |
---
## Installation and Build
```bash
git clone <REPOSITORY_URL>
cd mcp-pgs-tool
npm install
npm run build
```
After building, the main entry point is: `dist/index.js`.
There is also a root `index.js` (shim) that loads `dist/index.js`.
---
## Connecting MCP to Cursor
Edit the file `~/.cursor/mcp.json` (on Windows: `C:\Users\<user>\.cursor\mcp.json`) and add the server.
Example for Windows:
```json
{
"mcpServers": {
"mcp-pgs-tool": {
"command": "node",
"args": [
"C:/Users/<USER>/IdeaProjects/mcp-pgs-tool/dist/index.js"
],
"env": {
"DATABASE_URL": "postgresql://DB_USER:DB_PASSWORD@HOST:5432/DB_NAME"
}
}
}
}
```
Important:
- The path must point to an **existing** file (`dist/index.js` or root `index.js`).
- After changing the config, restart MCP/IDE.
- If using the root `index.js`, the project must be built (`npm run build`), otherwise the shim will exit with an error.
---
## Connecting MCP to GigaCode CLI
For GigaCode CLI, add the server to the project file:
- `.gigacode/settings.json`
Example configuration:
```json
{
"mcpServers": {
"mcp-pgs-tool": {
"command": "node",
"args": [
"C:/Users/<USER>/IdeaProjects/mcp-pgs-tool/index.js"
],
"env": {
"DATABASE_URL": "postgresql://DB_USER:DB_PASSWORD@HOST:5432/DB_NAME"
}
}
}
}
```
Recommendations:
- Ensure `npm run build` is executed and `dist/index.js` exists.
- The root `index.js` is a shim that loads `dist/index.js` after building.
- If GigaCode CLI has an MCP configuration reload command, execute it after changing the file.
- Do not store real passwords in an open repository; use local config or environment secrets.
---
## Environment Variables
| Variable | Mandatory | Purpose |
|------------|----------------|-----------|
| `DATABASE_URL` | Yes | PostgreSQL URI for connection |
Example:
```text
postgresql://user:password@localhost:5432/mydb
```
If `DATABASE_URL` is not set, the server starts, but DB tools will return an error.
---
## Tools
### `pg_health`
Checking database connection:
- PostgreSQL version,
- current database,
- presence of `pg_stat_statements` extension.
### `pg_list_schemas`
List of user schemas (without system ones).
### `pg_list_tables`
List of tables/views:
- schema,
- name,
- type,
- row count estimate (`reltuples`).
Parameters:
- `schemas?: string[]` — schema filter.
### `pg_list_columns`
List of columns:
- table/schema name,
- column name,
- type,
- nullable,
- default.
Parameters:
- `schema?: string`
- `table?: string`
### `pg_table_activity`
Table activity from `pg_stat_user_tables`:
- `seq_scan`, `idx_scan`,
- `n_tup_ins`, `n_tup_upd`, `n_tup_del`,
- `seq_tup_read`, `idx_tup_fetch`,
- vacuum/analyze dates.
Parameters:
- `order: "hot" | "cold"` (default `"cold"`)
- `limit: number` (1..500)
### `pg_column_stats_suspicious`
Heuristics from `pg_stats`:
- `null_frac`,
- `n_distinct`,
- `correlation`,
- `most_common_vals`.
Parameters:
- `limit: number` (1..500)
- `minNullFrac: number` (0..1)
### `pg_columns_not_in_any_index`
User table columns not included in any index.
Parameters:
- `limit: number` (1..2000)
### `pg_stat_statements_top`
Top queries from `pg_stat_statements`.
Parameters:
- `sortBy`: `total_time | mean_time | calls | rows | shared_blks_read`
- `limit`: 1..200
- `minCalls`
- `queryContains?`
- `currentDatabaseOnly`
- `maxQueryChars`
- `includeInfo`
Note on time:
- PostgreSQL 13+: `total_exec_time` / `mean_exec_time`
- PostgreSQL 12: `total_time` / `mean_time`
### `pg_scan_codebase_usage`
Scans local code under `codebaseRoot` and searches for whole words:
- table name,
- `schema.table`,
- column name,
- `table.column`,
- `schema.table.column`.
Parameters:
- `codebaseRoot` (mandatory)
- `schemas?`
- `maxTables`
- `maxColumnsPerTable`
- `maxFiles`
Output:
- scan summary,
- sample hit lines,
- `possiblyNotReferencedInCode` (candidates "not found in code").
---
## Usage Safety
The project implements two levels of protection.
### 1) Read-only SQL Policy
All queries go through `safeQuery()` in `src/db.ts`.
Only allowed:
- `SELECT`
- `WITH`
- `SHOW`
- `EXPLAIN`
Blocked:
- DML/DDL/control operations (`INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, `GRANT`, `SET`, `COPY`, etc.),
- multi-statement SQL (e.g., `SELECT ...; DELETE ...`).
Result: MCP tools in this server **cannot change data** in the database.
### 2) Sensitive Data Masking
Before returning a response to the client, sanitization is performed (`src/index.ts`):
- By field keys (e.g., `password`, `token`, `secret`, `card`, `account`, `email`, `phone`) values are edited to `"[redacted]"`.
- In strings, patterns are masked:
- email -> `***@***`
- phone -> `[masked-phone]`
- card number (with Luhn check) -> `[masked-card]`
This reduces the risk of leaking PII/financial data in tool responses.
---
## PostgreSQL Rights and Extensions
Some statistical views require elevated rights (often `pg_read_all_stats`).
For `pg_stat_statements_top`, you need:
1. Enable the extension in preload:
```text
shared_preload_libraries = 'pg_stat_statements'
```
2. Restart PostgreSQL.
3. Execute in the required database:
```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```
---
## Smoke Test
```bash
npm run build
npm run smoke
```
Script: `scripts/mcp-smoke.mjs`.
Checks:
- MCP handshake (`initialize`),
- `tools/list`,
- `pg_health` call.
---
## Limitations and Result Interpretation
1. `pg_stat_*` metrics are cumulative (since startup/stats reset).
2. PostgreSQL does not have a direct universal counter for "how many times a specific column was read"; `pg_column_stats_suspicious` is a heuristic.
3. `pg_scan_codebase_usage` may:
- skip dynamic SQL/ORM construction,
- give false positives for similar words.
4. Masking in responses reduces risks but does not replace full DLP/access policy on the database and infrastructure side.
---
## Development
| Command | Purpose |
|---------|------------|
| `npm run dev` | run `src/index.ts` via `tsx` |
| `npm run build` | compile to `dist/` |
| `npm run start` | run `node dist/index.js` |
| `npm run smoke` | smoke test MCP |
Structure:
| Path | Role |
|------|------|
| `src/index.ts` | MCP server and tool routing |
| `src/queries.ts` | SQL queries to PostgreSQL |
| `src/db.ts` | connection pool + read-only guard |
| `src/codeScan.ts` | source code scan for identifier mentions |
| `src/config.ts` | reading `DATABASE_URL` |
| `scripts/mcp-smoke.mjs` | local smoke test |
