allow SESSION_READ_ONLY open a DBI book without write privileges

[array_hourly_0u at icloud.com]

Hi John,

> How about leveraging the database’s locking? Wrap all of the queries with dbi_conn_transaction_begin and dbi_conn_transaction_commit. That will make multi-table writes atomic with an exclusive lock, blocking a reader from reading them until the whole write is complete, while a read query while loading will block the exclusive write lock until the read is complete.

That's a helpful reframe. I agree that leaning on the database's own
transaction handling is a better foundation than making a read-only
session write a row to the gnclock table and complicate the session
mode semantics. That's the direction I'm going to research and design
a PR around.

I don't have the full design nailed down yet, but the shape I'm taking
from your suggestion:

- Bracket the whole book load in a single transaction, so a read gets
a consistent snapshot — this looks like a small, self-contained first
step.
- Wrap a logical edit (a transaction and its splits) in one
transaction, so the multi-object write commits atomically instead of
the current behavior of per-instance commits.

I want to work through the details before proposing anything concrete.
I see friction points around rollback across a multi-instance edit,
and how the retry/reconnect path interacts with a longer-lived
transaction. I think I'll enjoy working on this; it seems like the
right direction. I'll follow up on the list once I have a design worth
reviewing.


> Please always copy one of the mailing lists when you email one of the GnuCash developers unless it’s to send something like a GnuCash book; in that case the email should just have the file, all of the discussion around it should be copied to the list.

I thought I got it right that time;  I see my message landed on the
list archives.  Perhaps the icloud email address obfuscation will be
less confusing if I reply only to the list.


Cheers,

Noah


On Sat, Jul 11, 2026 at 4:18 PM John Ralls
<jralls_at_ceridwen_us_pgk183pwhb49kj_sc4m4741 at icloud.com> wrote:
>
> Noah,
>
> Please always copy one of the mailing lists when you email one of the GnuCash developers unless it’s to send something like a GnuCash book; in that case the email should just have the file, all of the discussion around it should be copied to the list.
>
> I’ve passed on to Derek via IRC the Doxygen-docs problem just in case he misses it here.
>
> Yup, readonly-mode is largely cosmetic and applied only to the GUI. GnuCash is not designed for use by more than one user at a time and read-only mode is very simplistic.
>
> I don’t think it would be too hard to extend its force to the backend so that a read-only session is blocked from writing. But if you want a read-only session to lock the database then it’s not really read-only anymore.
>
> How about leveraging the database’s locking? Wrap all of the queries with dbi_conn_transaction_begin and dbi_conn_transaction_commit. That will make multi-table writes atomic with an exclusive lock, blocking a reader from reading them until the whole write is complete, while a read query while loading will block the exclusive write lock until the read is complete.
>
> Regards,
> John Ralls
>
>
>
>
> > On Jul 11, 2026, at 12:24 PM, array_hourly_0u at icloud.com wrote:
> >
> > Thanks for merging #2264 and #2266 John.
> >
> > Arrg.  As I've started to poke more into this read-only and lock
> > topic, I've found some surprises and ultimately a correctness matter.
> >
> > What SESSION_READ_ONLY actually means
> > ------------------------------------------------------------
> >
> > 1. At the engine level, SESSION_READ_ONLY doesn't actually enforce
> > read-only. The mode only affects lock handling -- it does not mark the
> > book read-only. Book write-protection is applied separately by the GUI
> > only, which calls qof_book_mark_readonly() after opening. I confirmed
> > with an API bindings test that a SESSION_READ_ONLY session can still
> > edit an account and have the change persisted to the SQL store. So an
> > API/bindings consumer that opens SESSION_READ_ONLY expecting the book
> > to be unwritable does not get that guarantee unless it also calls
> > qof_book_mark_readonly() itself. [a bug if one interprets
> > SESSION_READ_ONLY reading the enum name while not carefully parsing
> > the header comments ]
> >
> > 2.  SESSION_READ_ONLY ignores the lock. A read-only consumer opens a
> > book even while another process holds it open, perhaps mid-write,
> > which is exactly when the state can be inconsistent; and because the
> > SQL backend commits incrementally, a reader can catch a
> > partially-applied edit. [ a read-only client correctness bug ]
> >
> > So today the mode neither protects read consistency (it ignores the
> > lock) nor guarantees read-only-ness (that's a GUI add-on), even though
> > the name implies both.
> >
> > Proposed path forward
> > -----------------------------------
> >
> > For building read-only tools on the API, what I'd want is a read-only
> > session mode that (a) takes the lock, so a writer cannot be active
> > while the tool reads, and (b) actually enforces no writes.
> >
> > A possible direction would be to replace SESSION_READ_ONLY with two
> > modes whose names state what they do, and have both mark the book
> > read-only so enforcement is intrinsic rather than GUI-only:
> > - SESSION_READ_ONLY_EXCLUSIVE -- takes the lock (fails if the book is
> > already locked); for tools that need a consistent, uninterrupted read.
> > Calls qof_book_mark_readonly(). Obviously a SQL backend user would
> > need sufficient permissions to take the lock.  I'd want to add some
> > user guide documentation about this where none exists today.
> > - SESSION_READ_ONLY_IGNORE_LOCK -- opens regardless of the lock state;
> > the path the GUI's "open read-only anyway" override would use. (i.e.
> > today's SESSION_READ_ONLY behavior, but with qof_book_mark_readonly()
> > )
> >
> > These could be added now, with SESSION_READ_ONLY marked deprecated,
> > and SESSION_READ_ONLY removed in a later release (v6? whatever that
> > timeframe turns out to be).  I believe the deprecation warning would
> > only apply to users of the API `SESSION_READ_ONLY` mode today. (The
> > GUI internal use would be swapped to SESSION_READ_ONLY_IGNORE_LOCK.)"
> >
> > Does folding read-only enforcement into the mode (so bindings
> > consumers get it too), plus splitting the lock behavior, sound like an
> > OK path?
> >
> > Aside,  is it a known issue Doxygen isn't updating / is stale? (title
> > says "GnuCash  5.6-150-g038405b370+" )
> > https://code.gnucash.org/docs/STABLE/qofsession_8h.html
> > I was initially looking for the enum descriptions but didn't find them
> > outside of the code.  Double btw:  There is a documentation bug in the
> > source here in that the five /** … */ comments need to be trailing
> > /**< … */ comments (Doxygen trailing-member syntax).  Right now, when
> > the comments are rendered, they are offset by one.
> >
> > Regards,
> >
> > Noah
> >
> >
> > On Sat, Jul 11, 2026 at 8:47 AM Noah <> wrote:
> >>
> >>
> >> Thanks, John.
> >>
> >>> Your premise that the database was necessarily written by a GnuCash session is false.
> >>
> >> You're right, I was biasing by my own use principles.
> >> I never want to modify my production book through anything but the GnuCash GUI or the official GnuCash API / bindings.
> >>
> >> My household moved our finances to GnuCash 14 years ago motivated by two challenges.
> >> - need to support two users on different computers (in serial, not simultaneously). We were bit too many times with filesystem-based commercial options at the time.
> >> - a Q* commercial software was dropping support for Mac, or non-cloud, or both
> >>
> >> In more recent years, our financial institutions have been making it harder to get our transaction data into GnuCash (eg. removing OFX/QFX download)
> >> In order to hold the line at home, I've been doing more and more work adopting use of the  API & Python bindings to help a lot in this regard.  I've also been creating some reporting and exploration tools that don't need write access (and tinkering with other's work like piecash). Since they don’t need write access, I want them to access MySQL with the minimum privileges necessary.
> >>
> >>
> >>> The ffastmath problem underlying bug 611936 was on the write side of libdii so a read-only test wouldn’t find the problem.
> >>
> >> That settles it for me: since the -ffast-math defect in #611936 is on the write side of libdbi, a read-only session can neither trigger nor detect it, so running the probe on a read-only open guards nothing. I've opened a PR that skips dbi_library_test() for SESSION_READ_ONLY and leaves all write-capable modes untouched:
> >> https://github.com/Gnucash/gnucash/pull/2266
> >>
> >> I'm not trying to bolt access control onto GnuCash, and I agree that wouldn't end well. The narrower goal is just to let a read-only consumer honor a least-privilege credential a DBA may already have in place -- and, independently, to stop running a write-only probe on a read-only open. The PR stands on that second part alone.
> >>
> >> Thanks again,
> >> Noah
> >>
> >>
> >>
> >> On Thu, Jul 9, 2026 at 8:31 PM John Ralls <jralls_at_ceridwen_us_pgk183pwhb49kj_sc4m4741 at icloud.com> wrote:
> >>>
> >>>
> >>>> On Jul 9, 2026, at 14:34, array_hourly_0u at icloud.com wrote:
> >>>> Hello again,
> >>>> Obviously backend stuff is on my mind lately and there's been a
> >>>> quirk impacting read-only access to SQL back-ed books that has
> >>>> bothered me for a while.
> >>>> Opening a book with SESSION_READ_ONLY through the SQL/DBI backend
> >>>> still requires the database user to have permission to create a
> >>>> temporary table. On every session_begin (regardless of mode),
> >>>> dbi_library_test() runs the 64-bit round-trip guard from Bug #611936
> >>>> -- CREATE TEMPORARY TABLE / INSERT / SELECT / DROP -- to detect a
> >>>> 15-year-old -ffast-math libdbi miscompilation. A least-privileged,
> >>>> SELECT-only database user therefore cannot open the book at all: the
> >>>> create fails and begin returns ERR_SQL_DBI_UNTESTABLE.
> >>>> (SESSION_READ_ONLY already skips the gnclock lock, so this probe is
> >>>> the only remaining reason a read-only open needs write-ish rights.)
> >>>> I'd like to propose making a read-only open work without those
> >>>> privileges. Two possible directions:
> >>>> 1. Skip dbi_library_test() when the session is opened SESSION_READ_ONLY.
> >>>> 2. Or, for read-only, replace it with a check that needs no write
> >>>> privileges -- for example, verify the driver retrieves large
> >>>> int64/uint64 and a high-precision FLOAT8 via a SELECT of
> >>>> literals/casts (dbi_result_get_longlong / get_ulonglong / get_double),
> >>>> so the reader's conversion path is still validated. I'd welcome
> >>>> suggestions on the best minimal form.
> >>>> The justification for either approach: the data already in the
> >>>> database was written by a GnuCash session, and dbi_library_test() ran
> >>>> for that session, so any book that was successfully saved was
> >>>> necessarily saved by a driver that passed the round-trip test -- the
> >>>> stored 64-bit and FLOAT8 values are therefore correct at rest. A
> >>>> read-only session never writes back, so the only thing left to guard
> >>>> against is a reader-side retrieval defect. For a modern 64-bit libdbi
> >>>> that is the 2011, 32-bit / -ffast-math case from #611936, which is
> >>>> essentially historical (or maybe never existed on the read)
> >>>> If there is a preferred direction, I'm happy to prepare a PR.
> >>>
> >>> Your premise that the database was necessarily written by a GnuCash session is false. Anybody with db write access (and for a SQLite3 database that means anyone with write access to the filesystem) can write (or delete) whatever they want into the database. There is at least one public Python library that reads and writes GnuCash SQLite3 files (https://github.com/sdementen/piecash). The databases have no integrity checks enabled: Any integrity checking (and it’s pretty minimal) is in GnuCash’s engine.
> >>>
> >>> The ffastmath problem underlying bug 611936 was on the write side of libdii so a read-only test wouldn’t find the problem. I think it unlikely that anyone is using that version of gcc anymore so it’s probably safe to remove the test.
> >>>
> >>> That said, I have my doubts about the value of having users without database write access. Access control hasn’t ever been a consideration in GnuCash’s design and trying to bolt it on 30 years later isn’t likely to produce a satisfactory result.
> >>>
> >>> Regards,
> >>> John Ralls
>