How to Configure Pydantic AI MariaDB Least Privilege
You set up pydantic ai mariadb least privilege by pairing strict tool schemas with scoped database roles. This setup limits your AI agent to specific tables and safe query actions. Many teams start by giving their agent a broad admin database user. That approach lets a bad prompt drop tables or pull sensitive user rows. Scoped roles and strict tool rules stop most of these mistakes early. How pydantic ai mariadb least privilege works in practice Pydantic AI lets you build agents that call str
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 mariadb least privilege by pairing strict tool schemas with scoped database roles. This setup limits your AI agent to specific tables and safe query actions. Many teams start by giving their agent a broad admin database user. That approach lets a bad prompt drop tables or pull sensitive user rows. Scoped roles and strict tool rules stop most of these mistakes early.
How pydantic ai mariadb least privilege works in practice
Pydantic AI lets you build agents that call structured Python functions as tools. Each tool uses a strict data schema to check inputs before they run. When the agent needs data, it fills out the schema fields first. The app checks those fields against your rules and rejects bad inputs right away.
Next, the tool opens a database session to MariaDB using a dedicated role. You create this MariaDB user with grants for only the needed tables and columns. For example, a support agent role might only read from orders and items tables. It has no rights to change rows, drop tables, or read billing records.
You can also use MariaDB views to hide secret columns from the agent role. The agent queries the view instead of the raw base table. This setup keeps password hashes and internal notes out of the query results. Together, the input schema and the MariaDB role form a solid first defense.
Why static database roles leave runtime gaps
Static grants in MariaDB decide who can log in and which tables they touch. Still, they do not inspect what each query does while it runs. An agent with read rights on a customer table can still dump every row. Static grants also cannot tell if a human approved a sensitive write action.
Also, allowed tables often hold personal data like emails, phone numbers, or card numbers. If the agent has read rights on those columns, MariaDB returns the raw text. The language model then ingests those real emails or card numbers in its prompt. Data masking is needed to rewrite matched fields in results before the agent reads them.
App teams also struggle to manage database keys across many agent workers. Storing static passwords in app files creates rotation work and leak risks. Teams need a way to check queries in flight and mask sensitive fields. They also need full logs that tie every query back to the caller.
Least Privilege Principle + AI Agent Security: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
How to structure safe agent tools for MariaDB
Start by breaking one broad database tool into several small, task-specific tools. Do not give the model a tool that runs raw SQL strings from prompts. Instead, build one tool to look up an order and another to update status. Each tool should accept typed parameters like an integer order ID or status code.
Inside each tool function, use parameterized queries to send data to MariaDB. Parameterized queries keep user input separate from the SQL command structure. This step stops prompt tricks from turning a simple lookup into a broad query. Always set a row limit and a short timeout on every database call.
Finally, split your MariaDB users by the type of work the tool performs. Use a read-only database user for all lookup and search tools. Use a separate write user only for tools that must update specific columns. Keep destructive permissions like table drops out of every agent role.
Check MySQL queries and results on the wire
MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL. It runs next to MySQL, RDS, or Aurora and reads the MySQL wire protocol.
Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails. When a rule refuses a query, MySQL returns a native error packet with your message. On the response path, hoop.dev data masking rewrites string values in returned rows that match your rules. Every query and decision is logged per resource. See the MIT-licensed project on GitHub.
Frequently asked questions
Should a Pydantic AI agent ever generate raw SQL for MariaDB?
Avoid raw SQL generation whenever you can use fixed, typed tool functions instead. If the agent must write SQL, restrict its MariaDB role to read-only views. You should also inspect every statement before it reaches the database engine.
How do I keep personal data out of agent prompts?
First, revoke column rights or use MariaDB views to exclude fields you do not need. For columns the agent must query, apply runtime data masking to rewrite matched fields. Masking replaces emails, phone numbers, and card numbers in results before the model processes them.
How often should we rotate MariaDB credentials used by AI agents?
Static database passwords stored in app settings should be rotated on a tight schedule. A better approach is to remove static database passwords from the agent host entirely. That way, workers never hold long-lived keys that could leak in logs or prompts.
Related guides
In this post