WIP: Postgresql simple auth

This is just for fun.

Let's create an auth table. It will hold a username primary key and a password.

create table auth (
  username text primary key, 
  password text not null
);

To create a user, we need a function that uses pgcrypto to generate a password with salt. Enable that with create extension pgcrypto;.

create function auth_insert(
  p_username text, 
  p_password text)
returns text
language plpgsql
as $$
declare
  p_password_crypt text := 
    crypt(p_password, gen_salt('bf'));
begin
  insert into auth (username, password) 
    values (p_username, p_password_crypt);
  return p_username;
end;  
$$;

We need a session table to store session tokens.

create table session (
  uuid text primary key,
  username text references auth(username) not null
);

We need a login function that checks the supplied password and add session entry using gen_random_uuid().

create function auth_login(
  p_username text, 
  p_password text)
returns text
language plpgsql
as $$
declare
  found_username text;
  gen_uuid text := gen_random_uuid();
begin
  select auth.username into found_username 
    from auth where auth.username = p_username
    and password = crypt(p_password, password);
  if not found then 
    raise exception 'user not found'; end if;
  insert into session (uuid, username) 
    values (gen_uuid, found_username);
  return gen_uuid;
end;  
$$;

We need a function to check if the user's session token is valid.

create function auth_session(
  p_session text)
returns text
language plpgsql
as $$
declare
  p_username text;
begin
  select username into p_username from session
    where session.uuid = p_session;
  if p_username is null then 
    raise exception 'user not found'; end if;
  return p_username;
end
$$;

Finally we can register a user, login and check the session token:

select * from auth_insert('auser', 'apassword');
select * from auth_login('auser', 'apassword');
-- copy the returned session token and run
select * from auth_session('...');
-- now you should see the username for that sessoin

There are obvious caveats: the session table grows infinitely; the username can't easily be changed when we start using it as a foreign key; if we try to register with multiple username sql throws a unique constraint exception; in fact, there is next to no fine tuned exception handling; generating the salted passwords in a reproducible database may be a security vulnerability; using the text type means there's no limit to input; and more.

But let's continue to add this CRUD functions in the next post.

postgresql

Edit on github
comments powered by Disqus
Click me