fix(postgres): check and enable standard conforming strings when required

[sushantdhiman]

Pull Request check-list

Please make sure to review and check all of these items:

• Does npm run test or npm run test-DIALECT pass with this change (including linting)?
• Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
• Have you added new tests to prevent regressions?
• Is a documentation update included (if this change modifies existing APIs, or introduces new ones)?
• Did you follow the commit message conventions explained in CONTRIBUTING.md?

Description of change

1. This enable sequelize to set standard_conforming_strings = on whenever they are disabled regardless of postgres version. This fixes a security issue.

2. It saves one extra query for postgres version per connection, which is available with parameterStatus as server_version

[codecov]

Codecov Report

Merging #10746 into master will increase coverage by 0.01%.
The diff coverage is 100%.

@@            Coverage Diff             @@
##           master   #10746      +/-   ##
==========================================
+ Coverage   96.31%   96.33%   +0.01%     
==========================================
  Files          93       93              
  Lines        8980     8993      +13     
==========================================
+ Hits         8649     8663      +14     
+ Misses        331      330       -1

Impacted Files	Coverage Δ
lib/dialects/abstract/connection-manager.js	90.26% <100%> (+0.08%)	⬆️
lib/dialects/postgres/connection-manager.js	95.77% <100%> (+1.15%)	⬆️

---

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 7c3c18a...6f8a0c4. Read the comment docs.

[SimonSchick]

Can we test this?

[maxdos64]

This appears to fix the security issue

[sushantdhiman]

Thanks @maxdos64 for reporting this issue

[sushantdhiman]

🎉 This PR is included in version 5.3.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[ragibsmajic]

"error":{"message":"unrecognized configuration parameter "standard_conforming_strings"","stack":"error: unrecognized configuration parameter "standard_conforming_strings"\n

After this update I'm getting this error when using sequelize with Amazon Redshift.
Also, node process is stucked after this error.

[gabegorelick]

Is there a write up of the security issue?

[jhoch]

Will a new v4 release be released that includes this security fix?

[maxdos64]

There is a cve connected to the issue:
CVE-2019-11069

[gabegorelick]

@maxdos64 The CVE is light on details (all I see is "Sequelize before 5.3.0 does not properly ensure that standard conforming strings are used.").

Is this only an issue if standard_conforming_strings is disabled?

[younanjo]

will you be patching the v4 with the security fix?

[sushantdhiman]

v4 and v3 are not affected, someone reported that CVE incorrectly. I have asked for an update in CVE, waiting for reply from MITRE

[sushantdhiman]

This issue only affects Postgres with standard_conforming_strings = off, which I assume is on by default for most versions.

[maxdos64]

@gabegorelick I am unsure if it is ok to publish the PoC in this thread.
@sushantdhiman what would you say ?

[sushantdhiman]

@maxdos64 I see no problem with that as the patch is already out

[maxdos64]

Alright :)
Here is the PoC i used to confirm the issue:
https://drive.google.com/file/d/16SeKwKPvzrOMd0Apgnn-qapxMs87XIQP/view?usp=sharing

If you have more questions please don't hesitate to email me at maximilian.tschirschnitz@gmail.com

[yotamofek]

@sushantdhiman Seems like the version range in the CVE was fixed: https://nvd.nist.gov/vuln/detail/CVE-2019-11069#VulnChangeHistorySection