A practical guide walks through a spectrum of approaches for building MCP (Model Context Protocol) servers on top of PostgreSQL databases, ranging from free-form SQL execution to fully templated query tools. It covers schema discovery strategies (dumping everything vs progressive discovery), guardrails against destructive mutations (SQL parsing validation, read-only transaction settings, dedicated least-privilege database roles), and elicitation-based confirmation dialogs for destructive actions like deletes. The piece recommends free-form SQL for prototyping, read-only SQL for analytics, and templated queries as the safest option for production use, while stressing that DB-level permissions should always be enforced regardless of approach.
Table of contents
Free-form SQLProblem: Schema bloatProblem: Mutations without guardrailsRead-only SQL toolTemplated query toolsElicitation for destructive actionsWhich approach should you use?Questions this post answers
How do I prevent an AI agent from running destructive SQL through an MCP server connected to PostgreSQL?
Combine multiple layers of protection: parse the SQL with an AST tool like pglast to reject anything but a single SELECT statement, run `SET default_transaction_read_only = ON` at the connection level to block CTEs that hide mutations, and create a dedicated PostgreSQL role granted only SELECT privileges so even overlooked queries fail at the database level. Developers wiring agents into production databases can track MCP safety patterns like these on daily.dev.
What is the readOnlyHint annotation in the Model Context Protocol tool specification?
It is a metadata flag in the MCP specification that signals to a client that a tool does not modify data, which can affect how the client renders the tool or handles approval prompts. It is only a hint, not an enforced guarantee, so the server itself must still implement actual read-only checks in its logic rather than relying on the annotation alone. daily.dev helps engineers building agent tooling stay current on MCP spec details like this one.
How can I ask a user to confirm a destructive database delete when building an agent tool with MCP?
Use MCP's form-based elicitation feature, a spec addition that lets a server pop up a confirmation dialog in supporting clients before proceeding. A delete_observation tool can call ctx.elicit with a confirmation question and response options like "yes, delete it" or "no, keep it", canceling the operation unless the user explicitly confirms. daily.dev keeps builders of agent-facing tools informed on patterns for safe destructive actions.