2
* $Id: postgresql.sql,v 1.1.2.2 2006/04/11 13:14:41 pnixon Exp $
4
* Postgresql schema for FreeRADIUS
6
* All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13
11
* Table structure for table 'radacct'
13
* Note: Column type BIGSERIAL does not exist prior to Postgres 7.2
14
* If you run an older version you need to change this to SERIAL
16
CREATE TABLE radacct (
17
RadAcctId BIGSERIAL PRIMARY KEY,
18
AcctSessionId VARCHAR(32) NOT NULL,
19
AcctUniqueId VARCHAR(32) NOT NULL,
20
UserName VARCHAR(253),
22
NASIPAddress INET NOT NULL,
23
NASPortId VARCHAR(15),
24
NASPortType VARCHAR(32),
25
AcctStartTime TIMESTAMP with time zone,
26
AcctStopTime TIMESTAMP with time zone,
27
AcctSessionTime BIGINT,
28
AcctAuthentic VARCHAR(32),
29
ConnectInfo_start VARCHAR(50),
30
ConnectInfo_stop VARCHAR(50),
31
AcctInputOctets BIGINT,
32
AcctOutputOctets BIGINT,
33
CalledStationId VARCHAR(50),
34
CallingStationId VARCHAR(50),
35
AcctTerminateCause VARCHAR(32),
36
ServiceType VARCHAR(32),
37
FramedProtocol VARCHAR(32),
39
AcctStartDelay BIGINT,
42
-- This index may be usefull..
43
-- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
45
-- For use by onoff-, update-, stop- and simul_* queries
46
CREATE INDEX radacct_active_user_idx ON radacct (userName) WHERE AcctStopTime IS NULL;
47
-- and for common statistic queries:
48
CREATE INDEX radacct_start_user_idx ON radacct (acctStartTime, UserName);
50
-- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
53
* There was WAAAY too many indexes previously. This combo index
54
* should take care of the most common searches.
55
* I have commented out all the old indexes, but left them in case
56
* someone wants them. I don't recomend anywone use them all at once
57
* as they will slow down your DB too much.
62
* create index radacct_UserName on radacct (UserName);
63
* create index radacct_AcctSessionId on radacct (AcctSessionId);
64
* create index radacct_AcctUniqueId on radacct (AcctUniqueId);
65
* create index radacct_FramedIPAddress on radacct (FramedIPAddress);
66
* create index radacct_NASIPAddress on radacct (NASIPAddress);
67
* create index radacct_AcctStartTime on radacct (AcctStartTime);
68
* create index radacct_AcctStopTime on radacct (AcctStopTime);
74
* Table structure for table 'radcheck'
76
CREATE TABLE radcheck (
77
id SERIAL PRIMARY KEY,
78
UserName VARCHAR(64) NOT NULL DEFAULT '',
79
Attribute VARCHAR(64) NOT NULL DEFAULT '',
80
op VARCHAR(2) NOT NULL DEFAULT '==',
81
Value VARCHAR(253) NOT NULL DEFAULT ''
83
create index radcheck_UserName on radcheck (UserName,Attribute);
85
* Use this index if you use case insensitive queries
87
-- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
90
* Table structure for table 'radgroupcheck'
92
CREATE TABLE radgroupcheck (
93
id SERIAL PRIMARY KEY,
94
GroupName VARCHAR(64) NOT NULL DEFAULT '',
95
Attribute VARCHAR(64) NOT NULL DEFAULT '',
96
op VARCHAR(2) NOT NULL DEFAULT '==',
97
Value VARCHAR(253) NOT NULL DEFAULT ''
99
create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
102
* Table structure for table 'radgroupreply'
104
CREATE TABLE radgroupreply (
105
id SERIAL PRIMARY KEY,
106
GroupName VARCHAR(64) NOT NULL DEFAULT '',
107
Attribute VARCHAR(64) NOT NULL DEFAULT '',
108
op VARCHAR(2) NOT NULL DEFAULT '=',
109
Value VARCHAR(253) NOT NULL DEFAULT ''
111
create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
114
* Table structure for table 'radreply'
116
CREATE TABLE radreply (
117
id SERIAL PRIMARY KEY,
118
UserName VARCHAR(64) NOT NULL DEFAULT '',
119
Attribute VARCHAR(64) NOT NULL DEFAULT '',
120
op VARCHAR(2) NOT NULL DEFAULT '=',
121
Value VARCHAR(253) NOT NULL DEFAULT ''
123
create index radreply_UserName on radreply (UserName,Attribute);
125
* Use this index if you use case insensitive queries
127
-- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
130
* Table structure for table 'usergroup'
132
CREATE TABLE usergroup (
133
UserName VARCHAR(64) NOT NULL DEFAULT '',
134
GroupName VARCHAR(64) NOT NULL DEFAULT '',
135
priority INTEGER NOT NULL DEFAULT 0
137
create index usergroup_UserName on usergroup (UserName);
139
* Use this index if you use case insensitive queries
141
-- create index usergroup_UserName_lower on usergroup (lower(UserName));
144
* Table structure for table 'realmgroup'
145
* Commented out because currently not used
147
--CREATE TABLE realmgroup (
148
-- id SERIAL PRIMARY KEY,
149
-- RealmName VARCHAR(30) DEFAULT '' NOT NULL,
150
-- GroupName VARCHAR(30)
152
--create index realmgroup_RealmName on realmgroup (RealmName);
155
* Table structure for table 'realms'
156
* This is not yet used by FreeRADIUS
158
--CREATE TABLE realms (
159
-- id SERIAL PRIMARY KEY,
160
-- realmname VARCHAR(64),
163
-- options VARCHAR(128) DEFAULT ''
167
* Table structure for table 'nas'
170
id SERIAL PRIMARY KEY,
171
nasname VARCHAR(128) NOT NULL,
172
shortname VARCHAR(32) NOT NULL,
173
type VARCHAR(30) NOT NULL DEFAULT 'other',
175
secret VARCHAR(60) NOT NULL,
176
community VARCHAR(50),
177
description VARCHAR(200)
179
create index nas_nasname on nas (nasname);
182
-- Table structure for table 'radpostauth'
185
CREATE TABLE radpostauth (
186
id BIGSERIAL PRIMARY KEY,
187
username VARCHAR(253) NOT NULL,
190
authdate TIMESTAMP with time zone NOT NULL default 'now'
193
CREATE TABLE radippool (
195
pool_name text NOT NULL,
197
nas_ip_address text NOT NULL,
198
nas_port integer NOT NULL,
199
calling_station_id text DEFAULT ''::text NOT NULL,
200
expiry_time timestamp(0) without time zone NOT NULL,
201
username text DEFAULT ''::text,
202
calledstationid character varying(64),
203
poolkey character varying(120)
207
-- Table structure for table 'dictionary'
208
-- This is not currently used by FreeRADIUS
210
-- CREATE TABLE dictionary (
211
-- id SERIAL PRIMARY KEY,
213
-- Attribute VARCHAR(64),
214
-- Value VARCHAR(64),
215
-- Format VARCHAR(20),
216
-- Vendor VARCHAR(32)
220
* Note: (pnixon: 2003-12-10) The following function should not be required
221
* if you use the PG specific queries in raddb/postgresql.conf
223
* Common utility function for date calculations. This is used in our
224
* alternative account stop query to calculate the start of a session.
226
* This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
228
* Please note that this requires the plpgsql to be available in your
229
* radius database. If it is not available you can register it with
230
* postgres by running this command:
232
* createlang plpgsql <databasename>
234
CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
239
var2 = $2 || '' '' || $3;
241
to_date($1 - var2::interval, ''YYYY-MM-DD'');
243
END;' LANGUAGE 'plpgsql';