Block Full-Table Operations on Amazon RDS Postgres

[christiaanwesterbeek]

I'm not reporting a bug nor a feature here. This is for Blocking Full-Table Operations using the pg-safeupdate PostgreSQL extension. That extension is not available on Amazon RDS Postgres.

I posted a tweet about it, but since I have quasi-zero followers I don't think it will have any effect.

https://twitter.com/Devotisnl/status/1009876549967994880

Maybe retweet to draw more attention to it? Maybe somebody else here has more leverage with Amazon to get this through?

[steve-chavez]

I don't hold high hopes for AWS adding that any time soon. An alternative would be to restrict the method at the proxy level. In nginx, you could do something like:

location /rest/ {
  proxy_pass http://postgrest/; # Reverse proxy to your PostgREST

  if ( $request_method ~ ^(DELETE|PATCH)$ ) {
    set $allowed N; #Nginx doesn't support multiple conditions inside if, this is a workaround
  }

  if ( $is_args = "" ) { # No query args
    set $allowed "${allowed}O";
  }

  if ( $allowed = NO ){
    return 405;
  }
}

The example could be improved, and more fine-grained logic could be done with lua(OpenResty).

Another alternative could be that, in addition to headers, we add access to other request attributes in PostgREST, like the http method(access through current_setting('request.method', true)) and the query args, so we could have this custom logic in the pre-request function.

Recently, a way to access the uri was requested in gitter chat and also access to some query args was requested in #1124, so maybe it makes sense for PostgREST to offer these meta capabilities out of the box.

[christiaanwesterbeek]

Thanks. I'll close this for now since an alternative strategy is available. Also, people visiting this issue can continue to retweet if they like even if this issue is closed.

[saksham-kakkar]

@christiaanwesterbeek - I was looking for this, but it seems it's still not available in AWS postgres RDS. May i know the alternative strategy that you used for this? Thanks in advance.

[christiaanwesterbeek]

The alternative strategy is not the one at the proxy level that @steve-chavez proposed. What I did was that my authenticated users simply don't have delete permissions on the table, but only select, insert and update where appropriate. My table has a deleted column. My view filter out the table's records with ... where deleted is null. Finally I implemented an instead of delete trigger on the view. That trigger updates the record with the deleted field set to the current datetime instead of deleting the record. And because the view filters out those records, they're effectively deleted for the api.

[robertsosinski]

Just adding my process of dealing with this, if it helps.

Similar to @christiaanwesterbeek I do not let the user delete records. Instead, I have a function (say for a table tasks the function is tasks_delete) that accepts an array of task ids as input, verifies the user owns these records, deletes them (with returning *) and returns a setof tasks so the user can get a list of what records were deleted. This way, users must be specific of what they want to delete. If your users need to delete wide swaths of data with a predicate, this wont work; but I find the extra security around this useful in my cases.

I've also done this exact pattern with batch updates as well: a function that takes an array of ids and a json of what fields to update and the new values for each. Same benefit, user must specify what records to update; but also same drawback, user cannot update wide swaths of records.

Hope this is useful.