Thread: 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<br /><br />We use plproxy for this kind of security enhancement. We create plpgsql functions that do whats needed andthen we create so called proxy database that contains only plproxy interfaces for these functions. Users get access onlyto proxy database. This way it is easier to rest assured that users don't get access by accident to something they shouldnot.<br /><br />regards,<br />Asko <br /><br /><div class="gmail_quote">On Sat, Dec 6, 2008 at 8:21 PM, Andrew Chernow<span dir="ltr"><<a href="mailto:ac@esilo.com">ac@esilo.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Lookingfor 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?<br /><br /> I currently need this as an extra security measure for a libpqclient app (want to block arbitrary queries from malicious attackers). The easiest way I found was to add some query_stringchecks 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".<br /><br/> I think it is something rather simple to design/implement (probably use a table of user allowed queries, supportregex matches, etc.. loaded at session startup and SIGHUP).<br /><br /> -- <br /> Andrew Chernow<br /> eSilo, LLC<br/> every bit counts<br /><a href="http://www.esilo.com/" target="_blank">http://www.esilo.com/</a><br /><font color="#888888"><br/> -- <br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org" target="_blank">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br />
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@krosing.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@postgresql.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@krosing.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/