How to use the PostgreSQL MCP server

Before connecting your AI to a database, create a dedicated read-only user. This ensures the AI cannot modify data even if the MCP server has a bug or the read-only enforcement fails. Connect to your database as an admin and run the following SQL commands to create a user with SELECT-only permissions on all existing and future tables.

1-- Connect to your database as admin and run:
2CREATE ROLE mcp_readonly WITH LOGIN PASSWORD 'your_secure_password';
3GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
4GRANT USAGE ON SCHEMA public TO mcp_readonly;
5GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
6ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;

Open your MCP host's configuration file and add a postgres entry. The connection string is the last argument in the args array. Format it as postgresql://user:password@host:port/database. For Claude Desktop and Cursor, use the mcpServers key. For VS Code, use the servers key.

1{
2  "mcpServers": {
3    "postgres": {
4      "command": "npx",
5      "args": [
6        "-y",
7        "@modelcontextprotocol/server-postgres",
8        "postgresql://mcp_readonly:your_secure_password@localhost:5432/mydb"
9      ]
10    }
11  }
12}

Fully quit and reopen Claude Desktop, Cursor, or VS Code. The server will attempt to connect to your database on startup. If the connection fails (wrong credentials, database not running, network issues), the server will show as disconnected. Check your host's MCP server status to confirm a successful connection.

Start by asking the AI to describe your database. It will use the server's schema inspection tools to list tables, columns, data types, and relationships. This is a safe way to confirm the connection works and understand what data is available before running queries.

1Example prompts:
2
3"What tables are in my database and what columns does each have?"
4
5"Describe the schema of the users table including data types and constraints"
6
7"Show me the foreign key relationships between tables"

Ask questions about your data in plain English. The AI translates your questions into SQL, executes them through the MCP server, and presents the results in a readable format. You can ask for aggregations, filtering, joins, and complex analysis without writing SQL yourself.

1Example prompts:
2
3"How many users signed up in the last 30 days, broken down by week?"
4
5"What are the top 10 products by total revenue this quarter?"
6
7"Find all orders that were placed but never fulfilled, and show the customer email for each"

For remote databases (AWS RDS, Supabase, Neon, Railway), update the connection string with the remote host, port, and credentials. For SSL-required connections, append ?sslmode=require to the connection string. Make sure your database's firewall rules allow connections from your local machine.

1{
2  "mcpServers": {
3    "postgres": {
4      "command": "npx",
5      "args": [
6        "-y",
7        "@modelcontextprotocol/server-postgres",
8        "postgresql://mcp_readonly:pass@db.example.com:5432/mydb?sslmode=require"
9      ]
10    }
11  }
12}