Is there any way to implement Row Level Security?

[DenisBessa]

I don’t think there is a support for RLS right now. I’m I wrong?

[romeerez]

You're right, no support.

By brief examining RLS, here is what could be added:

• establishing a new connection per tenant, and in this case the ORM could have some utilities for pooling tenant connections, making it easier.
• defining policies: there could be an additional config for tables that would generate SQL RLS policies.

Let me know if you'd like to have anything in particular. Maybe not all at once, but everything is doable step by step.

[DenisBessa]

Thanks for the reply, @romeerez!

Let me give you a bit more context. The idea behind Row Level Security is to automatically add a where clause to all queries executed on a specific table.

For example, I can set a RLS policy enforcing that records in a table can only be viewed by users with the admin permission.

The issue is that, for this to be implemented, the query must include information about who the user is. This can be done by setting a variable before executing the query, using SET.

Setting Variables with SET Command:

In PostgreSQL, you can set session variables that can be used by Row Level Security policies. Here are some examples:

-- Set a user ID for the current session
SET app.current_user_id = '123';

-- Set user role
SET app.user_role = 'admin';

-- Set tenant/organization context
SET app.tenant_id = 'org_456';

These variables can then be referenced in RLS policies:

-- Example RLS policy using session variables
CREATE POLICY user_data_policy ON user_data
FOR ALL
TO app_user
USING (user_id = current_setting('app.current_user_id')::integer);

-- Example policy for multi-tenant setup
CREATE POLICY tenant_isolation ON documents
FOR ALL
TO app_user  
USING (tenant_id = current_setting('app.tenant_id')::text);

Back to the main issue:

I tried implementing this with the beforeQuery hook, but they are executed in a different context from the queries. For the parameter to be set correctly, it must be part of the same database query/transaction. That way, it will be in the same context.

Given this, I believe a simple solution would be to create a configuration that allows executing arbitrary commands before the query. In these commands, it would be possible to set variables in the query execution context, which seems to address 99% of use cases.

[krishna-404]

Hi @DenisBessa can this be implemented using query builder. I understand there would be quite a bit of tooling work but possible right?

[DenisBessa]

Yes, I think it can be implemented using a raw query inside a transaction (https://orchid-orm.netlify.app/guide/transactions.html#transaction), but only in a query level, not in a table level.

[DenisBessa]

I just saw version 1.53.0. I believe the implementation of SET might solve the issues discussed here!

[romeerez]

1.53.0 is for this issue, and there are some steps left in it, it's not related to RLS or to SQL's SET.

I'm planning to work on that issue, then on selecting relations when updating/creating, and only then to jump to this RLS, and it won't be soon, maybe in September.

[krishna-404]

Hi there is one more use case to session data along with query other than RLS. I want the queries to auto-adapt to users timezone. having session data at postgres level can help with that too Im presuming. I havent researched much on the topic though... or is there something already existing for this?

[romeerez]

I want the queries to auto-adapt to users timezone

@krishna-404 it may not be the best idea. Managing time zones is often confusing, so we just accept a principle that the time is always absolute, it is stored in UTC, and only if there is a need to get different time-zone based results it should be carefully handled in an explicit way. So it's like in 95% it should not adapt to a user's time, while in 5% it really depends on task specifics and there are different ways to handle it.

Example: a pub opens at 11 AM, two users from different time zones should still see that it's 11 AM in the pub's local time.
But, for "open now" functionality you still want to use absolute time: no matter where two users are living, the "now" is absolute.

For pub's schedule it's more tricky, but if we imagine a simpler case of event such as a football match that starts at 6 PM, I'd store it as the default timestamptz which is UTC, and I'd store the event's time zone as text in a separate column, which will be used for displaying. Then you can get the local time right in SQL, or load both fields and get local time in the backend, or return these fields to client and let the client calculate the local time. Then you can query for past or future matches with starts_at > now(), and you can use the time zone for displaying to a user.

RLS is really a different topic to that.