No, they aren’t. At least not Postgres, and AFAIK its true of every other major RDBMS, too.
Non-DB-specialists typical level of knowledge of DB permissions may be, but that’s a whole different problem.
Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong.
[EDIT] The following was removed from the parent post. It was in response to "you can't audit queries".
> No, you don’t
Citation needed. Yelling "No, you don't" without anything else is fscking useless in moving a conversation forward.
If the query is coming from the frontend, it's coming from a client that is outside of your control. I.E. you don't know what queries will come from the frontend, because the user can execute an arbitrary query.
If you take an effort to extract and bake those queries into a backend (perhaps calling it a proxy), then you're not actually emitting SQL from the front end, you're relying on a backend to emit the actual SQL used after performing a number of security checks. AKA, the status quo.
So?
You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other.
You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine.
I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.
0 millions for me.
I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership.
The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.
Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.
For non Kerberos Linux systems using LDAP to synchronize group membership is the only extra automation needed.
It is possible and fairly easy to manage a very large number of accounts IF done properly.
SPARQL is really nice for querying graph data, but I'd argue that for most applications tree-like hierarchies are good enough. Also GraphQL and associated frontend libraries are written to be consumed by browsers and results are much easier to handle than results from a SPARQL query, JSON+LD is not the easiest format to handle. The tooling around SPARQL is not great compared to GraphQL either.
Furthermore I'd argue that SPARQL queries are really hard to statically analyze / optimize on the Backend. Different SPARQL engines behave differently and whether you run your query against e.g. Stardog or Virtuoso can have vast performance differences. SPARQL being hard to analyze statically actually becomes apparent once you start thinking about authorization of resources, it is not a trivial problem to know to which resources a client should have access to or not.
And while it is true that you can model provenance, authorization and all kind of other niceties in SPARQL as well, you will likely be on your own building all those tools yourself.
I believe SPARQL is a great query language for public / open data, e.g. Wiki Data, Government data, etc, but in a business context I'd rather choose REST or GraphQL over it.
The complexity and security costs just keep going up the more logic you shove into an untrusted client.
- super hard to cache if any client can basically generate an infinite number of different queries.
- which dialect of SQL ? If you change DB or upgrade version, do your ask all clients to change as well ?
- how do you do query that involves several data sources ? Some response data come from redis + elastic search + postgres. That's why FB created graphql. That's also why it's so limited in scope, so that complexity is manageable.
- do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?
No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement.
Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features).
Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins.
Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level.
This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.
The way I understood the article, the ideas is 'can we expose a safe subset of SQL or fix the security issues instead of continuing to build entirely new systems'
People forget (or don't care) about security or safety when it lets them move faster.
"Move fast and break things," is a terrible security model.
For one, permissions around SQL are already crap. It takes the smallest screwup to expose data in co-mingled databases. No need to make it even worse.
For two, at least if the SQL is on the backend, a fix for an exponential query DDOSing your DB is fairly quick; you don't have to worry about some client keeping a cached copy of the frontend around for months at a time.
Finally, if you let the frontend send SQL, you have lost any and all ability to do a static audit against the queries that will be run against your database - because you have no control over what the client does.