postgres: create dynamic metadata

[cpakulski]

Create dynamic metadata when parsing Postgres messages (similar to mySQL). The metadata will indicate an operation (create, delete, insert, etc) and resource on which the operation will be performed.

The metadata may be used by RBAC or routing of queries to specific instances of Postgres based on operation type.

Ref: https://www.envoyproxy.io/docs/envoy/latest/configuration/advanced/well_known_dynamic_metadata

[cpakulski]

Please assign it to me.

[ahachete]

This would be very helpful.

What kind of metadata do you plan to expose, do you have an initial design for it? Happy to provide feedback if that could help :)

[cpakulski]

As the first step I was thinking about producing the same metadata as mySQL: https://www.envoyproxy.io/docs/envoy/latest/configuration/listeners/network_filters/mysql_proxy_filter#config-network-filters-mysql-proxy-dynamic-metadata.

[ahachete]

I checked that, and left me with some doubts. For example:

• <table.db>. What if the operation references more than one table, like in a JOIN query, subselect or CTE? Maybe should be an array?
• [] (A list of strings representing the operations executed on the resource. Operations can be one of insert/update/select/drop/delete/create/alter/show). We should determine which operations apply to Postgres. Actually, as we found with the monitoring statistics, there might be combined operations (for example a CTE that reads data and then inserts, how would you categorize that?).

Moreover, how do you plan to parse, with the SQL parser already available in Envoy? It would be interesting to see how it would handle Postgres queries, but any work in this direction will be extremely helpful for this and other future goals that require SQL parsing.

[cpakulski]

Moreover, how do you plan to parse, with the SQL parser already available in Envoy? It would be interesting to see how it would handle Postgres queries, but any work in this direction will be extremely helpful for this and other future goals that require SQL parsing.

Yes, that was the idea - to start with SQL parser available in Envoy and extend it when required.

<table.db>. What if the operation references more than one table, like in a JOIN query, subselect or CTE? Maybe should be an array?
[] (A list of strings representing the operations executed on the resource. Operations can be one of insert/update/select/drop/delete/create/alter/show). We should determine which operations apply to Postgres. Actually, as we found with the monitoring statistics, there might be combined operations (for example a CTE that reads data and then inserts, how would you categorize that?).

Very valid points. I suggest that we join forces and work on document summarizing use cases and describing the logic how to handle non-trivial queries.

[ahachete]

Yes, perfect. Feel free to create and share a document, we can work there.

[cpakulski]

/assign @cpakulski

[stale]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. It will be closed in the next 7 days unless it is tagged "help wanted" or other activity occurs. Thank you for your contributions.

[cpakulski]

Work in progress.