PostgreSQL

You run a PostgreSQL MCP Server to access and manage PostgreSQL data remotely over HTTP/SSE. It lets you run queries, manage schemas, manipulate data, control users, and perform maintenance from an MCP client, while enforcing secure access and connection pooling.

How to use

Connect your MCP client to the server using the HTTP transport. You will authenticate with an API key and issue high-level actions like running SELECT queries, inspecting schemas, inserting or updating data, managing users, and performing maintenance tasks. Use natural language prompts to describe the actions you want (for example, show me tables, or create a products table with id, name, and price). The MCP client will translate your requests into the corresponding server tools and return results.

Configure the MCP client with the server URL and your API key. The server exposes an HTTP endpoint over HTTPS for remote access. Ensure you keep the API key secret and rotate it periodically. When you perform actions, you will see results such as table definitions, query results, and status updates for maintenance operations.

How to install

Prerequisites: you need Docker and Docker Compose installed on your machine. You also need a PostgreSQL instance you will connect to.

# 1) Build the Docker image
docker build -t postgresql-mcp-server .

# 2) Configure environment variables
# Generate an API key for client authentication
python3 -c "import secrets; print(secrets.token_urlsafe(32))"

# Copy example env file and edit with your settings
cp .env.example .env
# Edit .env with your PostgreSQL settings and API key

3. Run with Docker Compose. The server will be exposed at http://localhost:3000 once it starts.

docker-compose up -d

4. Configure the MCP client (Cursor) to connect using the server URL and the API key you generated.

{
  "mcpServers": {
    "postgresql": {
      "url": "https://your-server.com:3000",
      "headers": {
        "Authorization": "Bearer your_api_key"
      }
    }
  }
}

Additional configuration and usage notes

Environment variables you may configure in the server to control PostgreSQL access and behavior include the host, port, database name, user, password, read-only mode, SSL mode, connection pool size, and query timeouts.

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=your_db
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_password
POSTGRES_READONLY=false
POSTGRES_SSLMODE=require
POSTGRES_POOL_MIN=1
POSTGRES_POOL_MAX=10
QUERY_TIMEOUT=30

Security and maintenance tips

Use environment variables for credentials, enable read-only mode for exploration, create dedicated users with minimal privileges, enforce SSL connections, set query timeouts to prevent long-running queries, and tune the connection pool according to your database capacity.

Maintenance tasks you can perform include VACUUM ANALYZE, backups, restoration, and managing active connections. Regularly test database connections to verify your MCP setup remains healthy.

Troubleshooting

If you encounter connection issues, ensure your PostgreSQL instance is accessible from the MCP server environment, verify Docker networking, and check client logs for MCP errors.

If you see permission errors, verify that the database user has the required privileges and that access rules (pg_hba.conf) permit the connection from the MCP server.

If Docker-related issues arise, confirm the Docker daemon is running, check for port conflicts, and verify that the MCP server image built successfully.

Project notes

This server supports a broad set of MCP tools for querying, schema management, data manipulation, user management, and maintenance, all designed to be used from an MCP client with HTTP/SSE transport.

Available tools

execute_query

Run SELECT queries and retrieve results from the database via the MCP interface.

execute_explain

Obtain query execution plans to analyze performance.

list_databases

List all databases accessible to the connected PostgreSQL instance.

list_tables

List tables within a specified schema.

list_columns

Show column information for a given table.

get_table_info

Get detailed metadata about a table, including columns and constraints.

get_database_size

Return size information for databases and tables.

create_table

Create a new table with specified columns and types.

drop_table

Remove an existing table.

alter_table

Modify the structure of an existing table.

create_index

Create indexes on table columns to optimize queries.

drop_index

Remove an existing index.

get_table_ddl

Generate a CREATE TABLE statement for an existing table.

insert_data

Insert a single row into a table.

bulk_insert

Insert multiple rows in a single operation.

update_data

Update existing rows in a table.

delete_data

Delete rows from a table.

list_users

List database users.

create_user

Create a new database user.

grant_permissions

Grant privileges to a user.

revoke_permissions

Revoke privileges from a user.

list_permissions

Show user permissions.

vacuum_analyze

Run VACUUM ANALYZE on databases or tables.

backup_database

Create a pg_dump backup of the database.

restore_database

Restore from a pg_dump backup.

kill_connections

Terminate active connections to a database.

get_active_connections

Show currently active connections.

test_connection

Test connectivity to the PostgreSQL database.