CSP has been an inconsistent, ever growing standard, with a history of implementation breakages. I think only CSP 3.0, and a long WIP safe types standard have approached the level of being actually useful.

I saw websites with a screen long CSP header to work around standard's inconsistencies like frame-src/child-src, and form redirects. I think it's only a matter of time when somebody will find a chink in that weak armour.

The lack of sane by-default policy makes you vulnerable the moment attacker finds a way to redirect a browser to a non-CSP protected page on your domain. Mitigations exist, but... many buts.

Reflection attacks, when attacker injects your own hashed scripts, and data exfiltrating css on pages where they shouldn't be are still possible in various scenarios unless you inline all scripts, and set CSP per every URL.

Other data exfiltration sources, and injection sinks keep being found few times a year.

CSP is a paint over rust.