4 ms·

This is super cool - but as others have said, you can't expose this to a public URL, it needs a security model. The security model in PG is, unfortunately, seve

This is super cool - but as others have said, you can't expose this to a public URL, it needs a security model. The security model in PG is, unfortunately, severely outdated. Now, if you combined this with a good RBAC security model, particularly if you baked that model into the GraphQL -> SQL conversion layer, so it sends SQL queries that work on an allowed subset.. that'd be very cool. The security mechanisms in Firebase might serve as an inspiration :)

deleted 10y ago
[deleted]
netghost 10y ago
If you look at the demos, it looks like you can do RBAC security. The user id is stored into a session (transaction?) local variable, and then used to filter various tables using postgres' row based security model.
- floatboth 10y ago
  Makes sense — PostGraphQL was inspired by PostgREST, and that's exactly what PostgREST does.
anarazel 10y ago
> The security model in PG is, unfortunately, severely outdated. What would you like to see? (PG dev here)
- moflome 10y ago
  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/ https://firebase.google.com/docs/database/security/
  - anarazel 10y ago
    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...
- shermanyo 10y ago
  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. :)
  - 3131s 10y ago
    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.
- jakewins 10y ago
  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.
  - egeozcan 10y ago
    I don't understand how you can't express this with some triggers and row-level security. Could you please give a technical example?
  - floatboth 10y ago
    > 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!

deleted 10y ago

[deleted]

netghost 10y ago

If you look at the demos, it looks like you can do RBAC security. The user id is stored into a session (transaction?) local variable, and then used to filter various tables using postgres' row based security model.

floatboth 10y ago
Makes sense — PostGraphQL was inspired by PostgREST, and that's exactly what PostgREST does.

anarazel 10y ago

> The security model in PG is, unfortunately, severely outdated. What would you like to see? (PG dev here)

moflome 10y ago
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/ https://firebase.google.com/docs/database/security/
- anarazel 10y ago
  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...
shermanyo 10y ago
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. :)
- 3131s 10y ago
  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.
jakewins 10y ago
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.
- egeozcan 10y ago
  I don't understand how you can't express this with some triggers and row-level security. Could you please give a technical example?
- floatboth 10y ago
  > 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!