How to Configure Pydantic AI Bigquery Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up pydantic ai bigquery least privilege by making a custom service role. Then you limit that role to read only specific tables and run jobs. Many teams start by giving their AI agent a wide editor or owner role. That choice lets the agent read every table or delete data by mistake. A tight role stops the agent from touching outside datasets in your cloud project. How pydantic ai bigquery least privilege works Pydantic AI agents use tool functions to talk to your cloud data warehouse.

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up pydantic ai bigquery least privilege by making a custom service role. Then you limit that role to read only specific tables and run jobs. Many teams start by giving their AI agent a wide editor or owner role. That choice lets the agent read every table or delete data by mistake. A tight role stops the agent from touching outside datasets in your cloud project.

How pydantic ai bigquery least privilege works

Pydantic AI agents use tool functions to talk to your cloud data warehouse. You write a Python function that sends a query to the database client. The model picks the query text and passes it to this tool function. The database client then uses a cloud service account to run the job.

To keep this safe, you split permissions into two small cloud parts. First, the service account needs permission to create and run a query job. You grant this job role at the project level so queries can start. Second, the account needs permission to read rows from your target data tables. You grant this viewer role on one dataset or on a single table.

Never give the data viewer role at the top project level. Project-level read roles expose every dataset in that project to the AI agent. Instead, attach the read role right on the dataset the agent needs. You can also build authorized views to show only safe columns and rows. Then you let the agent read the view instead of the base table.

How to set up your agent and cloud roles

Start in your cloud console by creating a new service account for the agent. Do not reuse a service account from another app or backend worker. Give this new account the basic job user role on your main project. Next, open the target dataset and grant data viewer access to that account.

In your Python app, pass the service account into the database client object. Put that client inside your Pydantic AI dependency object for the agent run. Inside the tool function, use the dependency client to send the SQL text. You should also set a maximum bytes billed limit on the query job. This cap stops a bad model prompt from scanning terabytes of raw data.

Least Privilege Principle + BigQuery IAM: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native roles solve and what they miss

Native cloud roles decide which tables and views the agent account can open. They stop the agent from dropping tables or reading private datasets elsewhere. They also help you track agent costs by grouping jobs under one account.

Still, cloud roles only check who logs in and which table they touch. They do not inspect what individual SQL statements do while in flight. If an authorized table holds user emails or card numbers, the query returns them. Data masking rewrites matched fields in results, such as phone numbers or national IDs. Without runtime checks, a valid read role still returns every allowed column in full.

Static service keys also create risk when stored on developer laptops or servers. If a key leaks, anyone can run queries with the agent's read role. Teams also struggle to link a cloud audit log back to one user prompt. The database only sees the shared service account name on every single query.

Govern statements and responses across databases and APIs

Good config and scoped roles are needed setup. They do not check single queries in flight or mask sensitive fields in returned rows. Across Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, and internal HTTP endpoints, hoop.dev runs beside each supported service as an open-source proxy, a sidecar for your database or API.

First, hoop.dev guardrails block banned queries before they run. Then hoop.dev data masking rewrites matched sensitive values in responses in memory. The audit trail records every statement and verdict per resource. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Should I give my Pydantic AI agent a project viewer role?

No, a project viewer role lets the agent read every dataset you own. Grant the job role on the project and the viewer role on one dataset.

How do I stop an AI agent from running costly BigQuery scans?

Set a maximum bytes billed limit on the query job in your tool code. The database will cancel any query that tries to scan above that limit.

Can BigQuery roles hide sensitive columns from my AI agent?

Standard dataset roles apply to whole tables, not single columns or specific text values. You must set up column policy tags, authorized views, or runtime data masking.

The query runs. The emails stay hidden.