---
title: "Secure PostgreSQL AI Agent: LangChain + Ollama with Read-Only Access and SQL Guardrails"
url: "https://binary.ph/2026/05/12/secure-postgresql-ai-agent-langchain-ollama-with-read-only-access-and-sql-guardrails/"
description: "Discover a secure PostgreSQL AI Agent setup using LangChain and Ollama with read-only access and SQL guardrails to prevent leaks and mistakes."
author: "BinaryPH"
published: "2026-05-12T04:48:44+00:00"
modified: "2026-05-12T04:48:44+00:00"
tags: ["Main"]
---

# 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.