--- title: Building safe MCP servers for your PostgreSQL database description: A practical guide walks through a spectrum of approaches for building MCP (Model Context Protocol) servers on top of PostgreSQL databases, ranging from... canonical: https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5 twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Building safe MCP servers for your PostgreSQL database | daily.dev og:description: A practical guide walks through a spectrum of approaches for building MCP (Model Context Protocol) servers on top of PostgreSQL databases, ranging from... og:url: https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5 og:image: https://api.daily.dev/og/posts/CToYiTZR5.png og:image:alt: Building safe MCP servers for your PostgreSQL database og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Building safe MCP servers for your PostgreSQL database **[Pamela Fox](https://daily.dev/sources/pamelafox)** · 12 min read · 2 upvotes · 0 comments ## Summary 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. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I prevent an MCP server tool from executing destructive SQL like DELETE or UPDATE statements? Use multiple layers: parse the SQL with an AST library like pglast to confirm it is a single SELECT statement, set default_transaction_read_only=ON on the database connection to block CTE-hidden mutations like WITH d AS (DELETE...), and create a dedicated least-privilege PostgreSQL role granted only SELECT permissions. Combining all four layers protects against gaps in any single approach. _Developers hardening database-facing AI tools compare guardrail patterns like these on daily.dev._ ### What is the readOnlyHint annotation in the Model Context Protocol and can I rely on it for security? readOnlyHint is a Model Context Protocol tool annotation that signals to an MCP client that a tool does not modify data, potentially affecting how the client renders approvals. It is only a hint, not an enforced contract, since a server could misreport it intentionally or by mistake, so actual read-only behavior must still be enforced in the tool's own logic and at the database level. _Teams building MCP integrations track spec nuances like this on daily.dev before shipping agent tools._ ### How can I make an AI agent confirm before deleting a database row through an MCP tool? Use MCP's form-based elicitation feature to pop up a confirmation dialog before executing a destructive action. A delete_observation tool can call ctx.elicit with a question and response options like 'yes, delete it' or 'no, keep it', then only run the DELETE statement if the user explicitly confirms, canceling otherwise. _Anyone wiring confirmation flows into agent tools follows patterns like this on daily.dev._ ## Similar posts on daily.dev - [How to Build MCP Servers for Your Internal Data](https://daily.dev/posts/how-to-build-mcp-servers-for-your-internal-data-mal9z290q) · freeCodeCamp · 3 upvotes · 0 comments - [MCP For PostgreSQL: Automated Health Checks & Performance Analysis](https://daily.dev/posts/mcp-for-postgresql-automated-health-checks-performance-analysis-5fmjhpx51) · Planet PostgreSQL · 0 upvotes · 0 comments - [So you want an MCP Database?](https://daily.dev/posts/so-you-want-an-mcp-database--wfse2uidq) · DoltHub Blog · 0 upvotes · 0 comments - [Your database is about to become an AI tool. Is it ready?](https://daily.dev/posts/your-database-is-about-to-become-an-ai-tool-is-it-ready--jdwwvjydr) · The New Stack · 0 upvotes · 0 comments --- Tags: [#python](https://daily.dev/tags/python), [#ai-agents](https://daily.dev/tags/ai-agents), [#sql](https://daily.dev/tags/sql), [#postgresql](https://daily.dev/tags/postgresql), [#mcp](https://daily.dev/tags/mcp) [View this post on daily.dev](https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Building safe MCP servers for your PostgreSQL database","url":"https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5"},"datePublished":"2026-08-12T18:12:36.414Z","dateModified":"2026-09-14T07:06:27.131Z","description":"A practical guide walks through a spectrum of approaches for building MCP (Model Context Protocol) servers on top of PostgreSQL databases, ranging from...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/a57845014489eec296de2dd9ae540a6f?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/a57845014489eec296de2dd9ae540a6f?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Pamela Fox","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Pamela Fox","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/9c7963f6b19440b1b6bc15aff07efda7","url":"https://daily.dev/sources/pamelafox"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":2},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"python,ai-agents,sql,postgresql,mcp","timeRequired":"PT12M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Pamela Fox","item":"https://daily.dev/sources/pamelafox"},{"@type":"ListItem","position":3,"name":"Building safe MCP servers for your PostgreSQL database"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/building-safe-mcp-servers-for-your-postgresql-database-ctoyitzr5#faq","mainEntity":[{"@type":"Question","name":"How do I prevent an MCP server tool from executing destructive SQL like DELETE or UPDATE statements?","acceptedAnswer":{"@type":"Answer","text":"Use multiple layers: parse the SQL with an AST library like pglast to confirm it is a single SELECT statement, set default_transaction_read_only=ON on the database connection to block CTE-hidden mutations like WITH d AS (DELETE...), and create a dedicated least-privilege PostgreSQL role granted only SELECT permissions. Combining all four layers protects against gaps in any single approach. Developers hardening database-facing AI tools compare guardrail patterns like these on daily.dev."}},{"@type":"Question","name":"What is the readOnlyHint annotation in the Model Context Protocol and can I rely on it for security?","acceptedAnswer":{"@type":"Answer","text":"readOnlyHint is a Model Context Protocol tool annotation that signals to an MCP client that a tool does not modify data, potentially affecting how the client renders approvals. It is only a hint, not an enforced contract, since a server could misreport it intentionally or by mistake, so actual read-only behavior must still be enforced in the tool's own logic and at the database level. Teams building MCP integrations track spec nuances like this on daily.dev before shipping agent tools."}},{"@type":"Question","name":"How can I make an AI agent confirm before deleting a database row through an MCP tool?","acceptedAnswer":{"@type":"Answer","text":"Use MCP's form-based elicitation feature to pop up a confirmation dialog before executing a destructive action. A delete_observation tool can call ctx.elicit with a question and response options like 'yes, delete it' or 'no, keep it', then only run the DELETE statement if the user explicitly confirms, canceling otherwise. Anyone wiring confirmation flows into agent tools follows patterns like this on daily.dev."}}]} ```