About protected pages

[monsieurmerle]

Hi again everyone,

I have a new question, but first some context :

The app I am making has four types of users : administrators, companies, managers and workers (sometimes called technical workers). Most pages are only accessible to one type of user, though some are shared between the companies and managers, as well as handlers and pages where the user is asked if they are sure about deleting some things.

Each user must log in to use the app, and I am storing session tokens in four separate tokens (one per user type) :

-- Creation of the session tables for the session tokens

create table if not exists session_company (
    session_token text primary key,
    created_at timestamp default current_timestamp,
    modified_at timestamp,
    id_company integer,
    foreign key (id_company) references companies(id_company)
);

create table if not exists session_manager (
    session_token text primary key,
    created_at timestamp default current_timestamp,
    modified_at timestamp,
    id_manager integer,
    foreign key (id_manager) references parc_managers(id_manager)
);

create table if not exists session_worker (
    session_token text primary key,
    created_at timestamp default current_timestamp,
    modified_at timestamp,
    id_worker integer,
    foreign key (id_worker) references technical_users(id_worker)
);

create table if not exists session_admins (
    session_token text primary key,
    created_at timestamp default current_timestamp,
    modified_at timestamp,
    id_admin integer,
    foreign key (id_admin) references admins(id_admin)
);

For each user, there is a dedicated table where the hashed password, username, etc.. are stored.

Each protected page begins with some variations of :

-- Validate session and get user_id
WITH session_data AS (
    SELECT id_company FROM session_company 
    WHERE session_token = sqlpage.cookie('session_token')
)
-- Redirect to login if session is invalid
SELECT 'redirect' AS component, '/landingpage' AS link
WHERE NOT EXISTS (SELECT 1 FROM session_data);

set id_user = (select id_company from session_company where session_token = sqlpage.cookie('session_token'))

-- Displays the page header
select
    'dynamic' AS component,
    sqlpage.run_sql('commons/shells/shell-company.sql', json_object('title', 'Espace Entreprise')) AS properties;

Where I check the user is allowed to be here, redirected to login if he isn't and the id of the user is gathered before the header is displayed. (depending on the page, the token is compared to the matching session table) (there are also different headers for each user type)

So, everything works as it is now, but I am wondering if it would be possible to tuck some part of it away to only have the code once and just have to call it.
I don't know if I can protect a page while allowing multiple types of users to have access to it (because pages accessible to multiple user types are not protected for now), I am maybe thinking of using something like :

-- Validate session and get user_id
WITH session_data AS COALESCE( 
(   SELECT id_company FROM session_company 
    WHERE session_token = sqlpage.cookie('session_token') 
),
(   SELECT id_manager FROM session_manager
    WHERE session_token = sqlpage.cookie('session_token')
),
(   SELECT id_worker FROM session_worker
    WHERE session_token = sqlpage.cookie('session_token')
))

You will have noticed the line :

set id_user = (select id_company from session_company where session_token = sqlpage.cookie('session_token'))

I use it for a lot of different things, in pages shared between user types it helps determine where to redirect depending who the user is. So I do need it.

Please tell me if it is possible to simplify the code, I am aware that this is not the best at all, but I do not know of any better way to do it yet. I can't really make a minimal reproducible example, as it works as it is.

Thank you in advance for any help you can bring

EDIT : regarding the variable id_user, I am trying to find a way to declare varibles that I could access from all pages (it can be by calling the page where they are declared). I am trying to use the dynamic component for this without success, is this a job for a cookie ?

EDIT 2 : I found and have tried the way pages are protected in the User Management example, using only

set id_user = (select id_company from session_company where session_token = sqlpage.cookie('session_token'))

select 'redirect' as component,
        'login.sql?error' as link
where $id_user is null;

to handle the protection. It seems to work for now and is more compact. Please tell me if this is a bad idea

[sambai-dev]

Yes, you can factor this out, with two caveats about how SQLPage executes files: there is no include that shares the parent page's variable context, and sqlpage.run_sql renders its rows as component properties instead of executing them as part of your page.

What works is a shared auth_guard.sql that emits the redirect itself, called once at the top of each protected page:

-- commons/auth_guard.sql : pass the session table name, get back the user id (or redirect)
set id_user = (
    select case :user_type
        when 'company' then (select id_company from session_company where session_token = sqlpage.cookie('session_token'))
        when 'manager' then (select id_manager from session_manager where session_token = sqlpage.cookie('session_token'))
        when 'worker'  then (select id_worker  from session_worker  where session_token = sqlpage.cookie('session_token'))
    end
);
select 'redirect' as component, '/landingpage' as link where :id_user is null;

and on each protected page:

exec auth_guard(user_type => 'company');
-- from here :id_user is set, or the request has been redirected away

Why the guard must emit the redirect itself: sqlpage.exec only returns the included file's output as text (src/webserver/database/sqlpage_functions/functions/exec.rs on main); it does not execute the file's statements in your page's context. A redirect row emitted by the included file still works, because redirects are processed wherever the row appears in the stream (src/render.rs, PageContext::redirect). But any SET done inside an included file stays inside it: run_sql forks the execution context and passes a copy of the variables in via JSON (src/webserver/database/sqlpage_functions/functions/run_sql.rs, fork_with_variables). That is also why run_sql cannot hand id_user back to the caller: its result is the rendered rows of the included page serialized to JSON, not executed SQL. So keep one line per page (exec auth_guard(...)) rather than trying to route everything through a shell include.

On your COALESCE sketch: the idea is right but the syntax needs fixing. WITH x AS COALESCE(...) is not valid SQL; either put the CASE expression above into the CTE, or skip the CTE entirely and use the SET form shown, which matches how the official authentication example does it (examples/official-site/examples/authentication/index.sql: set user_role = (select ...); select 'redirect' ... where $user_role is null;).

For pages shared by several user types, call the same guard without filtering on type, then branch on which table matched:

set user_kind = (
    select case
        when exists (select 1 from session_company where session_token = sqlpage.cookie('session_token')) then 'company'
        when exists (select 1 from session_manager where session_token = sqlpage.cookie('session_token')) then 'manager'
        when exists (select 1 from session_worker  where session_token = sqlpage.cookie('session_token')) then 'worker'
    end
);
select 'redirect' as component, '/landingpage' as link where :user_kind is null;

On your edit: SET variables are per-request server-side state; there is no supported way to declare a variable on one page and read it from another. Cross-request state belongs in the database (your session tables) plus a browser cookie, which is exactly what you already have. If some day all four user tables merged into one session(user_id, role) table, the guard collapses to two lines and $user_kind/role comes out of the same lookup.

[monsieurmerle]

Thank you for answering !

I don't really get the explaination paragraph in its entirety, but I get the idea.

With the solution you are offering, I just need to include the exec auth_guard(user_type => 'company'); line at the top of a protected page and I'm good ?

I am not sure I understand how the code works for the shared pages, and how it is included in the guard. I don't really understand what defining user_kind does, and how it can work when placed right after the previous part of code in the guard.

Don't I want to define user_kind in the protected page just before calling exec auth_guard(user_type => $user_kind); to pass the role of the current user to the guard ?

Would you recommend merging all four user session tables into one ? It would take time to modify things but I do have backups and some time to do it.

EDIT : I wanted to try implementing the guard and the exec function (straight copy-paste) but I get an error message sql parser error: Expected: ), found: => at Line: 13, Column: 27, it seems to not like the => for some reason.