Secure PostgreSQL AI Agent: LangChain + Ollama with Read-Only Access and SQL Guardrails

Why a “database copilot” needs security

Turning natural language into database queries is useful, but it introduces a new risk class: an AI model can generate unsafe SQL, leak sensitive schema information, or be manipulated through prompt injection. A secure PostgreSQL AI agent mitigates those threats by combining local LLM inference, least-privilege database permissions, and strict SQL validation before any query is executed.

In practice, a user asks for a business result such as “show the top 10 customers by revenue.” The system uses LangChain to orchestrate tool calling, Ollama to run a local model, and PostgreSQL as the data source. A custom safety layer then ensures only approved operations and approved table scopes are reachable.

What a PostgreSQL AI agent does

A PostgreSQL AI agent typically follows this flow:

• Interpret the user question as an information need.
• Generate SQL using an LLM.
• Validate the SQL against a security policy (syntax rules, deny-list patterns, and allow-list table selection).
• Execute only if validation passes.
• Return results in a readable format while avoiding exposure of internal query logic.

This is similar to “ChatGPT connected to a database,” but with guardrails and controlled execution.

Core architecture for a local, secure setup

The recommended architecture keeps the LLM local and confines database access:

• User query enters the system.
• LangChain agent decides when to call the SQL tool.
• Ollama model converts text to SQL (no cloud API calls).
• SQL guardrails validate the query.
• PostgreSQL executes the query using a restricted account.

Security goal: make “unsafe SQL” either impossible to run or impossible to reach in the first place.

Tech stack components

• LangChain for agent orchestration and tool execution.
• Ollama for running local LLMs.
• PostgreSQL as the relational data store (optionally with vector extensions for hybrid search scenarios).
• Python as the application runtime.
• psycopg/psycopg2 as the PostgreSQL adapter (depending on the chosen client library).

SQL injection protection is not optional

Even with an LLM, the system must treat every generated query as untrusted input. A secure design includes multiple layers:

• Read-only database user: allow only SELECT and deny data modification privileges.
• Table allow-list: restrict accessible tables to an explicit list, such as reporting or analytics views.
• Schema isolation: store agent-related objects in a separate schema from production tables.
• Query validation: run a checker that blocks destructive patterns (for example: DROP, DELETE, UPDATE, INSERT), and enforces safe syntax constraints.
• Parameterization: avoid string concatenation for variable inputs. Use parameterized queries wherever user parameters are involved.

These controls reduce the likelihood that malicious instructions (or model mistakes) can cause data loss or unauthorized reads.

Least-privilege PostgreSQL configuration

Creating a dedicated PostgreSQL role is one of the most impactful steps.

• Role permissions: grant CONNECT, USAGE, and SELECT only on approved schemas and tables.
• No write permissions: ensure the role cannot INSERT, UPDATE, DELETE, or DROP.
• Use views for controlled access: expose sanitized reporting views instead of base tables when possible.

This ensures that even if unsafe SQL is generated, the database account cannot execute it.

Local Ollama model workflow

Running the LLM locally lowers the risk of sending sensitive prompts and results to external services. A typical approach is to pull and serve a coding-capable model suitable for SQL translation.

Example commands:

• ollama serve
• ollama pull llama3.1 (or another model that performs well for SQL generation)

For multi-model strategies, select one model for SQL generation and a different one for optional summarization, while keeping all inference local.

Building the LangChain SQL tool with guardrails

In modern LangChain patterns, the SQL integration can be configured to reduce risky disclosures and to apply validation. The following implementation principles align with 2025 security best practices:

• Use an allow-list of tables when initializing the SQL database wrapper.
• Limit schema exposure by limiting sample rows and restricting which tables are visible to the agent.
• Do not return intermediate steps to the end user to avoid leaking raw SQL attempts.
• Enable a query checker so SQL is validated before execution.

Conceptual pseudocode (configuration-level view):

from langchain_community.llms import Ollama
from langchain_postgres import PGEngine
from langchain_community.utilities import SQLDatabase
from langchain_experimental.sql import SQLDatabaseChain

# 1) Connect using a read-only PostgreSQL URI
# 2) Create SQLDatabase with include_tables=[...] allow-list
# 3) Instantiate Ollama with a local model name
# 4) Use SQLDatabaseChain with query checking enabled
# 5) Ensure intermediate SQL steps are not returned

Handling prompt injection and unsafe requests

Prompt injection can appear when user input tries to override system instructions, smuggle hidden directives, or coerce the model into exposing sensitive data. A robust strategy includes:

• Strict prompt policy: instruct the model to generate only read-only, schema-compliant queries.
• Input validation: block suspicious constructs and enforce that only allowed operations are requested.
• Denial patterns: reject SQL that matches forbidden keywords or attempts to escape constraints.
• Operational logging: record validation failures and successful query executions for auditability.

Security issues can stem from both the LLM and the execution layer. Applying controls at both levels provides defense-in-depth.

Production deployment checklist

• Create a read-only PostgreSQL user.
• Restrict access using schema isolation and table allow-lists.
• Enable query validation before executing any AI-generated SQL.
• Ensure parameterized queries for any dynamic inputs.
• Prevent data leakage by disabling intermediate SQL step output to users.
• Keep dependencies updated and monitor known vulnerabilities in agent and SQL chain components.
• Run Ollama in a controlled environment and avoid exposing the inference service publicly.

Alternative: cloud-compatible model endpoints

Some teams prefer cloud inference using OpenAI-compatible endpoints from providers such as Pollinations. Even then, the security model remains the same: read-only database permissions and SQL validation must still be enforced locally or in a trusted gateway. Local inference simply reduces data exposure in transit and simplifies compliance for some deployments.

Conclusion

A secure PostgreSQL AI agent requires more than connecting an LLM to a database. The most reliable approach combines local LLM inference with layered database security, strict SQL query validation, and careful LangChain configuration. With a read-only role, an allow-listed schema and table scope, and a query checker that blocks destructive statements, the system can support natural-language analytics while significantly reducing the risk of harmful outcomes.