Wappler database management vs 3rd party API

[Rytter]

Its really important for me to be able to impose strict limitations on which users can access what data. From what i have been able to gather the best option seems to be using RLS.

As far as i understand wappler does not support this and the alternative i have been able to find on the forum is running a check globally for the userID and restricting access this way, however it might be prone to errors.

Since it would be an unmitigated disaster if data from one user became available to other users i am heavily swung in favour of using a 3rd party solution which allows me to use RLS.

My solution so far is using supabase´s API which not only allows me to impose RLS on the database but also easily access features like syncing data from supabase to a table on the frontend.

Supabase also offers prebuilt user authentication flows through the API.

Im just stuck between where to use what wappler offers and what supabase offers. Can i still use the database connection feature in wappler if i go this route? Or should i just use the API supabase provides for interacting with the database.

Any input and/or other solutions would be highly appreciated. Thanks in advance

[Notum]

How about add additional column - owner (which is link to user ID). Then - before SQL query execute Security Identity and in SQL query filter needed rows where owner is output from Security Identity.

Or create in Wappler table - users and create all users related tables as a reference (child) tables.

[Hyperbytes]

"however it might be prone to errors."

ALL systems can experience errors however:

The security enforce used in Wappler is a “normally unauthorised” type of security, in other words access is rejected unless specifically authorised at server level so is very secure. Failure would mean the access is unauthorised.

However the suggestion by @Notum is also a good one which i have also used before.
I assume, by default, the data “owner” will be stored against the data so adding an additional layer of security by also checking the current user is the same as the “data owner”

i.e WHERE security.identifier == datatable.ownerID

then this is about as secure as it comes.

I have never seen a report from any Wappler user regarding failure of the security enforcer.

[kfawcett]

I wonder if this might be easy to add into a Wappler project that uses Postgres? Supabase is built on top of Postgres.

GitHub - franzon/postgres-rls-example: Example app for testing Postgres Row...

[JonL]

Before you posted the link I spent some time investigating how to integrate RLS with SC or, actually, how to pass info from SC to PG so that it takes into account the identity of the user when performing any query. The investigation took me to that link also but I have my doubts with the solution applied there.
I need to spend additional time on this topic as it’s something I’ve meant to look into for quite sometime already.

This is one of those times I hate with all my guts that Wappler is so “flexible” as to the amount of options it allows for users and not focusing on getting the most out of one thing.

[JonL]

Ok. So here is a possible approach.

I’m going to use the simple example in the supabase docs: Only the logged user can view his todos.

First you would enable RLS for the todos table.

ALTER TABLE "todos" ENABLE ROW LEVEL SECURITY;

In the supabase docs the policy is created like this.

CREATE POLICY  "Individuals can view their own todos."
    on todos for select
    using ( auth.uid() = user_id );

auth.uid() is a PG helper function creted by Supabase that returns the uuid of the logged user and user_id is the column name of the user that created that todo.

create or replace function auth.uid() returns uuid as $$
    select
        coalesce(
            current_setting('request.jwt.claim.sub', true),
            current_setting('request.jwt.claims', true)::jsonb ->> 'sub'
        )::uuid;
$$ language sql stable;

It uses PG current_setting function to retrieve a parameter that has been set previously.

So this could be done in Wappler similarly.

You can modify the policy to read a PG parameter set in Server Connect.

CREATE POLICY  "Individuals can view their own todos."
    on todos for select
    using ( current_setting('app.user_id', true) = user_id );

Now you just need to set that parameter via a custom query and the Security Provider Identity. A good place for this would be the Globals so it runs for all requests or maybe you could SET the parameter on the login action and RESET on logout. At the moment I don’t know the best approach.

If you are using nodejs you could also write a middleware and use the db object to make the query

exports.handler = function (app) {
    app.use((req, res, next) => {
        console.log(req.session.mainId) // Output to console the identity of the logged user
        //Start DB parameter stuff
        ...
        //END DB parameter stuff
        next();
    });
}

I haven’t tested any of this but it should get you started.

Also @Notum and @Hyperbytes the purpose of delegating the security to the database layer is that once you have a policy in place it will always do it’s job. This means that the probability of data leakage from missing a critical WHERE clause is irrelevant as what the policy does is basically enforce that WHERE clause in all the queries that match the policy. And therefore the reason why RLS is so popular.

[aschoijett]

I totally agree with JonL. The intention of the Row Level Security policiy is to REMOVE THE NEED to embed the rule(s) in the query as part of the "where" clause.

In my example I have 3 access rules that would translate into three "AND"s in the WHERE clause. But this has to be done per table!

Imagine a query that requires 3 joined tables, something that is not very uncommon. This would require 9 AND clauses in addition to the ones that you really care about.

For example: Assume that I have 3 row access rules driven by the contents of 3 columns:

1. active is a boolean that determines if the record is enabled.
2. clearance is an enum containing values like PUBLIC, PRIVATE, and CONFIDENTIAL, that is determines the minimum level of security clearance that the user should have to access the record.
3. excluded_roles is an array of varying characters used to exclude roles that would meet the clearance rule but that should nonetheless be precluded from accessing the record.

If Row Level Security were enabled, the query would look like this:

select *
from table 1 as t1
join table 2 as t2 on ...
join table 3 as t3 on ...
where (the filtering rules I really care about) ;

If RLS were not enabled, the query would end up (ignoring the casting of :P1 to an enum) looking like something like this. The added conditions are shown in italics.

select *
from table 1 as t1
join table 2 as t2 on ...
join table 3 as t3 on ...
where (the filtering rules I really care about)
AND t1.active is true
AND :P1 >= t1.clearance
AND :P2 <> ANY(t1.excluded_roles)
AND t2.active is true
AND :P1 >= t2.clearance
AND :P2 <> ANY(t2.excluded_roles)
AND t3.active is true
AND :P1 >= t3.clearance
AND :P2 <> ANY(t3.excluded_roles)
;

@JonL,
Would you be so kind and elaborate a bit more on what to do to pass the set parameters to the postgres query? I really do not understand your example. Many thanks in advance!

Alex

[aschoijett]

Hi,

Let me clarify the question I asked here with a bit more context:

I have a postgres database and I set up Row Level Security (RLS) for a table. I can verify that all is working properly using the psql tool and running the following 2 commands:

SET SESSION rls.usr_access = 'PRIVATE' ;
SELECT * FROM mytable ;

I can see that the select command returns only the rows that require a usr_access level lesser or equal to 'PRIVATE', confirming that everything is good so far.

Enter Wappler now.

I used a custom database query command, where I enter the two lines that I entered in the psql Command Line Interface.

The result of the Wappler api is nothing. I tried different approaches to put these two commands in a way that they are read together (e.g., using a WITH ... SELECT and using a BEGIN-COMMIT pair around them and wrapping them with $$), but nothing worked.

What DID work was to split the two SQL statements and place each one in a separate custom db step.

So custom1 contains the SET SESSION rls.usr_access ... command and custom2 contains the SELECT * FROM mytable command.

That this works really surprised me, because I thought that the life of the set variable would end when the custom1 step concluded.

So now I have two questions:

1. Is there a better way to pass the variable and then execute th select command ?

2. If not, is what Im doing sound? I am afraid of the possibility of a concurrent user2 changing the value of the variable rls.usr_access right after user1 set it, which may cause the database server to return to user1 records with the access level required by user2.

Sorry for the long message, but I thought that more context would be better than less.

Many thanks in advance for your help!

Alex