Postgres row level security as a means to authorization

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] : http://postgrest.org/en/v5.2/tutorials/tut1.html
[2] : https://github.com/subzerocloud/postgrest-starter-kit/blob/master/db/src/authorization/privileges.sql#L22

5 Likes

I’m surprised this thread did not receive any comments. Row-level security seems like an elegant way to increase security while simplifying the elixir/phoenix code. Shouldn’t this be the default way all phoenix apps are built? Is there any good reason not to use it?

Actually, there is: you are bound to Postgres (or at least systems which implement such a concept), which makes it an ill fit for a lot of problems.

The main problem I saw back then (which was why I tried to gather some opinions here :wink:), was that you start coupling business logic with the datastore. This makes it - imho - completely inadequate for the usage with Ecto, which is an abstraction layer for database queries.

IMO there are two options one could implement in Elixir:

  1. Make a nice library which wraps all those details in a consumer friendly Elixir API and goes all in with Postgres and nothing else
  2. Do it like Hasura.io and implement an “SQL-compiler” which you can feed some DSL to express the rules that apply to a specific cell and then emits a query that fulfills all those rules

I started a sloppy implementation of the second back then but it is relatively complicated to get right - and it simply had no priority, so it went nowhere.

The first solution might be the easier of those two options but is hardly something that could be adopted in phoenix by default because the framework needs to also support SQLite, MariaDB and MSSQL out of the box.