So I was thinking about the options we have on authorization (read: methods to find out what a user is allowed to do in our application). Of course I know about the plug / elixir based solutions like policy_wonk and canary, but I have the feeling that this is simply not the correct approach, given we have a database like postgres as the default choice for a datastore.

While researching about this topic I found PostgREST [1], a haskell library for auto generated REST apis based on a postgres dataschema. Long story short, with this package I miss a lot I take for granted with elixir and phoenix and it makes the deployment a lot more complicated. But I must say that I love how they solved the data access story.

The idea, as I understand it, is that every DB request is performed using different database roles with varying permission levels. That way you minimize impact if a rouge query manages to get through your defense. The next step is to check, on the database level, if the requesting user can see the specified row (i. e. there is the id of the owner embedded into the row). For an example please see [2]

The only similar thing I have seen in elixir land is mbuhot/ecto_row_level_security, which looks promising.

Question

So now on to the real questions:

• Do you think it is a good approach, to let the database handle the authorization for your application?
• Would you still rather use a dataloader library like canary?

Sources
[1] : PostgREST Documentation — PostgREST 14 documentation
[2] : postgrest-starter-kit/db/src/authorization/privileges.sql at master · subzerocloud/postgrest-starter-kit · GitHub