Safe data access

Enforce read and write boundaries with database permissions and transaction settings.

A prompt that says “only run SELECT” is a suggestion. A connection that cannot write is a guarantee. A production data agent answers from a read-only connection and isolates any writes to a schema it owns. The boundary holds even when the model goes off-script.

uv pip install "agno[openai,psycopg,sql]"

from agno.agent import Agent
from agno.models.openai import OpenAIResponses
from agno.tools.sql import SQLTools
from sqlalchemy import create_engine

readonly_engine = create_engine(
    "postgresql+psycopg://readonly@warehouse/analytics",
    connect_args={"options": "-c default_transaction_read_only=on"},
)

analyst = Agent(
    name="Analyst",
    model=OpenAIResponses(id="gpt-5.5"),
    tools=[SQLTools(db_engine=readonly_engine)],
    instructions="Answer questions from the public schema. You cannot write.",
)

Create the readonly role without write grants before using this connection. The default_transaction_read_only=on setting blocks ordinary write statements, but it is a configurable session default. Database ownership and grants remain the security boundary.

Split the roles

Most data-agent questions are read-only. Writes (building a summary table, recording a correction) are rarer and riskier. Separate them into different agents on different connections.

Member	Connection	Can do	Cannot do
Analyst	Read-only role on public	Introspect, SELECT, answer	Any write, anywhere
Engineer	Read on public, read-write on an agent-owned schema	Build views in its own schema	Touch public
Leader	No direct database access	Route the request, compose the answer	Run SQL itself

The Engineer’s writes are scoped to a schema (for example dash) that the Analyst never touches. The database rejects DROP TABLE public.users because the Engineer’s role has no write grant on public.

Gate the writes that remain

For writes you do allow, add a human in the loop. requires_confirmation produces a paused run that the client must continue with an approval before the function executes.

from agno.tools import tool


@tool(requires_confirmation=True)
def materialize_view(name: str, sql: str) -> str:
    """Create a view in the agent-owned schema after human approval."""
    ...

For a dedicated writer built on SQLTools, set requires_confirmation_tools=["run_sql_query"]. That pauses every call to the tool, reads included, so a narrow custom write tool gives finer control. Gate the irreversible actions and leave reads ungated, so approval fatigue does not set in.

Layers of defense

Layer	Enforced by
Read-only answers	Database role with no write grant
Write isolation	Schema-scoped grant on a separate connection
Irreversible actions	Human approval via requires_confirmation
Auditability	The Decision Log records what changed and why

Next steps

Task	Guide
Let the Engineer build reusable views	Materialization
Approve sensitive actions	Human approval

Developer Resources

• Human approval
• Dash: dual-schema enforcement