TransWikia.com

How to efficiently combine user authentication with token authentication?

Database Administrators Asked by luukvhoudt on November 21, 2021

What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system?

For this question please consider the following tables:

create table users (
  id int generated always as identity primary key,
  username text not null unique,
  password text not null
);

create table tokens (
  id int generated always as identity primary key,
  user_id int not null references users(id),
  access_token text unique default generate_random_token(),
  refresh_token text default generate_random_token(),
  updated_on timestamp with time zone default current_timestamp,
  created_on timestamp with time zone default current_timestamp
);

As far as I know to create an user and allow it to authenticate the following steps are mandatory (explained with query statements):

  1. Optional: Create an user record.
    insert into users (username, password) values ('foo', create_password_hash('bar'));
    
  2. Check if the user can authenticate itself and remember the user’s ID:
    select * from users where username = 'foo' and password = validate_password_hash('bar', password);
    
  3. Check if the user has an existing token and hand over the access_token:
    select access_token from tokens where user_id = 123;
    
  4. Optional: Create a token if the user doesn’t have one yet:
    insert into tokens (user_id) values (123) returning access_token;
    

If my assumption for the steps to be taken is right, then I wonder how to minimize these four statements into a single one or have at least some guidance how this could be done more efficient. Also I could imagine that my approach for this use case incorrect and that there is an other more efficient approach, please let me know.


BONUS

Perhaps your approach could also cover the process of refreshing a token. Considering the approach explained above the refresh should only occur when a token is found at the third step and according to the updated_on timestamp the token is "expired".

One Answer

Apparently this use case is fairly specific and thus it is quite a challenge to write an "upsert" statement what covers all the steps as described in the question. Therefor I see the following procedure as the only way out.

create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
begin
    select id
    into v_user_id
    from users
    where username = p_user
        and password = validate_password_hash(p_pass, password)
    limit 1;

    if (v_user_id is not null) then
        select id, updated_on, access_token, refresh_token
        into v_token_id, v_updated_on, v_access_token, v_refresh_token
        from tokens
        where user_id = v_user_id
        order by updated_on desc
        limit 1;

        if (v_token_id is null) then
            insert into tokens (user_id)
            values (v_user_id)
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;

        if (v_updated_on < current_timestamp - interval '1 day') then
            update tokens
            set access_token = generate_random_token(),
                refresh_token = generate_random_token(),
                updated_on = current_timestamp
            where id = v_token_id
            returning access_token, refresh_token
            into v_access_token, v_refresh_token;
        end if;
    end if;

    return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;

Here is a working DB fiddle

Answered by luukvhoudt on November 21, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP