Cannot use an RLS rule that `JOIN`s with a private table

[egormanga]

Reproduction:

#[table(name=test, public)]
struct Test {
	#[primary_key]
	#[auto_inc]
	id: u64,
}

#[client_visibility_filter]
const TEST_FILTER: Filter = Filter::Sql("SELECT test.* FROM test JOIN test_access ON test.id = test_access.id WHERE user_id = :sender");

#[table(name=test_access)]
struct TestAccess {
	#[primary_key]
	id: u64,
	user_id: Identity,
}

Compiles and publishes successfully, but when querying it fails with:

`test_access` is not a valid table

Should be a pretty obvious case to add a more complex restriction logic based on some private (sensitive) fields.

[egormanga]

Update:

RLS queries seem to be evaluated from the caller identity, effectively rendering useless most part of it. In particular, this means RLS cannot reference not only private tables, but public ones with non-passing RLS as well.

This behavior heavily impedes my current use case.

[egormanga]

For clarity: I need the RLS query to be evaluated (incl. recursively) with the system identity, while the caller's query would still retain its identity.

The current behavior actually seems to be: evaluate the RLS query from the system identity for accessing the current table, but from the caller identity for accessing the joining table. It's clearly a bug, then.

[bfops]

Thank you for filing this!

Is this the exact repro? To compile and publish successfully, I had to change the second struct to TestAccess (to avoid two structs with the same name) and update the filter line to be:

const TEST_FILTER: Filter = Filter::Sql("SELECT test.* FROM test JOIN test_access ON test.id = test_access.id WHERE test_access.user_id = :sender");

Does that match what you're doing? I want to make sure we're looking at the right thing 🙂

[bfops]

Here's what I would suggest, at least for now: make the private table public, but add a restriction so that users are not able to see the data that doesn't pertain to them.

This seemed to work for me:

use spacetimedb::{ReducerContext, Table, Filter, Identity, table, client_visibility_filter, reducer};

#[table(name=test, public)]
struct Test {
	#[primary_key]
	id: u64,
}

#[client_visibility_filter]
const TEST_FILTER: Filter = Filter::Sql("SELECT test.* FROM test JOIN test_access ON test.id = test_access.id WHERE test_access.user_id = :sender");

#[table(name=test_access, public)]
struct TestAccess {
	#[primary_key]
	id: u64,
	user_id: Identity,
}

#[client_visibility_filter]
const TEST_ACCESS_FILTER: Filter = Filter::Sql("SELECT * FROM test_access WHERE user_id = :sender");

I used this reducer to test it:

#[reducer]
fn do_thing(ctx: &ReducerContext, id: u64) {
    ctx.db.test_access().insert(TestAccess {
        id,
        user_id: ctx.sender,
    });
    ctx.db.test().insert(Test { id });
}

(I removed the autoinc annotation to make it easier to have the test and test_access tables in sync)

[egormanga]

@bfops

Is this the exact repro?

Yeah, I mis-copy-pasted it a bit. Your version fully matches my original one, I apologize for the confusion.

This seemed to work for me

For that particular case, yes, this solves the issue, but imagine the access table storing some sensitive columns which cannot be exposed to the user at all (this being the reason to create a separate table in the first place), for instance, restricted to admins only (as opposed to :sender) -- with that, RLS would return no rows.

[bfops]

Ah yeah that makes sense. Thank you for clarifying!

[egormanga]

I've updated the issue text to reflect the original code.

Expanding on the topic, it would make a nice feature being able to extract data to user from the JOINs used in RLS, as the filter query is controlled fully by the backend. For example, include a single column for the matched rows from that admin-only table into all selects over the RLS-protected table in question:

#[client_visibility_filter]
const TEST_FILTER: Filter = Filter::Sql("SELECT test.*, test_access.granted_by FROM test JOIN test_access ON test.id = test_access.id WHERE test_access.user_id = :sender");