allow SESSION_READ_ONLY open a DBI book without write privileges

[array_hourly_0u at icloud.com]

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