npm package: @gishubperu/mcp-postgresql
Description: Presentation layer — MCP server with stdio and HTTP transports.
Author: GisHub Perú SAC
Latest version: 1.0.6

# @gishubperu/mcp-postgresql

Secure PostgreSQL MCP server for AI agents. Read-only by default, with runtime permission control.

## Features

- **12 MCP tools** — query, list tables, describe schema, execute DML/DDL/DCL, manage permissions and profiles
- **SQL injection protection** — 20+ pattern rules (stacked queries, file I/O, dblink, function creation, privilege escalation, XXE)
- **Runtime permissions** — agent enables/disables DML/DDL/DCL without restarting
- **Named profiles** — switch databases without exposing credentials
- **Zero-config start** — just create `.ghp/postgres.json`

## Quick start

```bash
npx @gishubperu/mcp-postgresql
```

Create `.ghp/postgres.json` in your workspace root:

```json
{
  "url": "postgresql://user:pass@host:5432/db",
  "permissions": { "dml": true, "ddl": true, "dcl": false }
}
```

## MCP client config

**Claude Desktop / Qwen Code / Cursor**

```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@gishubperu/mcp-postgresql"]
    }
  }
}
```

The server reads `.ghp/postgres.json` from the workspace root automatically.

## Configuration

### `.ghp/postgres.json`

**Simple** (one database):

```json
{
  "url": "postgresql://user:pass@host:5432/mydb",
  "permissions": { "dml": true, "ddl": true, "dcl": false }
}
```

**Multi-profile** (multiple databases):

```json
{
  "profiles": {
    "dev": {
      "url": "postgresql://dev:pass@localhost:5432/myapp_dev",
      "permissions": { "dml": true, "ddl": true, "dcl": false }
    },
    "prod": {
      "url": "postgresql://reader:pass@prod-host:5432/myapp_prod",
      "permissions": { "dml": false, "ddl": false, "dcl": false }
    }
  },
  "default": "dev"
}
```

Profiles include permission defaults — switching to `prod` automatically locks down write access.

### Global config

Place `~/.ghp/postgres.json` for cross-project access. Workspace config takes precedence.

### Environment variables

| Variable | Description |
|---|---|
| `DATABASE_URL` | Connection string fallback |
| `PROFILE` | Profile name at startup |
| `MCP_TRANSPORT` | `stdio` (default) or `http` |
| `MCP_HTTP_PORT` | Port for HTTP (default `3000`) |
| `DEBUG` | Print SQL to stderr |

## Tools

| Tool | Description |
|---|---|
| `query` | SELECT / EXPLAIN / SHOW / FETCH — parameterized `$1`, `$2` |
| `list_tables` | Tables with row counts and sizes |
| `describe_table` | Columns, indexes, constraints |
| `execute_dml` | INSERT / UPDATE / DELETE / CALL — supports multi-statement transactions |
| `execute_ddl` | CREATE / ALTER / DROP / TRUNCATE |
| `execute_dcl` | GRANT / REVOKE / CREATE ROLE |
| `configure_permissions` | Enable/disable DML/DDL/DCL at runtime |
| `get_permissions` | Current permission policy status |
| `set_profile` | Switch to a named connection profile |
| `list_profiles` | Available profiles (credentials redacted) |
| `get_current_profile` | Active profile and connection details |
| `setup_config` | Configuration guide with exact JSON format examples |

All tools are always available. Permissions start disabled (DQL only).

## Agent workflow

```
Agent: get_permissions()
→ { dql: true, dml: false, ddl: false, dcl: false }

Agent: configure_permissions({ "ddl": true })
→ { previous: {...}, current: { dml: false, ddl: true, ... } }

Agent: execute_ddl({ sql: "ALTER TABLE ..." })
→ { rowCount: 0 }

Agent: configure_permissions({ "ddl": false })
→ back to safe defaults
```

## Security

- **SQL injection protection**: Blocklist of 20+ patterns covering stacked queries, file I/O, dblink, large objects, function creation, privilege escalation, XXE, and more
- **Parameterized queries**: All user values use `$1`, `$2`, ...
- **Credential hiding**: Profile tools never expose full connection strings
- **Per-profile permissions**: Each profile defines its own access level

## License

MIT © GisHub Perú