Hacker News Reader

Comment by Glyptodon | Hacker News Reader

One of the things that's not obvious to me about things like this (and other similar tools) is where/how scopes/limitations/permissions are handled. I assume they either are or can be, I just never see it spelled out clearly. What am I missing?

View on HN

I can't speak for this project specifically but for some context, Postgraphile's way of solving this, as it only supports Postgres, is to use Postgres's Row Level Security feature, whereby you enforce scopes and permissions at the data layer, as well as using table grants to roles specified in JWTs and such. (https://www.graphile.org/postgraphile/postgresql-schema-desi...)

This project doesn't seem to have any inbuilt AuthZ functionality, so unless your database has that built in like Postgres, or you need something that's not possible in-database, I guess you just... can't.

I tied this "no backend" approach but ended up writing all of my business logic in the DB schema instead of in the app, which seemed to defeat the purpose.

Well if your application has business logic at all you're going to have to write business logic at some point, and I personally like the idea of keeping as much of it co-located with the data - it makes your schema the SSOT for your data structure and it's constraints and transformations.

The complication is as always external services, like sending email, validating IAP receipts, things like that. For that I liked to take some of the principles from the 3factor app devised by the Hasura team, and make them asynchronous, and performed by worker processes (in my case, with graphile-worker).

Agreed, I really liked having everything so close and tightly coupled with the DB. The integrations and business logic were the part I had to build traditionally and at that point it wasn't much more to query the DB from the backend rather than trying to learn how to script inside Postgres.

I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with.

The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB.

In my case, I simply could not figure out how to use the Users table without having the password returned in all queries. I could turn off the automatically generated queries that Postgraphile made, but at that point why bother with it?

Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer.

Graphile makes https://github.com/graphile/migrate, however, it seems incredibly brittle and is built on a workflow that will absolutely break production if it is slightly deviated from.

I really liked using it! I just hope some of these developer ergonomics issues could be better handled.

> In my case, I simply could not figure out how to use the Users table without having the password returned in all queries

The recommended way of doing this is to store anything you don't want public in a separate table with a one-to-one relationship, and then controlling access to that table through computed columns and such (https://www.graphile.org/postgraphile/postgresql-schema-desi...)

What I can't recommend enough to people starting with Postgraphile is to check out graphile-starter, and especially it's first migration: https://github.com/graphile/starter/blob/main/%40app/db/migr...

That really helped me to understand better how to structure my schema, as separation of concerns within a model by using different tables is not something that is necessary when doing a regular application-in-front-of-datastore type app.

> Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer.

Yes, I don't have to grapple with this issue so I don't really have much to say about this. Graphile-migrate works great for me, but I can see how it could be an issue for larger projects, although I would think that database migrations are simply tricky on their own, regardless of Postgraphile.

You’re completely right about the starter and I feel if I had discovered it first instead of trying to roll my own migration system with Knex and stuff, I would have stuck with Postgraphile.

But instead of rebasing my project with the starter I decided to go back to what I was comfortable with and do a Rails gql api.

I’ll definitely be trying it out in the future though! I felt so incredibly productive with rolling stuff out before auth became a concern.

Yes it's been incredibly quick for me to iterate and build my current project. You are still right about migrations though, I'm not sure how that would work in a bigger team with feature PRs etc.

Also, you’d want to put the passwords/secrets table in a separate schema from the one you’re exposing postgraphile to

I tackled this by using SQL as the filter language where the generated code will fill in some context-specific variables like current session user id or current request object id.

This is a little limited in this current form and I'm working to expand SQL filters to match up to HTTP codes so you can say this request needs to have a session otherwise it is a 401, then it also needs to match another filter otherwise it is a 403. But this other endpoint is ok to show without a session if the object being requested is marked public in the db.

There's a lot to think through especially when extending these filters to bulk methods.

https://github.com/eatonphil/dbcore/blob/master/examples/not...

Reply on news.ycombinator.com