boundary/internal/db/migrations/postgres.gen.go

// Code generated by "make migrations"; DO NOT EDIT.
package migrations

var postgresMigrations = map[string]*fakeFile{
	"migrations": {
		name: "migrations",
	},
	"migrations/01_domain_types.down.sql": {
		name: "01_domain_types.down.sql",
		bytes: []byte(`
begin;

drop domain wt_timestamp;
drop domain wt_public_id;
drop domain wt_private_id;
drop domain wt_scope_id;
drop domain wt_user_id;
drop domain wt_version;

drop function default_create_time;
drop function immutable_create_time_func;
drop function update_time_column;
drop function update_version_column;

commit;

`),
	},
	"migrations/01_domain_types.up.sql": {
		name: "01_domain_types.up.sql",
		bytes: []byte(`
begin;

create domain wt_public_id as text
check(
  length(trim(value)) > 10
);
comment on domain wt_public_id is
'Random ID generated with github.com/hashicorp/vault/sdk/helper/base62';

create domain wt_private_id as text
check(
  length(trim(value)) > 10
);
comment on domain wt_private_id is
'Random ID generated with github.com/hashicorp/vault/sdk/helper/base62';

create domain wt_scope_id as text
check(
  length(trim(value)) > 10 or value = 'global'
);
comment on domain wt_scope_id is
'"global" or random ID generated with github.com/hashicorp/vault/sdk/helper/base62';

create domain wt_user_id as text
check(
  length(trim(value)) > 10 or value = 'u_anon' or value = 'u_auth'
);
comment on domain wt_scope_id is
'"u_anon", "u_auth", or random ID generated with github.com/hashicorp/vault/sdk/helper/base62';

create domain wt_role_id as text
check(
  length(trim(value)) > 10 or value = 'r_default'
);
comment on domain wt_scope_id is
'"r_default", or random ID generated with github.com/hashicorp/vault/sdk/helper/base62';

create domain wt_timestamp as
  timestamp with time zone
  default current_timestamp;
comment on domain wt_timestamp is
'Standard timestamp for all create_time and update_time columns';

create or replace function
  update_time_column()
  returns trigger
as $$
begin
  if row(new.*) is distinct from row(old.*) then
    new.update_time = now();
    return new;
  else
    return old;
  end if;
end;
$$ language plpgsql;

comment on function
  update_time_column()
is
  'function used in before update triggers to properly set update_time columns';

create or replace function
  immutable_create_time_func()
  returns trigger
as $$
begin
  if new.create_time is distinct from old.create_time then
    raise warning 'create_time cannot be set to %', new.create_time;
    new.create_time = old.create_time;
  end if;
  return new;
end;
$$ language plpgsql;

comment on function
  immutable_create_time_func()
is
  'function used in before update triggers to make create_time column immutable';
  
create or replace function
  default_create_time()
  returns trigger
as $$
begin
  if new.create_time is distinct from now() then
    raise warning 'create_time cannot be set to %', new.create_time;
    new.create_time = now();
  end if;
  return new;
end;
$$ language plpgsql;

comment on function
  default_create_time()
is
  'function used in before insert triggers to set create_time column to now';


create domain wt_version as bigint
default 1 
check(
  value > 0
);
comment on domain wt_version is
'standard column for row version';

-- update_version_column() will increment the version column whenever row data
-- is updated and should only be used in an update after trigger.  This function
-- will overwrite any explicit updates to the version column. 
create or replace function
  update_version_column()
  returns trigger
as $$
begin
  if pg_trigger_depth() = 1 then
    if row(new.*) is distinct from row(old.*) then
      execute format('update %I set version = $1 where public_id = $2', tg_relid::regclass) using old.version+1, new.public_id;
      new.version = old.version + 1;
      return new;
    end if;
  end if;
  return new;
end;
$$ language plpgsql;

comment on function
  update_version_column()
is
  'function used in after update triggers to properly set version columns';

commit;

`),
	},
	"migrations/02_oplog.down.sql": {
		name: "02_oplog.down.sql",
		bytes: []byte(`
begin;

drop table oplog_metadata cascade;
drop table oplog_ticket cascade;
drop table oplog_entry cascade;

commit;

`),
	},
	"migrations/02_oplog.up.sql": {
		name: "02_oplog.up.sql",
		bytes: []byte(`
begin;

create table if not exists oplog_entry (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  version text not null,
  aggregate_name text not null,
  "data" bytea not null
);

create trigger 
  update_time_column 
before 
update on oplog_entry 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on oplog_entry 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on oplog_entry
  for each row execute procedure default_create_time();

create table if not exists oplog_ticket (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  "name" text not null unique,
  "version" bigint not null
);

create trigger 
  update_time_column 
before 
update on oplog_ticket 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on oplog_ticket 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on oplog_ticket
  for each row execute procedure default_create_time();

create table if not exists oplog_metadata (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  entry_id bigint not null references oplog_entry(id) on delete cascade on update cascade,
  "key" text not null,
  value text null
);

create trigger 
  update_time_column 
before 
update on oplog_metadata 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on oplog_metadata 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on oplog_metadata 
  for each row execute procedure default_create_time();

create index if not exists idx_oplog_metatadata_key on oplog_metadata(key);

create index if not exists idx_oplog_metatadata_value on oplog_metadata(value);

insert into oplog_ticket (name, version)
values
  ('auth_token', 1),
  ('default', 1),
  ('iam_scope', 1),
  ('iam_user', 1),
  ('iam_group', 1),
  ('iam_group_member', 1),
  ('iam_role', 1),
  ('iam_role_grant', 1),
  ('iam_group_role', 1),
  ('iam_user_role', 1),
  ('db_test_user', 1),
  ('db_test_car', 1),
  ('db_test_rental', 1),
  ('db_test_scooter', 1),
  ('auth_account', 1),
  ('iam_principal_role', 1);
  

commit;


`),
	},
	"migrations/03_db.down.sql": {
		name: "03_db.down.sql",
		bytes: []byte(`
begin;

drop table db_test_rental cascade;
drop table db_test_car cascade;
drop table db_test_user cascade;
drop table db_test_scooter cascade;

commit;

`),
	},
	"migrations/03_db.up.sql": {
		name: "03_db.up.sql",
		bytes: []byte(`
begin;

-- create test tables used in the unit tests for the internal/db package 
-- these tables (db_test_user, db_test_car, db_test_rental, db_test_scooter) are
-- not part of the watchtower domain model... they are simply used for testing
-- the internal/db package 
create table if not exists db_test_user (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  public_id text not null unique,
  name text unique,
  phone_number text,
  email text,
  version wt_version
);

create trigger 
  update_time_column 
before 
update on db_test_user 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on db_test_user 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on db_test_user 
  for each row execute procedure default_create_time();

create trigger 
  update_version_column
after update on db_test_user
  for each row execute procedure update_version_column();
  
create table if not exists db_test_car (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  public_id text not null unique,
  name text unique,
  model text,
  mpg smallint
);

create trigger 
  update_time_column 
before 
update on db_test_car 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on db_test_car 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on db_test_car
  for each row execute procedure default_create_time();

create table if not exists db_test_rental (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  public_id text not null unique,
  name text unique,
  user_id bigint not null references db_test_user(id),
  car_id bigint not null references db_test_car(id)
);

create trigger 
  update_time_column 
before 
update on db_test_rental 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on db_test_rental 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on db_test_rental
  for each row execute procedure default_create_time();


create table if not exists db_test_scooter (
  id bigint generated always as identity primary key,
  create_time wt_timestamp,
  update_time wt_timestamp,
  private_id text not null unique,
  name text unique,
  model text,
  mpg smallint
);

create trigger 
  update_time_column 
before 
update on db_test_scooter 
  for each row execute procedure update_time_column();

create trigger 
  create_time_column
before
update on db_test_scooter 
  for each row execute procedure immutable_create_time_func();

create trigger 
  default_create_time_column
before
insert on db_test_scooter
  for each row execute procedure default_create_time();

commit;

`),
	},
	"migrations/06_iam.down.sql": {
		name: "06_iam.down.sql",
		bytes: []byte(`
BEGIN;

drop table iam_group cascade;
drop table iam_user cascade;
drop table iam_scope_project cascade;
drop table iam_scope_org cascade;
drop table iam_scope_global cascade;
drop table iam_scope cascade;
drop table iam_scope_type_enm cascade;
drop table iam_role cascade;
drop view iam_principal_role cascade;
drop table iam_group_role cascade;
drop table iam_user_role cascade;
drop table iam_group_member_user cascade;
drop view iam_group_member cascade;
drop table iam_role_grant cascade;

drop function iam_sub_names cascade;
drop function iam_immutable_scope_type_func cascade;
drop function iam_sub_scopes_func cascade;
drop function iam_immutable_role_principal cascade;
drop function iam_user_role_scope_check cascade;
drop function iam_group_role_scope_check cascade;
drop function iam_group_member_scope_check cascade;
drop function iam_immutable_group_member cascade;
drop function get_scoped_member_id cascade;
drop function grant_scope_id_valid cascade;
drop function immutable_scope_id_func cascade;
drop function disallow_global_scope_deletion cascade;
drop function user_scope_id_valid cascade;
drop function iam_immutable_role_grant cascade;
drop function disallow_iam_anon_auth_deletion cascade;

COMMIT;

`),
	},
	"migrations/06_iam.up.sql": {
		name: "06_iam.up.sql",
		bytes: []byte(`
begin;

create table iam_scope_type_enm (
  string text not null primary key check(string in ('unknown', 'global', 'org', 'project'))
);

insert into iam_scope_type_enm (string)
values
  ('unknown'),
  ('global'),
  ('org'),
  ('project');


create or replace function
  iam_immutable_scope_id_func()
  returns trigger
as $$
begin
  if new.scope_id is distinct from old.scope_id then
    raise exception 'scope_id cannot be set to %', new.scope_id;
  end if;
  return new;
end;
$$ language plpgsql;

comment on function
  iam_immutable_scope_id_func()
is
  'function used in before update triggers to make scope_id column is immutable';

create table iam_scope (
    public_id wt_scope_id primary key,
    create_time wt_timestamp,
    update_time wt_timestamp,
    name text,
    type text not null references iam_scope_type_enm(string) check(
      (
        type = 'global'
        and parent_id is null
      )
      or (
        type = 'org'
        and parent_id = 'global'
      )
      or (
        type = 'project'
        and parent_id is not null
        and parent_id != 'global'
      )
    ),
    description text,
    parent_id text references iam_scope(public_id) on delete cascade on update cascade
  );

create table iam_scope_global (
    scope_id wt_scope_id primary key
      references iam_scope(public_id)
      on delete cascade
      on update cascade
      check(
        scope_id = 'global'
      ),
    name text unique
);

create table iam_scope_org (
  scope_id wt_scope_id primary key
    references iam_scope(public_id)
    on delete cascade
    on update cascade,
  parent_id wt_scope_id not null
    references iam_scope_global(scope_id)
    on delete cascade
    on update cascade,
  name text,
  unique (parent_id, name)
);

create table iam_scope_project (
    scope_id wt_scope_id not null references iam_scope(public_id) on delete cascade on update cascade,
    parent_id wt_public_id not null references iam_scope_org(scope_id) on delete cascade on update cascade,
    name text,
    unique(parent_id, name),
    primary key(scope_id, parent_id)
  );

create or replace function 
  iam_sub_scopes_func() 
  returns trigger
as $$ 
declare parent_type int;
begin
  if new.type = 'global' then
    insert into iam_scope_global (scope_id, name)
    values
      (new.public_id, new.name);
    return new;
  end if;
  if new.type = 'org' then
    insert into iam_scope_org (scope_id, parent_id, name)
    values
      (new.public_id, new.parent_id, new.name);
    return new;
  end if;
  if new.type = 'project' then
    insert into iam_scope_project (scope_id, parent_id, name)
    values
      (new.public_id, new.parent_id, new.name);
    return new;
  end if;
  raise exception 'unknown scope type';
end;
$$ language plpgsql;

create trigger 
  iam_scope_insert
after
insert on iam_scope 
  for each row execute procedure iam_sub_scopes_func();

create or replace function
  disallow_global_scope_deletion()
  returns trigger
as $$
begin
  if old.type = 'global' then
    raise exception 'deletion of global scope not allowed';
  end if;
  return old;
end;
$$ language plpgsql;

create trigger
  iam_scope_disallow_global_deletion
before
delete on iam_scope
  for each row execute procedure disallow_global_scope_deletion();

create or replace function 
  iam_immutable_scope_type_func() 
  returns trigger
as $$ 
declare parent_type int;
begin 
  if new.type != old.type then
    raise exception 'scope type cannot be updated';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger 
  iam_scope_update
before 
update on iam_scope 
  for each row execute procedure iam_immutable_scope_type_func();

create trigger 
  update_time_column 
before update on iam_scope 
  for each row execute procedure update_time_column();

create trigger 
  immutable_create_time
before
update on iam_scope 
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_scope
  for each row execute procedure default_create_time();


-- iam_sub_names will allow us to enforce the different name constraints for
-- orgs and projects via a before update trigger on the iam_scope
-- table. 
create or replace function 
  iam_sub_names() 
  returns trigger
as $$ 
begin
  if new.name != old.name then
    if new.type = 'global' then
      update iam_scope_global set name = new.name where scope_id = old.public_id;
      return new;
    end if;
    if new.type = 'org' then
      update iam_scope_org set name = new.name where scope_id = old.public_id;
      return new;
    end if;
    if new.type = 'project' then
      update iam_scope_project set name = new.name where scope_id = old.public_id;
      return new;
    end if;
    raise exception 'unknown scope type';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger 
  iam_sub_names 
before 
update on iam_scope
  for each row execute procedure iam_sub_names();

insert into iam_scope (public_id, name, type, description)
  values ('global', 'global', 'global', 'Global Scope');


create table iam_user (
    public_id wt_user_id primary key,
    create_time wt_timestamp,
    update_time wt_timestamp,
    name text,
    description text,
    scope_id wt_scope_id not null references iam_scope(public_id) on delete cascade on update cascade,
    unique(name, scope_id),
    disabled boolean not null default false,

    -- The order of columns is important for performance. See:
    -- https://dba.stackexchange.com/questions/58970/enforcing-constraints-two-tables-away/58972#58972
    -- https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field
    unique(scope_id, public_id)
  );

create or replace function
  user_scope_id_valid()
  returns trigger
as $$
begin
  perform from iam_scope where public_id = new.scope_id and type in ('global', 'org');
  if not found then
    raise exception 'invalid scope type for user creation';
  end if;
  return new;
end;
$$ language plpgsql;

create or replace function
  grant_scope_id_valid()
  returns trigger
as $$
declare parent_scope_id text;
declare role_scope_type text;
begin
  -- There is a not-null constraint so ensure that if the value passed in is
  -- empty we simply set to the scope ID
  if new.grant_scope_id = '' or new.grant_scope_id is null then
    new.grant_scope_id = new.scope_id;
  end if;
  -- If the scopes match, it's allowed
  if new.grant_scope_id = new.scope_id then
    return new;
  end if;
  -- Fetch the type of scope
  select isc.type from iam_scope isc where isc.public_id = new.scope_id into role_scope_type;
  -- Always allowed
  if role_scope_type = 'global' then
    return new;
  end if;
  -- Never allowed; the case where it's set to the same scope ID as the project
  -- itself is covered above
  if role_scope_type = 'project' then
    raise exception 'invalid to set grant_scope_id to non-same scope_id when role scope type is project';
  end if;
  if role_scope_type = 'org' then
    -- Look up the parent scope ID for the scope ID given
    select isc.parent_id from iam_scope isc where isc.public_id = new.grant_scope_id into parent_scope_id;
    -- Allow iff the grant scope ID's parent matches the role's scope ID; that
    -- is, match if the role belongs to a direct child scope of this
    -- org
    if parent_scope_id = new.scope_id then
      return new;
    end if;
    raise exception 'grant_scope_id is not a child project of the role scope';
  end if;
  raise exception 'unknown scope type';
end;
$$ language plpgsql;

create or replace function
  disallow_iam_anon_auth_deletion()
  returns trigger
as $$
begin
  if old.public_id = 'u_anon' then
    raise exception 'deletion of anonymous user not allowed';
  end if;
  if old.public_id = 'u_auth' then
    raise exception 'deletion of authenticated user not allowed';
  end if;
  return old;
end;
$$ language plpgsql;

create trigger
  ensure_user_scope_id_valid
before
insert or update on iam_user
  for each row execute procedure user_scope_id_valid();

create trigger 
  update_time_column 
before update on iam_user 
  for each row execute procedure update_time_column();

create trigger 
  immutable_create_time
before
update on iam_user 
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_user
  for each row execute procedure default_create_time();

create trigger immutable_scope_id_user
before
update on iam_user
  for each row execute procedure iam_immutable_scope_id_func();

create trigger
  iam_user_disallow_anon_auth_deletion
before
delete on iam_user
  for each row execute procedure disallow_iam_anon_auth_deletion();

-- TODO: Do we want to disallow changing the name or description?
insert into iam_user (public_id, name, description, scope_id)
  values ('u_anon', 'anonymous', 'The anonymous user matches any request, whether authenticated or not', 'global');

insert into iam_user (public_id, name, description, scope_id)
  values ('u_auth', 'authenticated', 'The authenticated user matches any user that has a valid token', 'global');

create table iam_role (
    public_id wt_role_id primary key,
    create_time wt_timestamp,
    update_time wt_timestamp,
    name text,
    description text,
    scope_id wt_scope_id not null references iam_scope(public_id) on delete cascade on update cascade,
    grant_scope_id wt_scope_id not null references iam_scope(public_id) on delete cascade on update cascade,
    unique(name, scope_id),
    disabled boolean not null default false,
    -- version allows optimistic locking of the role when modifying the role
    -- itself and when modifying dependent items like principal roles. 
    -- TODO (jlambert 6/2020) add before update trigger to automatically
    -- increment the version when needed.  This trigger can be addded when PR
    -- #126 is merged and update_version_column() is available.
    version bigint not null default 1,
    
    -- add unique index so a composite fk can be declared.
    unique(scope_id, public_id)
  );

  -- Grants are immutable, which is enforced via the trigger below
  create table iam_role_grant (
    create_time wt_timestamp,
    role_id wt_role_id -- pk
      references iam_role(public_id)
      on delete cascade
      on update cascade,
    canonical_grant text -- pk
      check(
        length(trim(canonical_grant)) > 0
      ),
    raw_grant text not null
      check(
        length(trim(raw_grant)) > 0
      ),
    primary key(role_id, canonical_grant)
  );

-- iam_immutable_role_grant() ensures that grants assigned to roles are immutable. 
create or replace function
  iam_immutable_role_grant()
  returns trigger
as $$
begin
  raise exception 'role grants are immutable';
end;
$$ language plpgsql;

create trigger immutable_role_grant
before
update on iam_role_grant
  for each row execute procedure iam_immutable_role_grant();
  
create trigger
  default_create_time_column
before
insert on iam_role_grant
  for each row execute procedure default_create_time();

-- prefix a_ to make this trigger run before ensure_grant_scope_id_valid
create trigger a_immutable_scope_id
before
update on iam_role
  for each row execute procedure iam_immutable_scope_id_func();

create trigger 
  update_version_column
after update on iam_role
  for each row execute procedure update_version_column();

create trigger
  update_time_column 
before update on iam_role
  for each row execute procedure update_time_column();

create trigger 
  immutable_create_time
before
update on iam_role
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_role
  for each row execute procedure default_create_time();

create trigger
  ensure_grant_scope_id_valid
before
insert or update on iam_role
  for each row execute procedure grant_scope_id_valid();

-- disallow_default_role_deletion prevents the default role (r_default) from
-- being deleted
create or replace function
  disallow_default_role_deletion()
  returns trigger
as $$
begin
  if old.public_id = 'r_default' then
    raise exception 'deletion of default role not allowed';
  end if;
  return old;
end;
$$ language plpgsql;

create trigger
  disallow_default_role_deletion
before
delete on iam_role
  for each row execute procedure disallow_default_role_deletion();

-- immutable_default_role_id prevents the a role's id from being updated.
create or replace function
  immutable_role_id()
  returns trigger
as $$
begin
  if new.public_id is distinct from old.public_id then
    raise exception 'update of role primary key not allowed';
  end if;
  return new;
end;
$$ language plpgsql;

create trigger 
  immutable_role_id
before
update on iam_role
  for each row execute procedure immutable_role_id();

insert into iam_role (public_id, name, description, scope_id)
  values('r_default', 'default', 'default role', 'global');

insert into iam_role_grant (role_id, canonical_grant, raw_grant)
  values('r_default', 'type=org;actions=list', 'type=org;actions=list');

create table iam_group (
    public_id wt_public_id not null primary key,
    create_time wt_timestamp,
    update_time wt_timestamp,
    name text,
    description text,
    scope_id wt_scope_id not null references iam_scope(public_id) on delete cascade on update cascade,
    unique(name, scope_id),
    disabled boolean not null default false,
    -- version allows optimistic locking of the group when modifying the group
    -- itself and when modifying dependent items like group members. 
    version bigint not null default 1,

    -- add unique index so a composite fk can be declared.
    unique(scope_id, public_id)
  );
  
create trigger 
  update_version_column
after update on iam_group
  for each row execute procedure update_version_column();

create trigger 
  update_time_column 
before update on iam_group
  for each row execute procedure update_time_column();

create trigger 
  immutable_create_time
before
update on iam_group
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_group
  for each row execute procedure default_create_time();

create trigger immutable_scope_id_group
before
update on iam_group
  for each row execute procedure iam_immutable_scope_id_func();

-- iam_user_role contains roles that have been assigned to users. Users can be
-- from any scope. The rows in this table must be immutable after insert, which
-- will be ensured with a before update trigger using
-- iam_immutable_role_principal(). 
create table iam_user_role (
  create_time wt_timestamp,
  role_id wt_role_id
    references iam_role(public_id)
    on delete cascade
    on update cascade,
  principal_id wt_user_id 
    references iam_user(public_id)
    on delete cascade
    on update cascade,
  primary key (role_id, principal_id)
  );

-- iam_group_role contains roles that have been assigned to groups. 
-- Groups can be from any scope. The rows in this table must be immutable after
-- insert, which will be ensured with a before update trigger using
-- iam_immutable_role_principal(). 
create table iam_group_role (
  create_time wt_timestamp,
  role_id wt_role_id
    references iam_role(public_id)
    on delete cascade
    on update cascade,
  principal_id wt_public_id 
    references iam_group(public_id)
    on delete cascade
    on update cascade,
  primary key (role_id, principal_id)
  );

-- get_scoped_principal_id is used by the iam_principle_role view as a convient
-- way to create <scope_id>:<principal_id> to reference principals from
-- other scopes than the role's scope. 
create or replace function get_scoped_principal_id(role_scope text, principal_scope text, principal_id text) returns text 
as $$
begin
	if role_scope = principal_scope then
		return principal_id;
	end if;
	return principal_scope || ':' || principal_id;
end;
$$ language plpgsql;

insert into iam_user_role (role_id, principal_id)
  values 
    ('r_default', 'u_anon'),
    ('r_default', 'u_auth');


-- iam_principle_role provides a consolidated view all principal roles assigned
-- (user and group roles).
create view iam_principal_role as
select 
	ur.create_time, 
	ur.principal_id,
	ur.role_id,
	u.scope_id as principal_scope_id, 
	r.scope_id as role_scope_id,
	get_scoped_principal_id(r.scope_id, u.scope_id, ur.principal_id) as scoped_principal_id,
	'user' as type
from 	
	iam_user_role ur, 
	iam_role r,
	iam_user u
where
	ur.role_id = r.public_id and 
	u.public_id = ur.principal_id
union 
select 
	gr.create_time, 
	gr.principal_id,
	gr.role_id,
	g.scope_id as principal_scope_id, 
	r.scope_id as role_scope_id,
	get_scoped_principal_id(r.scope_id, g.scope_id, gr.principal_id) as scoped_principal_id,
	'group' as type
from 	
	iam_group_role gr, 
	iam_role r,
	iam_group g
where
	gr.role_id = r.public_id and 
	g.public_id = gr.principal_id;

-- iam_immutable_role_principal() ensures that roles assigned to principals are immutable. 
create or replace function
  iam_immutable_role_principal()
  returns trigger
as $$
begin
    raise exception 'roles are immutable';
end;
$$ language plpgsql;

create trigger immutable_role_principal
before
update on iam_user_role
  for each row execute procedure iam_immutable_role_principal();

create trigger 
  immutable_create_time
before
update on iam_user_role
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_user_role
  for each row execute procedure default_create_time();

create trigger immutable_role_principal
before
update on iam_group_role
  for each row execute procedure iam_immutable_role_principal();

create trigger 
  immutable_create_time
before
update on iam_group_role
  for each row execute procedure immutable_create_time_func();
  
create trigger 
  default_create_time_column
before
insert on iam_group_role
  for each row execute procedure default_create_time();

-- iam_group_member_user is an association table that represents groups with
-- associated users.
create table iam_group_member_user (
  create_time wt_timestamp,
  group_id wt_public_id references iam_group(public_id) on delete cascade on update cascade,
  member_id wt_public_id references iam_user(public_id) on delete cascade on update cascade,
  primary key (group_id, member_id)
);

-- iam_immutable_group_member() ensures that group members are immutable. 
create or replace function
  iam_immutable_group_member()
  returns trigger
as $$
begin
    raise exception 'group members are immutable';
end;
$$ language plpgsql;

create trigger 
  default_create_time_column
before
insert on iam_group_member_user
  for each row execute procedure default_create_time();

create trigger 
  immutable_create_time
before
update on iam_group_member_user
  for each row execute procedure immutable_create_time_func();

create trigger iam_immutable_group_member
before
update on iam_group_member_user
  for each row execute procedure iam_immutable_group_member();

-- get_scoped_principal_id is used by the iam_group_member view as a convient
-- way to create <scope_id>:<member_id> to reference members from
-- other scopes than the group's scope. 
create or replace function get_scoped_member_id(group_scope text, member_scope text, member_id text) returns text 
as $$
begin
	if group_scope = member_scope then
		return member_id;
	end if;
	return member_scope || ':' || member_id;
end;
$$ language plpgsql;

-- iam_group_member provides a consolidated view of group members.
create view iam_group_member as
select
  gm.create_time,
  gm.group_id,
  gm.member_id,
  u.scope_id as member_scope_id,
  g.scope_id as group_scope_id,
  get_scoped_member_id(g.scope_id, u.scope_id, gm.member_id) as scoped_member_id,
  'user' as type
from
  iam_group_member_user gm,
  iam_user u,
  iam_group g
where
  gm.member_id = u.public_id and
  gm.group_id = g.public_id;
  

commit;

`),
	},
	"migrations/07_auth.down.sql": {
		name: "07_auth.down.sql",
		bytes: []byte(`
begin;

  drop function insert_auth_account_subtype;
  drop function insert_auth_method_subtype;

  drop table auth_account cascade;
  drop table auth_method cascade;

commit;

`),
	},
	"migrations/07_auth.up.sql": {
		name: "07_auth.up.sql",
		bytes: []byte(`
begin;

/*

  ┌────────────────┐               ┌────────────────┐
  │   iam_scope    │               │  auth_method   │
  ├────────────────┤               ├────────────────┤
  │ public_id (pk) │              ╱│ public_id (pk) │
  │                │┼┼───────────○─│ scope_id  (fk) │
  │                │              ╲│                │
  └────────────────┘               └────────────────┘
           ┼                                ┼
           ┼                                ┼
           │                                │
           │                                │ ▲fk1
           │                                │
           ○                                ○
          ╱│╲                              ╱│╲
  ┌────────────────┐          ┌──────────────────────────┐
  │    iam_user    │          │       auth_account       │
  ├────────────────┤          ├──────────────────────────┤
  │ public_id (pk) │          │ public_id      (pk)      │
  │ scope_id  (fk) │   ◀fk2   │ scope_id       (fk1,fk2) │
  │                │┼○──────○┼│ auth_method_id (fk1)     │
  │                │          │ iam_user_id    (fk2)     │
  └────────────────┘          └──────────────────────────┘

  An iam_scope can have 0 to many iam_users.
  An iam_scope can have 0 to many auth_methods.

  An iam_user belongs to 1 iam_scope.
  An auth_method belongs to 1 iam_scope.

  An iam_user can have 0 or 1 auth_account.
  An auth_account belongs to 0 or 1 iam_user.

  An auth_method can have 0 to many auth_accounts.
  An auth_account belongs to 1 auth_account.

  An auth_account can only be associated with an iam_user in the same scope of
  the auth_account's auth_method. Including scope_id in fk1 and fk2 ensures this
  restriction is not violated.

  Design influenced by:
  https://dba.stackexchange.com/questions/58970/enforcing-constraints-two-tables-away/58972#58972

*/

  -- base table for auth methods
  create table auth_method (
    public_id wt_public_id primary key,
    scope_id wt_scope_id not null
      references iam_scope(public_id)
      on delete cascade
      on update cascade,

    -- The order of columns is important for performance. See:
    -- https://dba.stackexchange.com/questions/58970/enforcing-constraints-two-tables-away/58972#58972
    -- https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field
    unique(scope_id, public_id)
  );


  -- base table for auth accounts
  create table auth_account (
    public_id wt_public_id primary key,
    auth_method_id wt_public_id not null,
    scope_id wt_scope_id not null,
    iam_user_id wt_public_id,
    -- including scope_id in fk1 and fk2 ensures the scope_id of the owning
    -- auth_method and the scope_id of the owning iam_user are the same
    foreign key (scope_id, auth_method_id) -- fk1
      references auth_method (scope_id, public_id)
      on delete cascade
      on update cascade,
    foreign key (scope_id, iam_user_id) -- fk2
      references iam_user (scope_id, public_id)
      on delete set null
      on update cascade,
    unique(scope_id, auth_method_id, public_id)
  );

  create or replace function
    insert_auth_method_subtype()
    returns trigger
  as $$
  begin
    insert into auth_method
      (public_id, scope_id)
    values
      (new.public_id, new.scope_id);
    return new;
  end;
  $$ language plpgsql;

  create or replace function
    insert_auth_account_subtype()
    returns trigger
  as $$
  begin

    select auth_method.scope_id
      into new.scope_id
    from auth_method
    where auth_method.public_id = new.auth_method_id;

    insert into auth_account
      (public_id, auth_method_id, scope_id)
    values
      (new.public_id, new.auth_method_id, new.scope_id);

    return new;

  end;
  $$ language plpgsql;

commit;

`),
	},
	"migrations/10_static_host.down.sql": {
		name: "10_static_host.down.sql",
		bytes: []byte(`
begin;

  drop table static_host_set_member cascade;
  drop table static_host_set cascade;
  drop table static_host cascade;
  drop table static_host_catalog cascade;

commit;

`),
	},
	"migrations/10_static_host.up.sql": {
		name: "10_static_host.up.sql",
		bytes: []byte(`
begin;

  create table static_host_catalog (
    public_id wt_public_id primary key,
    scope_id wt_scope_id not null
      references iam_scope (public_id)
      on delete cascade
      on update cascade,
    name text,
    description text,
    create_time wt_timestamp,
    update_time wt_timestamp,
    unique(scope_id, name)
  );

  create trigger
    update_time_column
  before update on static_host_catalog
    for each row execute procedure update_time_column();

  create trigger
    immutable_create_time
  before
  update on static_host_catalog
    for each row execute procedure immutable_create_time_func();

  create trigger
    default_create_time_column
  before
  insert on static_host_catalog
    for each row execute procedure default_create_time();

  create table static_host (
    public_id wt_public_id primary key,
    static_host_catalog_id wt_public_id not null
      references static_host_catalog (public_id)
      on delete cascade
      on update cascade,
    name text,
    description text,
    address text not null
    check(
      length(trim(address)) > 7
      and
      length(trim(address)) < 256
    ),
    create_time wt_timestamp,
    update_time wt_timestamp,
    unique(static_host_catalog_id, name)
  );

  create trigger
    update_time_column
  before update on static_host
    for each row execute procedure update_time_column();

  create trigger
    immutable_create_time
  before
  update on static_host
    for each row execute procedure immutable_create_time_func();

  create trigger
    default_create_time_column
  before
  insert on static_host
    for each row execute procedure default_create_time();

  create table static_host_set (
    public_id wt_public_id primary key,
    static_host_catalog_id wt_public_id not null
      references static_host_catalog (public_id)
      on delete cascade
      on update cascade,
    name text,
    description text,
    create_time wt_timestamp,
    update_time wt_timestamp,
    unique(static_host_catalog_id, name)
  );

  create trigger
    update_time_column
  before update on static_host_set
    for each row execute procedure update_time_column();

  create trigger
    immutable_create_time
  before
  update on static_host_set
    for each row execute procedure immutable_create_time_func();

  create trigger
    default_create_time_column
  before
  insert on static_host_set
    for each row execute procedure default_create_time();

  create table static_host_set_member (
    static_host_set_id wt_public_id
      references static_host_set (public_id)
      on delete cascade
      on update cascade,
    static_host_id wt_public_id
      references static_host (public_id)
      on delete cascade
      on update cascade,
    primary key(static_host_set_id, static_host_id)
  );

  insert into oplog_ticket (name, version)
  values
    ('static_host_catalog', 1),
    ('static_host', 1),
    ('static_host_set', 1),
    ('static_host_set_member', 1);

commit;

`),
	},
	"migrations/11_auth_token.down.sql": {
		name: "11_auth_token.down.sql",
		bytes: []byte(`
begin;

  drop view auth_token_account cascade;
  drop table auth_token cascade;

  drop function update_last_access_time cascade;
  drop function immutable_auth_token_columns cascade;
  drop function expire_time_not_older_than_token cascade;

commit;

`),
	},
	"migrations/11_auth_token.up.sql": {
		name: "11_auth_token.up.sql",
		bytes: []byte(`
begin;

  -- an auth token belongs to 1 and only 1 auth account
  -- an auth account can have 0 to many auth tokens
  create table auth_token (
    public_id wt_public_id primary key,
    token bytea not null unique,
    auth_account_id wt_public_id not null
      references auth_account(public_id)
      on delete cascade
      on update cascade,
    create_time wt_timestamp,
    update_time wt_timestamp,
    -- This column is not updated every time this auth token is accessed.
    -- It is updated after X minutes from the last time it was updated on
    -- a per row basis.
    approximate_last_access_time wt_timestamp
      check(
        approximate_last_access_time <= expiration_time
      ),
    expiration_time wt_timestamp
      check(
        create_time <= expiration_time
      )
  );

  create view auth_token_account as
        select at.public_id,
               at.token,
               at.auth_account_id,
               at.create_time,
               at.update_time,
               at.approximate_last_access_time,
               at.expiration_time,
               aa.scope_id,
               aa.iam_user_id,
               aa.auth_method_id
          from auth_token as at
    inner join auth_account as aa
            on at.auth_account_id = aa.public_id;

  create or replace function
    update_last_access_time()
    returns trigger
  as $$
  begin
    if new.approximate_last_access_time is distinct from old.approximate_last_access_time then
      new.approximate_last_access_time = now();
    end if;
    return new;
  end;
  $$ language plpgsql;

  comment on function
    update_last_access_time()
  is
    'function used in before update triggers to properly set last_access_time columns';

  create or replace function
    immutable_auth_token_columns()
    returns trigger
  as $$
  begin
    if new.auth_account_id is distinct from old.auth_account_id then
      raise exception 'auth_account_id is read-only';
    end if;
    if new.token is distinct from old.token then
      raise exception 'token is read-only';
    end if;
    return new;
  end;
  $$ language plpgsql;

  comment on function
    immutable_auth_token_columns()
  is
    'function used in before update triggers to make specific columns immutable';

  -- This allows the expiration to be calculated on the server side and still hold the constraint that
  -- the expiration time cant be before the creation time of the auth token.
  create or replace function
    expire_time_not_older_than_token()
    returns trigger
  as $$
  begin
    if new.expiration_time < new.create_time then
      new.expiration_time = new.create_time;
    end if;
    return new;
  end;
  $$ language plpgsql;

  comment on function
      expire_time_not_older_than_token()
  is
    'function used in before insert triggers to ensure expiration time is not older than create time';

  create trigger
    default_create_time_column
  before insert on auth_token
    for each row execute procedure default_create_time();

  create trigger
    expire_time_not_older_than_token
  before insert on auth_token
    for each row execute procedure expire_time_not_older_than_token();

  create trigger
    update_time_column
  before update on auth_token
    for each row execute procedure update_time_column();

  create trigger
    update_last_access_time
  before update on auth_token
    for each row execute procedure update_last_access_time();

  create trigger
    immutable_create_time
  before update on auth_token
    for each row execute procedure immutable_create_time_func();

  create trigger
    immutable_auth_token_columns
  before update on auth_token
    for each row execute procedure immutable_auth_token_columns();

commit;

`),
	},
	"migrations/12_auth_password.down.sql": {
		name: "12_auth_password.down.sql",
		bytes: []byte(`
begin;

  drop table auth_password_credential;
  drop table auth_password_conf cascade;
  drop table if exists auth_password_account;
  drop table if exists auth_password_method;

  drop function insert_auth_password_credential_subtype;
  drop function insert_auth_password_conf_subtype;

commit;

`),
	},
	"migrations/12_auth_password.up.sql": {
		name: "12_auth_password.up.sql",
		bytes: []byte(`
begin;

/*

       ┌────────────────┐                 ┌──────────────────────┐             ┌────────────────────────────┐
       │  auth_method   │                 │ auth_password_method │             │     auth_password_conf     │
       ├────────────────┤                 ├──────────────────────┤             ├────────────────────────────┤
       │ public_id (pk) │                 │ public_id (pk,fk)    │            ╱│ public_id          (pk,fk) │
       │ scope_id  (fk) │┼┼─────────────○┼│ scope_id  (fk)       │┼┼─────────○─│ password_method_id (fk)    │
       │                │                 │ ...                  │            ╲│                            │
       └────────────────┘                 └──────────────────────┘             └────────────────────────────┘
                ┼                                     ┼                                       ┼
                ┼                                     ┼                                       ┼
                │                                     │                                       │
                │ ▲fk1                                │ ▲fk1                                  │ ▲fk1
                │                                     │                                       │
                ○                                     ○                                       ○
               ╱│╲                                   ╱│╲                                     ╱│╲
  ┌──────────────────────────┐          ┌──────────────────────────┐          ┌───────────────────────────────┐
  │       auth_account       │          │  auth_password_account   │          │   auth_password_credential    │
  ├──────────────────────────┤          ├──────────────────────────┤          ├───────────────────────────────┤
  │ public_id      (pk)      │          │ public_id      (pk,fk2)  │          │ public_id           (pk)      │
  │ scope_id       (fk1,fk2) │   ◀fk2   │ scope_id       (fk1,fk2) │   ◀fk2   │ password_method_id  (fk1,fk2) │
  │ auth_method_id (fk1)     │┼┼──────○┼│ auth_method_id (fk1,fk2) │┼┼──────○┼│ password_conf_id    (fk1)     │
  │ iam_user_id    (fk2)     │          │ ...                      │          │ password_account_id (fk2)     │
  └──────────────────────────┘          └──────────────────────────┘          └───────────────────────────────┘

  An auth_method is "abstract". An auth_password_method is a concrete
  auth_method. For every row in auth_password_method there is one row in
  auth_method with the same public_id and scope_id.

  Similarly, an auth_account is "abstract". An auth_password_account is a concrete
  auth_account. For every row in auth_password_account there is one row in
  auth_account with the same public_id, scope_id, and auth_method_id.

  Both auth_password_conf and auth_password_credential are abstract. Each
  password key derivation function will require a concrete auth_password_conf
  and auth_password_credential table.

  An auth_method can have 0 or 1 auth_password_method.
  An auth_account can have 0 or 1 auth_password_account.

  An auth_password_method belongs to 1 auth_method.
  An auth_password_method can have 0 to many auth_password_accounts.
  An auth_password_method can have 0 to many auth_password_confs.

  An auth_password_account belongs to 1 auth_account.
  An auth_password_account belongs to 1 auth_password_method.
  An auth_password_account can have 0 or 1 auth_password_credential.

  An auth_password_conf belongs to 1 auth_password_method.
  An auth_password_conf can have 0 to many auth_password_credentials.

  An auth_password_credential belongs to 1 auth_password_account.
  An auth_password_credential belongs to 1 auth_password_conf.

*/

  create table auth_password_method (
    public_id wt_public_id primary key,
    scope_id wt_scope_id not null,
    name text,
    description text,
    create_time wt_timestamp,
    update_time wt_timestamp,
    min_user_name_length int not null default 5,
    min_password_length int not null default 8,
    foreign key (scope_id, public_id)
      references auth_method (scope_id, public_id)
      on delete cascade
      on update cascade,
    unique(scope_id, name),
    unique(scope_id, public_id)
  );

  create trigger
    insert_auth_method_subtype
  before insert on auth_password_method
    for each row execute procedure insert_auth_method_subtype();

  create table auth_password_account (
    public_id wt_public_id primary key,
    auth_method_id wt_public_id not null,
    -- NOTE(mgaffney): The scope_id type is not wt_scope_id because the domain
    -- check is executed before the insert trigger which retrieves the scope_id
    -- causing an insert to fail.
    scope_id text not null,
    name text,
    description text,
    create_time wt_timestamp,
    update_time wt_timestamp,
    user_name text not null
      check(
        lower(trim(user_name)) = user_name
        and
        length(user_name) > 0
      ),
    foreign key (scope_id, auth_method_id)
      references auth_password_method (scope_id, public_id)
      on delete cascade
      on update cascade,
    foreign key (scope_id, auth_method_id, public_id)
      references auth_account (scope_id, auth_method_id, public_id)
      on delete cascade
      on update cascade,
    unique(auth_method_id, name),
    unique(auth_method_id, user_name),
    unique(auth_method_id, public_id)
  );

  create trigger
    insert_auth_account_subtype
  before insert on auth_password_account
    for each row execute procedure insert_auth_account_subtype();

  create table auth_password_conf (
    public_id wt_public_id primary key,
    password_method_id wt_public_id not null
      references auth_password_method (public_id)
      on delete cascade
      on update cascade
      deferrable initially deferred,
    unique(password_method_id, public_id)
  );

  -- insert_auth_password_conf_subtype() is a trigger function for concrete
  -- implementations of auth_password_conf
  create or replace function
    insert_auth_password_conf_subtype()
    returns trigger
  as $$
  begin
    insert into auth_password_conf
      (public_id, password_method_id)
    values
      (new.public_id, new.password_method_id);
    return new;
  end;
  $$ language plpgsql;

  create table auth_password_credential (
    public_id wt_public_id primary key,
    password_account_id wt_public_id not null unique,
    password_conf_id wt_public_id not null,
    password_method_id wt_public_id not null,
    foreign key (password_method_id, password_conf_id)
      references auth_password_conf (password_method_id, public_id)
      on delete cascade
      on update cascade,
    foreign key (password_method_id, password_account_id)
      references auth_password_account (auth_method_id, public_id)
      on delete cascade
      on update cascade,
    unique(password_method_id, password_conf_id, password_account_id)
  );

  -- insert_auth_password_credential_subtype() is a trigger function for concrete
  -- implementations of auth_password_credential
  create or replace function
    insert_auth_password_credential_subtype()
    returns trigger
  as $$
  begin

    select auth_password_account.auth_method_id
      into new.password_method_id
    from auth_password_account
    where auth_password_account.public_id = new.password_account_id;

    insert into auth_password_credential
      (public_id, password_account_id, password_conf_id, password_method_id)
    values
      (new.public_id, new.password_account_id, new.password_conf_id, new.password_method_id);
    return new;
  end;
  $$ language plpgsql;

  --
  -- triggers for time columns
  ---

  create trigger
    update_time_column
  before
  update on auth_password_method
    for each row execute procedure update_time_column();

  create trigger
    immutable_create_time
  before
  update on auth_password_method
    for each row execute procedure immutable_create_time_func();

  create trigger
    default_create_time_column
  before
  insert on auth_password_method
    for each row execute procedure default_create_time();

  create trigger
    update_time_column
  before
  update on auth_password_account
    for each row execute procedure update_time_column();

  create trigger
    immutable_create_time
  before
  update on auth_password_account
    for each row execute procedure immutable_create_time_func();

  create trigger
    default_create_time_column
  before
  insert on auth_password_account
    for each row execute procedure default_create_time();

  insert into oplog_ticket (name, version)
  values
    ('auth_password_method', 1),
    ('auth_password_account', 1);

commit;

`),
	},
}