Discussion: Need for Granular Schema & Column-Level Allowlisting

[ahammednibras8]

🔍 Observation

In the current implementation of postgres-mcp:

• AccessMode.RESTRICTED enables SafeSqlDriver, which blocks unsafe queries but still allows SELECT on any table the DB user can access.
• There is no interception of SELECT * to enforce column-level filtering.
• There is no config mechanism (e.g., config.yaml) to define which columns are safe for exposure.

This means: once the MCP has SELECT access to a table, it gets all columns, regardless of sensitivity.

---

⚠️ Risk: Breaks Zero-Trust / Need-to-Know Isolation

Real-world tables often mix public and sensitive fields:

• users: id, loyalty_tier (safe) + email, phone, address (PII)
• products: price (safe) + cost_price, supplier_margin (internal financials)
• orders: status (safe) + shipping_address (PII leak)

Under the current setup:

SELECT * FROM users;

…returns everything, because the DB user has SELECT privileges — even if only certain fields should be visible to the agent.

This violates Zero-Trust data exposure principles and makes the system unsafe for production deployments.

---

✅ Proposed Enhancement: Column-Level Allowlisting

Introduce a schema configuration layer such as:

allowlist:
  users:
    safe_columns:
      id: "Anonymized user identifier"
      loyalty_tier: "Marketing segment only"
  products:
    safe_columns:
      id:
      name:
      price:
      category:
  orders:
    safe_columns:
      id:
      customer_id:
      total_amount:
      status:
      created_at:

Suggested Behavior

1. Intercept all SELECT queries.
2. Parse requested columns:
   • If SELECT *, rewrite to include only the allowed columns.
   • If specific columns are requested, reject the query if any appear outside the allowlist.
3. Return filtered row objects to the client.
4. Produce audit entries if blocked fields were attempted.

---

💡 Why This Matters

• Enforces Least Privilege in a practical, production-safe way.
• Prevents accidental leakage of:
  • PII
  • Internal financial metrics
  • Admin-only operational fields
• Makes MCP usable in:
  • SaaS analytics
  • FinTech
  • E-Commerce
  • Enterprise internal tools
• Enables companies to confidently integrate LLMs with sensitive databases.

---

This feature would significantly level-up postgres-mcp for real-world enterprise use.