How to Give an AI Agent Read-Only Database Access (Postgres, MySQL, SQL Server, Oracle, Snowflake, SQLite)

Step-by-step SQL to create a read-only database user for an AI agent on PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, Snowflake and SQLite, how to add read-only enforcement in the MCP or REST layer, and how to prove it by attempting a write.

Key takeaways

  • Start in the database. A dedicated login with SELECT-only grants is the control that holds no matter which MCP server, REST layer or client the agent uses. Session settings such as Postgres default_transaction_read_only or SQLite PRAGMA query_only are defaults the session can switch off, not controls.
  • Add read-only enforcement in the MCP or REST layer too. It refuses writes before they reach the database, gives each agent its own key, and covers SQLite, which has no users. In our SQLite harness, Faucet (GET-only key or read_only flag), DBHub (readonly = true) and Datasette let 0 writes land, MCP Toolbox relied on a mode=ro URI, and the ExecuteAutomation server has no read-only mode.
  • Watch for writes that don't look like writes: stacked COMMIT; statements, COPY ... TO PROGRAM, SECURITY DEFINER functions and stored procedures that run as their owner. A SELECT-only user with no EXECUTE or file privileges blocks all of these.
  • Prove it. Attempt an INSERT, UPDATE, DELETE and a CTE-prefixed DELETE as the agent, then check the data over a separate connection instead of trusting the reply.
  • Tool annotations are hints. The MCP spec says clients must treat readOnlyHint and destructiveHint as untrusted unless the server is trusted, so use them for approval prompts, not as the security boundary. See the database MCP server security checklist.

Three layers that keep an AI agent from changing your data: a read-only database user, read-only enforcement in the MCP or REST server, and a write test that proves both work.

How do you give an AI agent read-only database access?

Create a dedicated database login that can only SELECT from the schemas the agent needs, with no EXECUTE, file or admin privileges. Then turn on read-only enforcement in the MCP or REST server the agent calls, and prove both by attempting a write as the agent and checking the data over a separate connection. SQLite has no users, so open it with mode=ro.

Read-only database access compared at a glance

Native read-only patterns per engine, checked against each vendor’s documentation on October 16, 2026. Only SQLite was run hands-on for this post.

EngineRead-only grant patternCovers tables created later?Can the session undo it?Writes that can slip pastHands-on here
PostgreSQLGRANT SELECT + ALTER DEFAULT PRIVILEGES, or pg_read_all_data (14+)Only with default privileges or pg_read_all_dataGrants: no. default_transaction_read_only: yesSECURITY DEFINER functions (EXECUTE goes to PUBLIC by default); COPY ... TO PROGRAM if the role has pg_execute_server_programNo (docs)
MySQL / MariaDBGRANT SELECT ON db.*Yes (database-level grant)NoStored routines, if you grant EXECUTE; INTO OUTFILE, if you grant FILENo (docs)
SQL Server / Azure SQLUser in db_datareader or GRANT SELECT ON SCHEMA::, plus db_denydatawriterYes (database or schema scope)NoProcedures you grant EXECUTE onNo (docs)
Oracle19c: CREATE SESSION + READ per table. 23ai/26ai: READ ONLY user + SELECT ANY TABLE ON SCHEMAPer-table grants: no. Schema grant: yesNoDefiner’s-rights procedures you grant EXECUTE on (19c)No (docs)
SnowflakeRole with USAGE on warehouse, database and schema, SELECT on ALL and FUTURE tablesOnly with FUTURE grantsNoOwner’s-rights procedures you grant USAGE on; warehouse costNo (docs)
SQLiteNo users. file:...?mode=ro or read-only file permissionsYes (whole file)mode=ro: no. PRAGMA query_only: yesNone through a mode=ro connectionYes (Chinook harness)

How we evaluated

We checked every statement on this page against the vendor’s current documentation on October 16, 2026: PostgreSQL 18, MySQL 8.4, MariaDB, SQL Server 2025 and Azure SQL, Oracle AI Database 26ai (formerly 23ai), Snowflake and SQLite. We did not run the Postgres, MySQL, SQL Server, Oracle or Snowflake snippets for this post, so nothing in those sections says “tested”. Run Layer 3 against your own server before you rely on them.

Layer 2 and SQLite are hands-on. We ran Faucet, the open-source database-to-REST-API and MCP server, plus DBHub, MCP Toolbox for Databases, the ExecuteAutomation database server and Datasette against the Chinook 1.4.5 SQLite database. For each server we tried INSERT, UPDATE and DELETE through every write path, plus a CTE-prefixed DELETE on raw-SQL paths, and judged each attempt by reading the file back over a separate mode=ro connection. Faucet’s raw-SQL tool was refused before execution in every configuration (off by default, or blocked by role or read_only), so the CTE test did not apply to it. Faucet is our product. We tested a source build (v0.1.15 plus 4 commits, 2327d63); every other server was a published release. For the wider server comparison, see the best MCP servers for databases.

Layer 1: create a read-only database user

Give each agent, or each server that agents use, its own login. Don’t reuse the application’s user. Replace the names and passwords below with your own, and keep the password in a secret store rather than the MCP client config.

PostgreSQL

-- size the limit to your server's connection pool, not the number of agents
CREATE ROLE agent_ro LOGIN PASSWORD 'change-me' CONNECTION LIMIT 30;
GRANT CONNECT ON DATABASE app TO agent_ro;
GRANT USAGE ON SCHEMA public TO agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_ro;

-- tables that app_owner creates later
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO agent_ro;

-- defaults, not controls: they catch mistakes and runaway queries
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '30s';

On PostgreSQL 14 and later, GRANT pg_read_all_data TO agent_ro; replaces the schema grants. It reads every table, view and sequence in every schema, and row-level security policies still apply.

Things to know

  • ALTER DEFAULT PRIVILEGES only covers objects created by the role named in FOR ROLE. If migrations run as a different role, name that one.
  • default_transaction_read_only is a default. The session can run SET TRANSACTION READ WRITE. The grants are what stop writes.
  • PostgreSQL grants EXECUTE on new functions to PUBLIC. A SECURITY DEFINER function runs with its owner’s privileges, so audit those and revoke PUBLIC on any that write.
  • Never give the agent’s role superuser, pg_execute_server_program or pg_write_server_files. The COPY ... TO PROGRAM bypass in AWS bulletin 2026-104 (CVE-2026-87911, self-managed PostgreSQL profile, fixed in 1.1.7) needed one of those.
  • Databases upgraded from PostgreSQL 14 or earlier still let everyone CREATE in public. Run REVOKE CREATE ON SCHEMA public FROM PUBLIC;.

For which Postgres MCP servers enforce read-only and how, see the Postgres MCP servers comparison.

MySQL and MariaDB

CREATE USER 'agent_ro'@'10.0.%' IDENTIFIED BY 'change-me'
  WITH MAX_USER_CONNECTIONS 30 MAX_QUERIES_PER_HOUR 5000;
GRANT SELECT, SHOW VIEW ON app.* TO 'agent_ro'@'10.0.%';

The same GRANT SELECT ON db.* form works on MariaDB. A database-level grant covers tables created later.

Things to know

  • Don’t grant FILE. It allows SELECT ... INTO OUTFILE and LOAD_FILE() on the server host.
  • Don’t grant EXECUTE unless you have read the routines. Stored routines can write.
  • MySQL has no per-user read-only flag. The read_only and super_read_only variables are server-wide and meant for replicas.
  • Pin the account’s host part ('10.0.%', not '%') to where your server runs.

SQL Server and Azure SQL

CREATE LOGIN agent_ro WITH PASSWORD = 'change-me';
GO
USE app;
CREATE USER agent_ro FOR LOGIN agent_ro;
GRANT SELECT ON SCHEMA::reporting TO agent_ro;   -- or: ALTER ROLE db_datareader ADD MEMBER agent_ro;
ALTER ROLE db_denydatawriter ADD MEMBER agent_ro;
GO

In Azure SQL Database, use a contained user (CREATE USER agent_ro WITH PASSWORD = '...') or FROM EXTERNAL PROVIDER for Entra.

Things to know

  • db_datareader reads every user table and view in every schema. A schema grant is narrower.
  • db_denydatawriter adds DENY INSERT, UPDATE, DELETE on the database, so a later accidental grant doesn’t open writes.
  • Neither role includes EXECUTE. Leave it off unless you have reviewed the procedure, because procedures run under ownership chaining.
  • Our SQL Server MCP servers comparison and SQL Server REST API guide cover the server side.

Oracle

-- Oracle 19c and later
CREATE USER agent_ro IDENTIFIED BY "change-me";
GRANT CREATE SESSION TO agent_ro;
GRANT READ ON hr.employees TO agent_ro;      -- repeat per table or view

-- Oracle 23ai and Oracle AI Database 26ai, local PDB user
CREATE USER agent_ro IDENTIFIED BY "change-me" READ ONLY;
GRANT CREATE SESSION TO agent_ro;
GRANT SELECT ANY TABLE ON SCHEMA hr TO agent_ro;

Things to know

  • READ lets the user query but not run SELECT ... FOR UPDATE, so the agent can’t hold row locks. SELECT allows both.
  • Oracle AI Database 26ai replaced 23ai in October 2025 and keeps READ ONLY users. Such a user’s session behaves as if the database were open read-only. Writes fail with ORA-28194, even if someone later grants write privileges. It applies to local PDB users.
  • SELECT ANY TABLE ON SCHEMA hr covers tables added to hr later. Never grant the database-wide SELECT ANY TABLE.
  • Tools often default the schema to the connecting user (Faucet does), so pass --schema HR.

Snowflake

CREATE ROLE agent_ro;
GRANT USAGE ON WAREHOUSE agent_wh TO ROLE agent_ro;
GRANT USAGE ON DATABASE analytics TO ROLE agent_ro;
GRANT USAGE ON SCHEMA analytics.marts TO ROLE agent_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts TO ROLE agent_ro;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.marts TO ROLE agent_ro;
GRANT SELECT ON ALL VIEWS IN SCHEMA analytics.marts TO ROLE agent_ro;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA analytics.marts TO ROLE agent_ro;

CREATE USER agent_svc TYPE = SERVICE
  DEFAULT_ROLE = agent_ro DEFAULT_WAREHOUSE = agent_wh
  RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
GRANT ROLE agent_ro TO USER agent_svc;

ALTER WAREHOUSE agent_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 120;

Things to know

  • ON ALL only touches objects that exist now. The FUTURE grants cover new ones.
  • TYPE = SERVICE users authenticate without a password. Use key-pair auth. Faucet supports it with --private-key-path.
  • Reads still cost credits. A small dedicated warehouse with a timeout caps a looping agent.

SQLite

SQLite has no users or grants. Read-only is decided when the file is opened.

file:/data/app.db?mode=ro

Things to know

  • mode=ro is enforced by SQLite. In our harness, MCP Toolbox with a mode=ro URI let 0 of 4 writes land, and sqlite-rest v0.4.1 with a mode=ro DSN let 0 of 3 land (HTTP 500, “attempt to write a readonly database”).
  • PRAGMA query_only = ON blocks writes, but the same connection can switch it off. It is a guard, not a control.
  • File permissions also work: run the API as an OS user that can only read the file.
  • The Faucet SQLite tutorial shows one way to put a SQLite file behind keyed REST and MCP access.

Layer 2: enforce read-only in the MCP or REST server

The database user is the boundary. The server layer still matters: it refuses writes before they reach the database, can hide write tools from the model, gives each agent a revocable key, and covers SQLite or databases where you can’t create users.

Here is what each server’s read-only setting did in our SQLite harness on October 16, 2026. Each count is the number of write attempts that changed the file: one INSERT, UPDATE and DELETE per exposed write tool or endpoint, plus a CTE-prefixed DELETE where raw SQL runs.

Server (version)How read-only is setWrites landed with defaultsWrites landed with read-only onWrite tools hidden when read-only?
Datasette 0.65.5Read-only by default0/60/6No write tools by default¹
DBHub 1.5.0readonly = true in dbhub.toml (7 lines)4/40/4No write-only tool to hide; execute_sql is re-annotated read-only²
MCP Toolbox 1.14.0No setting for the SQLite source; mode=ro URI4/40/4 (enforced by SQLite)No: both tools annotated destructiveHint: true
Faucet (source build, v0.1.15+4 commits)GET-only role plus API key, or the service read_only flag3/3 (admin CRUD)³0/9 (GET-only key); 0/9 HTTP, 0/6 stdio (read_only)—⁵
ExecuteAutomation database-server 1.1.0No read-only mode3/3 through write_queryNot available⁴No

Rows are ordered by read-only mechanism, from built-in to none, not ranked.

¹ Writes only through canned queries with write: true or plugins. The 1.0 alphas add a write API (not tested). ² search_objects was absent only because our TOML declared just execute_sql, which DBHub correctly re-annotates readOnlyHint: true, destructiveHint: false. ³ CRUD tools with an admin session. Raw SQL is off by default. ⁴ read_query refused 4/4 non-SELECT statements; write_query, create_table, alter_table and drop_table remain. Last release May 2025. ⁵ Re-testing on Faucet v0.1.16.

DBHub’s read-only mode combines a keyword allow-list with engine enforcement: BEGIN READ ONLY on PostgreSQL, START TRANSACTION READ ONLY on MySQL/MariaDB, and PRAGMA query_only on SQLite, so its SQLite guard is session-level. Its docs (engine enforcement since 0.22.6) warn that MySQL/MariaDB DDL is stopped only by the classifier, and say to always use a least-privilege read-only user.

MCP Toolbox’s read-only mode prunes write tools and locks the session: a locked session parameter on Cloud SQL Postgres and AlloyDB, a read-only driver attribute on Cloud SQL MySQL, write mode blocked on BigQuery. Other sources, including SQLite, have no Toolbox setting.

Engine-specific options (from docs, not tested):

  • Postgres MCP Pro (MIT): --access-mode=restricted uses read-only transactions and rejects COMMIT/ROLLBACK via pglast; unsafe procedural languages can bypass it.
  • Supabase MCP: read_only=true excludes mutating tools.
  • awslabs postgres-mcp-server: read-only by default; run 1.1.7 or later.
  • Data API builder’s SQL MCP Server: role permissions per entity apply to every MCP tool.

Example: a read-only DBHub server

DBHub sets read-only per tool. This is the 7-line dbhub.toml from the harness:

[[sources]]
id = "chinook"
dsn = "sqlite:///data/chinook.db"

[[tools]]
name = "execute_sql"
source = "chinook"
readonly = true

For Postgres or MySQL, point dsn at the Layer 1 user rather than an admin account.

Example: a read-only agent key with Faucet

Faucet (our product) has two read-only controls: a GET-only role bound to an API key, and a service-wide read_only flag. Point it at the Layer 1 user:

faucet db add --name app --driver postgres \
  --dsn "postgres://agent_ro:[email protected]:5432/app?sslmode=require"
faucet role create --name readonly --service app --verbs GET
faucet key create --role readonly
faucet serve

The agent sends the key in X-API-Key to /mcp. Reads work; insert, update, delete and raw SQL are refused. In the harness, a REST POST with that key returned:

{"error":{"code":403,"message":"Role \"readonly\" does not permit POST on chinook/_table/Artist","context":{"component":"_table/Artist","role":"readonly","service":"chinook","verb":"POST"}}}

The service-wide read_only flag refuses writes for every caller, admins included. Toggle Read-only on the database in the admin UI, or use the API. Create an admin with faucet admin create --email [email protected], then get a token from POST /api/v1/system/admin/session (the session_token field):

curl -X PUT http://localhost:8080/api/v1/system/service/app \
  -H "Authorization: Bearer $ADMIN_TOKEN" -H "Content-Type: application/json" \
  -d '{"read_only": true, "raw_sql_allowed": false}'

The RBAC docs cover per-table rules, and the read-only and raw SQL modes reference covers the flags. To wire it into a client, follow connect Claude to your database or connect ChatGPT to your database.

Things to know about Faucet

  • Read-only is enforced in Faucet, not in the database. Keep the Layer 1 user as well.
  • If you enable raw SQL, faucet_raw_sql runs whatever statement it receives (there is no SELECT-only filter), so only the read_only flag or the Layer 1 grants stop writes through it.
  • faucet db add gives each database a pool of up to 100 open connections (25 idle). Set the user’s connection limit above your expected concurrency, or put PgBouncer in front.
  • Faucet authenticates agents with API keys (no OAuth) and scopes them by service, table and verb; it has no row- or column-level security. An admin can turn read_only off.
  • It covers SQL databases only, and it is a newer project than most tools in this table.
  • Its structured query tool can’t name arbitrary functions in fields (only AVG, COUNT, MAX, MIN and SUM). Functions inside views you expose still run when the view is read, so audit views as you would SECURITY DEFINER functions.

Tool annotations are not controls

readOnlyHint and destructiveHint help a client decide what to auto-approve. The MCP spec says clients must treat them as untrusted unless the server is trusted, so use them for approval prompts, not as the boundary. We covered how clients use them in MCP tool annotations for database safety.

Layer 3: verify by attempting a write

Test the path the agent uses (MCP tool or REST call), then the database login on its own. Judge each attempt by reading the data back over a separate connection, not by the reply: a tool can report an error after a write committed.

Try each write shape. For raw-SQL tools, add a CTE prefix and a stacked statement, because keyword filters often check only the first word:

INSERT INTO artist (name) VALUES ('ro-test');
UPDATE artist SET name = name || '' WHERE artist_id = 1;
DELETE FROM invoice_line WHERE invoice_line_id = 1;
WITH x AS (SELECT 1) DELETE FROM invoice_line WHERE invoice_line_id = 2;
COMMIT; DELETE FROM invoice_line WHERE invoice_line_id = 3;

Then confirm the grants are what you think they are:

EngineCheck the grantsExpected failure on a write
PostgreSQLSELECT table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'agent_ro';permission denied for table ...
MySQL / MariaDBSHOW GRANTS FOR 'agent_ro'@'10.0.%';INSERT command denied to user ... (error 1142)
SQL ServerEXECUTE AS USER = 'agent_ro'; SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); REVERT;The INSERT permission was denied ... (error 229)
OracleSELECT * FROM dba_tab_privs WHERE grantee = 'AGENT_RO'; and DBA_SCHEMA_PRIVS on 23ai/26aiORA-01031, or ORA-28194 for a READ ONLY user (23ai/26ai)
SnowflakeSHOW GRANTS TO ROLE agent_ro; and SHOW FUTURE GRANTS IN SCHEMA analytics.marts;An insufficient-privileges access control error
SQLiteOpen with mode=roattempt to write a readonly database

For SQLite, the harness counted rows over a second read-only connection:

import sqlite3
ro = sqlite3.connect("file:chinook.db?mode=ro", uri=True)
print(ro.execute("SELECT COUNT(*) FROM Artist WHERE Name = 'ro-test'").fetchone())
print(ro.execute("SELECT COUNT(*) FROM InvoiceLine").fetchone())  # Chinook ships with 2240

Rerun it after every server upgrade and every role change.

Known bypass patterns

  • Stacked statements. In the archived @modelcontextprotocol/server-postgres, a leading COMMIT; ended its read-only transaction (Datadog Security Labs). See our write-up of the Postgres MCP read-only bypass.
  • Server-side programs. awslabs postgres-mcp-server before 1.1.7 (self-managed PostgreSQL / PG wire profile) let a planted COPY ... TO PROGRAM through read-only mode when the role had superuser or pg_execute_server_program (CVE-2026-87911). A SELECT-only role has neither.
  • Functions called from a SELECT. Read-only checks that look at the first keyword miss a SELECT that calls a function which writes. DBHub fixed such a gap in 0.22.6 (CVE-2026-61788). A role without EXECUTE on writing functions blocks this.
  • Code that runs as someone else. SECURITY DEFINER functions in Postgres, definer’s-rights procedures in Oracle, owner’s-rights procedures in Snowflake, and ownership chaining in SQL Server. Keep EXECUTE or USAGE off them.
  • Session toggles. SET TRANSACTION READ WRITE in Postgres and PRAGMA query_only = OFF in SQLite undo session-level guards.
  • Admin paths. Local stdio servers and admin tokens often skip the role checks that HTTP keys get. Read-only has to hold there too.

The PocketOS incident analysis shows what happens when an agent finds a credential with more scope than its task needs.

Read-only database user vs MCP server read-only mode

You want both. A read-only database user is enforced by the engine, so prompt injection, a buggy keyword filter or a server admin path can’t get past it, but it can’t hide write tools, cap rows or give agents separate keys.

An MCP or REST read-only mode refuses writes before the database sees them, can drop write tools, cap results and scope access per key. Its weak points are what Layer 3 tests: keyword filters that miss stacked statements, and admin or stdio paths that skip roles.

If you can only do one, do the database user. For a full control list, see the database MCP server security checklist.

Next steps

Create the database user first, then add a server-side read-only control and run the write test. To try Layer 2 with Faucet, the getting started guide takes you from install to a GET-only API key, and the PostgreSQL REST API and MCP guide shows the same setup on Postgres.

Frequently asked questions

Is a read-only database user enough for an AI agent?

It is the most important layer, because the database enforces it whatever tool the agent uses. It does not cap rows, time out slow queries, hide write tools from the model, or give each agent its own revocable key. Add read-only enforcement in the MCP or REST server, set statement timeouts, and keep EXECUTE, file and superuser-style privileges off the account.

Can a read-only Postgres transaction be bypassed?

Yes. A read-only transaction or default_transaction_read_only is a session setting, so a stacked COMMIT; or SET TRANSACTION READ WRITE can get past it when a tool sends raw SQL, while a role with only SELECT grants cannot write in any transaction mode. Our analysis of the Postgres MCP read-only bypass has the details.

How do I make a SQL Server login read-only?

Create a login, map a database user to it with CREATE USER ... FOR LOGIN, then grant SELECT on the schemas the agent needs (GRANT SELECT ON SCHEMA::reporting) or add it to db_datareader. Adding it to db_denydatawriter denies INSERT, UPDATE and DELETE on the whole database. Don't grant EXECUTE unless you have reviewed the procedure.

Does SQLite support read-only users?

No. SQLite has no users or grants, so read-only is a property of how the file is opened. Open it with a file:...?mode=ro URI or give the process read-only file permissions. PRAGMA query_only also blocks writes, but the same connection can turn it off, so it is a guard rather than a control. Put any network API in front of it behind read-only keys.

Does Oracle have a read-only user?

Yes, since Oracle Database 23ai, and in Oracle AI Database 26ai, which replaced it. CREATE USER or ALTER USER with the READ ONLY clause makes a local PDB user's session behave as if the database were open read-only, and write attempts fail with ORA-28194. On 19c, create a user with CREATE SESSION and grant READ on specific tables, which also blocks SELECT ... FOR UPDATE row locks.

Should I point the AI agent at a read replica instead?

A replica protects the primary's performance, but it does not replace a read-only login. The agent can still read every table its login can read, and a promoted replica, or a MySQL replica without read_only set, accepts writes. Use a replica for heavy analytical agents, connect with the same SELECT-only user, and keep statement timeouts on.

Sources

Last reviewed