Engine-enforced read-only: how DBeaver read-only mode works on PostgreSQL and MySQL
DBeaver's read-only connection blocks writes in the client; PostgreSQL and MySQL can enforce read-only at the engine. Why the difference matters.
“Read-only” is one of those settings that sounds absolute. You tick a box, the connection is read-only, nothing can change your data — done. But read-only is not one mechanism. It is at least two, enforced in two different places, with two very different guarantees. One lives in your client application; the other lives in the database engine. If you work in DBeaver against PostgreSQL or MySQL, it is worth knowing exactly which one you are relying on — because only one of them holds when something goes wrong.
This article separates the two. It is useful whether or not you ever touch a particular tool; the goal is to stop you from mistaking a UI guardrail for a hard boundary.
Disclosure: I work on SQL Sage, which I’ll use later as a concrete example. The mechanisms below are the database engines’ and DBeaver’s own — verify them against the sources I link.
What does DBeaver’s read-only connection mode actually do?
DBeaver has a per-connection “Read-only” toggle. In DBeaver’s own words, it “restricts the connection to read-only operations, ensuring no data can be modified or deleted” (DBeaver: security restrictions for a database connection). Alongside it, the Edit permissions panel exposes finer switches: Restrict data edit (“Disables the ability to modify data within the database through this connection”), Restrict structure edit, Restrict script execute, and Restrict data import.
Read the phrase “through this connection” carefully. These are restrictions DBeaver imposes on itself. When read-only is on, DBeaver greys out the data-grid editing, refuses to generate UPDATE/DELETE from the UI, and blocks the write paths it knows about. The documentation describes the effect — no modifications, no deletions — but it does not say the database is told anything. That is the defining trait of client-side read-only: the application is the thing saying no.
This is genuinely useful. It catches the accidental double-click in a results grid, the stray edit on a production connection you opened “just to look.” For the everyday “don’t let me fat-finger prod” case, it does its job. But the enforcement lives in the same process that could have a bug, and the server on the other end is still perfectly willing to accept a write. The guardrail is in the car, not on the road.
What does engine-enforced read-only look like on PostgreSQL?
PostgreSQL moves the decision into the server. You can mark a transaction read-only with SET TRANSACTION READ ONLY, or set the session default with the default_transaction_read_only configuration parameter (in postgresql.conf, per role, or with SET). Once that is in force, the engine itself refuses to write.
The refusal is specific and documented. In a read-only transaction, PostgreSQL disallows INSERT, UPDATE, DELETE, MERGE, and COPY FROM when the target is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and even EXPLAIN ANALYZE or EXECUTE when the command they would run is one of those (PostgreSQL: SET TRANSACTION). The write does not fail in the UI; it fails at the server, with an error, no matter what client sent it.
That last part is the whole point. A psql session, a rogue script, a buggy application, DBeaver with its toggle accidentally off — all of them hit the same wall, because the wall is in the database. The guarantee no longer depends on any one client behaving.
How does MySQL enforce read-only — and where are the gaps?
MySQL offers read-only at two levels, and the distinction matters.
At the transaction level, START TRANSACTION READ ONLY or SET TRANSACTION READ ONLY makes a single transaction read-only. MySQL’s manual is blunt about the effect: “If the transaction access mode is set to READ ONLY, changes to tables are prohibited” (MySQL: SET TRANSACTION). There is a documented exception, and it is worth internalising: “In read-only mode, it remains possible to change tables created with the TEMPORARY keyword using DML statements,” though DDL on them is still blocked. So “read-only” here means permanent tables are protected, not that the session is inert.
At the server level, the read_only and super_read_only system variables make the whole instance refuse writes — the mechanism behind a replica or a maintenance window. But read_only has a hole by design: updates from clients holding the CONNECTION_ADMIN (or deprecated SUPER) privilege are still allowed, which is exactly why super_read_only exists — with it on, “clients cannot make any updates, even if they have the CONNECTION_ADMIN privilege” (MySQL: server system variables). Setting read_only is itself a privileged, global act, not a per-user leash.
The practical reading: MySQL’s transaction-level read-only is the one you compose into a normal workflow, and it is a real server-side refusal — but scope it honestly. Temporary tables stay writable, and instance-wide read_only is a different, heavier tool with its own privilege exceptions.
Client-side or engine-enforced: when does the difference bite?
For the common case — one careful person, one tool, one connection — client-side read-only and engine-enforced read-only feel identical. The difference surfaces exactly when you most need it to hold:
- A bug in the client. If the application has the only veto and it misfires, the write lands. An engine that refuses writes does not care whether the client was correct.
- More than one path to the database. The moment a script, a cron job, or a second tool shares the credentials, the one tool’s checkbox protects only that one tool. The engine’s read-only transaction protects every session that opens one.
- Automation you did not write. An assistant, a migration runner, anything that executes SQL on your behalf — a UI toggle it never sees cannot constrain it. A server that rejects
UPDATEconstrains it regardless.
This is the same principle as why an AI SQL assistant should be read-only by default: the security boundary is where the refusal lives, not where the button is. A client-side setting expresses intent; an engine-enforced one enforces it.
What’s the strongest read-only boundary, then?
Honesty requires naming the ceiling. Even engine-enforced transaction read-only is something the session opts into. The strongest boundary is one the session cannot opt out of: database permissions. A login that was only ever GRANTed SELECT cannot write, full stop — there is no mode to flip, no transaction to start differently, no privilege to escalate within. It is read-only because the account has no other power.
So the hierarchy, weakest to strongest, is: a client that declines to issue writes; an engine transaction that refuses them; and a credential that was never allowed to perform them in the first place. Most real setups blend these — a read-only login for exploration, engine-level read-only for an extra belt, and a separate privileged account for the rare, deliberate change. The engines differ in dialect, as the cross-engine differences that trip you up show, but the layering idea is the same everywhere.
Where SQL Sage fits (the honest part)
I work on SQL Sage for DBeaver, a plugin that adds an AI copilot inside DBeaver across PostgreSQL, MySQL/MariaDB, Oracle, SQL Server, and SQLite. Two facts about its read-only behaviour, stated plainly and no further:
- It is read-only by default — the assistant reads freely, and
DML/DDLonly runs after your explicit confirmation. - On PostgreSQL and MySQL/MariaDB that read-only is engine-enforced, not just a UI state: the write is refused by the database, not only by the plugin. On the other engines it stays read-only by default with confirmation gating writes.
The plugin is Windows-only (Linux and macOS are not supported) and keyless — you sign in with your own Claude or ChatGPT account, no API key. Read more about it on the SQL Sage for DBeaver product page, or see it alongside the SSMS add-in on the products overview. SQL Sage is made by LumaSoft (LUMA sp. z o.o.) and is not affiliated with DBeaver, Oracle, Microsoft, Anthropic, or OpenAI.
The takeaway stands without any of that: when a setting promises read-only, ask who is refusing the write. If the answer is “the tool,” you have a useful guardrail. If the answer is “the database,” you have a boundary.