On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula <pon@xxxxxx> wrote: > On Fri, 2 Dec 2022 at 02:24, raf <raf@xxxxxxx> 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 That's a different concern to mine. I just want to be able to check that multiple test systems all have the same schema and stored procedures, and to be able to reload an old database, apply any schema updates made since it was backed up, and load/drop any stored procedures that were added/changed/removed since then without having to reload all of them. So storing procedure code in a user table would be OK. I just have to update my db loading/auditing tools for v14+. It was great to be able to make use of the source in pg_proc for my needs, but that's not what it was for. I was just lucky it was there. 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. cheers, raf
