[GENERAL] Re: Are new connection/security features in order, given connectionpooling?

Поиск
Список
Период
Сортировка
От Karl Czajkowski
Тема [GENERAL] Re: Are new connection/security features in order, given connectionpooling?
Дата
Msg-id 20170112021244.GC9709@moraine.isi.edu
обсуждение исходный текст
Ответ на Re: [GENERAL] Are new connection/security features in order, givenconnection pooling?  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Список pgsql-general
I can relate to the original plea from my own exploration of this
topic.

Before I get into that, I will mention as an aside that to date we
have found RLS to be really slow for web client authorization, given
that we have to use session parameters to store web client context and
there doesn't seem to be a way to get the query planner to treat these
settings as constants nor to lift static parts of RLS policy out of
per-row loops.  It's so slow that I wonder whether future releases
will improve it before we are forced to move authorization back into
the client-side application logic, making our generated SQL much more
complex but better tuned to the query planner's capabilities.

As background, our web applications essentially compile new SQL
queries on the fly and send them through to PostgreSQL.  There is a
lot of metaprogramming involved in generating SQL statements to embody
the work we want done on behalf of our clients, and this doesn't fit
well into traditional prepared statements etc.

The web service needs to assert the client context and the
client-level statements we generate and execute should not be able to
override this. The service logic to determine and assert client
context (once per web request) is dramatically simpler than the
service logic producing all the client-level SQL statements, and it
would be nice to have a restricted execution context to isolate the
two kinds.  We also like the idea that policy enforcement mechanisms
could be applied orthogonally to the generated client-level SQL
statements, as it feels like a better defense-in-depth architecture
and is also easier to reason about. To do so in our service logic
would mean replicating more and more of the query parsing and engine
to do general query rewriting.

So, it would be nice to have a tunneling mechanism where I can
distinguish the control-plane operations I am performing from the
application operations I am translating and pushing down to PostgreSQL
on behalf of the web client. In my case, I might want to perform a
mixture of service-level and client-level statements within the same
transaction. The client-level statements cannot control transactions.

I could imagine something like a two-level feature set. At the
top-level in the connection, we can statefully manipulate our security
contexts, set configurable privilege masks for the second-level
execution context, set effective roles (reversibly, as today), manage
transactions, etc.  With some kind of nested execution block, we could
submit less trusted statements to run within the second-level
execution context:

   EXECUTE RESTRICTED $guard$ app_query... $guard$ ;

This would take the guarded query string, restart a safe parser on it,
and only on successful parse go forward with planning and executing it
in the restricted mode that has been configured for the connection.

Eventually, I could see wanting more resource management controls on
this restricted context too, i.e. setting limits of CPU/IO/RAM
consumption or execution time. Both planning time limits (reject based
on estimates) and runtime (abort query if limit is reached).


Karl



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Means to emulate global temporary table
Следующее
От: Patrick B
Дата:
Сообщение: [GENERAL] Question slow query