Newer
Older
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);
CREATE UNIQUE INDEX group_path_idx ON gms_group USING btree(path);
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,
group_path ltree NOT NULL, -- group_path is copied here for performance reasons
primary key (group_id, user_id), -- we can have 1 permission for each group
foreign key (group_id) references gms_group(id),
foreign key (group_path) references gms_group(path)
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)
);
CREATE TABLE error_log (
"date" timestamp DEFAULT NOW(),
"exception_class" varchar,
"stack_trace" TEXT
);
CREATE TABLE audit_log (
"date" timestamp DEFAULT NOW(),
"user" varchar,
"ip_address" varchar,
"action" TEXT
);