Custom auxiliary views/triggers in the repository
(1) By george on 2020-02-28 13:04:01 [link] [source]
Hello!
TL;DR: Please explain why these three lines?
Perhaps it's worth adding a comment into the source code.
I would like to create auxiliary database view(s) that facilitate writing of multiple ticket reports (avoiding the repeatition of SQL codeblocks).
It seems that Ticket Table Schema allows broad adjustment of SQL statements.
As far as I understand, report_query_authorizer() is used to enforce
access rights of a (user-supplied) report's query.
It only permits reading of the few tables,
but also contains these three lines.
Is it safe to create arbitrary views/tables/triggers/indexes
(avoiding name clashes)?
Do SQL-statements that are entered at /tktsetup_tab
execute at the repository's clones?
Is it enough to name a supplementary view with "fx_" prefix to make it
accessible for report queries?
Is there any flaw in the idea concerned?
(2) By Stephan Beal (stephan) on 2020-02-28 13:13:39 in reply to 1 [link] [source]
TL;DR: Please explain why these three lines?
That's a blast from the past. A very long time ago (2012? 13?) several of us discussed conventions for names of tables created inside a repo by client-side applications, the context being that we wanted a way to ensure that fossil would not destroy those tables during its normal goings-on (like during a rebuild). The result of the discussion was that such tables should have an fx_ name prefix (Fossil eXtension).
Perhaps it's worth adding a comment into the source code.
i dunno - the reason for that particular pattern isn't really important to that code, and what it's doing seems clear at a glance: if it sees a table named fx_something, it skips the authorizer check (that is, it allows access).
fossil blame says it was 2013. The other reference to that convention is in rebuild.c:
https://fossil-scm.org/fossil/artifact?udc=1&ln=396&name=c09d72ba95c62bb3
That said... to the best of my knowledge, other than some early test/prototype code for libfossil, no software has ever actually taken advantage of that.
(3) By Stephan Beal (stephan) on 2020-02-28 13:22:58 in reply to 1 [source]
And to try to answer your other questions:
Is it safe to create arbitrary views/tables/triggers/indexes (avoiding name clashes)?
That was the intent of the fx_ prefix. Those tables will not be synced across repositories, but fossil will not outright destroy them when doing a rebuild.
Do SQL-statements that are entered at /tktsetup_tab execute at the repository's clones?
i'm not familiar enough with the ticketing system to answer that.
Is it enough to name a supplementary view with "fx_" prefix to make it accessible for report queries?
It looks like that's the case, but i've never tried it. Back when we discussed the idea, we figured that access to client-custom tables should not be inherently restricted because fossil, by itself, would not be providing any users access to those tables (so fossil could not (by itself) accidentally leak them to unauthorized users). A user who has permission to install SQL and create such tables is assumed to know what they're doing and the implications of doing so.
Is there any flaw in the idea concerned?
If you mean security flaw, then in my humble opinion, no. Unless, of course, every user has access to install SQL and your fx_ tables contain sensitive data.
If you mean a flaw in your thinking about how they can be used, i believe you have understood it properly.
(4) By Andreas Kupries (aku) on 2020-02-28 17:50:17 in reply to 2 [link] [source]
Not quite true.
There is fx ;)