81015[BUGFIX] Provide CSP ReportRepository ONLY_FULL_GROUP_BY support
Commit message
Review
-
Code review CR +2 Done
- Oliver Hader +2
- Garvin Hicking +1
- Stefan Bürk +1
-
Verified V +2 Done
- Oliver Hader +2
- Garvin Hicking +1
- Stefan Bürk +1
- core-ci +1 CI
Issues 1
Files 4
- typo3/sysext/core/Classes/Security/ContentSecurityPolicy/Reporting/ReportRepository.php3+118 −27
- typo3/sysext/core/Tests/Functional/Security/ContentSecurityPolicy/Reporting/Fixtures/sys_http_report.csvnew+6 −0
- typo3/sysext/core/Tests/Functional/Security/ContentSecurityPolicy/Reporting/ReportRepositoryTest.phpnew+38 −0
- typo3/sysext/core/ext_tables.sql+3 −1
Review log 3340
Show 1115 earlier messages
-
Nikita Hovratov uploaded patch set 1 09/13/23, 22:07
-
core-ci voted on patch set 1 V -1 09/13/23, 22:16 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46552
-
Nikita Hovratov commented on patch set 1 09/13/23, 22:23
Hey, I added a testcase which shows the failing SQL statement. Maybe someone can pick this up and make it right. -
Stefan Bürk uploaded patch set 2
V -109/14/23, 11:20 -
core-ci voted on patch set 2 V +1 09/14/23, 11:27 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46575
-
Stefan Bürk rebased onto patch set 3
V +109/20/23, 15:51 -
core-ci voted on patch set 3 V +1 09/20/23, 16:00 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46913
-
Stefan Bürk rebased onto patch set 4
V +109/20/23, 19:38 -
Stefan Bürk commented on patch set 4 09/20/23, 19:39
Note: I took over the autorship of this change because I added the needed code to fix the issue. I follow a suggestion from Nikita with that, as he pushed only the test in the first place. -
core-ci voted on patch set 4 V +1 09/20/23, 19:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46923
-
Oliver Klee voted on patch set 4 V +1 09/20/23, 20:11
-
Garvin Hicking commented on patch set 4 09/20/23, 21:39
(adding myself to CC as I really would like to understand the original query intent. Dry-Read the code three times now and still don't fully grasp what actually should be fetched, and if maybe instead of a sub-sub-query the data retrieval could be split up to make it maintainable/understandable in the future for old-us? Don't mind my writing here, this just sparked my interest) -
Garvin Hicking commented on patch set 4 09/21/23, 11:14
Thinking out loud: The way I read the initial issue https://forge.typo3.org/issues/101913#change-501542 it seems to me that the idea behind the current query is to "just" get a list of any UUID with the condition `("type" = $1) AND ("status" = $2)` and then only return one UUID for each group of items where the summary is the same. So I believe two other choices might be possible: * As reported in the issue, actually remove the "GROUP BY". This will yield a larger subquery result set, but probably perform better because the DB doesn't need to "fold" data. * Since the current logic doesn't really care about which actual UUID is returned in case the summary is the same, we could actually use a RANDOM(uuid) aggregate here, as it relates to any random uuid within the same-summary resultset. The later code of the CSP backend module seems to take care of presenting other uuid occurrences from the same 'summary' collection...? The outer select actually sorts the final result data by 'created', so sorting it inside the subquery too (and creating aggregate trouble) wouldn't be needed, too...? -
Stefan Bürk commented on patch set 4 09/21/23, 12:09
Reply
I want to review and rework the whole queries later, but we need a "bugfix" for now. Each record has basicilly two kind of uuid's: * `uuid`: unique identifier per record * `summary`: a uuid (hash) based on some data - or so to say the `group identifier` The point is, to display records with applied filter, but as a summerized view. That means, that for the displayed range only "one" record per `summary` group should be display. That means, we need to group the data based on the summary identifier, and get "one" uuid out of a summery group pool to display the record later on. Removing the group would show "all" records - which would violate a summerized view. The point is to get "one" entry per "type/kind" and get a count for it. That makes the "display" data smaller. Writing this, we could talk about another approach - but have to talk with Oliver Hader about it: We could "simply" display all entries but have a count per sumery for all of them. However, that may be another change. At least, the current solution would keep the current behaviour but fix it for non mysql datenbank systems. -
Garvin Hicking commented on patch set 4 09/21/23, 12:14
Reply
Thanks for the writeup. Could the picked uuid of a grouped set be random? Then RANDOM() could work, without the need of sub-sub-queries? I now better understand the remark about non-integer uuids. For an integer autoincrement, the aggregate function MAX() could've been used to show the most recent one.
-
Stefan Bürk commented on patch set 4 09/22/23, 15:46
Reply
Nope. Random() generates a integer value. Still, we need to aggreate records based on a group value (`summary`) and then retrieve one `uuid` per group. The list displays records where the `uuid` are these "selected" uuids in the range. On top of that, for each group (`summary`) a counting is executed to show the number in the list but have the details for one record specified by the `uuid`. That's the current overall state of the summerized view. The problem here comes with cross database vendor support and possible settings, default onces, wildy provided by hosting providers and so on. To archieve the `group on summary but retrieve only one uuid per summary group` we have the problem, because the group field needs to be in the `SELECT <fields>` list AND the `uuid` field needs to be in the `GROUP BY` list for some dbms vendors and settings. Without a aggrate function, this is not possible. Sadly, there is no suituable aggregation method for a field value like a string based `uuid` value. There may be multiple variants to set this up, but all needs the one or other subquery. Relational databases are not build for these kind of "analyctics" aggregation views. Point. We can have a deep talk at some time to discuss this in gernerall. However, to fix the currently existing issue for non-mysql/non-mariadb the provided solution is the one to go. We may rethink the whole view and data visualization in a dedicated step, but for now we need a fix for the next relase as this is fully broken for `PostgresSQL` for example. Even that, subqueries are not evil per default even if the spread opionen hits into that mind set. I can tell you, that I have created solutions with multiple nested subquerie layers which performed really complex data searches in a way you would never reach even nearly without them. But that's another topic why reducing 10k of single database queries with one multi layer subquery would performe drastically quicker and faster. You are right, for some cases subqueries may be replaced by faster solutions, but that's not always possible - like in this case. At least not across all dbms vendors and possible server configuration settings. -
Stefan Bürk updated the commit message in patch set 5
V +109/22/23, 17:30 -
core-ci voted on patch set 5 V +1 09/22/23, 17:42 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47099
-
Garvin Hicking commented on patch set 5 09/23/23, 08:22
Reply
(Thanks for your time and explanation, again) If I understand correctly, it wouldn't matter which uuid is fetched in the sub-query, right? Maybe then using MAX()/MIN() does work, which works on strings too. I tried this in MySQL, PostgreSQL and SQLIte for a test: create table scientist (id integer, firstname varchar(100), lastname varchar(100), sciencetype varchar(20)); insert into scientist (id, firstname, lastname, sciencetype) values (1, 'albert', 'einstein', 'energy'); insert into scientist (id, firstname, lastname, sciencetype) values (2, 'isaac', 'newton', 'energy'); insert into scientist (id, firstname, lastname, sciencetype) values (3, 'marie', 'curie', 'radiation'); insert into scientist (id, firstname, lastname, sciencetype) values (4, 'no', 'body', 'radiation'); select sciencetype, max(firstname) from scientist group by sciencetype; Wasn't able to whip up a specific sys_http_report query to match this data, but that query above would be adapted to something like: select summary, max(uuid) from sys_http_report group by summary I can try to test that implementation later next week, but of course I am just scratching the surface now and want to reduce complexity - please for a bugfix don't mind my writing here and proceed as you/the team sees fit, it can always be adressed later. -
Stefan Bürk voted on patch set 5 CR -1 09/23/23, 10:24
-
Oliver Hader commented on patch set 5 10/06/23, 08:18
Reply
Similar reports are identified by having the same `summary` hash - they just are stored again and again at a different time. The query should return the most recent item - one item - (ordered by `created`) that is in a group with the same `summary` hash. -
Stefan Bürk commented on patch set 5 10/06/23, 08:40
Reply
@Oliver Are there plans to enhance the be module in the future ? * filter options / sorting options / ranging options * pagination So that it's not only "created" but may be supporting any demand things / settings to get the correct "recent/top" entry per choosen selection ? I guess, the current implementation has been choosen to have the "detail" info diretly at hand avoiding "ajax requests" for detail retrievment if clicked - correct ? -
Oliver Hader commented on patch set 5 10/06/23, 08:59
Reply
* see `ReportDemand` class * yes for filter per scope, summary, after a given request time (might also become a data range [from/to]) * probably yes for "pagination" but in terms of "loading chunks" * the `details` value is used since it contains specifics that are shown in the list already (e.g. `script-src` as the violation (`details.effectiveDirective`) or `https://whatever.org/file.js` as affected URI (`details.documentUri`)) * in case the payload for the list is getting too big, there might be a reduced set of data (reduced by the controller by only keeping the relevant properties for the corresponding action) I don't have much specific plans for the backend module, that highly depends on how people are using it, what kind of data and how many of it will be visualized there and what kind of CSP-specific violations need to be resolved. There is a patch that extends the UI in the backend module, which is pending since March/April 2023 since it lacks UX guidance/concept, https://review.typo3.org/c/Packages/TYPO3.CMS/+/78250 -
Stefan Bürk commented on patch set 5 10/06/23, 09:19
Reply
Thanks for the insight and answers. Will rework this patch/bugfix than slightly and finish it. -
Oliver Hader commented on patch set 5 10/06/23, 22:16
I think the query could be written as materialized join (CTE might work as well), like this (omitted additional conditions): ``` SELECT report.* FROM sys_http_report report JOIN ( SELECT MAX(created) AS aggregate_created, summary FROM sys_http_report WHERE type = 'csp-report' AND status = 0 GROUP BY summary ) summarized ON report.summary = summarized.summary AND report.created = summarized.aggregate_created ORDER BY report.created DESC; ``` When playing around with the data, I realized that lots of indexes are missing - `summary_created` is enhances the temp. table that is joined, `conditions` contains potential filters (a value for `type` is always given, the rest is optional). ``` CREATE INDEX summary_created ON sys_http_report (summary,created); CREATE INDEX conditions ON sys_http_report (type,status,scope,summary,request_time); ``` In case you don't mind, I'd be working on adjusting the patch a bit. And it's great to have tests for the repository, to verify the PostgreSQL issue is solved. -
Oliver Hader uploaded patch set 6
CR -1V +110/08/23, 18:28 -
Oliver Hader commented on patch set 6 10/08/23, 18:29
Reply
Concerning indexes... before: ``` -> Sort row IDs: report.created DESC (actual time=2.82..2.83 rows=77 loops=1) -> Table scan on <temporary> (cost=35.7..38 rows=7.83) (actual time=2.76..2.79 rows=77 loops=1) -> Temporary table (cost=35.4..35.4 rows=7.83) (actual time=2.76..2.76 rows=77 loops=1) ``` after: ``` -> Sort row IDs: report.created DESC (actual time=2.24..2.25 rows=77 loops=1) -> Table scan on <temporary> (cost=225..233 rows=397) (actual time=2.19..2.21 rows=77 loops=1) -> Temporary table (cost=225..225 rows=397) (actual time=2.19..2.19 rows=77 loops=1) ``` The predicted costs went up, however the actual times went down a bit `2.82 ms → 2.24 ms`, tested with 500 records)The query now looks like this: ``` SELECT `report`.* FROM `sys_http_report` `report` INNER JOIN ( SELECT ANY_VALUE(`tab_uuid`.`uuid`) AS `uuid` FROM `sys_http_report` `tab_uuid` INNER JOIN ( SELECT MAX(`tab_summary`.`created`) AS `created`, summary FROM `sys_http_report` `tab_summary` GROUP BY `summary` ) `res_summary` ON ((`tab_uuid`.`summary` = res_summary.summary) AND (`tab_uuid`.`created` = res_summary.created)) GROUP BY `tab_uuid`.`summary` ) `res_uuid` ON `report`.`uuid` = res_uuid.uuid WHERE (`report`.`type` = 'csp-report') AND (`report`.`status` = 0) ORDER BY `report`.`created` desc; ``` -
core-ci voted on patch set 6 V +1 10/08/23, 18:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47565
-
core-ci commented on patch set 6 10/08/23, 21:36 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47577
-
Stefan Bürk commented on patch set 6 V +1 CR +1 10/09/23, 20:35
Tested and verified the changes from Oliver. That's fine enough to have a bugfix in place. There are other thungs currently left out, so with a generic enhancement (filters, ranging etc) this can be further improved. Additionally, after getting the CTE api in place this may be replaced with a CTE then if it pay's out. Or solved in another way. However, all of these things needs quite some work and time - and I would argue that we should have a working solution first for a LTS version before working on optimiations.ReportRepository.php:65Quite cryptical comment here. Needs quite some knowledge with dbms to sort that comment out. However, fine to add it for this bugfix. We need to readdress this later with enhancements on the backend module anyway, and the rest could be picked up in the gerrit change comments.ReportRepository.php:94PostgreSQL added `ANY_VALUE()` with v16. Sadly, it would be quite early to raise supported min PostgreSQL version to that version for v13. Looking at the version policy page https://www.postgresql.org/support/versioning/ the min version to raise would be 12 or 13 .. at hightest. Would not help. `MAX()` is not really correct, but using one function accross all dbms version at least means to retreive the same result on all dbms vendors. Therefore, ack with using MAX here (for now).ReportRepository.php:304Could be considered as a QueryBuilder() addon. Keeping it here local for now is okay. Enhancing QueryBuilder and Expression Builder with additional stuff is already on the table for v13 for CTE support. Than this may be moved at that point. -
Garvin Hicking commented on patch set 6 CR +1 10/10/23, 06:21
(Couldn't verify it 100% myself yet, but quite confident the current approach is better than the existing implementation, and I'd opt for using this patch for now. Thanks for all the thoughts put into it!) -
Garvin Hicking commented on patch set 6 V +1 10/10/23, 10:32
Now I have verified it, works for me with intended results. I tried it on all RDBMS that TYPO3v11 currently supports, it yields the same query results everywhere. :-) I do believe, too, that in the future this backend should get enhanced with distinct AJAX sub-requests to make the query less complex, also in terms of possible pagination and filtering. -
Garvin Hicking commented on patch set 6 10/11/23, 10:08
Quick update on this. I still think that the current implementation yields proper results. However, there is some minor difference we should be aware of. Turns out my queryrunner hat a bug and did NOT test all DB variants properly, because it missed connecting to the right port. So I only got results for ONE of each DB system. Running it now with all backends (mysql 5.7, 8.0, 8.1 + mariadb 10.2-10.11, 11.0, 11.1 + postgres 10 - 16 + sqlite) there actually is a difference in output. For pgsql10 and pqsql11 and sqlite the returned UUID does not match the expected UUID in my testdata. It doesn't matter that much because as long as it's ANY UUID out of the resultset, but there's still a minor difference. I tested it with a dummyfixture here (part of my "queryrunner" that I used to check it with): https://github.com/garvinhicking/docker-queryrunner/blob/main/tasks/sys_http_report-core/fixture.csv -
Oliver Hader commented on patch set 6 10/11/23, 10:50
Reply
Can you summarize, what is different? In this patch there is a slight ambiguity for records that are in a partition with the same `created` timestamp and `summary` hash - any `uuid` of this dataset would be okay. -
Garvin Hicking commented on patch set 6 10/11/23, 11:33
Reply
yes, sorry for not adressing it. Thats exactly the case in my fixture. Shouldn't be a showstopper in "real-life", especially if its only happening on the two older postgresses. -
Stefan Bürk commented on patch set 6 10/11/23, 22:30
Reply
That's logical, and there is "no way" around except doing what I did in between to add a subquery retrieving a uuid without the group for a summary per record. The reason for this is the used `MAX()` aggregate function on the `uuid` field. `MAX()` is designed for number values (integer/float). For the dbms over the years that is a known fact. Sadly, all dbmns `allows` to use it on non-number values, but each vendor implements it differently how to interpate the non-number value in a number form and how to determine which is higher (and therefore the MAX value). Even further, some changed this implementation over time. There is no "FIRST" or "LAST" (semi)aggregate function in the SQL standard, nor implemented by any vendor. A missing feature for such summerize/analytic like queries. The `ANY_VALUE` at least would be a useable function for the LAST case based on the sorting and order, but as we already know - e.g. PostgreSQL add's that function with 16 which would be a quite high dbms requirement change because of the LTS timeframe for PostgreSQL itself. Therefore, the last solution from Oliver has a sharp difference for some edge case versions. Which is something we need to "live" with for now. As long as we do not use a dbms version having a flacky result this is save. If that occures we need to find a solution for the testing. From my point of view, I would suggest - if you both aggree - to take this now as a first working solution and to fix it for a LTS version. As released happend recently, we now have some "time" - leaving this at least as a possible composer patch candiate open. As a follow-up we could think about a general change for the future: * Spare the uuid from the direct result, only having a concrete summary and switch to a ajax request per detail opening. Granted, clicking through the list would be become something more delayed, but it would spare extensive query level and nestings. * After getting the CTE Api in (which will still take some time and is delayed until we know how doctrine/dbal 4 changes the system) we could make it with that solution, which allows us to work with a reusable semi-temporary table as abase and reue it in differnt levens (not speaking about recursive here) - on a query level. This would be possible for main only, but mid/long-term maybe the better solution than overall. Having the votes now, leaving it to your final decision here @Oliver how to continue with it. Take it and work with followup on further improvements with some time in the back - or directly making the splitting to the ajax detail thing as a backportable solution ? -
Stefan Bürk commented on patch set 6 10/11/23, 22:32
Reply
Forgot to write - thank you Oliver for your rework and the index/costs analyses and commenting it directly. And also thank you Garvin for putting your brain and time to test and verify this one. -
Oliver Hader voted on patch set 6 V +2 CR +2 10/16/23, 11:06
-
Oliver Hader merged patch set 7 10/16/23, 11:06
-
Oliver Hader commented on patch set 7 10/16/23, 11:08
Thx folks!