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
|
CREATE TABLE openvpn_certificates (
depth integer NOT NULL,
digest varchar(64) NOT NULL,
common_name varchar(64) NOT NULL,
organisation varchar(64) NOT NULL,
email varchar(256) NOT NULL,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
certid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX opevpn_certificates_digest ON openvpn_certificates(digest);
CREATE TABLE openvpn_users (
username varchar(32) NOT NULL,
password varchar(64) NOT NULL,
activated timestamp ,
deactivated timestamp ,
last_accessed timestamp ,
uid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_users_uname ON openvpn_users(username);
CREATE TABLE openvpn_usercerts (
uid integer NOT NULL,
certid integer NOT NULL,
accessprofile integer ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
uicid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_usercerts_uid ON openvpn_usercerts(uid);
CREATE INDEX openvpn_usercerts_certid ON openvpn_usercerts(certid);
CREATE TABLE openvpn_accesses (
access_descr varchar(128) ,
fw_profile varchar(64) NOT NULL,
accessprofile integer PRIMARY KEY AUTOINCREMENT
);
CREATE TABLE openvpn_lastlog (
uid integer ,
certid integer ,
protocol varchar(4) NOT NULL,
remotehost varchar(128) NOT NULL,
remoteport integer NOT NULL,
macaddr varchar(20) ,
vpnipaddr varchar(32) NOT NULL,
vpnipmask varchar(32) NOT NULL,
sessionstatus integer NOT NULL DEFAULT 0,
sesskey varchar(128) ,
login timestamp ,
logout timestamp ,
session_del timestamp ,
bytes_sent integer ,
bytes_received integer ,
llid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_lastlog_sesskey ON openvpn_lastlog(sesskey);
CREATE TABLE openvpn_sessions (
sesskey varchar(128) NOT NULL,
datakey varchar(256) NOT NULL,
dataval text ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
sessid integer PRIMARY KEY AUTOINCREMENT
);
CREATE INDEX openvpn_sessions_sesskey ON openvpn_sessions(sesskey);
CREATE UNIQUE INDEX openvpn_sessions_sess_datakey ON openvpn_sessions(sesskey, datakey);
CREATE TABLE openvpn_blacklist (
digest varchar(64) ,
username varchar(32) ,
remoteip varchar(32) ,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_accessed timestamp ,
blid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_blacklist_digest ON openvpn_blacklist(digest);
CREATE UNIQUE INDEX openvpn_blacklist_username ON openvpn_blacklist(username);
CREATE UNIQUE INDEX openvpn_blacklist_remoteip ON openvpn_blacklist(remoteip);
CREATE TABLE openvpn_attempts (
username varchar(32) ,
digest varchar(64) ,
remoteip varchar(32) ,
attempts integer DEFAULT 0,
registered timestamp DEFAULT CURRENT_TIMESTAMP,
last_attempt timestamp ,
atpid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_attempts_username ON openvpn_attempts(username);
CREATE UNIQUE INDEX openvpn_attempts_digest ON openvpn_attempts(digest);
CREATE UNIQUE INDEX openvpn_attempts_remoteip ON openvpn_attempts(remoteip);
CREATE TABLE openvpn_sessionkeys (
sessionseed varchar(64) NOT NULL,
sessionkey varchar(64) NOT NULL,
PRIMARY KEY(sessionseed)
);
CREATE TABLE openvpn_config (
datakey varchar(64) NOT NULL,
dataval text ,
cfgid integer PRIMARY KEY AUTOINCREMENT
);
CREATE UNIQUE INDEX openvpn_config_key ON openvpn_config(datakey);
|
