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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
/*
* $Id$
*
* PostgreSQL schema for FreeRADIUS
*
*/
/*
* Table structure for table 'radacct'
*
*/
CREATE TABLE IF NOT EXISTS radacct (
RadAcctId bigserial PRIMARY KEY,
AcctSessionId text NOT NULL,
AcctUniqueId text NOT NULL UNIQUE,
UserName text,
Realm text,
NASIPAddress inet NOT NULL,
NASPortId text,
NASPortType text,
AcctStartTime timestamp with time zone,
AcctUpdateTime timestamp with time zone,
AcctStopTime timestamp with time zone,
AcctInterval bigint,
AcctSessionTime bigint,
AcctAuthentic text,
ConnectInfo_start text,
ConnectInfo_stop text,
AcctInputOctets bigint,
AcctOutputOctets bigint,
CalledStationId text,
CallingStationId text,
AcctTerminateCause text,
ServiceType text,
FramedProtocol text,
FramedIPAddress inet,
FramedIPv6Address inet,
FramedIPv6Prefix inet,
FramedInterfaceId text,
DelegatedIPv6Prefix inet,
Class text
);
-- This index may be useful..
-- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
-- For use by update-, stop- and simul_* queries
CREATE INDEX radacct_active_session_idx ON radacct (AcctUniqueId) WHERE AcctStopTime IS NULL;
-- Add if you you regularly have to replay packets
-- CREATE INDEX radacct_session_idx ON radacct (AcctUniqueId);
-- For backwards compatibility
-- CREATE INDEX radacct_active_user_idx ON radacct (AcctSessionId, UserName, NASIPAddress) WHERE AcctStopTime IS NULL;
-- For use by onoff-
CREATE INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL;
-- and for common statistic queries:
CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
-- and, optionally
-- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
-- and for Class
CREATE INDEX radacct_calss_idx ON radacct (Class);
/*
* Table structure for table 'radcheck'
*/
CREATE TABLE IF NOT EXISTS radcheck (
id serial PRIMARY KEY,
UserName text NOT NULL DEFAULT '',
Attribute text NOT NULL DEFAULT '',
op VARCHAR(2) NOT NULL DEFAULT '==',
Value text NOT NULL DEFAULT ''
);
create index radcheck_UserName on radcheck (UserName,Attribute);
/*
* Use this index if you use case insensitive queries
*/
-- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
/*
* Table structure for table 'radgroupcheck'
*/
CREATE TABLE IF NOT EXISTS radgroupcheck (
id serial PRIMARY KEY,
GroupName text NOT NULL DEFAULT '',
Attribute text NOT NULL DEFAULT '',
op VARCHAR(2) NOT NULL DEFAULT '==',
Value text NOT NULL DEFAULT ''
);
create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
/*
* Table structure for table 'radgroupreply'
*/
CREATE TABLE IF NOT EXISTS radgroupreply (
id serial PRIMARY KEY,
GroupName text NOT NULL DEFAULT '',
Attribute text NOT NULL DEFAULT '',
op VARCHAR(2) NOT NULL DEFAULT '=',
Value text NOT NULL DEFAULT ''
);
create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
/*
* Table structure for table 'radreply'
*/
CREATE TABLE IF NOT EXISTS radreply (
id serial PRIMARY KEY,
UserName text NOT NULL DEFAULT '',
Attribute text NOT NULL DEFAULT '',
op VARCHAR(2) NOT NULL DEFAULT '=',
Value text NOT NULL DEFAULT ''
);
create index radreply_UserName on radreply (UserName,Attribute);
/*
* Use this index if you use case insensitive queries
*/
-- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
/*
* Table structure for table 'radusergroup'
*/
CREATE TABLE IF NOT EXISTS radusergroup (
id serial PRIMARY KEY,
UserName text NOT NULL DEFAULT '',
GroupName text NOT NULL DEFAULT '',
priority integer NOT NULL DEFAULT 0
);
create index radusergroup_UserName on radusergroup (UserName);
/*
* Use this index if you use case insensitive queries
*/
-- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
--
-- Table structure for table 'radpostauth'
--
CREATE TABLE IF NOT EXISTS radpostauth (
id bigserial PRIMARY KEY,
username text NOT NULL,
pass text,
reply text,
CalledStationId text,
CallingStationId text,
authdate timestamp with time zone NOT NULL default now(),
Class text
);
CREATE INDEX radpostauth_username_idx ON radpostauth (username);
CREATE INDEX radpostauth_class_idx ON radpostauth (Class);
/*
* Table structure for table 'nas'
*/
CREATE TABLE IF NOT EXISTS nas (
id serial PRIMARY KEY,
nasname text NOT NULL,
shortname text NOT NULL,
type text NOT NULL DEFAULT 'other',
ports integer,
secret text NOT NULL,
server text,
community text,
description text
);
create index nas_nasname on nas (nasname);
/*
* Table structure for table 'nasreload'
*/
CREATE TABLE IF NOT EXISTS nasreload (
NASIPAddress inet PRIMARY KEY,
ReloadTime timestamp with time zone NOT NULL
);
|
