how to give an agent read-only warehouse access safely

TL;DR

Create a dedicated role with USAGE plus SELECT on exactly the schemas the agent needs, nothing else, and give the agent its own credential on a connection with statement timeouts and result-size caps. Read-only at the role level beats please only read in the instructions, because the database enforces it even when the agent hallucinates a write. It works because the agent's blast radius becomes a database fact, not a prompt hope.

how to give an agent read-only warehouse access safely

Use this when

• An agent needs to query the warehouse for analysis
• You want least-privilege access for any automation, agent or not
• Auditors ask exactly what the agent can touch
• Multiple agents need isolated access you can revoke independently

Not for

• Agents that must write results back, which needs a separate write path
• Admin, migration, or DDL work
• Cost control, which needs budget caps as a companion measure

Steps

1. Create a dedicated role and a dedicated user for the agent:

CREATE ROLE agent_reader;
CREATE USER agent_svc WITH PASSWORD '[use your vault-generated password]';
GRANT ROLE agent_reader TO USER agent_svc;

Expected output: a role and a user that exist for one purpose. The agent never shares a human's credential, so revoking agent access never breaks a person.

1. Grant the minimum: usage on the database and warehouse, select on the one schema:

GRANT USAGE ON DATABASE analytics TO ROLE agent_reader;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;

Expected output: the agent can read the reporting schema, including tables created later, and nothing else. No INSERT, UPDATE, DELETE, or CREATE anywhere.

1. Strip the defaults that quietly grant too much:

-- Postgres example: the public schema is readable by default
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;

Expected output: no accidental access through default privileges. Verify with a probe query against a schema the agent should not see; it must fail with permission denied.

1. Put guardrails on the connection itself:

-- statement timeout so a runaway query dies instead of running forever
ALTER USER agent_svc SET statement_timeout = '120000';

Expected output: any query over two minutes gets killed by the database. Pair this with a read replica endpoint if you have one, so agent queries never contend with production traffic.

1. Document the rotation and revocation procedure before you need it:

-- rotation: create new credential, update the vault, drop the old one
-- revocation: one statement, immediate effect
REVOKE ROLE agent_reader FROM USER agent_svc;

Expected output: a runbook entry, not a scramble. Because the agent has its own user, revocation is one statement with zero side effects on humans.

Variant phrasings

agent database read only role

The role is the whole mechanism. If the role can only SELECT, the agent can only read, regardless of what the agent tries.

safe agent warehouse credentials

Safe means dedicated, least-privilege, and revocable. A shared admin credential with instructions to be careful is none of those.

restrict agent to select only

Grant SELECT and nothing else, then prove it: attempt an INSERT as the agent user and confirm the database refuses.

Why it happens

Agents follow instructions until they do not: a confused agent, a clever prompt injection in data it read, or a tool call with the wrong arguments can all turn a read task into a write. Instructions are suggestions; grants are walls. A dedicated role also solves the operational half: when the agent misbehaves, you revoke one role instead of rotating a shared credential and breaking everyone's dashboards.

Edge cases

• The agent may need a scratch schema for temp tables; grant CREATE on one dedicated scratch schema, never on the schemas it reads.
• Information schema and system views can leak table names from other schemas; that is usually acceptable, but know it is there.
• Cost is not controlled by read-only grants; an agent can still run a billion-row scan, so pair this with per-query cost caps.
• Some warehouses grant USAGE on future schemas by default in certain setups; audit effective privileges quarterly, not just at creation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_7XeZe0TgBOhzQa4w0s1A4g