Re: [HACKERS] user-based query white list

[Euler Taveira]

2017-07-03 3:11 GMT-03:00 Tim Burgan :

>
> Since then, is it now possible to configure a user to only be able to
> execute a limited white-listing of queries? Is this something that could
> now be implemented through extensions?
>

Since pg_stat_statements infrastructure, it is possible to create
extensions that prohibit query execution for certain users (see
sql_firewall [1] as an example).


[1] https://github.com/uptimejp/sql_firewall


-- 
   Euler Taveira   Timbira -
http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

[HACKERS] user-based query white list

[Tim Burgan]

This old thread on "user-based query white list" is now nearly 10 years old!
http://grokbase.com/t/postgresql/pgsql-hackers/08c6zh42fa/user-based-query-white-list

Since then, is it now possible to configure a user to only be able to
execute a limited white-listing of queries? Is this something that could
now be implemented through extensions?

Re: [HACKERS] user-based query white list

[Andrew Chernow]

Andrew Dunstan wrote:



Andrew Chernow wrote:


I think what is missing is a way to deny the execution of queries that 
don't operate on an object (like a table, sequence, role, schema, 
etc...), OR queries not covered by the priv system.  Object-based 
queries can be locked down using the existing priv system.  Not sure 
if denying non-object related queries would work; what happens when 
you call SELECT NOW() within an allowed function?





What exactly are you trying to protect against?

In general, my attitude is that databases should not allow direct access 
from untrusted sources.  The API restriction you are talking about is 
something that is trivially easy to build into middleware, and only the 
middleware should be allowed access to the database.


cheers

andrew




Well, it sounds like the ability to do what I am looking for is not 
available in the current feature set; that answers my first question. 
It also sounds like the backend can be patched in such a way that 
negates the need for middleware.  I didn't really hear any convincing 
security implications from opening the backend up to world when using a 
white list; probably because it appears to lock it down.  If there is 
something I'm missing, please let me know.


The question I am really trying to answer is, what is required to safely 
remove a layer of abstraction and point of failure, not to mention an 
extra level of complexity?


Previously, I labeled this as a hack.  I was only referring to my 
implementation which is currently not very general purpose.  I don't 
think the concept is a hack.


Thanks,
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Asko Oja]

Hi

We use plproxy for this kind of security enhancement. We create plpgsql
functions that do whats needed and then we create so called proxy database
that contains only plproxy interfaces for these functions. Users get access
only to proxy database. This way it is easier to rest assured that users
don't get access by accident to something they should not.

regards,
Asko

On Sat, Dec 6, 2008 at 8:21 PM, Andrew Chernow [EMAIL PROTECTED] wrote:

 Looking for a way to limited a user to a specific set of queries.  I don't
 think this can be done right now ... or can it?  Has this feature request
 surfaced in the past?

 I currently need this as an extra security measure for a libpq client app
 (want to block arbitrary queries from malicious attackers).  The easiest way
 I found was to add some query_string checks into backend/tcop/postgres.c for
 the 'Q' and 'P' commands in PostgresMain().  Seems to work just fine.  If it
 doesn't match, I issue an ereport FATAL since that is seen as a malicious
 query execution attempt.

 I think it is something rather simple to design/implement (probably use a
 table of user allowed queries, support regex matches, etc.. loaded at
 session startup and SIGHUP).

 --
 Andrew Chernow
 eSilo, LLC
 every bit counts
 http://www.esilo.com/

 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Hannu Krosing]

On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote:
 Grzegorz Jaskiewicz wrote:
  
  On 2008-12-06, at 18:21, Andrew Chernow wrote:
  
  Looking for a way to limited a user to a specific set of queries.  I 
  don't think this can be done right now ... or can it?  Has this 
  feature request surfaced in the past?
 
  I currently need this as an extra security measure for a libpq client 
  app (want to block arbitrary queries from malicious attackers).  The 
  easiest way I found was to add some query_string checks into 
  backend/tcop/postgres.c for the 'Q' and 'P' commands in 
  PostgresMain().  Seems to work just fine.  If it doesn't match, I 
  issue an ereport FATAL since that is seen as a malicious query 
  execution attempt.
 
  I think it is something rather simple to design/implement (probably 
  use a table of user allowed queries, support regex matches, etc.. 
  loaded at session startup and SIGHUP).
  
  Can it be done with views, and adjusting permissions so user is only 
  allowed to use few views ??
  
  
 
 Not sure.  The client I am working on only calls functions, small API to 
 interact with (no knowledge of views or tables).  

Then grant access to those functions only.

 Even if that were not the 
 case, would views stop a client from sending in other queries, like SELECT 
 1+1 
 or something that could bog down the server?

Use statement_timeout GUC to prevent bogging


Hannu




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Merlin Moncure]

There is extra safety from using whitelists...

For one, it's trivial to write a query that consumes unlimited CPU
resources that accesses no built in tables or functions.  There are
various other dangerous things that are difficult to lock down like
temp tables.

Assuming you can handle paramaterized queries on the client, a
whitelist is pretty easy and powerful safeguard on top of the normal
protections.  Your biggest concern is malformed protocol messages or
parameters and there are extra possible defenses there.

A whitelist is trivial to implement.  So the question is: is the OP
suggesting how one could be done and if so, does it make it safe to
allow ssl connections from $WORLD.

merlin

On 12/7/08, Hannu Krosing [EMAIL PROTECTED] wrote:
 On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote:
 Grzegorz Jaskiewicz wrote:
 
  On 2008-12-06, at 18:21, Andrew Chernow wrote:
 
  Looking for a way to limited a user to a specific set of queries.  I
  don't think this can be done right now ... or can it?  Has this
  feature request surfaced in the past?
 
  I currently need this as an extra security measure for a libpq client
  app (want to block arbitrary queries from malicious attackers).  The
  easiest way I found was to add some query_string checks into
  backend/tcop/postgres.c for the 'Q' and 'P' commands in
  PostgresMain().  Seems to work just fine.  If it doesn't match, I
  issue an ereport FATAL since that is seen as a malicious query
  execution attempt.
 
  I think it is something rather simple to design/implement (probably
  use a table of user allowed queries, support regex matches, etc..
  loaded at session startup and SIGHUP).
 
  Can it be done with views, and adjusting permissions so user is only
  allowed to use few views ??
 
 

 Not sure.  The client I am working on only calls functions, small API to
 interact with (no knowledge of views or tables).

 Then grant access to those functions only.

 Even if that were not the
 case, would views stop a client from sending in other queries, like
 SELECT 1+1
 or something that could bog down the server?

 Use statement_timeout GUC to prevent bogging

 
 Hannu




 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Chernow]

Merlin Moncure wrote:

There is extra safety from using whitelists...

For one, it's trivial to write a query that consumes unlimited CPU
resources that accesses no built in tables or functions.  There are
various other dangerous things that are difficult to lock down like
temp tables.

Assuming you can handle paramaterized queries on the client, a
whitelist is pretty easy and powerful safeguard on top of the normal
protections.  Your biggest concern is malformed protocol messages or
parameters and there are extra possible defenses there.

A whitelist is trivial to implement.  So the question is: is the OP
suggesting how one could be done and if so, does it make it safe to
allow ssl connections from $WORLD.

merlin

On 12/7/08, Hannu Krosing [EMAIL PROTECTED] wrote:

On Sat, 2008-12-06 at 13:30 -0500, Andrew Chernow wrote:

Grzegorz Jaskiewicz wrote:

On 2008-12-06, at 18:21, Andrew Chernow wrote:


Looking for a way to limited a user to a specific set of queries.  I
don't think this can be done right now ... or can it?  Has this
feature request surfaced in the past?

I currently need this as an extra security measure for a libpq client
app (want to block arbitrary queries from malicious attackers).  The
easiest way I found was to add some query_string checks into
backend/tcop/postgres.c for the 'Q' and 'P' commands in
PostgresMain().  Seems to work just fine.  If it doesn't match, I
issue an ereport FATAL since that is seen as a malicious query
execution attempt.

I think it is something rather simple to design/implement (probably
use a table of user allowed queries, support regex matches, etc..
loaded at session startup and SIGHUP).

Can it be done with views, and adjusting permissions so user is only
allowed to use few views ??



Not sure.  The client I am working on only calls functions, small API to
interact with (no knowledge of views or tables).

Then grant access to those functions only.


Even if that were not the
case, would views stop a client from sending in other queries, like
SELECT 1+1
or something that could bog down the server?

Use statement_timeout GUC to prevent bogging


Hannu











I think what is missing is a way to deny the execution of queries that 
don't operate on an object (like a table, sequence, role, schema, 
etc...), OR queries not covered by the priv system.  Object-based 
queries can be locked down using the existing priv system.  Not sure if 
denying non-object related queries would work; what happens when you 
call SELECT NOW() within an allowed function?


Andrew Chernow
esilo, LLC.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Dunstan]

Andrew Chernow wrote:


I think what is missing is a way to deny the execution of queries that 
don't operate on an object (like a table, sequence, role, schema, 
etc...), OR queries not covered by the priv system.  Object-based 
queries can be locked down using the existing priv system.  Not sure 
if denying non-object related queries would work; what happens when 
you call SELECT NOW() within an allowed function?





What exactly are you trying to protect against?

In general, my attitude is that databases should not allow direct access 
from untrusted sources.  The API restriction you are talking about is 
something that is trivially easy to build into middleware, and only the 
middleware should be allowed access to the database.


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Chernow]

Andrew Dunstan wrote:



Andrew Chernow wrote:


I think what is missing is a way to deny the execution of queries that 
don't operate on an object (like a table, sequence, role, schema, 
etc...), OR queries not covered by the priv system.  Object-based 
queries can be locked down using the existing priv system.  Not sure 
if denying non-object related queries would work; what happens when 
you call SELECT NOW() within an allowed function?





What exactly are you trying to protect against?

In general, my attitude is that databases should not allow direct access 
from untrusted sources.  The API restriction you are talking about is 
something that is trivially easy to build into middleware, and only the 
middleware should be allowed access to the database.


cheers

andrew




Why must this be done in middleware?  Middleware wouldn't be needed as 
protection against untrusted sources if random queries could be denied.  My 
little hack in PostgresMain() made it impossible to execute queries unless they 
are on a white list (there could be better ways of doing this).  Now add in SSL 
and verification of certificates and things are tightly nailed down; as much as 
the classic application server (middleware) would be ... no?


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] user-based query white list

[Andrew Chernow]

Looking for a way to limited a user to a specific set of queries.  I don't think 
this can be done right now ... or can it?  Has this feature request surfaced in 
the past?


I currently need this as an extra security measure for a libpq client app (want 
to block arbitrary queries from malicious attackers).  The easiest way I found 
was to add some query_string checks into backend/tcop/postgres.c for the 'Q' and 
'P' commands in PostgresMain().  Seems to work just fine.  If it doesn't match, 
I issue an ereport FATAL since that is seen as a malicious query execution 
attempt.


I think it is something rather simple to design/implement (probably use a table 
of user allowed queries, support regex matches, etc.. loaded at session startup 
and SIGHUP).


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Grzegorz Jaskiewicz]

On 2008-12-06, at 18:21, Andrew Chernow wrote:

Looking for a way to limited a user to a specific set of queries.  I  
don't think this can be done right now ... or can it?  Has this  
feature request surfaced in the past?


I currently need this as an extra security measure for a libpq  
client app (want to block arbitrary queries from malicious  
attackers).  The easiest way I found was to add some query_string  
checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in  
PostgresMain().  Seems to work just fine.  If it doesn't match, I  
issue an ereport FATAL since that is seen as a malicious query  
execution attempt.


I think it is something rather simple to design/implement (probably  
use a table of user allowed queries, support regex matches, etc..  
loaded at session startup and SIGHUP).


Can it be done with views, and adjusting permissions so user is only  
allowed to use few views ??



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Chernow]

Grzegorz Jaskiewicz wrote:


On 2008-12-06, at 18:21, Andrew Chernow wrote:

Looking for a way to limited a user to a specific set of queries.  I 
don't think this can be done right now ... or can it?  Has this 
feature request surfaced in the past?


I currently need this as an extra security measure for a libpq client 
app (want to block arbitrary queries from malicious attackers).  The 
easiest way I found was to add some query_string checks into 
backend/tcop/postgres.c for the 'Q' and 'P' commands in 
PostgresMain().  Seems to work just fine.  If it doesn't match, I 
issue an ereport FATAL since that is seen as a malicious query 
execution attempt.


I think it is something rather simple to design/implement (probably 
use a table of user allowed queries, support regex matches, etc.. 
loaded at session startup and SIGHUP).


Can it be done with views, and adjusting permissions so user is only 
allowed to use few views ??





Not sure.  The client I am working on only calls functions, small API to 
interact with (no knowledge of views or tables).  Even if that were not the 
case, would views stop a client from sending in other queries, like SELECT 1+1 
or something that could bog down the server?


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Grzegorz Jaskiewicz]

On 2008-12-06, at 18:30, Andrew Chernow wrote:


Grzegorz Jaskiewicz wrote:

On 2008-12-06, at 18:21, Andrew Chernow wrote:
Looking for a way to limited a user to a specific set of queries.   
I don't think this can be done right now ... or can it?  Has this  
feature request surfaced in the past?


I currently need this as an extra security measure for a libpq  
client app (want to block arbitrary queries from malicious  
attackers).  The easiest way I found was to add some query_string  
checks into backend/tcop/postgres.c for the 'Q' and 'P' commands  
in PostgresMain().  Seems to work just fine.  If it doesn't match,  
I issue an ereport FATAL since that is seen as a malicious query  
execution attempt.


I think it is something rather simple to design/implement  
(probably use a table of user allowed queries, support regex  
matches, etc.. loaded at session startup and SIGHUP).
Can it be done with views, and adjusting permissions so user is  
only allowed to use few views ??


Not sure.  The client I am working on only calls functions, small  
API to interact with (no knowledge of views or tables).  Even if  
that were not the case, would views stop a client from sending in  
other queries, like SELECT 1+1 or something that could bog down  
the server?



I use views to simplify code. Say you have a simple join, with one  
WHERE. You omit the WHERE in view, and leave it like that. Than just  
select foo1, foo2 from VIEW WHERE boo1=foo1 and foo3  '123';
Postgresql is smart enough, to run it as one query (as oppose to  
mysql), so the code is simpler, everybody's happy.


If you want to continue on that discussion, I suggest we move it to pg- 
general.





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Chernow]

Grzegorz Jaskiewicz wrote:


On 2008-12-06, at 18:30, Andrew Chernow wrote:


Grzegorz Jaskiewicz wrote:

On 2008-12-06, at 18:21, Andrew Chernow wrote:
Looking for a way to limited a user to a specific set of queries.  I 
don't think this can be done right now ... or can it?  Has this 
feature request surfaced in the past?


I currently need this as an extra security measure for a libpq 
client app (want to block arbitrary queries from malicious 
attackers).  The easiest way I found was to add some query_string 
checks into backend/tcop/postgres.c for the 'Q' and 'P' commands in 
PostgresMain().  Seems to work just fine.  If it doesn't match, I 
issue an ereport FATAL since that is seen as a malicious query 
execution attempt.


I think it is something rather simple to design/implement (probably 
use a table of user allowed queries, support regex matches, etc.. 
loaded at session startup and SIGHUP).
Can it be done with views, and adjusting permissions so user is only 
allowed to use few views ??


Not sure.  The client I am working on only calls functions, small API 
to interact with (no knowledge of views or tables).  Even if that were 
not the case, would views stop a client from sending in other queries, 
like SELECT 1+1 or something that could bog down the server?



I use views to simplify code. Say you have a simple join, with one 
WHERE. You omit the WHERE in view, and leave it like that. Than just 
select foo1, foo2 from VIEW WHERE boo1=foo1 and foo3  '123';
Postgresql is smart enough, to run it as one query (as oppose to mysql), 
so the code is simpler, everybody's happy.


If you want to continue on that discussion, I suggest we move it to 
pg-general.





I don't think view-based security solves my problem.  I need to limit a user to 
20 fixed queries, for example.  That means the user cannot execute SELECT 
NOW() or SELECT 'hello world'.  The user can only execute a pre-defined list 
of queries.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] user-based query white list

[Andrew Dunstan]

Andrew Chernow wrote:


I don't think view-based security solves my problem.  I need to limit 
a user to 20 fixed queries, for example.  That means the user cannot 
execute SELECT NOW() or SELECT 'hello world'.  The user can only 
execute a pre-defined list of queries.




Put your queries in security definer functions and put those in a schema 
that is the only one your user has access to. That should just about do 
the trick, although s/he might still be able to do select 'foo';


cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers