Security + multi-tenancy

[derekperkins]

The power of Mosaic is the embrace of SQL as the core language, allowing it to issue arbitrary SQL commands to build tables, and through DuckDB, interact with other databases, files, and apis. If I'm running DuckDB on the server, what is the path for client authorization? I know that the requests themselves can authenticate the user, but how can I make sure that the sql queries themselves aren't accessing data that they shouldn't be? DuckDB doesn't have user permissions, so I can't lean on it.

I'm thinking that the most viable option is to use DuckDB json_serialize_sql, and recurse through it to make sure that only an allowlist of schemas/tables/commands are included, and reject anything that doesn't match. Setting preagg.schema in mosaic to something like the tenant_id, I could match the user from the request to the duckdb schema, preventing any cross-tenant arbitrary queries.

Does that sound like a viable solution? Are there considerations aside from just SQL that I'm missing?

[jheer]

(Converted this from an issue to a discussion, as I think that is more appropriate.)

The main issue (as you note) is that DuckDB doesn't include a permissions model. So you'll have to institute your own access control / permissions mechanisms. There are various ways you could do this, including using a custom server (and perhaps customized Mosaic DB connectors) that require authentication and perform analysis of queries (possibly via json_serialize_sql). And I agree that including a tenant id as part of the preagg schema makes sense!

Another approach would be to have no multi-tenancy and instead spin up separate DuckDB servers per user, but don't know if that is reasonable.

In a multi-tenant situation, one question is if multiple users should see the exact same data. If so, you might want those users to have the same preagg schema to avoid redundant computation and resource use.

[derekperkins]

using a custom server (and perhaps customized Mosaic DB connectors)

Yes, my plan definitely included a custom server. What are you thinking in terms of "customized Mosaic DB connectors"?

if multiple users should see the exact same data. If so, you might want those users to have the same preagg schema to avoid redundant computation and resource use.

100% agreed. Switching between tenant_id and user_id depending on what should be shared is a useful distinction. Both could be used together for the right use cases, and should be able to be used with similar auth mechanisms.

[jheer]

Yes, my plan definitely included a custom server. What are you thinking in terms of "customized Mosaic DB connectors"?

You may want a connector implementation that also authenticates against your custom database server.