\pset null _null_
\pset format unaligned
SET client_min_messages = warning;
SET ROLE postgres;
-- row level security
create extension "uuid-ossp" ;
create table if not exists items (
  id uuid default uuid_generate_v4() not null primary key,
  value text,
  acl_read uuid[] default array[]::uuid[],
  acl_write uuid[] default array[]::uuid[]
);
-- e.g. ('f386...5e99', 'I row and therefore I am', {'eac6...f6c9'}, {'0fdc...947f'})
create policy item_owner
on items
for all
to postgres
using (
  items.acl_read && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
  or items.acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
)
with check (
  items.acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
);
-- create index read_permissions_index on items using gin(acl_read);
-- create index write_permissions_index on items using gin(acl_write);
alter table items enable row level security;
alter table items force row level security;
select ddlx_script('items');
ddlx_script
BEGIN;

/*
DROP POLICY item_owner ON items;

DROP TABLE items;
*/

-- Type: TABLE ; Name: items; Owner: postgres

CREATE TABLE items (
    id uuid NOT NULL,
    value text,
    acl_read uuid[],
    acl_write uuid[]
);

COMMENT ON TABLE items IS NULL;
ALTER TABLE items OWNER TO postgres;

ALTER TABLE items ALTER id SET DEFAULT uuid_generate_v4();
ALTER TABLE items ALTER acl_read SET DEFAULT ARRAY[]::uuid[];
ALTER TABLE items ALTER acl_write SET DEFAULT ARRAY[]::uuid[];

ALTER TABLE items ADD CONSTRAINT items_pkey
  PRIMARY KEY (id);

ALTER TABLE items ENABLE ROW LEVEL SECURITY;
ALTER TABLE items FORCE ROW LEVEL SECURITY;

-- DEPENDANTS

CREATE POLICY item_owner ON items
  FOR ALL
  TO postgres
  USING (acl_read && regexp_split_to_array(current_setting('jwt.claims.roles'::text), ','::text)::uuid[] OR acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'::text), ','::text)::uuid[])
  WITH CHECK (acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'::text), ','::text)::uuid[]);
COMMENT ON POLICY item_owner ON items IS NULL;

END;

(1 row)