Quartz 4

ai-db-access

2 min read

AI Read-Only Database Access

How to give an AI (e.g. Claude via MCP) read-only access to a production Postgres database safely.

Key details

Step 1 — Create the role

CREATE ROLE ai_readonly WITH LOGIN PASSWORD 'strong_password_here';
GRANT CONNECT ON DATABASE your_db TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ai_readonly;

Repeat USAGE and SELECT grants for each additional schema.

Step 2 — Lock it down further

-- Prevent function execution
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM ai_readonly;
 
-- Row-level security for sensitive rows
ALTER TABLE sensitive_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY ai_policy ON sensitive_table FOR SELECT TO ai_readonly
  USING (classification != 'restricted');
 
-- Prevent expensive runaway queries
ALTER ROLE ai_readonly SET statement_timeout = '30s';
 
-- Limit connections
ALTER ROLE ai_readonly CONNECTION LIMIT 5;

Step 3 — Network layer (in order of preference)

  1. Read replica — point AI at the replica, not the primary. Eliminates lock contention and load impact on writes.
  2. PgBouncer in front of the replica with pool size limits for this role.
  3. If direct connection is unavoidable, restrict via pg_hba.conf to the AI service’s IP only.

Step 4 — Connect the AI

Connection string: postgresql://ai_readonly:password@replica-host:5432/your_db

For Claude via MCP, there is a Postgres MCP server that accepts a connection string and exposes read-only query tools.

What people get wrong

  • Using superuser or the app’s read-write credentials “just for now.” It never stays temporary.
  • Forgetting ALTER DEFAULT PRIVILEGES — new tables created after the grant won’t be accessible.
  • No statement timeout. An AI-generated SELECT with bad joins on large tables can lock up the replica.
  • Skipping the read replica. A SELECT-only grant doesn’t prevent performance impact on the primary.

Relevance

This setup is relevant when using Claude (via MCP Postgres server) or any AI tool that needs live query access to Guild or store production data — customer records, purchase history, loyalty transactions.

Graph View

Backlinks