Designing safe and efficient API for item state updates via events

Question

Recently I've been working on a simple state-tracking system, its main purpose is to persist updates, sent periodically from a mobile client in relational database for further analysis/presentation.

The mobile client uses JWTs issued by AAD to authenticate against our APIs. I need to find a way to verify if user has permissions to send an update for a certain Item (at this moment only its creator should be able to do that).

We assume that those updates could be sent by a lot of clients, in small intervals (15-30 seconds). We will only have one Item in active state per user.

The backend application is based on Spring-Boot, uses Spring Security with MS AAD starter and Spring Data JPA.

Obviously we could just do the following:

1. User_1 creates Item_1
2. User_1 sends an Update for Item_1

Item has an owner_ID field, before inserting Update we simply check if Item_1.owner_ID=User_1.ID - this means we need to fetch the original Item before every insert.

I was wondering if there was a more elegant approach to solving these kind of problems. Should we just use some kind of caching solution to keep allowed ID pairs, eg. {User_1, Item_1}?

Answer 1

WHERE clause

You can include it as a condition in your WHERE clause. For example, if you are updating record X you might have started with:

UPDATE table_name SET column1 = value1 WHERE id = X

However, you can instead do:

UPDATE table_name SET column1 = value1 WHERE id = X AND owner_id = Y

If the owner isn't Y, then the value won't get updated. You can introduce a method in your Spring Data repository that looks up the Spring Security value:

@Query("UPDATE table_name SET column1 = ?value1 WHERE id = ?id AND owner_id = ?#{principal.ownerId}")
public int updateValueById(String value1, String id);

where principal is whatever is returned from Authentication#getPrincipal.

Cache

You are correct that technically a cache would prevent the first database call, but it would introduce other complexities. Keeping a cache fresh is enough of a challenge that I would try it only when it's obvious that introducing the complexity of a cache brings the required, observed performance gains.

@PostAuthorize

Alternatively, you can make the extra call and use the framework to simplify the boilerplate. For example, you can use the @PostAuthorize annotation, like so, in your controller:

@PutMapping("/updatevalue")
@Transactional
@PostAuthorize("returnObject?.ownerId == authentication.principal.ownerId")
public MyWidget update(String value1, String id) {
    MyWidget widget = this.repository.findById(id);
    widget.setColumn1(value1);
    return widget;
}

With this arrangement, Spring Security will check the return value's ownerId against the logged-in user. If it fails, then the transaction will be rolled back, and the changes won't make it into the database.

For this to work, ensure that Spring's transaction interceptor is placed before Spring Security's post authorize interceptor like so:

@EnableMethodSecurity
@EnableTransactionManagement(order=-1)

The downside to this solution is that there are still the same two DB calls. I like it because it's allowing the framework to enforce the authorization rule. To learn more, take a look at this sample application that follows this pattern.