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]

Correction, August 29: This answer was wrong about exec auth_guard(...). sqlpage.exec() runs an external program, and sqlpage.run_sql() uses a forked variable context, so neither can set $id_user in the parent page. Please use the corrected explanation here. I am leaving this note in place rather than silently replacing the mistake.

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

[sambai-dev]

Hi @monsieurmerle, I need to correct my earlier reply. Your EDIT 2 approach is right, and my suggestion to use exec auth_guard(...) was wrong.

I mixed up what sqlpage.exec() and sqlpage.run_sql() do. sqlpage.exec() runs an external program; it does not include a SQL file. sqlpage.run_sql() can run another SQL file, but it gets its own variable context. That means a SET id_user inside it will not set $id_user in the parent page.

So yes, keep this at the top of each protected page:

set id_user = (...);

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

This is the same pattern used in SQLPage's authentication example. If you want to reduce the repetition, you can move the user lookup into a database view or function, or use one session table with user_id and user_kind. The short guard still needs to stay at the top of each protected page.

Sorry about that. I should not have presented the exec approach as a working solution.