To create a read-only PostgreSQL user, create a role with LOGIN, grant CONNECT on the database and USAGE plus SELECT on the schema, then set ALTER DEFAULT PRIVILEGES so future tables are covered too. On MySQL, GRANT SELECT ON db.* is enough because the wildcard already applies to tables created later.
Any AI database tool worth connecting will tell you it only issues reads. Believe it, and then enforce it anyway — not because the vendor is lying, but because a guarantee implemented in someone else's application is one refactor away from being different, and a guarantee implemented in your database is not.
PostgreSQL: four statements, and one people forget
The first three statements are the ones every tutorial gives you. The fourth is the one that matters in three months.
-- 1. The role itself
CREATE ROLE ai_readonly LOGIN PASSWORD 'use-a-real-secret';
-- 2. Let it reach the database and see the schema
GRANT CONNECT ON DATABASE analytics TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
-- 3. Read every table that exists right now
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
-- 4. ...and every table created from now on
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO ai_readonly;Step three grants on the tables that exist at the moment you run it. Without step four, the table your next migration adds is invisible to the role, and the failure surfaces as an AI tool insisting a table does not exist while you are looking at it in your client. It is the single most common misconfiguration in this setup.
If you use more than one schema
Both the USAGE grant and the DEFAULT PRIVILEGES statement are per-schema. A database with reporting, billing and public schemas needs all three repeated, and DEFAULT PRIVILEGES additionally applies only to objects created by the role that ran it — so if your migrations run as a different user, run it as that user.
-- Run as the role your migrations use, for each schema
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA billing
GRANT SELECT ON TABLES TO ai_readonly;MySQL: one statement, and a different gotcha
MySQL is simpler because a wildcard grant covers tables created later automatically — there is no DEFAULT PRIVILEGES equivalent to forget.
CREATE USER 'ai_readonly'@'%' IDENTIFIED BY 'use-a-real-secret';
GRANT SELECT ON analytics.* TO 'ai_readonly'@'%';
FLUSH PRIVILEGES;The gotcha here is the host part. `'ai_readonly'@'%'` accepts connections from anywhere, which is usually more than you meant. Scope it to the address range your team actually connects from, and remember that a desktop tool connects from a laptop rather than from your application servers.
Verify it, because an ungrant is silent
A grant that did not do what you thought produces no error until something needs it. Connect as the new user and try to write — the statement should be rejected outright.
-- Connected as ai_readonly. Both of these must fail.
CREATE TABLE should_not_work (id int);
UPDATE customers SET email = 'x' WHERE id = 1;Then check the read side is complete, including anything created since: query a table added after you set the grants up. If it is missing on Postgres, step four did not run or ran as the wrong role.
What this does not protect against
A read-only role stops writes. It does not stop an expensive query, and an AI tool that can issue arbitrary SELECTs against a production primary can absolutely cause an incident by reading too much.
- Set a statement timeout on the role, so a runaway query dies rather than accumulating.
- Point the tool at a read replica if you have one — read-only and off the primary are different protections.
- Consider a connection limit, so one tool cannot exhaust the pool.
-- PostgreSQL: cap query time and connections for this role
ALTER ROLE ai_readonly SET statement_timeout = '30s';
ALTER ROLE ai_readonly CONNECTION LIMIT 5;Two guarantees are better than one
SQLore parses every statement it generates to an AST and validates it down to a single read-only SELECT before execution, then runs it on a read-only connection under a statement timeout. We still recommend the grants above. Ours is the guarantee we control; yours is the one that keeps holding if we ship a bug — and a security review that only has to trust one of them is a much shorter meeting.