From: raf <raf@raf.org>
To: pgsql-general@lists.postgresql.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Date: Sat, 3 Dec 2022 00:47:17 +1100
Message-ID: <Y4oB5RLrbTFLGWsW@raf.org> (raw)
In-Reply-To: <CAJvus-MKW1tFhnotbQXbvCdXbfgH6WpvmExsMrYi1Yfov85KOA@mail.gmail.com>
References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS>
<CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com>
<1579912.1669908194@sss.pgh.pa.us>
<CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com>
<Y4lFsaF3xz24K0mr@raf.org>
<CAJvus-MKW1tFhnotbQXbvCdXbfgH6WpvmExsMrYi1Yfov85KOA@mail.gmail.com>
On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula <pon@iki.fi> wrote:
> On Fri, 2 Dec 2022 at 02:24, raf <raf@raf.org> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: raf@raf.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
In-Reply-To: <Y4oB5RLrbTFLGWsW@raf.org>
References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com> <1579912.1669908194@sss.pgh.pa.us> <CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com> <Y4lFsaF3xz24K0mr@raf.org> <CAJvus-MKW1tFhnotbQXbvCdXbfgH6WpvmExsMrYi1Yfov85KOA@mail.gmail.com> <Y4oB5RLrbTFLGWsW@raf.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox