Designing the most performant row-level security schema in Postgres (2018) | Hacker News Reader

Designing the most performant row-level security schema in Postgres (2018)

medium.com

75 pointsdfee10 comments

What is the best practice nowadays for handling RBAC/DAC/ACLs policies for a normal modern SQL-backed rich web app? In-SQL, a policy decision point microservice...?

I keep seeing systems too tied to partocular backend web frameworks, which makes it difficult to have a heterogenous backend. Further, unclear how to deal with say app caches in modern frontend and service systems, or avoiding getting too tied to an authentication system / API gateway / some VC company (eg, auth0, replicated) / non-OSS. Keeping a clean layer here has gotten quite confusing, afaict!

Ah... the eternal problem of how to solve pushing down authorisation into the database layer without wrapping all "simple" queries with logic along the lines of "only if owner imatchess id...". I understand SQL is a generic tabular data store concept, but it would be nice sometimes to have a concept of users or data owners without mixing the authorisation and business logic into complex queries.

I don't think the current multi-user functionality of databases is terribly useful as most applications only use one database user to connect to the database, but then have to emulate record ownership on an application level (i.e. lots of website accounts) which tends to get pushed down into the SQL itself for performance reasons.

Now we move into big data, data ownership and user permission grants on that data, it would make sense to have a better concept of records having an owner (in the GDPR sense of the word) and it would be convenient if this could be abstracted somewhat into SQL itself instead of complicating all queries.

Yeah I assume most apps are still single SQL user so auth is code-managed while data lakes are better as native.

So my q is modern arch for the app code services -- like, authenticate via JWT and pass that around, maybe even through internal requests, but where does the authorization logic go? A middle tier REST microservice that does all SQL queries, like a graphql layer? (And then DIY RBAC wrappers over the graphql?)

Great points, there are a lot of benefits to using a separate service to manage access control. You could even imagine using one service to control access to different datastores, then forwarding a role/user credential to each datastore to allow it to further control access, (making the solution offered in this article just one of those stores).

Many datastores support RBAC natively, so each of your stores could define access controls exactly internally which seems like a great separation of concerns to me. Personally, I've been digging into row-level security in Neo4j and Elasticsearch, both of which may be on their way to stealing my heart from Postgres but haven't quite yet.

How frowned upon is it today to just do

``` const { accountId } = decodeJwt(req.headers.authorization) SELECT * FROM items WHERE account_id = :accountId ```

I get that it's about the exact opposite of what the article is trying to do but it looks a whole lot easier to maintain.

I wouldn't frown upon it, and would be surprised if the vast majority of apps didn't use app level segregation.

At some level of complexity SQL gets hard to migrate, upgrade, switch vendors and even cloud providers

The article is solving a much more complicate use case where items can be read by a list of users and written to by another list of users.

"Performant" again. That term needs to go away.

Use "efficient" or simply "fast" if that's what you mean.

There seems to be a general tendency for fields to come up with useless jargon. My favorite for years has been "deplane" for letting people off airplanes, but "performant" seems even more pointless.

Performant means time efficient. Efficient could mean time, memory or both. Fast can also refer to development time.

Reply on news.ycombinator.com