Wrap sql in read only transaction for postgres still executes?

[leenyburger]

I am either misusing or misunderstanding wrapping a sql statement in a read-only transaction

Using a local db for testing, the following does create a new table in my database

db.transaction(read_only: true) { db[sql] }

Where db.class is Sequel::Postgres::Database and db = #<Sequel::Postgres::Database: {:adapter=>"postgres", :host=>"localhost", :database=>"local_dev", :user=>"", :password=>"", :port=>"5432"}>
and sql = "CREATE TABLE employees_four (\n id SERIAL PRIMARY KEY,\n username VARCHAR(50) NOT NULL\n);\n"

I want to wrap my sql statements in a read only transaction to prevent any kind of db modification. For various reason I would like to do this instead of creating a read only users. Am I using the transaction statement wrong?

[leenyburger]

It appears this has to do with the way I'm executing sql.
If I use db.run instead of db[sql] it enforces the read only transaction.

[jeremyevans]

Database#[] does not execute the SQL on the database, it only creates a dataset.

Try running with a database logger to see what SQL is actually being executed: https://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdoc.html#label-Logging+SQL+statements

If you actually issue non-read-only queries inside a read-only transaction, it should raise a Sequel::DatabaseError. I tried and that's what I got:

$ bin/sequel -E postgres:///... -c "DB.transaction(:read_only=>true){DB.create_table(:t){primary_key :t}}"
I, [2024-03-11T10:25:35.262718 #44104]  INFO -- : (0.000385s) SET standard_conforming_strings = ON
I, [2024-03-11T10:25:35.262974 #44104]  INFO -- : (0.000109s) SET client_min_messages = 'WARNING'
I, [2024-03-11T10:25:35.263062 #44104]  INFO -- : (0.000045s) SET DateStyle = 'ISO'
I, [2024-03-11T10:25:35.263311 #44104]  INFO -- : (0.000046s) BEGIN
I, [2024-03-11T10:25:35.263451 #44104]  INFO -- : (0.000091s) SET TRANSACTION READ ONLY
I, [2024-03-11T10:25:35.264591 #44104]  INFO -- : (0.000859s) SELECT CAST(current_setting('server_version_num') AS integer) AS v
E, [2024-03-11T10:25:35.265079 #44104] ERROR -- : PG::ReadOnlySqlTransaction: ERROR:  cannot execute CREATE TABLE in a read-only transaction: CREATE TABLE "t" ("t" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY)
I, [2024-03-11T10:25:35.265349 #44104]  INFO -- : (0.000054s) ROLLBACK
Error: Sequel::DatabaseError: PG::ReadOnlySqlTransaction: ERROR:  cannot execute CREATE TABLE in a read-only transaction

/data/code/sequel/lib/sequel/adapters/postgres.rb:171:in `exec'

[leenyburger]

Hmmm, it appears that db[sql] does run the sql?

Basically I'm trying to create a dataset (which is working great) but I want to make sure that no execution is done accidentally on the db. I do heavy sanitization on the incoming sql string, but it's still a sql string so I was trying to understand how to further protect the db from accidental writes.

[jeremyevans]

The reuslts = db['...'] doesn't run the SQL. The SQL is not run until the reuslts.all call later. You can see that by the logger output, which shows when the DROP TABLE statement is issued.

[leenyburger]

Yes, I see it now! Thanks!