[charcircuit]

>We added a point of failure, as the permissions table can get out of sync with the actual data.

>The main risk with pre-computed permissions is data getting out of sync.

It would make sense to have permissions be a first class concept for databases and to ensure such a desync could never happen. Data being only read or written from specific users is a very common thing for data so it would be worth having first class support for it.

[eliocs]

Lot of 'new' databases are basing their moat on this and sync engines. Eg: supabase, zero.dev, jazzdb, etc.

[valiant55]

I'm struggling to understand what the issue that the author is getting at. The point of a database is that it's ACID compliant, wrap insets/updates/deletes in a transaction and no such drift would occur. What am I missing?

[charcircuit]

I don't think you are missing anything. I think he is just pointing out that technically nothing is enforcing this synchronization, so if someone forgets to wrap things in a transaction, it could get out of sync.

[ahsisibssbx]

Depending on your DBMS and isolation level, using a transaction might not fix things. That being said I don’t think (at least for Postgres) most people are using an isolation level that could cause this.

Much more likely I think is that you can’t use the db to prevent invalid states here (unique constraint, etc) and you’re dependent on other areas of the code correctly implementing concurrency controls. Race condition in resource A causes problems in your permissions table now.

And just from a general engineering perspective, you should assume things are going to fail and assess what your path forward looks like when they do. Recovery script sounds like a good idea for a critical area.

[eliocs]

I just want to point out you have to take care about that, yes you can have a trigger or a transaction to make sure it happens but it isn't there out of the box

[jeffbee]

Why is it a useful property that everything is always "in sync"? I propose this is not possible anyway. These systems are always asynchronous, and the time of check is always before the time of use, and it is always possible that a revocation occurs between them, and this problem cannot be eliminated.