Wappler database management vs 3rd party API

Wappler General How To
#1

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 :slight_smile:

Row Level Security
#2

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.

1 Like
#3

"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.

2 Likes
#4

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

Hi all! New here and have some questions
#5

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.

#6

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.

Wappler 03-03-2023 12.15.36 000240
Wappler 03-03-2023 12.15.36 000240817×346 9.63 KB

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.

2 Likes