pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Bilek <jan.bilek@eftlab.com.au>
To: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Re: PCI:SSF - Safe SQL Query & operators filter
Date: Tue, 8 Nov 2022 04:14:20 +0000
Message-ID: <f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au> (raw)
In-Reply-To: <87E0DAE2-248C-4F47-B333-436D515FD36D@thebuild.com>
References: <e3cb6a78-8ff2-dcf1-2fdd-26e3093d3106@eftlab.com.au>
	<23C450AB-9333-40CB-8ED1-F15D15BED878@thebuild.com>
	<33a762ed-a045-c1e5-a042-cd4903f7de4b@eftlab.com.au>
	<87E0DAE2-248C-4F47-B333-436D515FD36D@thebuild.com>

On 11/8/22 11:50, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:43, Jan Bilek <jan.bilek@eftlab.com.au> wrote:
>>
>> Well, superuser (our App) is already logged in and as it is designed
>> very much as an "appliance" it simply does that job - manages its
>> database.
> Well... don't do that. :)  The problem is analogous to having root log into a Linux box and run application commands.  It works, but it opens a security hole, as you've discovered.
>
>> Yes, agreed. Any ideas?
> In this particular case (creating an untrusted PL and functions therein), you'll need to use a PostgreSQL superuser.  This is a separate operation from routine application use, though.  (I'll note that having functions in an untrusted PL in a PCI-sensitive system is not a great idea, as you'll need to audit them very closely to make sure that they can't do anything untoward outside the role system.)

Thank you David, Laurentz & Christophe,

All excellent inputs.

I've realized that our reporting feature is wrapped-bound in a 
Transaction & Rollback. This actually came with an idea to Alter that 
role as a part of transaction.

It works in an excellent way!

BEGIN TRANSACTION;
alter role CURRENT_USER with NOSUPERUSER;
select * from pg_read_file('/etc/passwd' , 0 , 1000000);
ROLLBACK TRANSACTION;
BEGIN
ALTER ROLE
ERROR:  permission denied for function pg_read_file
ROLLBACK
bp-node=#

Even trying to break it seems to be difficult.

BEGIN TRANSACTION;
alter role CURRENT_USER with NOSUPERUSER;
alter role CURRENT_USER with SUPERUSER;
ROLLBACK TRANSACTION;
BEGIN
ALTER ROLE
ERROR:  must be superuser to alter superuser roles or change superuser 
attribute
ROLLBACK

I know it is not exactly what you suggested (and agreeing a lot with our 
app user shouldn't be running as superuser), but as all other inputs 
from our application come sanitized through bind and this is the only 
way where user can send an explicit command in there - I think it should do!

Please let me know if you approve.

Thanks & Cheers,
Jan

-- 
Jan Bilek - CTO at EFTlab Pty Ltd.



view thread (10+ messages)  latest in thread

Message-ID: <f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au>
Permalink:  ../f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au/
Also on:    postgresql.org/message-id/f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au

 · 

reply

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: jan.bilek@eftlab.com.au, xof@thebuild.com
  Subject: Re: PCI:SSF - Safe SQL Query & operators filter
  In-Reply-To: <f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au>
  References: <e3cb6a78-8ff2-dcf1-2fdd-26e3093d3106@eftlab.com.au> <23C450AB-9333-40CB-8ED1-F15D15BED878@thebuild.com> <33a762ed-a045-c1e5-a042-cd4903f7de4b@eftlab.com.au> <87E0DAE2-248C-4F47-B333-436D515FD36D@thebuild.com> <f413286c-ae0f-2649-4288-5f614e7453ef@eftlab.com.au>

* 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