Question
DataSpell read-only mode enforcement when testing against non-production databases
Sky Finch
0 reputation · 04 Oct 2022, 17:54 UTC
89.8K views
Loading…
0 reputation · 04 Oct 2022, 17:54 UTC
DataSpell provides a read-only toggle per data source that blocks DML statements in the IDE, yet the documentation notes this guard operates only on the client side and does not translate to server-level permissions. When configuring a dedicated test database (for example, a PostgreSQL instance spun up in CI) to avoid production credentials, the read-only flag can be flipped accidentally or bypassed by editing the connection, leaving the test database vulnerable to unintended writes.
The goal is to run exploratory queries and notebook SQL cells against a realistic dialect without risking data mutation, while still exercising production-like features such as stored procedures or advisory locks that SQLite cannot replicate. Constraints include the need for zero production credentials, CI-friendly setup, and assurance that neither a misclick nor a notebook cell can persist changes.
Does DataSpell offer a mechanism to bind the read-only intent to the database connection itself—such as opening the session with SET TRANSACTION READ ONLY or using a restricted database role—so the enforcement survives IDE restarts and connection edits? Which configuration approach ensures that a test database receives the same dialect fidelity as production while remaining write-protected at the server level?
33775 reputation · 05 Oct 2022, 02:13 UTC
DataSpell's internal 'read-only' toggle is a client-side guard. It prevents the IDE from sending DML statements through the UI, but it does not modify the permissions of the database session. To ensure a test database remains protected against accidental writes from notebook cells or manual connection edits, you must move the enforcement from the IDE to the database engine itself.
The most robust way to bind read-only intent to a connection is to use a restricted database role. This ensures that even if the DataSpell 'read-only' checkbox is toggled off, the database server will reject any write attempts.
For a PostgreSQL-based CI environment, follow these steps to create a restricted test user:
CREATE USER test_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE test_db TO test_user; GRANT USAGE ON SCHEMA public TO test_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO test_user;
dataspelltest_user credentials in your Data Source configuration.If you cannot create new roles in your environment, you can force DataSpell to execute a session-level command immediately upon connecting. This is more reliable than the IDE toggle because it affects the session state.
Open your Data Source properties in DataSpell:
SET SESSION CHARACTERISTICS READ ONLY;
Note: This approach applies to the session. If the IDE uses a connection pool, ensure the script is configured to run on every new connection acquisition to maintain the protection.
To verify that the protection is active and not just a UI visual state, run the following in a DataSpell SQL console:
CREATE TABLE test_protection (id int);
If the configuration is successfully enforced at the server or session level, the database will return an ERROR: cannot execute in read-only transaction, regardless of DataSpell's internal settings.
Use comments to ask for clarification. Post a solution as an answer.
33,775 reputation · 04 Oct 2022, 20:41 UTC
DataSpell lets you specify arbitrary JDBC URL parameters in the Advanced tab of a data source. By appending ?defaultTransactionReadOnly=true (or adding the property defaultTransactionReadOnly=true in the URL options field) the PostgreSQL JDBC driver will prepend SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY to every newly opened connection, regardless of the IDE’s client‑side toggle.
If you prefer to keep the setting inside the database, you can alter the test role:
ALTER ROLE test_user SET default_transaction_read_only = true;
This makes any session started by test_user read‑only unless the application explicitly issues SET TRANSACTION READ WRITE. Because the property lives on the role, changing the connection UI in DataSpell cannot disable it.
Both approaches allow read‑only compatible features such as advisory locks (SELECT pg_try_advisory_lock(1)) to succeed, while blocking INSERT/UPDATE/DDL with a permission error.