I should have said "the security model in most databases is outdated" - the bitterness comes from my own failure to argue for an alternative when we built the security model in Neo4j.

Just like PG, we based security in Neo on role-based access. Users have roles, those roles then have permissions to do lots of cool things.

What we should have done, had we had infinite time (and to be clear: it was the correct choice to build a role-based system given the constraints, I just wish there'd be more time in a day..), was to build a resource-based system; instead of roles, you have graphs of direct and delegated privileges over specific resources; resource-based access control.

If I build an app today, I don't want to say "Users can edit x,y,z columns on the comments table". I want to say "Users can edit x,y,z columns on the comments table, if they were the ones that wrote them and the comment does not have a lock flag".

The bulk of web app code today, for small to mid-sized apps anyway, is about dealing with this resource control. Which is ridiculous - imagine how many CPU cycles are spent on secondary queries to look up ownership hierarchies from dumb ORMs. There's no good reason that couldn't be modeled at the level of, or below, the query planner, having it simply design the query plans to abide by the per-user access constraints.

The cost of that would be massively higher than what the planner does today - but looking at the performance of the whole system, it'd be amazing.

This is, kind of, what Firebase does, right - it rolls resource-based access control into its query planner, combined with good federated user management.

> Users can edit x,y,z columns on the comments table, if they were the ones that wrote them and the comment does not have a lock flag

GRANT UPDATE (subject, text) ON comments TO user;

CREATE POLICY comments_update_own_not_locked ON comments FOR UPDATE TO user

USING (locked IS NOT TRUE AND created_by = current_setting('postgrest.claims.username'))

WITH CHECK (created_by = current_setting('postgrest.claims.username'));

Row-level security in Postgres is awesome!

Holy crap, I hadn't realized just how powerful RLS ended up - last time I looked at this was before 9.5 and it was roles all the way down.. That is fantastic!

That's exactly what I wanted.

I guess it's true as they say, that the easiest way to get the right answer on the internet is to publish the wrong answer :)

The features provided by RLS are not really new, they just add a nice syntax to define the rules. All the things you mentioned can be implemented using roles and views.

Be careful, RLS and views don't play well together, that is to say, your policies on a table for a particular user will not be applied if that tables is accessed through a view.

I don't understand how you can't express this with some triggers and row-level security. Could you please give a technical example?

Hi Jake.

We are currently developing a security model for http://graph.cool that sounds very similar to what you describe wanting for Neo4j. If you have time I would love to show it to you and get your feedback (email in profile).

You mention the security model of Firebase as an example to emulate. Unfortunately firebase only allows permission rules to consider path elements to the data, so in reality it actually ends up being fairly restricted and difficult to maintain.

I <3 you guys in every way :P this is a perfect example of why:

No defensiveness, no push back, just a prompt to start a conversation with a potential user to better their product. :)

The PostgreSQL community has so many friendly people that just exude knowledge and charm. I really like watching talks by Christophe Pettus, to name one person in particular.

Hi, we typically run PG behind a Rails API for authentication but presumably the OP was referring to the Google Firebase JSON based security models [0] which, it seems, are related to "native" authentication methods. Not sure how this would be supported by PG though.

[0] https://firebase.google.com/docs/database/security/

The toolset for something like this is there (row level security, column based permissions), but to get to something equivalent as described there you'd need to build something using the available infrastructure...