Reason for not supporting things like update ... field=some_function() ...

[gc-ft]

Hi,

I am curious and would like to better understand reasoning for some of the limitations PostgREST have. I am guessing this is a security precaution, but I would like to hear from the community...

I am talking about having the flexibility to do some updates of tables using columns or functions instead of just pure data. As a simple example, there is no way to run this query in PostgREST directly:

UPDATE my_table SET count = count + 1 WHERE id = 123;

In PostgREST I would either have to do a SELECT and then an UPDATE, or use a RPC call to a function that executes this query. Both seem weird when supporting this kind of update seems simple and not extremely problematic?

Another example which seems even "safer" to me or easier would be:

UPDATE my_table SET previous_info = info, info = 'new info' WHERE id = 123;

being able to send a json body with the PATCH request in the form of, for example the following:

{ "previous_info": { "column": "info" }, "info": "new info" }

Again, I am guessing this is not allow to stop injecting malicious code into queries, but there must be a rather simple way to protect against this or at least allow "simple" versions of such queries?

[wolfgangwalther]

First of all, PostgREST is not designed to "expose the full SQL language as REST", so our goal is not to make everything that is possible via SQL possible to do via the API. Instead our goal is to create well designed APIs. If something can't be nicely expressed at the API level, then it's unlikely to be done. This doesn't mean that your proposal it immediately out of scope, but it gives background to the "why didn't we feel the urge to do something about this, yet?" question.

Your first example is discussed in #465.

Now to the answer that you probably didn't want to hear: Nothing has been done about this, because nobody came up with a convincing concept and stepped up to implement it, yet. This is the case for the vast majority of cases - we have a lot more ideas than we can implement alone.

There are some challenges with your specific proposal, though. We currently do two things on purpose in PostgREST:

• We try to avoid parsing the JSON body in haskell for performance reasons and instead try to pass it on to PostgreSQL to parse it whenever possible.
• We don't use user input for column or table names. Instead we have the internal "schema cache", in which we have all the column names. The request is matched against those, then the query is created as a parametrized query to avoid SQL injection. This means that the values (i.e. the JSON body) is not added into the query string, but passed separately to PostgreSQL.

Both of that would need to change for what you propose. I'm not saying it's impossible, but it does need more changes and careful thinking.

[gc-ft]

Do not worry, there is nothing at all I do not like to hear 😀

Thank you for explaining! I know you have a lot to work on, and I really appreciate the work you are doing.

This is why I worded the question the way I did! I wanted to better understand the decisions you make regarding features! If I would have any experience in Haskell I would contribute.

[wolfgangwalther]

If I would have any experience in Haskell I would contribute.

I started contributing without Haskell experience at first, so that should not stop you :)

[gc-ft]

If I would have any experience in Haskell I would contribute.

I started contributing without Haskell experience at first, so that should not stop you :)

I feared such a reply :) I will for sure when I have the capacity! It's a great addition for postgres!