Advantages over Row Level Security?

[mlegenhausen]

Just in case you can compare both versions?

[sjmcdowall]

@mlegenhausen -- I am going through implementing security myself within postgraphile -- when you say "compare both versions" .. what exactly are you talking about? Is there a plugin that does automatic RLS somehow or ??

[mlegenhausen]

Not automatically. I set the RLS on each table by myself.

I was just currious about your design decisions. Maybe this plugin delivers a better performance than the RLS version. RLS is sill very young and maybe not as optimized than setting the tenant_id directly via the query.

I would like to implement the multi tenancy separation and other security models directly into the database. For me it feals more natural to see postgres as an inteligent data vault instead of a dump data store (farewell mysql 😉).

[sjmcdowall]

Our design decision is based on two things -- our application is currently 15 years old (!!) and back then Postgres didn't have a lot to offer security wise and 2) it's a SSR app (back then about the only way) so 80% of all DB queries are using a abstraction layer that generates the queries to make sure there is no leakage, etc.

Now we are moving towards a SPA type app and Graphql .. so wondering how to implement security in this new world. :)

[mlegenhausen]

The most basic RLS policies can be

create policy mytable_tenant on myscheme.mytable to mytenant_role
  using (tenant_id = current_setting('jwt.claims.tenant_id')::uuid)
  with check (tenant_id = current_setting('jwt.claims.tenant_id')::uuid);

Thats it. This makes sure your data is isolated and any tenant can access the data of another tenant. Doing the separation in the application layer can result in data leaks.

Postgres has a very powerful security model and I was tired to always reimplement it by myself in the application layer. This also allows me to see my database as another API and postgraphile is just a translation layer from my database API to a graphql API. Nothing would stop me from running a postgREST instance in parallel with the same security as my postgraphile api. Who knows how long postgraphile will exists? But what I can say postgres will be there even after 😉

[sjmcdowall]

@mlegenhausen -- thanks, that makes total sense! BTW -- have you checked out Hasura for their (newly) open-source GraphQL / Postgres solution? Different approach for sure but pretty interesting in its own right.

[mlegenhausen]

Haven't find out the advantages over postgraphile? The graphical user interface looks good at first, but IMHO you should not use it for modifing your database.

The first thing I did after switching to postgraphile was to look for a migration tool that was technology independent and I made sure that all changes were only made through migrations that were also reversable. I am currently using sqitch which is database independent and follows a git like workflow. If you work in a team you should create roles that are unable to change the structure of the database and throw away the root password (metaphorically) 😉

Then I bundle up all my database changes in separate docker container that is only there for running my migrations. This makes the technology stack much cleaner.

I can also recommend pgTap for running unit tests directly in your database written in pure SQL.

[nick-kang]

As of Postgres v15, I don't think RLS policies are used to optimize the query plan. That means you'd query for all the rows and then filter by tenantId. By using this plugin to append the tenantId in the query, you can get optimized queries given a properly indexed schema.