init.sql 1.12 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE gms_group (
  id varchar NOT NULL,
  name text NOT NULL,
  path ltree NOT NULL,
  primary key(id)
);

CREATE INDEX group_path_gist_idx ON gms_group USING GIST(path);
11
CREATE UNIQUE INDEX group_path_idx ON gms_group USING btree(path);
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE INDEX group_name_idx ON gms_group USING btree(name);

CREATE TABLE gms_membership (
  group_id varchar NOT NULL,
  user_id varchar NOT NULL,
  primary key (group_id, user_id),
  foreign key (group_id) references gms_group(id)
);

CREATE TYPE permission_type AS ENUM ('VIEW_MEMBERS', 'MANAGE_MEMBERS', 'ADMIN');

CREATE TABLE gms_permission (
  group_id varchar NOT NULL,
  user_id varchar NOT NULL,
  permission permission_type NOT NULL,
27
  group_path ltree NOT NULL, -- group_path is copied here for performance reasons
28
  primary key (group_id, user_id, permission),
29
30
  foreign key (group_id) references gms_group(id),
  foreign key (group_path) references gms_group(path)
31
);
32
33
34
35
36
37
38
39

CREATE TABLE gms_client (
  client_id varchar NOT NULL,
  client_secret varchar NOT NULL,
  allowed_actions text[] NOT NULL,
  ip_filter text NULL,
  primary key (client_id)
);