[erichanson]

Big difference is it's implemented in pl/pgsql so as Aquameta evolves, there's no external deps. There's a function called endpoint.request(http_verb, URL, post vars) and then it does the rest in plpgsql. A thin Go daemon just takes the request and throws it at the function.

It does a lot of the same stuff PostgREST does. Automatic REST interface to any database, but it also hosts static resources, and dynamic mapping of URL templates to functions.

Needs a rewrite though. That's probably the next big dev push. Right now we're rewriting the system catalog (meta) and the data VCS.

[cryptonector]

Sounds really good. Your vision is excellent, and I admire it and your work.

BTW, it's very important to get the details of authentication and authorization right. PostgREST does: it validates JWTs and exposes the claims to the SQL application via set_config()s, just like every detail of every request, and it does a `SET` to set the role to whatever the authenticated user is, though this could be done by a SECURITY DEFINER function instead that looks at the claims.

[erichanson]

Real talk.

https://www.postgresql.org/message-id/flat/894C0144-5BCC-41C...

set_user extension is AFICT essential for scaling user-per-role.

[cryptonector]

I'm tempted to build something similar (because I can't use GPL libraries at $WORK and I also can't use PostgREST for reasons), but somewhere between what you do in Aquameta and what PostgREST does, with as much logic as possible in SQL or PlPgSQL.

Basically I'd have a thin HTTP/REST layer that does a set_config() for every request header and q-param, and which compiles the resource path to a SELECT (for GETs) or a DML (for POST/PUT/DELETE/PATCH), with similar restrictions to PostgREST, though perhaps a bit more liberal.

For DMLs the local-part would have to be just a table/view, or a function call (what to do with the request body in the case of a function call? reject it).

For a GET... I've ideas. The simplest would be to have a very simple "language" to compile to SQL, not too unlike PostgREST. The craziest one would be to have a transliteration of literal-value-free (certainly no strings, no quotes, no arrays, no row values, maybe only just integer and boolean literals) SQL statements to local-part, and the code to convert it back to a SELECT would parse the query and make sure it uses no tables/views/functions outside the public schema (and if functions, only ones marked pure), with q-params as... query parameters -- no SQL injection attacks here.

As in PostgREST the SQL code in the database (but not the caller's SQL) could set_config() things like response headers and status-code. Authentication would be done via SECURITY DEFINER functions that consume whatever Negotiate or Bearer token in the Authorization: header and SET the session role (which would always get RESET before the next request).

Except for the need for a parser for validating any queries (for the "crazy" design, if I go there) this would make the Java/Go/Rust/Whatever code quite simple, and w/o requiring any dynamic SQL in the DB.