
Designing the most performant row-level security schema in Postgres (2018) - dfee
https://medium.com/@cazzer/designing-the-most-performant-row-level-security-strategy-in-postgres-a06084f31945
======
MuffinFlavored
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.

~~~
wefarrell
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.

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

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

~~~
api
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.

------
lmeyerov
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!

~~~
wvh
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.

~~~
lmeyerov
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?)

