Support for Postgresql as configuration store

[akhilac1]

Description

Added Support for Postgres as configuration store

Issue reference

We strive to have all PR being opened based on an issue, where the problem or feature have been discussed prior to implementation.

related to #4551

Checklist

Please make sure you've completed the relevant tasks for this PR, out of the following list:

• [X] Code compiles correctly
• [X] Created/updated tests
• [X] Extended the documentation / Created issue in the https://github.com/dapr/docs/ repo: dapr/docs#2779

[akhilac1]

@ItalyPaleAle added non-alphanumeric character check and prepared statements to buildQuery

[ItalyPaleAle]

What is the statement to create the table, by the way?

[akhilac1]

@ItalyPaleAle - configuration API does not support creation on config table/triggers. So these are external pre-requisites until Admin API is supported. The steps to setup postgres as configuration store can be referenced in https://github.com/dapr/docs/pull/2800.

Regarding Prepared statement - We donot pre-define the prepared statements as Users can query on any number of keys and/or metadata attributes. A sample generated query to GET configuration data based on Key and Metadata would be - "SELECT * FROM cfgtbl WHERE KEY IN ($1) AND $2 = $3".

pgxPool.Pool.Query builds a prepared statement and caches it. Every time the statement that is already cached is used, pgx uses the prepared statement from the cache.

[ItalyPaleAle]

Regarding Prepared statement - We donot pre-define the prepared statements as Users can query on any number of keys and/or metadata attributes. A sample generated query to GET configuration data based on Key and Metadata would be - "SELECT * FROM cfgtbl WHERE KEY IN ($1) AND $2 = $3".

pgxPool.Pool.Query builds a prepared statement and caches it. Every time the statement that is already cached is used, pgx uses the prepared statement from the cache.

Using $1 etc is using prepared statements: in this case, the query doesn't contain the exact values, but they are only referenced like you're doing there. Prepared statements are needed to avoid SQL injections

[akhilac1]

@ItalyPaleAle - closed comments. FYI Please

[akhilac1]

Regarding Prepared statement - We donot pre-define the prepared statements as Users can query on any number of keys and/or metadata attributes. A sample generated query to GET configuration data based on Key and Metadata would be - "SELECT * FROM cfgtbl WHERE KEY IN ($1) AND $2 = $3". pgxPool.Pool.Query builds a prepared statement and caches it. Every time the statement that is already cached is used, pgx uses the prepared statement from the cache.

Using $1 etc is using prepared statements: in this case, the query doesn't contain the exact values, but they are only referenced like you're doing there. Prepared statements are needed to avoid SQL injections

@ItalyPaleAle - We cannot pre define prepared statements as there is no cap on number of keys that can be queried for and/if metadata is provided. So we build the query and depend on the functionality of pgxpool library to convert these to prepared statements. SQL Injection scenarios are addressed via input validation.

Can you please clarify on where you see SQL Injection possibility?

[codecov[bot]]

Codecov Report

Merging #2063 (6a36ee6) into master (ea21957) will decrease coverage by 0.34%. The diff coverage is 12.50%.

@@            Coverage Diff             @@
##           master    #2063      +/-   ##
==========================================
- Coverage   38.45%   38.11%   -0.35%     
==========================================
  Files         193      194       +1     
  Lines       24233    24521     +288     
==========================================
+ Hits         9319     9346      +27     
- Misses      14132    14393     +261     
  Partials      782      782              

Impacted Files	Coverage Δ
configuration/postgres/postgres.go	12.50% <12.50%> (ø)
state/in-memory/in_memory.go	42.58% <0.00%> (-3.43%)	:arrow_down:

Help us with your feedback. Take ten seconds to tell us how you rate us. Have a feature suggestion? Share it here.

[akhilac1]

What is the statement to create the table, by the way?

https://github.com/dapr/docs/blob/357ffcf6dd53d5736461c7e63050da5917aa6bbf/daprdocs/content/en/reference/components-reference/supported-configuration-stores/postgres-configuration-store.md

[akhilac1]

@mukundansundar - can you please check the open clarifications and comment if this PR is good to merge? Please note that these are not code fixes but clarifications only.

@artursouza - FYI Please

[akhilac1]

@mukundansundar - closed comments. can you please check

[akhilac1]

https://github.com/dapr/dapr/issues/5236 - PR to track panic in runtime

[ItalyPaleAle]

LGTM!

[berndverst]

@akhilac1 please create documentation if you haven't already

[akhilac1]

@akhilac1 please create documentation if you haven't already

@berndverst - https://github.com/dapr/docs/issues/2779