Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
On Fri, 2 Dec 2022 at 02:24, raf wrote: > Same here. Accessing the loaded stored procedure source > is how I audit the state of stored procedures in the > database against the code in the code repository. Exactly. If our software is audited, how can I reliably prove to auditor that the running version of the procedure has not been tampered with either by customer, rogue developer or some malicious party? Suggested solution "store the text in user table" does not work, because it requires following a process. And the process overriding changes are those we are interested in. I'm not so much concerned about whether the stored procedure text compiles anymore (because of object name changes etc.) I just really would like to have the exact source code stored with the database. So if release management goes horribly wrong, there is at least the possibility to compare procedure text manually. Pasi
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
On Fri, 2 Dec 2022 at 15:02, Daniel Gustafsson wrote: > How do you today prove that for other compiled programs in your system? Good question. I guess I never considered database to be compiled. How do you debug this kind of system? What if the you suspect that there is a wrong version of procedure in the system? How can you then find what release or patch produced the problem if you can't see the actual procedure text that produced the running version? I'm not claiming that there isn't benefits in this system. I'm sure it actually prevents rogue changes for not having the source code readily available. It's just a big fundamental change to get over if you can't do comparison from database to version control. Pasi
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
On Fri, 2 Dec 2022 at 15:47, raf wrote: > If you're concerned about tampering by > customers/users/developers, you can either set > permissions to prevent it in some cases, and when you > can't prevent it, make it tamper-evident by logging > actions to somewhere remote and monitoring for what > concerns you. That should satisfy auditors. True. But isn't this extra work compared to previous situation? If you can compare procedure text directly and say to your developers "you scoundrel did a change outside version control, no dessert for you". I would be perfectly satisfied, if the sql that produced the procedure would be stored "as is" read-only copy when it was compiled. If an object rename makes it invalid, tweak a bit telling so, but don't change the text until next alter procedure is run. Pasi
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
> If you want an audit comparison point, I'd suggest capturing > the result of pg_get_functiondef or one of its sibling functions > just after creating your function. "pg_dump -s" is another > pretty credible mechanism for capturing schema details. That's very good info, thank you. I guess freaking out was unnecessary. Sorry. So the idea is to do apples to apples comparison and forget the sql source files with whitespace and comments. When comparing a know good database I can do pg_get_functiondef in both databases and compare the results. It's just a matter of how well each query tool can represent diffs or do results comparison. pg_dump output is even better for comparison. Pasi
