Postgres row level security as a means to authorization

Questions / Help
, ,
1

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

6 Likes
2

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?

3 Likes
3

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.

1 Like
4

Not sure but didn’t Supabase offer this? Recently they started publishing some of their code as open source so that could help, maybe.

1 Like
5

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.

1 Like
6

While I agree that Postgres is a fantastic database, support for features only it provides would drastically reduce the use cases where Phoenix can be utilized.

For example: every migration project that is not already using Postgres would be very complicated or not possible at all. Implementing authorization this way would also make it hard to have data readable from different stores: e. g. how do you make sure the user is authorized to access data in a redis store? Suddenly you have to check this in two places and you probably have two implementations to do so.

The comparison with tailwind is - in my opinion - lacking because opting out of it would be fairly easy. Opting out of a Postgres layer that not only provides the data access but also the authorization code is hairy because now you need to provide it by other means (like in the redis example above).

So as long as you’re using a framework that shall be useable for a variety of use cases/datastores you probably want to treat the store as “dumb” and something like Ecto seems to be a reasonable abstraction over all those different types of databases and it’s easy enough to write new adapters for it.

There are other frameworks like postgraphile, PostgREST and prest which use Postgres to it’s full potential and there are cases where this could be useful but in my mind this would be a different framework / library for data access that would be a replacement for Ecto. This should be completely possible using postgrex in Elixir. pRest (which I linked above) is nothing more but an implementation of PostgREST in Golang instead of Haskell - you could do that for Elixir and would probably use Phoenix as the web layer.

So all in all: PostgreSQL is fantastic but using something like Ecto, while having it’s own merits, will make compromises on features necessary.

1 Like
7

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…

2 Likes
8

I’m interested in your thoughts on this @mmmrrr because the more I think about it the more it seems to make sense to me to build web apps directly in Postgres, particularly using Supabase.

You mention in your Nov/2021 post that the main problem you saw with using row-level security in Postgres is that when you take this approach you end up coupling business logic with the datastore.

From my perspective, business logic ought to be coupled with the datastore because this prevents data corruption and enforces business rules. So I would be interested in learning about some of the practical drawbacks you saw in taking this approach.

As far as I can tell, Supabase is just a Postgres database, so you can fire up a Phoenix app and connect to it just like you can connect a Phoenix app to any Postgres database. This means that you can use Ecto with Supabase from Phoenix because all Ecto needs is a database connection.

I don’t see a conflict between using row-level Postgres security and Supabase as the datastore for a Phoenix app – in fact, since auth is moved out of the Phoenix layer and into the datastore layer, it makes it easier to write multiple different front end clients, thereby making the application more flexible by allowing multitenency.

So the question is why use Phoenix at all if you’re just going to have Supabase handle the data and the API generation and the auth and the business logic and basically everything except the UI widgets on the front end? I think this is in fact a very good question, and it does seem to me that most people would just be better off building their app using Supabase and some lipstick to paint a face on it with.

You make the point in your Dec/2021 post that while you think Supabase uses “the right tool for the right job”, you think it is a more complicated setup to operate than a majestic monolith in Phoenix – but this assumes that a person building an app using Supabase wouldn’t just pay $25/month to have the Supabase people operate everything, which seems to me to be the sensible way to go.

So where does that leave Phoenix? I personally think it still makes sense use Phoenix LiveView as a front-end framework for Supabase, but the particular business concern would be the determining factor here – you’re going to want a BEAM program for situations in which you need massive concurrency, or a way to hold open long connections, for some particular aspect of the business application.

9

If you can afford it, then this is not a problem. If you have projects where you have different data stores, e. g. migration projects, or multiple projects with the same team but different databases you simply cannot use Postgres in any situation.

In my experience greenfield projects are just a tiny, tiny fraction of real world scenarios. Most of the time you will have to integrate some legacy data store.

Be careful to be very specific here: you’re moving authorization into the datastore, not authentication. For example Hasura has this problem because you need a companion service to perform the authentication. Supabase is doing a better job in that regard afaict.

This is indeed a good question and it comes down to taste and skills. Like everything in IT this is a game of tradeoffs: how flexible do I need to be in the frontend (i. e. “can I use the supabase client integrations for all use cases”)? How can I write custom functionality in the backend (i. e. to process incoming datastreams that cannot be expressed using standard features of you choosen platform)? Do I need server side rendering? Do I need to expose non standard APIs? Etc. etc.

So when you’re mostly a frontend engineer and mainly need a relatively simple CRUD API, Supabase, Hasura, SubZero and the likes are probably a smart way to build your app. And this is imho the target audience for all these tools. For real world applications it simply doesn’t matter so much because the complexity is simply shifted to another area (you can solve every problem I described above with those tools but it’s neither simple nor easy).

This is true until it isn’t. To quote the supabase website: $25/month + any additional usage. So this might get expensive very fast. Also depending on the project you maybe cannot afford a vendor lock-in, or you cannot let someone else handle your data. Supabase at least does offer enterprise and on premise support - but I’d assume this is also for enterprise pricing :smiley: Since it is open source you might get away with hosting it yourself but then the operational complexity strikes again.

I don’t think this would be wise. As mentioned Supabase et. al. are awesome when you’re a frontend engineer and cannot be bothered to write a backend. LiveView will not help you at all to consume the awesome APIs you just created since with LV you’re already working directly with the database - so there is no need for an HTTP-API in between.

All in all: it depends :slight_smile: But in my experience in nearly all real world scenerios those tools crumble and you start working around shortcomings with really ugly hacks. That’s why I try those tools every few month and everytime I decide “nope, that’s not for me” :smiley:

10

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.

2 Likes