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):
insert into users (username, password) values ('foo', create_password_hash('bar'));
select * from users where username = 'foo' and password = validate_password_hash('bar', password);
select access_token from tokens where user_id = 123;
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".
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;
Answered by luukvhoudt on November 21, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP