Signed queries

An idea that may lead nowhere but I had to get it down so we can think/talk about it...

tldr; What if we signed queries instead of/in addition to storing them? It would completely decouple the client build from the server.

If we zoom way out what is the purpose of hashing? Well it serves multiple purposes:

• It obscures the query from an attacker
• It is more efficient to send over the wire than a full query
• It allows us to guarantee provenance

The final one is arguably the most important. It isn't really a problem for an attacker to know what the query is, at least our security defenses shouldn't rely on that fact. And there are other ways to optimize network efficiency. But the most important part of hashing a query is to guarantee provenance.

We've said it many times, but technically you could use a uuid in place of a hash and it would work, because we really just need a way to know we authored the query, not an attacker.

But then we have this problem, we need to store the queries. And we need to do that centrally. And that means hashql is more convenient in some dev workflows than others. If you use microservices, or you have a clear division between frontend and backend, it may not be so simple to store queries in the database at build time. This problem becomes even more difficult the larger the company gets.

But if instead, we signed the hash either with public key encryption, or a shared secret etc, the server could execute queries from remote distributed sources with guaranteed provenance.

We could use JWT's. This would give us a standard format where we could include expiry, capabilities, postgres role information and the query. Then that JWT can be stored directly in the client side build, and sent to the server. The server verifies the signature and the expiry and then executes the query with the encoded role.

You could make quite sophisticated systems in a large company to have complete control over execution and scope. E.g. you could give the marketing frontend team a different secret to an admin dashboard frontend team. And so even if the JWT accesses a table / schema / role that they shouldn't you can easily restrict access.

Another benefit would be, with a standard token format you could ship a standard server module/middleware that consumes these tokens and executes them once verified without any glue code (other than connection details). The JWT can include the role to use, server settings, timeouts, etc. It all comes down to how much you trust the signature, but for a lot of use cases that should suffice.

The issue names no files, tests, or entry points. Start by reviewing the existing query hashing and storage design, then determine whether signed queries, JWT verification, expiry, capabilities, and role handling fit the project. Done would require an agreed design and implementation scope, but the issue does not define those criteria.