{
  "markdown": "# PostgreSQL MCP Server\n\n[![MCP Registry](https://img.shields.io/badge/MCP_Registry-Available-blue)](https://registry.modelcontextprotocol.io)\n[![npm version](https://img.shields.io/npm/v/@hovecapital/read-only-postgres-mcp-server.svg)](https://www.npmjs.com/package/@hovecapital/read-only-postgres-mcp-server)\n\nA Model Context Protocol (MCP) server that enables Claude Desktop to interact with PostgreSQL databases through natural language queries.\n\n## Features\n\n- Execute read-only SQL queries through Claude Desktop or Claude Code\n- **Dynamic database connections** - connect to any PostgreSQL database at runtime\n- Built-in security: statement validation plus database-enforced `READ ONLY` transactions\n- Easy integration with Claude Desktop and Claude Code\n- JSON formatted query results\n- Environment-based default configuration with runtime override support\n\n## Quick Start\n\n### For Claude Code Users (Recommended - Easiest Method)\n\n```bash\nclaude mcp add postgres -s user -- npx -y @hovecapital/read-only-postgres-mcp-server\n```\n\nThen set your database environment variables:\n\n```bash\nexport DB_HOST=localhost\nexport DB_PORT=5432\nexport DB_DATABASE=your_database_name\nexport DB_USERNAME=your_username\nexport DB_PASSWORD=your_password\n```\n\n**Done!** Restart Claude Code and ask: \"What tables are in my database?\"\n\n### For Claude Desktop Users (Manual Configuration)\n\n**1. Open your config file:**\n\n```bash\n# macOS\nopen ~/Library/Application\\ Support/Claude/claude_desktop_config.json\n\n# Windows\nnotepad %APPDATA%\\Claude\\claude_desktop_config.json\n```\n\n**2. Add this configuration:**\n\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@hovecapital/read-only-postgres-mcp-server\"],\n      \"env\": {\n        \"DB_HOST\": \"localhost\",\n        \"DB_PORT\": \"5432\",\n        \"DB_DATABASE\": \"your_database_name\",\n        \"DB_USERNAME\": \"your_username\",\n        \"DB_PASSWORD\": \"your_password\"\n      }\n    }\n  }\n}\n```\n\n**3. Save, restart Claude Desktop, and test!**\n\n## Prerequisites\n\n- Node.js (v16 or higher) - If using mise, update the command path accordingly\n- PostgreSQL database server\n- Claude Desktop application\n\n## Installation\n\n### Option 1: Install from MCP Registry (Recommended)\n\nThis server is published in the [Model Context Protocol Registry](https://registry.modelcontextprotocol.io) as `capital.hove/read-only-local-postgres-mcp-server`.\n\n#### Method A: Claude Code CLI (Easiest!)\n\n```bash\nclaude mcp add postgres -s user -- npx -y @hovecapital/read-only-postgres-mcp-server\n```\n\nThen configure your database credentials using environment variables. Restart Claude Code and you're done!\n\n**Benefits:**\n\n- One command installation\n- No manual JSON editing\n- Automatic configuration\n\n#### Method B: Manual JSON Configuration\n\n**For Claude Desktop:**\n\nEdit `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS) or `%APPDATA%\\Claude\\claude_desktop_config.json` (Windows):\n\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@hovecapital/read-only-postgres-mcp-server\"],\n      \"env\": {\n        \"DB_HOST\": \"localhost\",\n        \"DB_PORT\": \"5432\",\n        \"DB_DATABASE\": \"your_database_name\",\n        \"DB_USERNAME\": \"your_username\",\n        \"DB_PASSWORD\": \"your_password\"\n      }\n    }\n  }\n}\n```\n\n**For Claude Code:**\n\nEdit `~/.config/claude-code/settings.json` (macOS/Linux) or `%APPDATA%\\claude-code\\settings.json` (Windows):\n\n```json\n{\n  \"mcp\": {\n    \"servers\": {\n      \"postgres\": {\n        \"command\": \"npx\",\n        \"args\": [\"-y\", \"@hovecapital/read-only-postgres-mcp-server\"],\n        \"env\": {\n          \"DB_HOST\": \"localhost\",\n          \"DB_PORT\": \"5432\",\n          \"DB_DATABASE\": \"your_database_name\",\n          \"DB_USERNAME\": \"your_username\",\n          \"DB_PASSWORD\": \"your_password\"\n        }\n      }\n    }\n  }\n}\n```\n\n### Option 2: Install from npm\n\n```bash\nnpm install -g @hovecapital/read-only-postgres-mcp-server\n```\n\n### Option 3: Installation with Claude Code\n\nIf you're using Claude Code, you can easily install this MCP server:\n\n```bash\n# Clone the repository\ngit clone https://github.com/hovecapital/read-only-local-postgres-mcp-server.git\ncd read-only-local-postgres-mcp-server\n\n# Install dependencies and build\nnpm install\nnpm run build\n```\n\nThen configure Claude Code by adding to your MCP settings.\n\n### Option 4: Manual Installation\n\n#### 1. Clone or Download\n\nSave the repository to a directory on your system:\n\n```bash\nmkdir ~/mcp-servers/postgres\ncd ~/mcp-servers/postgres\ngit clone https://github.com/hovecapital/read-only-local-postgres-mcp-server.git .\n```\n\n#### 2. Install Dependencies\n\n```bash\nnpm install\nnpm run build\n```\n\n## Configuration\n\n> **Note:** If you installed via Option 1 (MCP Registry with npx), you've already configured everything! This section is for users who chose Options 2, 3, or 4 (npm or manual installation).\n\n### Claude Code Configuration\n\nIf you're using Claude Code with a manual installation, add the PostgreSQL server to your MCP settings:\n\n1. Open your Claude Code settings (typically in `~/.config/claude-code/settings.json` on macOS/Linux or `%APPDATA%\\claude-code\\settings.json` on Windows)\n\n2. Add the PostgreSQL MCP server configuration:\n\n```json\n{\n  \"mcp\": {\n    \"servers\": {\n      \"postgres\": {\n        \"command\": \"node\",\n        \"args\": [\"/absolute/path/to/read-only-local-postgres-mcp-server/dist/index.js\"],\n        \"env\": {\n          \"DB_HOST\": \"localhost\",\n          \"DB_PORT\": \"5432\",\n          \"DB_DATABASE\": \"your_database_name\",\n          \"DB_USERNAME\": \"your_username\",\n          \"DB_PASSWORD\": \"your_password\"\n        }\n      }\n    }\n  }\n}\n```\n\n1. Restart Claude Code for the changes to take effect.\n\n### Claude Desktop Configuration\n\nIf you're using Claude Desktop with a manual installation, open your Claude Desktop configuration file:\n\n**macOS:**\n\n```bash\n~/Library/Application Support/Claude/claude_desktop_config.json\n```\n\n**Windows:**\n\n```bash\n%APPDATA%\\Claude\\claude_desktop_config.json\n```\n\nAdd the PostgreSQL server configuration:\n\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"node\",\n      \"args\": [\"/absolute/path/to/read-only-local-postgres-mcp-server/dist/index.js\"],\n      \"env\": {\n        \"DB_HOST\": \"localhost\",\n        \"DB_PORT\": \"5432\",\n        \"DB_DATABASE\": \"your_database_name\",\n        \"DB_USERNAME\": \"your_username\",\n        \"DB_PASSWORD\": \"your_password\"\n      }\n    }\n  }\n}\n```\n\n### Using mise for Node.js\n\nIf you're using [mise](https://mise.jdx.dev/) for Node.js version management, make sure to use the full path to the Node.js executable in your configuration.\n\n### Environment Variables\n\n| Variable | Description | Default |\n|----------|-------------|---------|\n| `DB_HOST` | PostgreSQL server hostname | `localhost` |\n| `DB_PORT` | PostgreSQL server port | `5432` |\n| `DB_DATABASE` | Database name | `postgres` |\n| `DB_USERNAME` | PostgreSQL username | `postgres` |\n| `DB_PASSWORD` | PostgreSQL password | (empty) |\n| `DB_SSL` | Enable SSL connection | `false` |\n| `DB_ALLOWED_HOSTS` | Comma-separated `host:port` pairs that runtime connection strings may target, in addition to `DB_HOST:DB_PORT` | (empty) |\n\n## Tools\n\nThis MCP server exposes three tools that Claude can use to interact with PostgreSQL databases.\n\n### `connect`\n\nConnect to a PostgreSQL database using a connection string. The connection persists for subsequent queries until changed or disconnected. The target must be `DB_HOST:DB_PORT` or listed in `DB_ALLOWED_HOSTS`.\n\n**Parameters:**\n\n| Parameter | Type | Required | Description |\n|-----------|------|----------|-------------|\n| `connectionString` | string | Yes | PostgreSQL connection string |\n\n**Connection String Format:**\n\n```\npostgres://username:password@host:port/database?sslmode=require\npostgresql://username:password@host:port/database\n```\n\n**SSL Modes Supported:**\n\n- `sslmode=require` - Require SSL (recommended for remote connections)\n- `sslmode=verify-full` - Require SSL with certificate verification\n- No sslmode parameter - No SSL (for local connections)\n\n**Example Usage (natural language):**\n\n```\n\"Connect to postgres://myuser:mypass@db.example.com:5432/production\"\n\"Connect to this database: postgres://admin:secret@localhost/analytics\"\n```\n\n**Response:**\n\n```json\n{\n  \"status\": \"connected\",\n  \"host\": \"db.example.com\",\n  \"port\": 5432,\n  \"database\": \"production\",\n  \"user\": \"myuser\",\n  \"ssl\": true\n}\n```\n\n---\n\n### `disconnect`\n\nDisconnect from the current runtime database and revert to the default environment-configured connection.\n\n**Parameters:** None\n\n**Example Usage (natural language):**\n\n```\n\"Disconnect from the current database\"\n\"Go back to the default database\"\n```\n\n**Response:**\n\n```json\n{\n  \"status\": \"disconnected\",\n  \"message\": \"Reverted to default environment connection\",\n  \"host\": \"localhost\",\n  \"database\": \"postgres\"\n}\n```\n\n---\n\n### `query`\n\nRun a read-only SQL query against the currently connected database. Optionally override the connection for a single query.\n\n**Parameters:**\n\n| Parameter | Type | Required | Description |\n|-----------|------|----------|-------------|\n| `sql` | string | Yes | SQL query to execute (SELECT only) |\n| `connectionString` | string | No | Override connection for this query only |\n\n**Example Usage (natural language):**\n\n```\n\"Show me all tables in the database\"\n\"SELECT * FROM users LIMIT 10\"\n\"Run this query on postgres://other:pass@host/db: SELECT count(*) FROM orders\"\n```\n\n**Response:**\n\n```json\n[\n  { \"id\": 1, \"name\": \"Alice\", \"email\": \"alice@example.com\" },\n  { \"id\": 2, \"name\": \"Bob\", \"email\": \"bob@example.com\" }\n]\n```\n\n---\n\n### Tool Reference for LLMs\n\nWhen using this MCP server, Claude can:\n\n1. **Query the default database** (configured via environment variables):\n\n   ```\n   User: \"What tables are in my database?\"\n   Claude: [Uses query tool with SQL: \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\"]\n   ```\n\n2. **Connect to a different database dynamically**:\n\n   ```\n   User: \"Connect to postgres://user:pass@newhost/newdb and show me the users table\"\n   Claude: [Uses connect tool first, then query tool]\n   ```\n\n3. **One-off query to a different database** (without switching active connection):\n\n   ```\n   User: \"How many records are in the orders table on postgres://user:pass@analytics/warehouse?\"\n   Claude: [Uses query tool with connectionString parameter]\n   ```\n\n4. **Revert to default connection**:\n\n   ```\n   User: \"Go back to my local database\"\n   Claude: [Uses disconnect tool]\n   ```\n\n## Usage\n\n1. **Restart Claude Desktop/Code** after updating the configuration\n2. **Start chatting** with Claude about your database\n\n### Example Queries\n\n**Basic queries (uses default/active connection):**\n\n```\n\"Show me all tables in my database\"\n\"What's the structure of the users table?\"\n\"Get the first 10 records from the products table\"\n\"How many orders were placed last month?\"\n\"Show me users with email addresses ending in @gmail.com\"\n```\n\n**Dynamic connection examples:**\n\n```\n\"Connect to postgres://analyst:password@analytics.example.com:5432/warehouse\"\n\"Now show me all the tables\"\n\"What's the total revenue in the sales table?\"\n\"Disconnect and go back to my local database\"\n```\n\n**One-off queries to different databases:**\n\n```\n\"Run SELECT count(*) FROM users on postgres://admin:secret@prod.example.com/app\"\n\"Check the orders table on my staging database: postgres://dev:dev@staging/app\"\n```\n\nClaude will automatically convert your natural language requests into appropriate SQL queries and execute them against your database.\n\n## Security Features\n\n### Read-Only Operations\n\nThe server enforces read-only access on **all connections** (both environment-configured and runtime dynamic connections) in three layers:\n\n1. **Database-enforced read-only transaction.** Every query runs inside `BEGIN READ ONLY`, and the connection is closed afterwards, so nothing is ever committed. PostgreSQL itself rejects `INSERT`, `UPDATE`, `DELETE`, `MERGE`, DDL, `SELECT INTO`, `nextval()`, large-object writes and any write hidden in a CTE (`WITH x AS (INSERT ...) SELECT ...`) or a function body, regardless of how the statement is spelled.\n2. **Single statement per query.** Queries are sent with the extended query protocol, which PostgreSQL restricts to one statement, so `SELECT 1; DROP TABLE ...` is rejected by the server.\n3. **Statement validation before execution.** Leading comments are stripped, then a query is rejected if it begins with any of the following statement keywords:\n\n- **Data manipulation** - `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `COPY`, `MERGE`\n- **Schema / DDL** - `CREATE`, `ALTER`, `DROP`, `COMMENT`, `RENAME`, `REASSIGN`\n- **Permissions** - `GRANT`, `REVOKE`, `SECURITY`\n- **Session / config** - `SET`, `RESET`, `DISCARD`, `LOAD`\n- **Procedures / dynamic execution** - `CALL`, `DO`, `EXECUTE`, `PREPARE`, `DEALLOCATE`\n- **Cursors** - `DECLARE`, `FETCH`, `MOVE`, `CLOSE`\n- **Transaction control** - `BEGIN`, `START`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE`, `LOCK`\n- **Maintenance** - `VACUUM`, `ANALYZE`, `REINDEX`, `CLUSTER`, `REFRESH`, `CHECKPOINT`\n- **Async notification** - `NOTIFY`, `LISTEN`, `UNLISTEN`\n\nOnly statements beginning with read verbs (e.g. `SELECT`, `WITH`, `EXPLAIN`, `SHOW`, `TABLE`, `VALUES`) are allowed through. Because `EXPLAIN ANALYZE` executes the statement it wraps, its inner statement is validated too.\n\nA read-only transaction does not stop functions that touch the server filesystem, run arbitrary SQL, or change process state, so a query is also rejected if it mentions any of these anywhere in its text: `pg_read_file`, `pg_read_binary_file`, `pg_stat_file`, `pg_ls_*`, `pg_file_*`, `pg_logdir_ls`, `pg_logfile_rotate`, `lo_import`, `lo_export`, `dblink*`, `query_to_xml*`, `crosstab*`, `connectby`, `set_config`, `pg_terminate_backend`, `pg_cancel_backend`, `pg_reload_conf`, `pg_rotate_logfile`, `pg_sleep*`. Unicode-escaped identifiers (`U&\"...\"`) are rejected because they could spell one of these names another way.\n\nFunction-name matching is a denylist and cannot be complete. The dedicated read-only role below removes the underlying privileges (`pg_write_server_files`, `pg_read_server_files`, superuser) that file operations need, and is the recommended setup.\n\n### Dynamic Connection Security\n\nWhen using the `connect` tool or `connectionString` parameter:\n\n- **Host allowlist** - Runtime connection strings may only target `DB_HOST:DB_PORT` or a `host:port` pair listed in `DB_ALLOWED_HOSTS`. Other targets are rejected before any socket is opened, so the tools cannot be used to scan ports on the host or its network.\n- **Read-only enforcement still applies** - All queries are validated regardless of connection source\n- **Credentials are not logged** - Connection strings with passwords are never written to logs\n- **Sanitized responses** - The `connect` tool response excludes passwords\n- **Session-based** - Runtime connections only persist for the current MCP session\n\n### Recommended Database Setup\n\nFor enhanced security, create a dedicated read-only user for the MCP server:\n\n```sql\n-- Create a read-only user\nCREATE USER claude_readonly WITH PASSWORD 'secure_password';\n\n-- Grant only SELECT permissions on your specific schema\nGRANT USAGE ON SCHEMA public TO claude_readonly;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_readonly;\n\n-- Grant permissions for future tables (optional)\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO claude_readonly;\n```\n\n## Troubleshooting\n\n### Connection Issues\n\n1. **Verify PostgreSQL is running**: Check if your PostgreSQL server is active\n2. **Check credentials**: Ensure username/password are correct\n3. **Network connectivity**: Confirm Claude Desktop can reach your PostgreSQL server\n\n### Configuration Issues\n\n1. **Restart required**: Always restart Claude Desktop after configuration changes\n2. **Path accuracy**: Ensure the absolute path to `dist/index.js` is correct\n3. **JSON syntax**: Validate your `claude_desktop_config.json` format\n\n### Debug Mode\n\nTo see server logs, you can run the server manually:\n\n```bash\nnode dist/index.js\n```\n\n## File Structure\n\n```bash\n~/mcp-servers/postgres/\n├── src/\n│   └── index.ts\n├── dist/\n│   ├── index.js\n│   └── index.d.ts\n├── package.json\n├── tsconfig.json\n└── node_modules/\n```\n\n## Dependencies\n\n- **@modelcontextprotocol/sdk**: MCP protocol implementation\n- **pg**: PostgreSQL client for Node.js\n\n## Contributing\n\nFeel free to submit issues and enhancement requests!\n\n## License\n\nThis project is open source and available under the [MIT License](LICENSE).\n\n## Support\n\nIf you encounter issues:\n\n1. Check the troubleshooting section above\n2. Verify your PostgreSQL connection independently\n3. Ensure Claude Desktop is updated to the latest version\n4. Review the Claude Desktop MCP documentation\n\n---\n\n**Note**: This server is designed for development and analysis purposes. For production use, consider additional security measures and monitoring.\n",
  "bytes": 16934,
  "sha": "f3ebf9e23dd2fe0b569862b36c974bd52684c5c68daf91e9c2d789404630266b",
  "repo_slug": "hovecapital/read-only-local-postgres-mcp-server",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_capital_hove_read_only_local_postgres_mc_042d775e/readme"
}