Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p16O6-0004K2-Cd for pgsql-general@arkaria.postgresql.org; Fri, 02 Dec 2022 13:47:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p16O4-0000BW-Di for pgsql-general@arkaria.postgresql.org; Fri, 02 Dec 2022 13:47:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p16O3-0000BN-K0 for pgsql-general@lists.postgresql.org; Fri, 02 Dec 2022 13:47:32 +0000 Received: from ook.raf.org ([139.99.156.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p16O0-00085H-Oy for pgsql-general@lists.postgresql.org; Fri, 02 Dec 2022 13:47:30 +0000 Received: from localhost (localhost [127.0.0.1]) by ook.raf.org (Postfix) with ESMTP id 4F9355DE1D for ; Sat, 3 Dec 2022 00:47:19 +1100 (AEDT) X-Virus-Scanned: Debian amavisd-new at ook.raf.org Received: from ook.raf.org ([127.0.0.1]) by localhost (ook.raf.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Jc6lZMVDpcWY for ; Sat, 3 Dec 2022 00:47:18 +1100 (AEDT) Received: by ook.raf.org (Postfix, from userid 1001) id EBBF75E86D; Sat, 3 Dec 2022 00:47:17 +1100 (AEDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=raf.org; s=001; t=1669988837; bh=9Li55eg2k1GDUTLPzr2ep1Y2UfUSlBSXu91KtwvNnVw=; h=Date:From:To:Subject:References:In-Reply-To:From; b=YRx425SjUJK2mV51ERXpd3BoRcgwrLzufKLU8djFcSaerKeqOg9MvXiJD7n7za3Rx 9NPaZU55csx0QbmpCOYZDzmWzsjVJk4KMr1fud5LAaZRM+tx1a776dPlp2F/FMuG6T BwFfQwecOEHFDEi5J+PyGR8shZYO7jzvSats6yaeklfv68UMpGGPcsp6bJm7ym81Vh 59v8X7hZa+wEmeZM9XHE28e9GRg80icF5QPAbkGYYi+tqp4xO+AsijcS1ChTp6v8gS 3h1r2lADcGM0Nsm2X/JDVmsbALrEij+80Wa+3UB56eOaWgphvYhPHeQi/Ay1Y0WnrK DXGAYdUEogBmw== Date: Sat, 3 Dec 2022 00:47:17 +1100 From: raf To: pgsql-general@lists.postgresql.org Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour Message-ID: References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <1579912.1669908194@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula wrote: > 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 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