granludo/postgresql_mcp_lite

postgresql_mcp_lite

Small MCP server to access postgresql - not for production

by Marc Alier

PostgreSQL MCP Server

FastMCP server for executing SQL queries on PostgreSQL databases. Designed for evaluating student database projects.

Features

• SSE Transport: Server-Sent Events for MCP communication
• Multi-Database Support: Execute queries on different student databases
• Read-Only Mode: Optional restriction to SELECT queries only
• Query Timeout: Automatic timeout for long-running queries
• Row Limiting: Prevents returning too many rows

Installation

1. Install dependencies:

pip install -r requirements.txt

2. Create configuration file:

cp config.json.example config.json

3. Edit config.json with your PostgreSQL credentials:

{
  "host": "localhost",
  "port": 5432,
  "user": "evaluator",
  "password": "your_password",
  "read_only": true,
  "query_timeout": 30,
  "max_rows": 1000
}

Configuration Options

• host: PostgreSQL server hostname
• port: PostgreSQL server port (default: 5432)
• user: Database user for connections
• password: Database password
• read_only: If true, only SELECT queries are allowed (default: true)
• query_timeout: Maximum query execution time in seconds (default: 30)
• max_rows: Maximum number of rows to return (default: 1000)

Running the Server

python server.py

The server will start with SSE transport, ready to accept MCP connections.

Available Tools

1. list_databases()

Lists all available databases on the PostgreSQL server.

Returns:

{
  "status": "success",
  "databases": ["postgres", "student_db1", "student_db2"],
  "count": 3
}

2. execute_sql(database, query)

Execute a SQL query on a specific database.

Parameters:

• database (str): Name of the database to connect to
• query (str): SQL query to execute

Returns:

{
  "status": "success",
  "rows": [...],
  "row_count": 10,
  "columns": ["id", "name", "email"],
  "message": "Query executed successfully. Returned 10 rows."
}

Example Queries

List all tables in a database:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public'

Describe a table structure:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns 
WHERE table_name = 'users'

Query data:

SELECT * FROM students WHERE grade >= 90

Security Notes

⚠️ This server is intended for educational/evaluation purposes only, not for production use.

• Set read_only: true to prevent data modifications
• Use a dedicated PostgreSQL user with limited privileges
• Consider network restrictions (firewall, localhost-only access)
• Review queries in logs for suspicious activity

Connecting with MCP Clients

Configure your MCP client (like Claude Desktop) to connect to this server via SSE transport. The server exposes two tools that can be used to explore and query student databases.

Troubleshooting

Connection refused:

• Check PostgreSQL is running: pg_isready
• Verify host/port in config.json
• Check PostgreSQL accepts connections (pg_hba.conf)

Authentication failed:

• Verify username/password in config.json
• Check user has CONNECT privilege on databases

Permission denied:

• Ensure user has SELECT (and other) permissions on target databases
• Grant necessary privileges: GRANT CONNECT ON DATABASE student_db TO evaluator;

License

GPL v3.0

Usage intention

For educational use. Not production!