70 lines
2.0 KiB
SQL
70 lines
2.0 KiB
SQL
create sequence application_s start with 1 increment by 1;
|
|
create table application_t (
|
|
id integer primary key not null default nextval('application_s'),
|
|
name varchar(128) not null unique
|
|
);
|
|
|
|
create sequence user_s start with 1 increment by 1;
|
|
create table user_t (
|
|
id integer primary key not null default nextval('user_s'),
|
|
login varchar(64) not null unique,
|
|
pwhash varchar(64) not null,
|
|
expiry integer not null default 600
|
|
);
|
|
|
|
create sequence token_s start with 1 increment by 1;
|
|
create table token_t (
|
|
id integer primary key not null default nextval('token_s'),
|
|
"user" integer not null references user_t (id),
|
|
salt varchar(64) not null,
|
|
valid boolean not null default true
|
|
);
|
|
|
|
create sequence claim_s start with 1 increment by 1;
|
|
create table claim_t (
|
|
id integer primary key not null default nextval('claim_s'),
|
|
key varchar(64) not null,
|
|
value varchar(64) not null,
|
|
application integer not null references application(id),
|
|
unique (key, value)
|
|
);
|
|
|
|
create table user_claim_mapping_t (
|
|
"user" integer not null references user_t(id),
|
|
claim integer not null references claim_t(id),
|
|
unique ("user", claim)
|
|
);
|
|
|
|
create table user_application_mapping_t (
|
|
"user" integer not null references user_t(id),
|
|
application integer not null references application_t(id),
|
|
unique ("user", application)
|
|
);
|
|
|
|
create or replace view claims_for_user_v as
|
|
select u.id as "user",
|
|
a.name as application,
|
|
c.key as key,
|
|
c.value as value
|
|
from user_t u,
|
|
claim_t c,
|
|
user_claim_mapping_t m,
|
|
application_t a
|
|
where m.user = u.id and
|
|
m.claim = c.id and
|
|
a.id = c.application;
|
|
|
|
create or replace view user_application_v as
|
|
select u.login as login,
|
|
u.pwhash as pwhash,
|
|
u.id as id,
|
|
u.expiry as expiry,
|
|
a.name as application
|
|
from user_t u,
|
|
application_t a,
|
|
user_application_mapping_t m
|
|
where u.id = m.user and
|
|
a.id = m.application;
|
|
|
|
|