`/v1/database/{db_name}/sql` should go through `onConnect` and require Authorization

[sean256]

POST /v1/database/{db_name}/sql

This bypasses onConnect and doesn't require a token. The docs indicate that it should require authorization

POST /v1/database/{db_name}/call/{reducer}

Yet this requires a JWT and runs through onConnect where owners can validate the JWT any way they wish.

Proposal

The sql endpoint should have auth validation and run through onConnect. As it is now it's a potential security vulnerability, especially since the docs indicate otherwise.

With a growing number of developers coming to SpacetimeDB, who knows what unexpected use cases people may find for this amazing tool. Someone may unwittingly expose user data they thought they protected via auth and onConnect validation.

If this is truly intended behavior, then the docs should really be updated to call this out. I would error on the side of caution and just protect it all.

[bfops]

Hey @sean256! Thanks for opening this issue.

The docs are actually wrong here - the Authorization header isn't required for most of our endpoints. Requests without that header will be given an anonymous identity (and so will tend to fail any auth checks that are done). We'll update the docs.

As for authorization, we don't view the current behavior as a security vulnerability, although it is possibly surprising. In SpacetimeDB this type of authorization should generally be done via views, not via the client connection callback. If a user is not authorized to view data, they will not be able to view it from anywhere, including SQL queries.

All that said, it is a bug that the client connection callback doesn't run for SQL queries. We'll patch that soon. Thank you for catching that!

[Lethalchip]

I know this is pretty wild, but I wanted to ask.

Have there been any discussions internally about limiting the SQL endpoint to the owner and/or collaborators? Similar to how the logs endpoint works currently.

In my view, there's almost no situation where I would want a non-owner/collaborator to be hitting the HTTP API making SQL queries to my database. I want them to run through my onconnect, which is done when they use a client sdk and connect over websocket.

And then from an administrative perspective, I really enjoy that the SQL route bypasses onconnect, because it allows me to run very strict filters in my onconnect method, verifying the client via jwt. If SQL then goes through that, I'll need to hardcode some bypass for my CLI identity (and my collaborators), which feels a bit weird.

I do agree though, if the SQL route continues to stay publicly accessible, it should run through onconnect so that we can properly filter. But in my mind, it shouldn't be available to non-owner/collaborators.

I'd be curious to hear your thoughts @bfops @cloutiertyler