how to scope an agent to one schema safely

TL;DR

Create a role with USAGE plus SELECT on the one schema and nothing else, revoke the public defaults, and set the agent's search path to that schema so unqualified names resolve there. Scoping at the grant level means a confused agent cannot wander, even if it guesses another schema's table names. It works because instructions are a suggestion but grants are a wall: the database refuses what the role was never given.

how to scope an agent to one schema safely

Use this when

• An agent should only see one domain's data
• Isolating agents per team or per project
• A confused agent must not wander into other schemas
• You need a clean story for auditors about data boundaries

Not for

• Agents that legitimately need cross-schema joins
• Write access, which needs a separate gated pattern
• Hiding sensitive columns inside the schema, which needs masking

Steps

1. Create the role and grant the absolute minimum:

CREATE ROLE agent_reporting_reader;
GRANT USAGE ON DATABASE analytics TO ROLE agent_reporting_reader;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE agent_reporting_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE agent_reporting_reader;

Expected output: the role can see the reporting schema and literally nothing else. No grants on other schemas means no access, regardless of what the agent types.

1. Cover tables created in the future so the scope does not silently widen or narrow:

ALTER DEFAULT PRIVILEGES FOR ROLE etl_owner IN SCHEMA analytics.reporting
  GRANT SELECT ON TABLES TO ROLE agent_reporting_reader;

Expected output: new tables in the reporting schema are automatically readable. Without this, the agent mysteriously loses access to new tables and someone grants too much while debugging.

1. Strip the defaults that leak across schemas:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- verify: list every grant the role actually holds
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'agent_reporting_reader';

Expected output: a grant list showing only the reporting schema. If anything else appears, the scope has a hole.

1. Point the agent's search path at its schema:

ALTER USER agent_svc SET search_path = analytics.reporting;

Expected output: unqualified table names resolve inside the agent's schema. Queries the agent writes without schema prefixes just work, and stay inside the boundary.

1. Prove the boundary with a probe the agent would try:

-- run as the agent user: this must fail
SET ROLE agent_reporting_reader;
SELECT * FROM analytics.finance LIMIT 1;
-- expected: permission denied for schema finance

Expected output: a permission-denied error on the other schema. The test is the proof; a scope you have not tested is a hope.

Variant phrasings

restrict agent to one database schema

The grant list is the scope. If a schema is not in the grants, the agent cannot read it, full stop.

Postgres schema isolation agent

Postgres makes this natural with schemas plus search_path. The default-privileges step is the one people forget.

Snowflake schema-scoped role

Same shape: USAGE on database and warehouse, USAGE plus SELECT on the one schema, future grants for new tables. The concepts map one to one.

Why it happens

Agents enumerate: given a vague instruction to stay in one schema, they will still try plausible table names elsewhere, follow foreign keys across boundaries, or misread which schema a table lives in. Least privilege handles this by making the boundary a database fact instead of a behavioral request. The search_path setting removes the friction that tempts people to over-grant: when unqualified names work inside the boundary, nobody needs cross-schema access just for convenience.

Edge cases

• Cross-schema views: a view in the reporting schema that selects from finance will fail for the agent, correctly, but the error confuses people; document which views are safe.
• The public schema defaults vary by Postgres version and cloud vendor; always verify with the probe in step 5 rather than assuming.
• Multiple agents sharing one role cannot be told apart in the audit log; give each agent its own user on the same role.
• Schema renames break the grants silently; include the grant verification query in your deploy pipeline.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_rx8k-c9PQmxA-ioBCFhULw