PostGraphile

• https://www.graphile.org/postgraphile/security/
• https://www.graphile.org/postgraphile/smart-tags/#primarykey
• https://www.graphile.org/postgraphile/views/#authorization
• https://www.graphile.org/postgraphile/jwt-guide/#example
• https://www.graphile.org/postgraphile/postgresql-indexes/
• https://www.graphile.org/postgraphile/security/

  create or replace function json(data jsonb) returns jsonb language sql stable as $$
  	select data
  $$;
  
  create or replace
  function test(s varchar) returns text language sql stable as $$
    select current_setting(s, true)
  $$;
  
  create type jwt as (
    exp integer,
    i integer, -- id
    s text -- session
  );
  
  create or replace function authenticate(email text, password text) returns jwt
    language plpgsql
    --strict security definer
    stable
  as $$
  declare
    account person_account;
    token jwt;
  begin
    select a.* into account from
      person_account as a
      where a.email = authenticate.email;
  
  if account.password = authenticate.password then
      token.exp = extract(epoch from now() + interval '7 days');
      token.i = 0;
      token.s = 'xxxxxx';
      return token;
  end if;
  
  return null;
  end;
  $$
  
  

  drop function if exists current_user_id() cascade;
  
  create function current_user_id() returns int language sql stable as $$
    select current_setting('jwt.claims.i', true)::int;
  $$;
  
  create view u as
   select * from "user" where id < current_user_id();
  comment on view u is '@primaryKey id';
  
  comment on view v is '
  @primaryKey id
  @foreignKey (user) references u (id)|@foreignFieldName vs|@fieldName u
  ';
  
  comment on constraint fk_group_user__group on group_user is '
  @manyToManyFieldName groups
  ';
  
  create function me() returns "user" language sql stable
  as $$
    select *
    from "user"
    where id = current_user_id();
  $$;
  
  
  -- m2m rename
  comment on constraint fk_group_user__group on group_user is '
  @manyToManyFieldName groups
  ';
  
  comment on table volumn is '
  @foreignKey (user) references u (id)
  ';
  
  drop view user_all_groups;
  create view user_all_groups as
  select distinct u.id as user, g2.id as group
  from "user" as u
  join group_user as x on u.id = x.user
  join "group" as g on x.group = g.id
  join "group" as g2 on g.name = g2.name or g2."name" like g."name" || '/%'
  ;
  comment on view user_all_groups is '
  @foreignKey (user) references user(id)
  @foreignKey (group) references group(id)|@manyToManyFieldName allGroups
  ';
  
  drop view user_sub_groups;
  create view user_sub_groups as
  select distinct u.id as user, g2.id as group
  from "user" as u
  join group_user as x on u.id = x.user
  join "group" as g on x.group = g.id
  join "group" as g2 on g2."name" like g."name" || '/%'
  ;
  comment on view user_sub_groups is '
  @foreignKey (user) references user(id)
  @foreignKey (group) references group(id)|@manyToManyFieldName subGroups
  ';
  
  drop view user_group_volumns;
  create view user_group_volumns as
  select distinct i.user, x.volumn
  from user_sub_groups as i
  join group_volumn as x on i.group = x.group
  left join volumn as v on i.user = v.user and x.volumn = v.id
  where v.id is null
  ;
  comment on view user_group_volumns is '
  @foreignKey (user) references user(id)
  @foreignKey (volumn) references volumn(id)|@manyToManyFieldName groupVolumns
  ';
  

尝试增加一个隐藏维度：

drop schema z cascade;
create schema z;
set search_path to z;

create function z() returns int language sql stable as $$
  select current_setting('jwt.claims.z')::int;
$$;

create table z (
  id serial primary key,
  key text unique not null,
  note text
);

create table g (
  z int references z default z(),
  id text,
  primary key (z, id),
  note text
);

create table u (
  z int references z default z(),
  id text,
  primary key (z, id),
  phone text
);

create table gu (
  z int references z default z(),
  g text,
  u text,
  primary key (z, g, u),
  foreign key (z, g) references g on update cascade,
  foreign key (z, u) references u on update cascade,
  ts timestamptz default now()
);


set search_path to public;

create view g as select id, note from z.g where z = z.z();
create view u as select id, phone from z.u where z = z.z();
create view gu as select g, u from z.gu where z = z.z();
comment on view g is '
@primaryKey id
';
comment on view u is '
@primaryKey id
';
comment on view gu is '
@primaryKey g,u
';

.psqlrc

set jwt.claims.z to 3;