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.
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.
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.
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.
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.
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)?
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.
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.
