PostgreSQL MCP Server (Enhanced)
A Model Context Protocol server that provides both read and write access to PostgreSQL databases. This server enables LLMs to inspect database schemas, execute queries, modify data, and create/modify database schema objects.
Note: This is an enhanced version of the original PostgreSQL MCP server by Anthropic. The original server provides read-only access, while this enhanced version adds write capabilities and schema management.
Components
Tools
Data Query
- query
- Execute read-only SQL queries against the connected database
- Input:
sql(string): The SQL query to execute - All queries are executed within a READ ONLY transaction
Data Modification
-
execute
- Execute a SQL statement that modifies data (INSERT, UPDATE, DELETE)
- Input:
sql(string): The SQL statement to execute - Executed within a transaction with proper COMMIT/ROLLBACK handling
-
insert
- Insert a new record into a table
- Input:
table(string): The table namedata(object): Key-value pairs where keys are column names and values are the data to insert
-
update
- Update records in a table
- Input:
table(string): The table namedata(object): Key-value pairs for the fields to updatewhere(string): The WHERE condition to identify records to update
-
delete
- Delete records from a table
- Input:
table(string): The table namewhere(string): The WHERE condition to identify records to delete
Schema Management
-
createTable
- Create a new table with specified columns and constraints
- Input:
tableName(string): The table namecolumns(array): Array of column definitions with name, type, and optional constraintsconstraints(array): Optional array of table-level constraints
-
createFunction
- Create a PostgreSQL function/procedure
- Input:
name(string): Function nameparameters(string): Function parametersreturnType(string): Return typelanguage(string): Language (plpgsql, sql, etc.)body(string): Function bodyoptions(string): Optional additional function options
-
createTrigger
- Create a trigger on a table
- Input:
name(string): Trigger nametableName(string): Table to apply trigger tofunctionName(string): Function to callwhen(string): BEFORE, AFTER, or INSTEAD OFevents(array): Array of events (INSERT, UPDATE, DELETE)forEach(string): ROW or STATEMENTcondition(string): Optional WHEN condition
-
createIndex
- Create an index on a table
- Input:
tableName(string): Table nameindexName(string): Index namecolumns(array): Columns to indexunique(boolean): Whether the index is uniquetype(string): Optional index type (BTREE, HASH, GIN, GIST, etc.)where(string): Optional condition
-
alterTable
- Alter a table structure
- Input:
tableName(string): Table nameoperation(string): Operation (ADD COLUMN, DROP COLUMN, etc.)details(string): Operation details
Resources
The server provides schema information for each table in the database:
- Table Schemas (
postgres://<host>/<table>/schema)- JSON schema information for each table
- Includes column names and data types
- Automatically discovered from database metadata
Usage with Claude Desktop
To use this server with the Claude Desktop app, add the following configuration to the "mcpServers" section of your claude_desktop_config.json:
Docker
- when running docker on macos, use host.docker.internal if the server is running on the host network (eg localhost)
- username/password can be added to the postgresql url with
postgresql://user:password@host:port/db-name - add
?sslmode=no-verifyif you need to bypass SSL certificate verification
{
"mcpServers": {
"postgres": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"mcp/postgres",
"postgresql://host.docker.internal:5432/mydb"]
}
}
}
NPX
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": [
"-y",
"@modelcontextprotocol/server-postgres",
"postgresql://localhost/mydb"
]
}
}
}
Replace /mydb with your database name.
Example Usage
Query Data
/query SELECT * FROM users LIMIT 5
Insert Data
/insert table="users", data={"name": "John Doe", "email": "john@example.com"}
Update Data
/update table="users", data={"status": "inactive"}, where="id='123'"
Create a Table
/createTable tableName="tasks", columns=[
{"name": "id", "type": "SERIAL", "constraints": "PRIMARY KEY"},
{"name": "title", "type": "VARCHAR(100)", "constraints": "NOT NULL"},
{"name": "created_at", "type": "TIMESTAMP", "constraints": "DEFAULT CURRENT_TIMESTAMP"}
]
Create a Function and Trigger
/createFunction name="update_timestamp", parameters="", returnType="TRIGGER", language="plpgsql", body="BEGIN NEW.updated_at = NOW(); RETURN NEW; END;"
/createTrigger name="set_timestamp", tableName="tasks", functionName="update_timestamp", when="BEFORE", events=["UPDATE"], forEach="ROW"
Building
Docker:
docker build -t mcp/postgres -f Dockerfile .
Security Considerations
- All data modification operations use transactions with proper COMMIT/ROLLBACK handling
- Each operation returns the SQL that was executed for transparency
- The server uses parameterized queries for insert/update operations to prevent SQL injection
- Security Fix (v1.0.2): The read-only query tool now uses prepared statements to prevent SQL injection attacks that could bypass transaction isolation by executing multiple statements
License
This MCP server is licensed under the MIT License. This means you are free to use, modify, and distribute the software, subject to the terms and conditions of the MIT License. For more details, please see the LICENSE file in the project repository.