Hi HN!
My name is Sven, I am an engineer at Timescale and this is my fun side project. The main purpose of pgspot is to check postgres extension sql scripts for vulnerabilities but it can also be used to check functions outside of extension context e.g. security definer functions.
I've started working on this after finding vulnerabilities in timescaledb to ensure similar vulnerabilities don't reappear.
While working on this i found vulnerabilities in over 30 extensions and some of the findings even lead to postgres upstream changes to reduce attack surface of extension scripts ( https://www.postgresql.org/support/security/CVE-2022-2625/ ).
pgspot is implemented in python and uses libpg_query to parse sql and analyzes the AST to find vulnerable patterns.