WIP: pg ignore

create table XXX(
  id serial primary key,
  username text references auth(username) not null,
  json jsonb not null
);

And

create or replace function _XXX_select(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  return query select XXX.* from XXX
    where XXX.username = p_username;
end
$$;

create or replace function _XXX_insert(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  insert into XXX (username, json)
    values (p_username, p_json->'json');
  return query select * 
    from _XXX_select(p_json);
end
$$;

create or replace function _XXX_update(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  update XXX 
    set json = json || (p_json->'json')::jsonb
    where XXX.id = (p_json->>'id')::int
    and XXX.username = p_username;
  return query select * 
    from _XXX_select(p_json);
end
$$;

create or replace function _XXX_delete(
  p_json jsonb)
returns setof XXX
language plpgsql
as $$
declare
  p_username text := _auth_session(p_json->>'session');
begin
  delete from XXX
    where XXX.id = (p_json->>'id')::int
    and XXX.username = p_username;
  return query select * 
    from _XXX_select(p_json);
end
$$;

And

select * from _xxx_insert(
  '{
    "session": "...",	
	"json": {"akey1":"avalue1"}
  }'
);

select * from _xxx_select(
  '{"session":"..."}'
);


select * from _xxx_update(
  '{ "id": 1,
     "session": "...",
     "json": {
       "additionalkey":"addtionalvalue"	
     }
  }'
);

select * from _xxx_delete(
  '{"id": 4, "session": "..."}'
);

wip

CRUD functions for storing JSON in postgresql

Let's create a table with an id and a json column. We use jsonb because postgresql offers us more functions to manipulate and use it.

create table jsonthing (
  id serial primary key,
  json jsonb not null
);

Here are the functions

create function jsonthing_select()
returns setof jsonthing
language plpgsql
as $$
begin
  return query select * from jsonthing;
end
$$;

create function jsonthing_insert(
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  insert into jsonthing (json)
    values (p_json);
  return query select * from jsonthing;
end
$$;

create function jsonthing_update(
  p_id int,
  p_json jsonb)
returns setof jsonthing
language plpgsql
as $$
begin
  update jsonthing 
    set json = json || p_json
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

create function jsonthing_delete(
  p_id)
returns setof jsonthing
language plpgsql
as $$
begin
  delete from jsonthing
    where jsonthing.id = p_id;
  return query select * from jsonthing;
end
$$;

Note our jsonthing_update function merges the new json object with the existing one. This means it won't delete fields. Another function could replace the json object with little effort.

We use it like this below.

select * from jsonthing_insert(
  '{"akey":"avalue"}'
);

select * from jsonthing_insert(
  '{ "anotherkey":"anothervalue"}'
);

select * from jsonthing_update(1,
  '{ "additionalkey":"addtionalvalue"}'
);

select * from jsonthing_delete(1);

select * from jsonthing_select();
postgresql

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

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

Convert a json array into a postgresql text array

We use json_array_elements_text, or its jsonb equivalent, to get the elements as a text table.

And put that into a select array() statement.

select array(
  select jsonb_array_elements_text('["hi", "there"]')
);
postgresql

Page 3 of 99
prev next
Click me