Secure Postgres agent with function calling (tutorial)

You'll build an agent that answers natural language questions about data in a Postgres project, without ever letting the model write SQL that executes as is. The model calls a tool named query_database, this tool translates the question into SQL validated via NL2SQL, then executes this read-only SQL and returns the lines to the model so that it formulates its answer.

Most Postgres agent tutorials, including some official guides, define a single tool: a execute_sql function that takes an SQL string as an argument and executes it as is. The model writes this string itself, based on the user's question and the schema given to it in context.

{
  "name": "execute_sql",
  "parameters": {
    "query": { "type": "string" }  // the model writes SQL directly
  }
}

This choice transfers a responsibility to the model that it cannot reliably fulfill. A prompt injection slipped into the question can produce destructive SQL that the tool executes indiscriminately, since it has no concept of what a "legitimate" query should look like. Our dedicated article details this attack vector: securing NL2SQL against SQL injection.

The alternative built in this tutorial exposes a narrower tool, query_database(question). The model can no longer write SQL directly: it can only ask a question in its own tool call. It is the Aurabase NL2SQL engine which translates this question into SQL, before passing it through a syntactic tree validator (SELECT alone, no subqueries, ten functions authorized, limited LIMIT).

Aurabase's three native LLM providers (OpenAI, Anthropic, Gemini) accept a table of tool definitions in JSON Schema format. A single tool is enough for this agent: query_database, which takes a question in natural language and nothing else. The model sees neither the SQL schema nor a query field that it could fill in itself.

export const tools = [
  {
    type: 'function',
    function: {
      name: 'query_database',
      description:
        "Interroge les données du projet en langage naturel. N'accepte pas de SQL : posez une question.",
      parameters: {
        type: 'object',
        properties: {
          question: {
            type: 'string',
            description: 'Question en français sur les données du projet.'
          },
        },
        required: ['question'],
        additionalProperties: false
      },
    },
  },
]

The tool handler runs on your backend, never in the browser. It carries the project key service_role, which bypasses RLS by design and therefore should never be exposed to a client. It makes two calls to the Aurabase SDK.

The first call translates the question into SQL validated via aura.ai.nl2sql(): SELECT only, LIMIT limited, no access to the system catalog. The second executes this SQL already validated via aura.db.sql(), with the readOnly: true option: Postgres itself then refuses any writing in this transaction, independently of the textual validation already applied by NL2SQL upstream.

// Client initialized with the service_role key, never on the browser side
import { aura } from '@/lib/aurabase'

export async function queryDatabase(question: string) {
  const { data: validated, error } = await aura.ai.nl2sql(
    question,
    undefined,
    { limit: 50 },
  )
  if (error) return { error: error.message }

  const { data: rows, error: execError } = await aura.db.sql(
    validated.sql,
    [],
    { readOnly: true },
  )
  if (execError) return { error: execError.message }

  return { sql: validated.sql, rows }
}

Aurabase exposes three native LLM providers, but its /chat endpoint does not yet relay an tools parameter or toolrole. ChatOptions only carries temperature, max_tokens and model, and accepted roles are limited to system, user and assistant (verified in llm/mod.rs and handlers/chat.rs). The function calling loop therefore runs today directly via the provider's SDK, not via the Aurabase proxy.

import OpenAI from 'openai'
import { tools } from './lib/agent-tools'
import { queryDatabase } from './tools/query-database'

const openai = new OpenAI()

export async function askAgent(question: string) {
  const messages = [{ role: 'user', content: question }]

  const first = await openai.chat.completions.create({
    model: 'gpt-4.1', messages, tools,
  })

  const call = first.choices[0].message.tool_calls?.[0]
  if (!call) return first.choices[0].message.content

  const args = JSON.parse(call.function.arguments)
  const result = await queryDatabase(args.question)

  const second = await openai.chat.completions.create({
    model: 'gpt-4.1',
    messages: [
      ...messages,
      first.choices[0].message,
      { role: 'tool', tool_call_id: call.id, content: JSON.stringify(result) },
    ],
  })

  return second.choices[0].message.content
}

The principle remains the same if you orchestrate the agent with LangChain or a service like Azure AI Agent: the tool declared in the framework must remain the same query_database, never a raw SQL executor. Our comparison details where LangChain and LlamaIndex provide real value on Postgres, and where they especially add complexity: Postgres agents with LangChain or LlamaIndex.

Question sent to agent: “How many premium customers placed an order this month?” ". The template calls query_database with this question as is, without ever seeing or writing any SQL. Here is the result of the two internal calls triggered by the tool.

{
  "sql": "SELECT count(*) FROM orders WHERE customer_plan = 'premium' AND created_at >= date_trunc('month', now()) LIMIT 50",
  "rows": [{ "count": 128 }]
}

The model's final answer is based on these actual lines, not a guess. If the tool returns zero rows, a number hallucination becomes significantly less likely than with a model that would respond without verified data.

• The service_role key never leaves your backend: neither in the prompt sent to the model, nor in a log, nor in a client-side environment variable.
• readOnly: true remains active on aura.db.sql() for this specific tool, even if your project needs writing elsewhere in the application.
• service_role bypasses RLS by design. If the agent should respond differently depending on the user asking the question, explicitly filter in the SQL or fall back to classic PostgREST endpoints, which respect RLS. See multi-tenant RLS isolation.
• Log each tool call (question asked, SQL validated, number of lines): this is the only usable trace if a question produces an unexpected result.
• Aurabase's rate limiting and monthly quota already apply per project on /nl2sql: a talkative agent cannot silently exceed your AI budget.

The query_database tool inherits all the limitations of the NL2SQL validator: no subqueries, no CTE/WITH, no UNION, and a closed list of ten SQL functions. A question which naturally calls for a sub-query (“customers who have never ordered”) must be reformulated, or processed by a second dedicated tool rather than forced into NL2SQL.

No orchestration of tool calls exists today inside the Aurabase /chat proxy: the agent loop described here lives in your application code, not in a managed service. If the agent must chain several tools (database and documentary RAG, for example), it is your backend which orchestrates the two calls.

This tutorial covers structured questions on relational data. For questions about unstructured content (documents, tickets, notes), the same agent can expose a second tool connected to Aurabase's native RAG (pgvector, HNSW search). The two capabilities and their articulation are detailed on the page Native AI on Postgres.