Wappler database management vs 3rd party API

Wappler General Need Help
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.

3 Likes