2
* $Id: db_postgresql.sql,v 1.16 2004/02/29 13:06:58 kkalev 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,
22
NASIPAddress INET NOT NULL,
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(32),
30
ConnectInfo_stop VARCHAR(32),
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,
43
CREATE INDEX radacct_active_user_idx ON radacct (userName) WHERE AcctStopTime IS NULL;
44
CREATE INDEX radacct_start_user_idx ON radacct (acctStartTime, UserName);
47
* There was WAAAY too many indexes previously. This combo index
48
* should take care of the most common searches.
49
* I have commented out all the old indexes, but left them in case
50
* someone wants them. I don't recomend anywone use them all at once
51
* as they will slow down your DB too much.
56
* create index radacct_UserName on radacct (UserName);
57
* create index radacct_AcctSessionId on radacct (AcctSessionId);
58
* create index radacct_AcctUniqueId on radacct (AcctUniqueId);
59
* create index radacct_FramedIPAddress on radacct (FramedIPAddress);
60
* create index radacct_NASIPAddress on radacct (NASIPAddress);
61
* create index radacct_AcctStartTime on radacct (AcctStartTime);
62
* create index radacct_AcctStopTime on radacct (AcctStopTime);
68
* Table structure for table 'radcheck'
70
CREATE TABLE radcheck (
71
id SERIAL PRIMARY KEY,
72
UserName VARCHAR(30) DEFAULT '' NOT NULL,
73
Attribute VARCHAR(30),
74
op VARCHAR(2) NOT NULL DEFAULT '==',
77
create index radcheck_UserName on radcheck (UserName,Attribute);
80
* Table structure for table 'radgroupcheck'
82
CREATE TABLE radgroupcheck (
83
id SERIAL PRIMARY KEY,
84
GroupName VARCHAR(20) DEFAULT '' NOT NULL,
85
Attribute VARCHAR(40),
86
op VARCHAR(2) NOT NULL DEFAULT '==',
89
create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
92
* Table structure for table 'radgroupreply'
94
CREATE TABLE radgroupreply (
95
id SERIAL PRIMARY KEY,
96
GroupName VARCHAR(20) DEFAULT '' NOT NULL,
97
Attribute VARCHAR(40),
98
op VARCHAR(2) NOT NULL DEFAULT '=',
101
create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
104
* Table structure for table 'radreply'
106
CREATE TABLE radreply (
107
id SERIAL PRIMARY KEY,
108
UserName VARCHAR(30) DEFAULT '' NOT NULL,
109
Attribute VARCHAR(30),
110
op VARCHAR(2) NOT NULL DEFAULT '=',
113
create index radreply_UserName on radreply (UserName,Attribute);
116
* Table structure for table 'usergroup'
118
CREATE TABLE usergroup (
119
id SERIAL PRIMARY KEY,
120
UserName VARCHAR(30) DEFAULT '' NOT NULL,
121
GroupName VARCHAR(30)
123
create index usergroup_UserName on usergroup (UserName);
126
* Table structure for table 'realmgroup'
128
CREATE TABLE realmgroup (
129
id SERIAL PRIMARY KEY,
130
RealmName VARCHAR(30) DEFAULT '' NOT NULL,
131
GroupName VARCHAR(30)
133
create index realmgroup_RealmName on realmgroup (RealmName);
136
* Table structure for table 'realms'
137
* This is not yet used by FreeRADIUS
139
CREATE TABLE realms (
140
id SERIAL PRIMARY KEY,
141
realmname VARCHAR(64),
144
options VARCHAR(128) DEFAULT ''
148
* Table structure for table 'nas'
149
* This is not currently used by FreeRADIUS but is usefull for reporting
153
ipaddr INET PRIMARY KEY,
154
shortname VARCHAR(32) NOT NULL,
155
secret VARCHAR(60) NOT NULL,
156
nasname VARCHAR(128),
159
community VARCHAR(50),
161
naslocation VARCHAR(32)
167
CREATE TABLE radpostauth (
168
id BIGSERIAL PRIMARY KEY,
169
username VARCHAR(64) NOT NULL,
172
authdate TIMESTAMP with time zone NOT NULL default 'now'
179
* Note: (pnixon: 2003-12-10) The following function should not be required
180
* if you use the PG specific queries in raddb/postgresql.conf
182
* Common utility function for date calculations. This is used in our
183
* alternative account stop query to calculate the start of a session.
185
* This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
187
* Please note that this requires the plpgsql to be available in your
188
* radius database. If it is not available you can register it with
189
* postgres by running this command:
191
* createlang plpgsql <databasename>
193
CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
198
var2 = $2 || '' '' || $3;
200
to_date($1 - var2::interval, ''YYYY-MM-DD'');
202
END;' LANGUAGE 'plpgsql';