pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPCI:SSF - Safe SQL Query & operators filter
10+ messages / 5 participants
[nested] [flat]
* PCI:SSF - Safe SQL Query & operators filter
@ 2022-11-08 01:24 Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 01:51 ` Re: PCI:SSF - Safe SQL Query & operators filter David G. Johnston <david.g.johnston@gmail.com>
2022-11-08 02:06 ` Re: PCI:SSF - Safe SQL Query & operators filter Jeffrey Walton <noloader@gmail.com>
2022-11-08 03:19 ` Re: PCI:SSF - Safe SQL Query & operators filter Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 4 replies; 10+ messages in thread
From: Jan Bilek @ 2022-11-08 01:24 UTC (permalink / raw)
To: pgsql-general
Hi team,
I am leading PCI:SSF audit on our payment switch platform product and having a issue where our QSA just came with multiple ways how to escalate privileges and get a shell to the remote host through our built-in Reporting system which runs on PostgreSQL (12-14).
1. select * from pg_read_file('/etc/passwd' , 0 , 1000000); -> it's possible to display content of '/etc/passwd/' file
2. select version(); -> Result of DBMS version request.
3. COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; -> it's possible to create files on the filesystem on behalf of 'postgres' user.
The main problem comes from obvious - our application's PostgreSQL user needs to have an Superuser role as it manages most of its (dedicated) database (creates tables, drops those, manages views, triggers ... ).
This all matches description from documentation as follows:
<https://www.postgresql.org/docs/14/predefined-roles.html><https://www.postgresql.org/docs/14/p...;
The pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are intended to allow administrators to have trusted, but non-superuser, roles which are able to access files and run programs on the database server as the user the database runs as. As these roles are able to access any file on the server file system, they bypass all database-level permission checks when accessing files directly and they could be used to gain superuser-level access, therefore great care should be taken when granting these roles to users.
</>
So these - pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are inherited from the Superuser and are also super sensitive for us, but our application user apparently still needs to be superuser for multiple reasons. Would there be any way to go around this?
1/ What we need is to create a postgresql user who would be pg_database_owner but not superuser ... but apparently we still need our user to be superuser to be able to carry on with some operations (picking one of more then few) ....
CREATE OR REPLACE LANGUAGE plpython3u;
HINT: Must be superuser to create this extension.
2/ Second option is to introduce some sort of limitation of allowed SQL queries and operators for our Reporting engine, but all that white-listing / blacklisting comes with its own problems where we are DB agnostic...
I am sorry for a long email, but any ideas/pointers will be greatly appreciated.
Thank you & Kind Regards,
Jan
--
Jan Bilek - CTO at EFTlab Pty Ltd.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 01:29 ` Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
3 siblings, 1 reply; 10+ messages in thread
From: Christophe Pettus @ 2022-11-08 01:29 UTC (permalink / raw)
To: Jan Bilek <jan.bilek@eftlab.com.au>; +Cc: pgsql-general
> On Nov 7, 2022, at 17:24, Jan Bilek <jan.bilek@eftlab.com.au> wrote:
> Would there be any way to go around this?
The typical configuration is to not permit the PostgreSQL superuser to log in remotely. The database can be managed by a different, non-superuser role, including schema migrations.
> CREATE OR REPLACE LANGUAGE plpython3u;
> HINT: Must be superuser to create this extension.
The reason only a superuser can create this extension is the "u" at the end of the name: It is an untrusted PL that can bypass PostgreSQL's role system. If anyone could create functions in it, anyone could bypass roles.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
@ 2022-11-08 01:43 ` Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:50 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Bilek @ 2022-11-08 01:43 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general
On 11/8/22 11:29, Christophe Pettus wrote:
>
>> On Nov 7, 2022, at 17:24, Jan Bilek <jan.bilek@eftlab.com.au> wrote:
>> Would there be any way to go around this?
> The typical configuration is to not permit the PostgreSQL superuser to log in remotely. The database can be managed by a different, non-superuser role, including schema migrations.
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. There might be a way to explore whether we can use internally
another (limited) user just for reporting queries on a different
connection session. But I am getting (security related) headaches just
thinking about it.
>
>> CREATE OR REPLACE LANGUAGE plpython3u;
>> HINT: Must be superuser to create this extension.
> The reason only a superuser can create this extension is the "u" at the end of the name: It is an untrusted PL that can bypass PostgreSQL's role system. If anyone could create functions in it, anyone could bypass roles.
Yes, agreed. Any ideas?
--
Jan Bilek - CTO at EFTlab Pty Ltd.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 01:50 ` Christophe Pettus <xof@thebuild.com>
2022-11-08 04:14 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Christophe Pettus @ 2022-11-08 01:50 UTC (permalink / raw)
To: Jan Bilek <jan.bilek@eftlab.com.au>; +Cc: pgsql-general
> 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.)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:50 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
@ 2022-11-08 04:14 ` Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 07:03 ` Re: PCI:SSF - Safe SQL Query & operators filter Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Bilek @ 2022-11-08 04:14 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:50 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 04:14 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 07:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-11-08 07:35 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2022-11-08 07:03 UTC (permalink / raw)
To: Jan Bilek <jan.bilek@eftlab.com.au>; Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general
On Tue, 2022-11-08 at 04:14 +0000, Jan Bilek wrote:
> 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.
I strongly disapprove, and any security audit you pass with such a setup
is worthless. I repeat: the application does not need to connect with
a superuser.
I don't understand what you want to demonstrate with the code samples, or
what you mean when you say that "the user can send an explicit command".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:50 ` Re: PCI:SSF - Safe SQL Query & operators filter Christophe Pettus <xof@thebuild.com>
2022-11-08 04:14 ` Re: PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 07:03 ` Re: PCI:SSF - Safe SQL Query & operators filter Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-11-08 07:35 ` Jan Bilek <jan.bilek@eftlab.com.au>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Bilek @ 2022-11-08 07:35 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general
On 11/8/22 17:03, Laurenz Albe wrote:
> On Tue, 2022-11-08 at 04:14 +0000, Jan Bilek wrote:
>
>> 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.
> I strongly disapprove, and any security audit you pass with such a setup
> is worthless. I repeat: the application does not need to connect with
> a superuser.
>
> I don't understand what you want to demonstrate with the code samples, or
> what you mean when you say that "the user can send an explicit command".
>
> Yours,
> Laurenz Albe
Interesting.
I agree that our app shouldn't need superuser, but that would mean that
some ... you made me give it some serious though here.
Installation itself is happening under elevated (root) rights. We are
using the postgres account for moving in all what's needed (e.g. that
plpython3u extension). Walking though our code for most of the day, I
can't see why that superuser would be really needed. Those plpython3u
functions are wrapped up under the hood already. I'm sending that in to
check if our QA will find anything.
Thanks for being stubborn about this!
Cheers,
Jan
--
Jan Bilek - CTO at EFTlab Pty Ltd.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 01:51 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2022-11-08 01:51 UTC (permalink / raw)
To: Jan Bilek <jan.bilek@eftlab.com.au>; +Cc: pgsql-general
On Mon, Nov 7, 2022 at 6:25 PM Jan Bilek <jan.bilek@eftlab.com.au> wrote:
> The main problem comes from obvious - our application's PostgreSQL user
> needs to have an Superuser role as it manages most of its (dedicated)
> database (creates tables, drops those, manages views, triggers ... ).
>
No, the things you want to manage within the database do not require
Superuser. You only need superuser once to configure the system in such a
way, through role and grants and possibly default permissions, that from
then on most everything an application user would want to do can be done by
the role(s) you have created.
> So these - pg_read_server_files, pg_write_server_files and
> pg_execute_server_program roles are inherited from the Superuser
>
Those roles are entirely independent of Superuser; and in fact exists for
that very purpose, since Superuser can already do those things.
and are also super sensitive for us, but our application user apparently
> still needs to be superuser for multiple reasons.
>
You will need to be specific as to exactly what capability you need that
you cannot get a non-superuser role to accomplish.
1/ What we need is to create a postgresql user who would be
> pg_database_owner but not superuser
>
OWNER is an attribute of specific objects, including the database object.
You can already do this.
> ... but apparently we still need our user to be superuser to be able to
> carry on with some operations (picking one of more then few) ....
>
> CREATE OR REPLACE LANGUAGE plpython3u;
> HINT: Must be superuser to create this extension.
>
Yeah, this will be a bit tougher since you are in conflict regarding
whether you need O/S resources or not. It is hard to solve this problem
without the bigger picture.
> 2/ Second option is to introduce some sort of limitation of allowed SQL
> queries
>
A useful tool is "SECURITY DEFINER" tagged routines.
The lack of a separate database migration process seems to be your main
issue - something that can run audited code in an elevated context to put
the system into a state where non-elevated users can then do the things
they need to do. Even if you just have modes in your application that are
"admin mode" and "user mode" so admin mode can do the, hopefully limited,
subset of actions that need superuser while user mode operates under the
only semi-powerful database owner role you can probably bridge the gap (if
you indeed must have the database do things directly on the underlying
operating system).
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 02:06 ` Jeffrey Walton <noloader@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Jeffrey Walton @ 2022-11-08 02:06 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Mon, Nov 7, 2022 at 8:25 PM Jan Bilek <jan.bilek@eftlab.com.au> wrote:
> ...
> select * from pg_read_file('/etc/passwd' , 0 , 1000000); -> it's possible to display content of '/etc/passwd/' file
> select version(); -> Result of DBMS version request.
Input filtering may help in the interim, until you get the roles and
privileges sorted out.
> COPY (SELECT 'nc -lvvp 2346 -e /bin/bash') TO '/tmp/pentestlab'; -> it's possible to create files on the filesystem on behalf of 'postgres' user.
Prepared Statement or Parameterized Query here. In this case, don't
execute data as code.
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PCI:SSF - Safe SQL Query & operators filter
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
@ 2022-11-08 03:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2022-11-08 03:19 UTC (permalink / raw)
To: Jan Bilek <jan.bilek@eftlab.com.au>; pgsql-general
On Tue, 2022-11-08 at 01:24 +0000, Jan Bilek wrote:
> I am leading PCI:SSF audit on our payment switch platform product and having a issue where our QSA
> just came with multiple ways how to escalate privileges and get a shell to the remote host through
> our built-in Reporting system which runs on PostgreSQL (12-14).
> So these - pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are
> inherited from the Superuser and are also super sensitive for us, but our application user apparently
> still needs to be superuser for multiple reasons. Would there be any way to go around this?
Your application user doesn't need to be a superuser.
> 1/ What we need is to create a postgresql user who would be pg_database_owner but not superuser ...
> but apparently we still need our user to be superuser to be able to carry on with some operations
> (picking one of more then few) ....
> CREATE OR REPLACE LANGUAGE plpython3u;
> HINT: Must be superuser to create this extension.
Your application user doesn't need to create PL/Python functions. It only has to use them.
If your application has to access the operating system, it has to do that in a controlled
fashion:
1. through well-written SECURITY DEFINER functions that belong to a highly privileged user
2. through PL/Python functions or other untrusted language functions that are created
by a superuser
> 2/ Second option is to introduce some sort of limitation of allowed SQL queries and operators
> for our Reporting engine, but all that white-listing / blacklisting comes with its own
> problems where we are DB agnostic...
That shouldn't be necessary. If your application user has restricted privileges as it should,
it is automatically limited in the damage it can do.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-11-08 07:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-08 01:24 PCI:SSF - Safe SQL Query & operators filter Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:29 ` Christophe Pettus <xof@thebuild.com>
2022-11-08 01:43 ` Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:50 ` Christophe Pettus <xof@thebuild.com>
2022-11-08 04:14 ` Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 07:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-11-08 07:35 ` Jan Bilek <jan.bilek@eftlab.com.au>
2022-11-08 01:51 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-08 02:06 ` Jeffrey Walton <noloader@gmail.com>
2022-11-08 03:19 ` Laurenz Albe <laurenz.albe@cybertec.at>
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