Re: user-based query white list

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/

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 ??

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/

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.

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/

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

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 <ac(at)esilo(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

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 <hannu(at)krosing(dot)net> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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 <hannu(at)krosing(dot)net> 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.

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

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/

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/