1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
-- ----------------------------------------------------- --
-- PostgreSQL Database --
-- Initialize Glewlwyd Database for the backend server --
-- The administration client app --
-- Copyright 2020 Nicolas Mora <mail@babelouest.org> --
-- License: MIT --
-- ----------------------------------------------------- --
DROP TABLE IF EXISTS g_misc_config;
DROP TABLE IF EXISTS g_api_key;
DROP TABLE IF EXISTS g_client_user_scope;
DROP TABLE IF EXISTS g_scope_group_auth_scheme_module_instance;
DROP TABLE IF EXISTS g_scope_group;
DROP TABLE IF EXISTS g_user_session_scheme;
DROP TABLE IF EXISTS g_scope;
DROP TABLE IF EXISTS g_plugin_module_instance;
DROP TABLE IF EXISTS g_user_module_instance;
DROP TABLE IF EXISTS g_user_middleware_module_instance;
DROP TABLE IF EXISTS g_user_auth_scheme_module_instance;
DROP TABLE IF EXISTS g_client_module_instance;
DROP TABLE IF EXISTS g_user_session;
CREATE TABLE g_user_module_instance (
gumi_id SERIAL PRIMARY KEY,
gumi_module VARCHAR(128) NOT NULL,
gumi_order INTEGER NOT NULL,
gumi_name VARCHAR(128) NOT NULL,
gumi_display_name VARCHAR(256) DEFAULT '',
gumi_parameters TEXT,
gumi_readonly SMALLINT DEFAULT 0,
gumi_multiple_passwords SMALLINT DEFAULT 0,
gumi_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_user_middleware_module_instance (
gummi_id SERIAL PRIMARY KEY,
gummi_module VARCHAR(128) NOT NULL,
gummi_order INTEGER NOT NULL,
gummi_name VARCHAR(128) NOT NULL,
gummi_display_name VARCHAR(256) DEFAULT '',
gummi_parameters TEXT,
gummi_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_user_auth_scheme_module_instance (
guasmi_id SERIAL PRIMARY KEY,
guasmi_module VARCHAR(128) NOT NULL,
guasmi_expiration INTEGER NOT NULL DEFAULT 0,
guasmi_max_use INTEGER DEFAULT 0, -- 0: unlimited
guasmi_allow_user_register SMALLINT DEFAULT 1,
guasmi_name VARCHAR(128) NOT NULL,
guasmi_display_name VARCHAR(256) DEFAULT '',
guasmi_parameters TEXT,
guasmi_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_client_module_instance (
gcmi_id SERIAL PRIMARY KEY,
gcmi_module VARCHAR(128) NOT NULL,
gcmi_order INTEGER NOT NULL,
gcmi_name VARCHAR(128) NOT NULL,
gcmi_display_name VARCHAR(256) DEFAULT '',
gcmi_parameters TEXT,
gcmi_readonly SMALLINT DEFAULT 0,
gcmi_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_plugin_module_instance (
gpmi_id SERIAL PRIMARY KEY,
gpmi_module VARCHAR(128) NOT NULL,
gpmi_name VARCHAR(128) NOT NULL,
gpmi_display_name VARCHAR(256) DEFAULT '',
gpmi_parameters TEXT,
gpmi_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_user_session (
gus_id SERIAL PRIMARY KEY,
gus_session_hash VARCHAR(128) NOT NULL,
gus_user_agent VARCHAR(256),
gus_issued_for VARCHAR(256), -- IP address or hostname
gus_username VARCHAR(256) NOT NULL,
gus_expiration TIMESTAMP NOT NULL DEFAULT NOW(),
gus_last_login TIMESTAMP NOT NULL DEFAULT NOW(),
gus_current SMALLINT,
gus_enabled SMALLINT DEFAULT 1
);
CREATE INDEX i_g_user_session_username ON g_user_session(gus_username);
CREATE INDEX i_g_user_session_last_login ON g_user_session(gus_last_login);
CREATE INDEX i_g_user_session_expiration ON g_user_session(gus_expiration);
CREATE TABLE g_user_session_scheme (
guss_id SERIAL PRIMARY KEY,
gus_id INTEGER NOT NULL,
guasmi_id INTEGER DEFAULT NULL, -- NULL means scheme 'password'
guss_expiration TIMESTAMP NOT NULL DEFAULT NOW(),
guss_last_login TIMESTAMP NOT NULL DEFAULT NOW(),
guss_use_counter INTEGER DEFAULT 0,
guss_enabled SMALLINT DEFAULT 1,
FOREIGN KEY(gus_id) REFERENCES g_user_session(gus_id) ON DELETE CASCADE,
FOREIGN KEY(guasmi_id) REFERENCES g_user_auth_scheme_module_instance(guasmi_id) ON DELETE CASCADE
);
CREATE INDEX i_g_user_session_scheme_last_login ON g_user_session_scheme(guss_last_login);
CREATE INDEX i_g_user_session_scheme_expiration ON g_user_session_scheme(guss_expiration);
CREATE TABLE g_scope (
gs_id SERIAL PRIMARY KEY,
gs_name VARCHAR(128) NOT NULL UNIQUE,
gs_display_name VARCHAR(256) DEFAULT '',
gs_description VARCHAR(512),
gs_password_required SMALLINT DEFAULT 1,
gs_password_max_age INTEGER DEFAULT 0,
gs_enabled SMALLINT DEFAULT 1
);
CREATE TABLE g_scope_group (
gsg_id SERIAL PRIMARY KEY,
gs_id INTEGER,
gsg_name VARCHAR(128) NOT NULL,
gsg_scheme_required INTEGER DEFAULT 1,
FOREIGN KEY(gs_id) REFERENCES g_scope(gs_id) ON DELETE CASCADE
);
CREATE TABLE g_scope_group_auth_scheme_module_instance (
gsgasmi_id SERIAL PRIMARY KEY,
gsg_id INTEGER NOT NULL,
guasmi_id INTEGER NOT NULL,
FOREIGN KEY(gsg_id) REFERENCES g_scope_group(gsg_id) ON DELETE CASCADE,
FOREIGN KEY(guasmi_id) REFERENCES g_user_auth_scheme_module_instance(guasmi_id) ON DELETE CASCADE
);
CREATE TABLE g_client_user_scope (
gcus_id SERIAL PRIMARY KEY,
gs_id INTEGER NOT NULL,
gcus_username VARCHAR(256) NOT NULL,
gcus_client_id VARCHAR(256) NOT NULL,
gcus_granted TIMESTAMP NOT NULL DEFAULT NOW(),
gcus_enabled SMALLINT DEFAULT 1,
FOREIGN KEY(gs_id) REFERENCES g_scope(gs_id) ON DELETE CASCADE
);
CREATE INDEX i_g_client_user_scope_username ON g_client_user_scope(gcus_username);
CREATE INDEX i_g_client_user_scope_client_id ON g_client_user_scope(gcus_client_id);
CREATE TABLE g_api_key (
gak_id SERIAL PRIMARY KEY,
gak_token_hash VARCHAR(512) NOT NULL,
gak_counter INTEGER DEFAULT 0,
gak_username VARCHAR(256) NOT NULL,
gak_issued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
gak_issued_for VARCHAR(256), -- IP address or hostname
gak_user_agent VARCHAR(256),
gak_enabled SMALLINT DEFAULT 1
);
CREATE INDEX i_gak_token_hash ON g_api_key(gak_token_hash);
CREATE TABLE g_misc_config (
gmc_id SERIAL PRIMARY KEY,
gmc_type VARCHAR(128) NOT NULL,
gmc_name VARCHAR(128),
gmc_value TEXT DEFAULT NULL
);
CREATE INDEX i_gmc_type ON g_misc_config(gmc_type);
CREATE INDEX i_gmc_name ON g_misc_config(gmc_name);
|
