What a Content-Security-Policy header actually does
A CSP is a list of rules, called directives, that describe permitted sources for different kinds of resources: scripts, stylesheets, images, fonts, frames and connections. When the browser is asked to load something the policy does not allow, it refuses and logs the refusal in the console.
The practical effect is that a script injected by an attacker often cannot run or cannot send data anywhere useful, because it does not come from a source the policy permits. That is meaningful risk reduction, but it depends entirely on how well the policy reflects your site.
