Hacker News Reader

Comment by rlili | Hacker News Reader

Unless you happen to be using PostgreSQL, in which case some tools like Hasura and Graphile can automate all of that.

I’ve never used those tools, but I don’t see how you can automate away authorization issues. The GraphQL spec[1] says authorization in the GraphQL layer is fine for prototyping or toy programs, but for a production system it needs to be in the business logic layer.

[1]: https://graphql.org/learn/authorization/

In Hasura, you authenticate externally -- can be custom API endpoint that signs a JWT/auth webhook, or an auth provider like Auth0, Okta, Firebase, Keycloak, etc. Doesn't matter, just have to return some claims values.

You can then use these claims values in your authorization (permissions) layer.

IE, when a user logs in, you can sign a claim with "X-Hasura-User-ID" = 1, and "X-Hasura-Org-ID" = 5, and then put rules on tables like:

  > "Role USER can SELECT rows in table 'user' WHEN X-Hasura-User-ID = user.id"

  > "Role USER can SELECT rows in table 'organization' WHEN X-Hasura-Org-Id = organization.id"

There's more depth to it than this, but this is the gist of it.

this is really powerful stuff when working with a CISO “the data itself defines who may access it”

You just handle this part my your code and leave the rest to Hasura.

PostgreSQL and other databases have fine grained authorization controls down to the column level, what more does one need?

Yeah this must be one of the most underused features ever. People don't realise that you can solve little bobby tables by just setting the permissions correctly in the database.

You cannot model every business constraint in DB permissions; Stuff like "If customer X has less than 3 active contracts, new contract activations require sign-off of Manager of at least level Y" etc.

That can absolutely be done via triggers or limit access by using functions for certain operations instead of direct table access

And how do you unit test triggers? Yes you can do it in a ton of ways, but you just end up scattering your business layer all over the database, the GraphQL adapter, API gateways etc. The alternative is just to create a dedicated BE service endpoint (in whatever you prefer, REST,HTTP/JSON,SOAP, gRPC etc.), which does the required checks for you.

Triggers, functions or whatever you use are just code; yes, that is my pitch: Have your buisness logic in code, ideally in a dedicated BE API endpoint instead in the DB.

That’s the path I ended up taking. The GraphQL resolvers had no idea idea there was a database. They talked to a layer that understood all the business objects and that sat on top of a layer that understood authorization and only that layer had any connection to the data store.

Are you suggesting that you don't use any constraints (unique, not null...) or defaults in the database either?

That's your business logic in the database right there.

I for one think that the code path that leads to an insert conflict should be integration/unit tested. Something needs to codify how the error is reported to the client.

So no, you don't get to wiggle out of that one.

Unit testing PL/pgSQL is nasty.

I agree for the most part.

But unit testing is not nasty at all: just look up pgTAP.

What's nasty is that the incumbent development model intersperses data modelling constraints in the database and in the non-db code, and then we test one layer implicitly from another layer.

Unit testing is nasty? You can use whatever tool you like. I've used phpunit, junit and jasmine for unit testing databases. Choose whichever tool you like most.

It can get hairy due to how most apps are developed.

You'd usually have backend code guarding against constraint-violating entries, and then you'd have a database constraint too.

So what you need to do now is test almost exactly the same thing from your code test and from your database test to get proper coverage.

Enter declarative ORM schemas, and suddenly there's not even a guarantee that the schema you are running really matches what your ORM thinks the schema is.

For that reason, I prefer all those SQL-based database migration/evolution approaches over ORM-based schema generation, coupled with pure SQL tests (eg. with pgTAP, but yeah, any tool can do).

Basically, even for declarative code, there should be a unit test, a la double-entry bookkeeping in accounting.

And even if this is what I prefer and believe is only right, I never worked on a large company project that did all of these things.

So I don't think the entire topic should be easily dismissed: while unit testing is simple, have you ever worked on a project that tested db-schema embedded logic exactly at the right level (and not the level up)?

In my mind that's just an insert that joins the contracts table and makes a case active_contracts < 3 then true else false, for the require_sign_off column.

You cannot trust the query issuer (Browser or App on the client). If you have a public GraphQL API, you need to enforce these rules. If you can just alter the query to bypass the business rule, this is called a security hole.

Observability, flexibility (I don't need to push a migration to change auth), SSO integration, and the ability to keep a clean separation between user and "machine" (service, replication, etc) accounts.

What does observability mean? Can you translate this into a concrete question?

As for flexibility, do you mean authentication or authorization?

SSO can be done at the SQL server level (MSSQL has it and so does Postgres, don't know about others), but handling the SSO part in your app and using "set role" and passing a user ID for your row level security policies to use is easier to set up and more flexible.

Clean separation between user and machine accounts can absolutely be done with MSSQL and Postgres.

I say it over and over again, row/column level permissions are not even close to enough for any larger app. How do you translate access restriction like "user X cannot view more than 10 articles per month" or "customer Y cannot insert any more orders if total outstanding/unpaid amount of invoices in last 30 days succeeds Z" into row/columnlevel permissions? You don't, that's why you have a business layer.

> How do you translate access restriction like "user X cannot view more than 10 articles per month"

That's not permissions in the general sense of authorisation, that should just be modelled as any other "business logic". Put that article_limit in the users table and join it in the selects.

Edit: or tracking the users article views with timestamps, and making a select aggregate over the month..

> "customer Y cannot insert any more orders if total outstanding/unpaid amount of invoices in last 30 days succeeds Z"

insert into orders select from user a inner join invoices b etc.. any kind of limitless complexity here on how you want to limit the orders.

But if you design a public GraphQL API, you cannot trust the query issuer (Browser or App on the Client). You have to enforce those rules outside of the query. And yes, there is tons of ways to do this outside of GraphQL, which is exactly my point, that row/column permissions alone do not suffice.

Ok I get you now, yes you are correct, if you have a public API and you have business constraints that need to be enforced, you have to do this server side.

But you can still implement this easily in queries, so for example granting the client only read permissions, plus execution permissions on certain database functions, and inside these functions you can implement the constraints.

What I'm trying to say, is that you don't necessarily need any backend "services" java etc, to implement these type of constraints, they can be modelled as any other business logic in the database.

agreed. i've worked extensively with graphile, and from working with people who were really strong in postgres i learned all the things we needed could be modeled in postgres. many of the less obvious solutions would involve functions, triggers, or stored procedures. but i liked that there was less ambiguity about where that kind of logic was implemented.

And now you're adding an extra layer you don't control with its own set of problems.

Using postgraphile for my current big project is the best technical choice I've ever made. There's been the occasional obscure sql incantation to learn but otherwise has been so much more productive than hand-crafting REST endpoints.

Reply on news.ycombinator.com