Re: PCI:SSF - Safe SQL Query & operators filter

On 11/8/22 11:50, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:43, Jan Bilek <jan(dot)bilek(at)eftlab(dot)com(dot)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.