mmmrrr
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
Marked As Solved
mmmrrr
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 
), 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:
- Make a nice library which wraps all those details in a consumer friendly Elixir API and goes all in with Postgres and nothing else
- 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.
Also Liked
tadasajon
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?
al2o3cr
The simplest reason, IMO, is that there’s a finite amount of CPU available to the database. Spending that budget on stuff besides answering database queries is fine, until you need it for queries.
Historically, scaling databases (especially for writes) is significantly harder than scaling the less-stateful tiers in front of them.
mmmrrr
Supabase is a clever combination of different “best in class” technologies: PostgreSQL as the datastore, PostgREST as the API layer, Phoenix for database change subscriptions, a storage layer / middleware in TypeScript utilizing AWS S3, GoTrue for authentication and Kong as a proxy.
While I like this approach of using “the right tool for the job” it is definitively harder to operate than a majestic monolith in Phoenix…
dimitarvp
tadasajon
I agree that this shouldn’t be the approach that Phoenix uses by default. I would compare using Postgres this way to using Tailwind – Phoenix shouldn’t default everyone into using Tailwind, but it seems to be the approach to CSS that many people in the the community agree is the best. You mention being “bound to Postgres” like it is a drawback, but many people see “access to Postgres” as a feature – we are talking about one of the most fully-featured and reliable database systems on offer – certainly more than adequate for over 90% or maybe over 99% of the scenarios that anyone would be using Phoenix for. In any case, there is a fair amount of commitment to a database that I guess I have come to expect – so, given that we’re making a commitment to Postgres anyway, why not make full use of it’s power and just go with row-level security? Building the app closer to the metal is always a good idea, other things being equal, it seems.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"