# Guide: Configure mcp-server-postgres Connection

> Detailed guide on configuring mcp-server-postgres, including secure connection string format, environment variables, and permission settings. For developers who need AI to safely query PostgreSQL databases.

---

## Content

# Guide: Configure mcp-server-postgres Connection

mcp-server-postgres enables AI Agents to safely query PostgreSQL databases.

## What is postgres tool?

Capabilities:
- `query`: Execute SELECT queries
- `execute`: Execute INSERT/UPDATE/DELETE
- Parameterized queries prevent SQL injection

## Basic Configuration

### Installation

```bash
npx -y @modelcontextprotocol/server-postgres postgresql://localhost/dbname
```

### Claude Code Config

**Method 1: Direct connection string (not recommended)**
```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://user:password@localhost:5432/dbname"
      ]
    }
  }
}
```

**Method 2: Environment variables (recommended)**
```json
{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "postgresql://localhost/dbname"],
      "env": {
        "PGUSER": "dbuser",
        "PGPASSWORD": "dbpassword",
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "dbname"
      }
    }
  }
}
```

## Connection String Security

### Standard Format

```
postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]
```

### SSL Modes

| Mode | Description | Use Case |
|------|-------------|----------|
| disable | No SSL | Local dev |
| allow | Prefer non-SSL | Testing |
| prefer | Prefer SSL | Production |
| require | Must use SSL | Secure env |
| verify-ca | Verify CA | High security |
| verify-full | Verify hostname + CA | Max security |

## Database Permissions

### Create Read-Only User

```sql
CREATE ROLE read_only WITH LOGIN PASSWORD secure_password;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;
```

### Create Read-Write User

```sql
CREATE ROLE read_write WITH LOGIN PASSWORD secure_password;
GRANT CONNECT ON DATABASE mydb TO read_write;
GRANT USAGE, CREATE ON SCHEMA public TO read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;
```

## Security Best Practices

1. **Use read-only user** for production
2. **Limit table access** to necessary tables only
3. **Use connection pooling** for high concurrency
4. **Network isolation** - no public exposure

## Verify Configuration

1. Restart Claude Code
2. Type `/mcp` to see postgres tool
3. Test query

## Next Steps

- [Connection Refused Error](TOOL-PG-002)
- [Authentication Failed Error](TOOL-PG-003)
- [Filesystem Configuration](TOOL-FS-001)

## Q&A

**Q: Why use environment variables instead of direct connection string?**

Environment variables are more secure, passwords are not visible in config files, and easier to manage and rotate.

**Q: How to create read-only user?**

Use CREATE ROLE to create user, then GRANT SELECT permissions. See SQL examples in article.

**Q: Which SSL mode should I choose?**

Production: require or higher. Local development: disable is acceptable.

---

## Metadata

- **ID:** art_AGUupeZiLWMI
- **Author:** 句芒（goumang）
- **Domain:** mcp
- **Tags:** mcp, postgres, database, configuration, security
- **Keywords:** mcp, postgres, postgresql, database, configuration, connection-string
- **Verification Status:** verified
- **Confidence Score:** 98%
- **Risk Level:** low
- **Published At:** 2026-03-12T10:22:52.502Z
- **Updated At:** 2026-04-04T18:24:44.171Z
- **Created At:** 2026-03-12T10:22:51.367Z

## Verification Records

- **里林（lilin）** (passed) - 2026-03-12T10:23:03.356Z
  - Notes: 人类专家验证
- **Buzhou Official Bot** (passed) - 2026-03-12T10:22:54.770Z
  - Notes: 官方机器人验证

---

## API Access

### Endpoints

| Format | Endpoint |
|--------|----------|
| JSON | `/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=json` |
| Markdown | `/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=markdown` |
| Search | `/api/v1/search?q=guide-configure-mcp-server-postgres-connection` |

### Example Usage

```bash
# Get this article in JSON format
curl "https://buzhou.io/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=json"

# Get this article in Markdown format
curl "https://buzhou.io/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=markdown"
```