miles-po

Another method, which has worked for me, is to never store passwords in the database at all but in a separate authenticator like Google Auth or AWS Cognito. Then the authenticated username is passed in via signed JWT. The database can perform authorization, but authentication (and authentication method) are kept far away from the data in your database.

49.7 days? I wish I could get more than 7 out of my MacOS Catalina laptops before I get a gray screen.

doubleplus-good

> Postgraphile requires setting up row level security policies, meaning you have no control over what layer of code you want authz policies to live; they must be in the database.

I might go ahead and argue that RLS in the database is exactly where security on the data model should go. Past a certain size, many databases have more than one app talking to it. This means N servers that must adhere to the same authorization rules. Hopefully at least they're written in the same language.

Not all restrictions are data-oriented though, and on that note, I agree that the restrictions should live above the database using some schema-stitching or federation.

Version control is a matter of saving the schema changes through migrations and optionally DDL audits through event triggers. I would argue that testing/tooling as you seem to be imagining it is a moot point though. I find it much easier to define table, column, and row restrictions at the DB level and let those constraints bubble up. If a table is unavailable to a set of roles/groups, better to say so in the database so that no query at any higher level could accidentally allow access when it shouldn't have. Row-level security policies don't care if someone is executing a simple `SELECT * FROM example` or a 100-line monster. That row in isolation or as part of a gargantuan query reading/writing through a view or three doesn't matter. The reason Postgraphile relies on PostgreSQL itself for authorization is precisely because PostgreSQL does such a good job of it.

You are correct that simple GUI tools are lacking in this regard, but I'm not sure the use cases I've come across for it fit a GUI tool. For a data platform in a large aerospace company the method of allowing/restricting access was dependent on tagging (both AND and OR). Combine it with some temporal query support, and the RLS policies were essentially code, not just declarative. Then the GUI was the web manager UI (custom). Worked quite well. And while that data platform had some bugs we had to work out as all software does, we never had a problem with the data-level security. We even found some bugs at the app and UI layers precisely because some developers overlooked some corner cases.

App-level integration tests appeared more than sufficient to flush out any problems (or verify correct behavior) in the data-layer security.

Data-level restrictions and query auditing (below the level of an app or server) become even MORE relevant as employee size grows, not less.

I love SQL. I'll write CTEs and window functions until the cows come home with a smile on my face. But not everyone needs to know SQL. There are many other worthy pursuits.

REST suffers from the N+1 problem and overfetching. Most ORMs do as well. "Oh! You want order info AND product reviews? (At least) 2 more REST calls for you. Oh! You don't need the person's shipping and billing addresses in this case? So sorry, take them anyway, otherwise we would have to make another REST endpoint just for you."

Also affects versioning. In REST you commonly see URLS like "/v1/foo/54321" because if you try to remove or rename ANY PART of the JSON payload coming back, you break a contract. With GraphQL, you make the new name available and mark the old name with a directive like @deprecated. Over time you watch the logs to see if any clients are still using the old name. If so, keep it around and write another blog post asking folks not to use it anymore. If not, just remove it. You're done. No broken contracts. No differently versioned URLs.

GraphQL handles N different clients with a single schema. Mobile app clients, desktop clients, B2B clients, et al get exactly the data they need—no more and no less. REST either shoehorns them all into one or two access models or explodes into an unmaintainable mess of hundreds or thousands of often redundant endpoints.

The biggest difference, the one that GraphQL was really originally built to solve at Facebook, was to allow the CLIENT to determine what data they needed at any given time rather than REST which may be driven by client requests but ultimately is what servers THINK clients will need, and is much harder to change as needs change (and they will change).

SPARQL is a flat result set, just like SQL. Also so much like SQL, it's not actually an advantage over SQL, just removed from a relational database.

GraphQL is hierarchical. The queries look substantially like the output JSON. SPARQL (and SQL) require training. With a common tool like GraphiQL or Playground, queries are simple enough and abstracted enough (am I querying Redis, PostgreSQL, S3, or some combination?) that clients don't have to know much of anything except what data they want. That's huge.

It puts the onus on server developers to audit their data and security models, but they should be doing that anyway no matter what the stack looks like.

"I've got 12 browser windows with 50 tabs each. And one of the tabs is playing a YouTube video while another is playing a movie on Netflix. And I've got a Facebook tab or two. Bunch of social media. Then there are the browser extensions. Gmail. A chat client or three. Basically running whatever quality of code and image optimization the sites I visit.

"Hey! Why is my browser using up 10GB of RAM all by itself? It's so bloated!"

tl;dr: don't just blame the browser

Let's be clear: many folks today are directly exposing their databases through REST. The API protocol really doesn't matter. CRUD has no affinity for any one technology or methodology.

GraphQL is no more vulnerable to crawlers than any REST server with OpenAPI on it. And yes, introspection can be disabled.

It's not like tools like Hasura, Prisma, or Postgraphile have no security baked into their products, often via a cryptographically signed JWT.

Query cost analysis. Query depth limits. Query pattern allow lists.

And that's all assuming the GraphQL server has a public IP, which is far from a certainty (just like REST).

Row-level security is not at all dependent upon DB users/roles. Storing app user data in a table for lookup is sufficient. Storing the user info in a session config value as provided by JWT can be even better.

Basic database table replication will suffice for the former. No replication required for the latter.

For the most part, you can insert/update/delete.

See: INSTEAD OF triggers.

That is unless you're using MySQL/MariaDB, in which case views are the least of your worries.

If you're trying to map app users to DB users for more than 1,000 users, you're doing it wrong. Roles at that scale tend to be more vague: admin, hr, analysis, etc. Users (and tags) go in a table. Then row-level security authorizes through the user table for individual queries.

Row-level security is absolutely not dependent upon DB roles. Table-level security on the other hand is sufficiently coarse-grained that mapping GRANT/REVOKE to applied roles should be feasible.

If you're punting all of this security to the app layer, that's your prerogative, but don't pretend that it's somehow more straightforward or secure. If it seems simpler at the app layer, you may very well be missing something.

Not clear how to add or notify anyone to add. Missing Prisma (https://www.prisma.io/) and Postgraphile (https://www.graphile.org/postgraphile/) in the Databases section.

In code, as long as that code is JavaScript. Looking at endpoints.getPost, assuming you pass in an id, what does that result look like? "It can look like whatever query you're making" is not a good answer. Even if you're the only developer on a project, code you wrote six months ago might as well have been written by someone else.

Let's look at a GraphQL call in terms of simplicity.

  await fetch({
    method: 'POST',
    url: 'http://server/',
    body: `mutation {
             deletePost(id: '542543') {
               id
             }
           }`
  });

That's it for a client call, and I know from the query that it'll return the id of the post (if any) that was deleted.

Using Postgraphile as an example, nothing to do on the GraphQL server side beyond starting it up. In your database:

  CREATE TABLE post (
    ...
  );
  REVOKE ALL ON post FROM public;
  GRANT SELECT INSERT UPDATE ON post TO public;
  GRANT DELETE ON post TO app_admin;

Done. The issue with your approach is that different sections of code may invoke SQL that deletes data. Once a data model lives long enough, edge cases show up. By having the security model live with the data itself, you never have to worry about these edge cases. Set theory for the win! Security bubbles up from the data layer, through the app layer, to the client layer. Also means that if you have two apps talking to the same database, you are no longer required to keep those security restrictions in sync. Got one app in PHP with another in Ruby? No problem. Data security is still intact. Forgot to restrict deletes? Fix the data layer, and the security fix bubbles up. No worrying about all of the possible places that might be affected by this.

Data security at the app level is arguably the wrong level to be implementing it.

Now you may decry the complexity of a fully-formed solution, but honestly Postgraphile meets your requirements quite well. GraphQL really isn't complex on its basic level.

  query {
    post(id: 13245) {
      id
      subject
      body
      author {
        id
        name
        email
      }
    }
  }

Boom. That's the JSON that comes back—follows the structure of the query. Self-documenting. The generated CRUD endpoints cover 95% of needs. Native queries written with the database itself and exposed as functions. Migrations should be handled as either idempotent SQL DDL or repeatable sequence of files at the database level, not the app layer.

Performance-wise, I strongly doubt you'll be faster/more scalable that Postgraphile out of the box. And just because the browser isn't calling the endpoint doesn't mean that GraphQL or REST are useless. Far from it. It's absolutely not just for publicly accessible APIs. That is a false assumption on your part.

GraphQL really isn't as heavyweight as you seem to presume it is. It's just a spec. How someone implements that spec is what makes the difference. As far as request/response sequence, GraphQL is quite cheap computationally. Well documented. Only as complex as the queries you want to make. Hierarchy-aware.

You might as well argue that HTTP is too complex and inefficient; that raw socket calls are better. Why not set up an exokernel?

The reason most of us don't is that it violates the 80/20 rule. Premature optimization and all that. If 99% of the time is spent in the database doing queries and only 1% in your app, fixating on the "efficiency" or perceived "simplicity" of that 1% is a fool's errand.

Okay, let's spell it out. Your tool requires that you specify endpoints. Then call the ORM or make manual SQL queries. In the example, you have a Todo object.

Do you see the code right here? ``` endpoints.createTodoItem = async function(text) { ... } ``` You've already missed the point of tools like Postgraphile et al when you propose tools like that in a discussion like this. You don't have to write endpoints or even GraphQL schemas at all. You literally just start the tool, passing in the database connection info, and start using it. That's it. That's all. That's what I meant by simple, not how many lines of code they required (which is not much of a benchmark). That's what I meant by apples and oranges. I'm sorry you saw that as an attack, but it's true. It is not just a matter of replacing GraphQL for RPC as you suggest. Or REST for RPC. Simplicity from the point of view of the user, not the server parsing code. Your server is effectively writing a data schema to be consumed by a client. These GraphQL tools write the schema for you.

No code at all (other than database setup) is required to get this working. Zero. https://www.graphile.org/postgraphile/usage-cli/

I hear you on the comment "Is your API meant to be used by yourself? Use Wildcard." However at that level, ANYTHING works. If you start with REST or GraphQL, you may not need it now, but if your project grows, you'll have to rewrite everything. Another minor nit: GraphQL does not require HTTP or even a network layer. GraphQL in and of itself is 100% transport-agnostic.

Sure you could auto-generate RPC endpoints from your database schema, but that requires a tool that does not yet exist, does it? And at that point, what advantage does it afford over GraphQL? It's not markedly more efficient. For that matter, if you wanted the most efficiency regarding RPC, why not use something like gRPC that uses protocol buffers for serialization? If that level of efficiency isn't necessary, then how can you criticize minimal protocol efficiency differences between RPC and GraphQL?

Then there's the issue of vendor lock-in. GraphQL is a spec, and a relatively simple on at that. Nothing more. Nothing less. For the most part different GraphQL backend implementations can be replaced with another vendor/implementation. REST is the same. Can the same be said of your RPC implementation? Could someone seamlessly replace it with another tool without changing any client code? Who are your competitors? (Not necessarily looking to outdo you, just occupying the same spec-level niche.)

As for VC funding, Hasura just got some. Prisma has some. Postgraphile is a one-couple shop with a simple Patreon. The REST libraries don't have VC funding to my knowledge, so that comment from you was largely unwarranted and frankly rude. But more to the point, lack of funding or a development team is not an effective point. What happens to the organization using your software if you get hit by a bus? Why should they take on that risk? Sure they can take over your code since it's open source, but if they wanted to go down that road, why wouldn't they just write their own code anyway? I'm not saying you have to be a major multinational corporation, but single points of failure are not selling points to most folks whether those single points of failure be technical or social.

Lack of redundancy is a hazard. Lack of choice is a hazard. I for one am glad so many healthy competitors exist in the auto-conversion of relational databases to GraphQL.

All the best of luck to you in your RPC endeavors. I'm sorry for being harsh, but you jumped into multiple threads promoting Wildcard without in my opinion understanding what you were comparing against. I hope you find an appropriate audience and user base for it. I fear however that this isn't it for the reasons I enumerated.

> Honestly, your whole post is weird to me, I'm not sure what your motive is. You seem to try to put down an open source project that is being developed by someone in his free time and without any remuneration. That's disgusting and without taste. If you only have a lack of tact then I apologize for my words.

Irony is dead.

I appreciate your zeal in marketing this product, but you're comparing apples and oranges. You are speaking of the ease of how you can write SQL queries. With Hasura, Postgraphile, Prisma, and their generated REST counterparts, you aren't writing any SQL queries. You are creating your database schema and then launching the respective apps. Obviously some customization will be necessary since almost no app are 100% CRUD.

But I took a look at your RPC tool, and it adds a lot of boilerplate work. This RPC boilerplate is exactly why alternatives like Hasura, Prisma, and Postgraphile exist.

Also you can't add an RPC layer and magically call it simpler. If you add Postgraphile, Prisma, Hasura in front of the database, it is just as simple, just as much a layer on top of the database. Only in this case you're writing to a published open spec (GraphQL) so the client doesn't have to know if it's talking to a relational database, a Redis cache, a REST data server, an in-memory data model, or whatever.

Your tool on the other hand is just another vendor-specific ORM or manual query engine. If that works for you, great. It is not a good alternative or answer to what is being discussed here.

While the unindexed time is impressive for the lack of index, the GIST and SP-GIST times are not 0.939 sec and 0.358 sec. All times are in milliseconds. GIST returns in slightly less than 1ms, not 1s. SP-GIST returns in slightly more than 1/3 of a millisecond.

Performance for GIST and SP-GIST are in fact three orders of magnitude better than what the author summarized!