unexpectedly, that would be missed by a PL/pgSQL analysis tool... There is no miracle.
No - metadata, in my design, are persistent - like tables - so you don't calculate so any functions can drop a variables. The deployment of secure variables is same like table deployment. No dynamic there.
You are missing my point: Static analysis is about proving properties. If you need metadata to be persistent, then you should check that it is the case, i.e. the static analysis must check that there is no metadata changes anywhere. For instance, an analysis tool should reject a function which contains:
GRANT UPDATE ON VARIABLE super_secret_variable TO PUBLIC;
It doesn't need to reject this functions - but this information is not visible in any other functions. But this tasks you are do in deployment scripts - not in functions.
Or does:
DROP VARIABLE super_secret; CREATE VARIABLE super_secret ...;
But you don't do it in functions - these variables are persistent - you don't create it or drop inside functions. The content is secure, so you don't need to hide this variable against other.
If a static analysis tool is specific to one language, then it can only checks that all is well in functions in those languages, but as there may be functions written in other languages as well then the check is somehow partial. This is not a bad thing, it just illustrate that you cannot check everything. That is quality ensurance.
[...] Indeed, probably there exists some class of typos that may not be found by some static analysis implementations on PL/pgSQL functions which uses basic session variables.
yes, and I would not to append any new case that cannot be covered by plpgsql check. Dynamic SQL and our temporal tables are enough issues already.
I'm not sure that I understand these sentences.
so I don't prefer any design that increase a area where plpgsql_check should not work.
I wrote my notes there.
Great! I restructured a little bit and tried to improve the English. I also added questions when some statement that I think are too optimistic, or are unclear to me.