WIP: Postgresql simple auth, part 2: CRUD

This is just for fun.

We can create our select, insert, update, and delete functions (or create, update and delete) now we have our auth functions.

Let's create a thing table with a auth username reference. This means a thing can only be owned by one user.

create table thing(
  id serial primary key,
  username text reference auth(username) not null,
  text text not null
);

We use our auth_session() function for the authorization and pass in session token to each function. We throw an exception from the functions through auth_session() if the session token is bad. We return the whole table for that user on return of each function.

create function thing_select(
  p_session text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  return query select thing.* from thing
    where thing.username = p_username;
end
$$;

create function thing_insert(
  p_session text,
  p_text text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
  p_id int;
begin
  insert into thing (username, text)
    values (p_username, p_text);
  return query select * 
    from thing_select(p_session);
end
$$;

create function thing_update(
  p_session text,
  p_id int,
  p_text text)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  update thing 
    set text = p_text
    where thing.id = p_id
    and thing.username = p_username;
  return query select * 
    from thing_select(p_session);
end
$$;

create function thing_delete(
  p_session text,
  p_id int)
returns setof thing
language plpgsql
as $$
declare
  p_username text := auth_session(p_session);
begin
  delete from thing
    where thing.id = p_id
    and thing.username = p_username;
  return query select * 
    from thing_select(p_session);
end
$$;

Those functions don't say when the user tries to update or delete a row that doesn't belong to them. It silently fails. This could be improved with little effort.

You can use this, with a session token, as follows.

select * from thing_select(
  '...'
);
select * from thing_insert(
  '...',
  'first thing'
);
select * from thing_update(
  '...',
  1,
  'updated thing'
);
select * from thing_delete(
  '...',
  1
);
postgresql

Edit on github
comments powered by Disqus
Click me