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),
21 GroupName VARCHAR(253),
23 NASIPAddress INET NOT NULL,
24 NASPortId VARCHAR(15),
25 NASPortType VARCHAR(32),
26 AcctStartTime TIMESTAMP with time zone,
27 AcctStopTime TIMESTAMP with time zone,
28 AcctSessionTime BIGINT,
29 AcctAuthentic VARCHAR(32),
30 ConnectInfo_start VARCHAR(50),
31 ConnectInfo_stop VARCHAR(50),
32 AcctInputOctets BIGINT,
33 AcctOutputOctets BIGINT,
34 CalledStationId VARCHAR(50),
35 CallingStationId VARCHAR(50),
36 AcctTerminateCause VARCHAR(32),
37 ServiceType VARCHAR(32),
38 XAscendSessionSvrKey VARCHAR(10),
39 FramedProtocol VARCHAR(32),
41 AcctStartDelay BIGINT,
44 -- This index may be usefull..
45 -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
47 -- For use by onoff-, update-, stop- and simul_* queries
48 CREATE INDEX radacct_active_user_idx ON radacct (UserName, NASIPAddress, AcctSessionId) WHERE AcctStopTime IS NULL;
49 -- and for common statistic queries:
50 CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
52 -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
55 * There was WAAAY too many indexes previously. This combo index
56 * should take care of the most common searches.
57 * I have commented out all the old indexes, but left them in case
58 * someone wants them. I don't recomend anywone use them all at once
59 * as they will slow down your DB too much.
64 * create index radacct_UserName on radacct (UserName);
65 * create index radacct_AcctSessionId on radacct (AcctSessionId);
66 * create index radacct_AcctUniqueId on radacct (AcctUniqueId);
67 * create index radacct_FramedIPAddress on radacct (FramedIPAddress);
68 * create index radacct_NASIPAddress on radacct (NASIPAddress);
69 * create index radacct_AcctStartTime on radacct (AcctStartTime);
70 * create index radacct_AcctStopTime on radacct (AcctStopTime);
76 * Table structure for table 'radcheck'
78 CREATE TABLE radcheck (
79 id SERIAL PRIMARY KEY,
80 UserName VARCHAR(64) NOT NULL DEFAULT '',
81 Attribute VARCHAR(64) NOT NULL DEFAULT '',
82 op VARCHAR(2) NOT NULL DEFAULT '==',
83 Value VARCHAR(253) NOT NULL DEFAULT ''
85 create index radcheck_UserName on radcheck (UserName,Attribute);
87 * Use this index if you use case insensitive queries
89 -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
92 * Table structure for table 'radgroupcheck'
94 CREATE TABLE radgroupcheck (
95 id SERIAL PRIMARY KEY,
96 GroupName VARCHAR(64) NOT NULL DEFAULT '',
97 Attribute VARCHAR(64) NOT NULL DEFAULT '',
98 op VARCHAR(2) NOT NULL DEFAULT '==',
99 Value VARCHAR(253) NOT NULL DEFAULT ''
101 create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
104 * Table structure for table 'radgroupreply'
106 CREATE TABLE radgroupreply (
107 id SERIAL PRIMARY KEY,
108 GroupName VARCHAR(64) NOT NULL DEFAULT '',
109 Attribute VARCHAR(64) NOT NULL DEFAULT '',
110 op VARCHAR(2) NOT NULL DEFAULT '=',
111 Value VARCHAR(253) NOT NULL DEFAULT ''
113 create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
116 * Table structure for table 'radreply'
118 CREATE TABLE radreply (
119 id SERIAL PRIMARY KEY,
120 UserName VARCHAR(64) NOT NULL DEFAULT '',
121 Attribute VARCHAR(64) NOT NULL DEFAULT '',
122 op VARCHAR(2) NOT NULL DEFAULT '=',
123 Value VARCHAR(253) NOT NULL DEFAULT ''
125 create index radreply_UserName on radreply (UserName,Attribute);
127 * Use this index if you use case insensitive queries
129 -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
132 * Table structure for table 'radusergroup'
134 CREATE TABLE radusergroup (
135 UserName VARCHAR(64) NOT NULL DEFAULT '',
136 GroupName VARCHAR(64) NOT NULL DEFAULT '',
137 priority INTEGER NOT NULL DEFAULT 0
139 create index radusergroup_UserName on radusergroup (UserName);
141 * Use this index if you use case insensitive queries
143 -- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
146 * Table structure for table 'realmgroup'
147 * Commented out because currently not used
149 --CREATE TABLE realmgroup (
150 -- id SERIAL PRIMARY KEY,
151 -- RealmName VARCHAR(30) DEFAULT '' NOT NULL,
152 -- GroupName VARCHAR(30)
154 --create index realmgroup_RealmName on realmgroup (RealmName);
157 * Table structure for table 'realms'
158 * This is not yet used by FreeRADIUS
160 --CREATE TABLE realms (
161 -- id SERIAL PRIMARY KEY,
162 -- realmname VARCHAR(64),
165 -- options VARCHAR(128) DEFAULT ''
169 * Table structure for table 'nas'
172 id SERIAL PRIMARY KEY,
173 nasname VARCHAR(128) NOT NULL,
174 shortname VARCHAR(32) NOT NULL,
175 type VARCHAR(30) NOT NULL DEFAULT 'other',
177 secret VARCHAR(60) NOT NULL,
178 community VARCHAR(50),
179 description VARCHAR(200)
181 create index nas_nasname on nas (nasname);
184 -- Table structure for table 'radpostauth'
187 CREATE TABLE radpostauth (
188 id BIGSERIAL PRIMARY KEY,
189 username VARCHAR(253) NOT NULL,
192 CalledStationId VARCHAR(50),
193 CallingStationId VARCHAR(50),
194 authdate TIMESTAMP with time zone NOT NULL default 'now'
198 -- Table structure for table 'radippool'
201 CREATE TABLE radippool (
202 id BIGSERIAL PRIMARY KEY,
203 pool_name varchar(64) NOT NULL,
204 FramedIPAddress INET NOT NULL,
205 NASIPAddress VARCHAR(16) NOT NULL default '',
206 pool_key VARCHAR(64) NOT NULL default 0,
207 CalledStationId VARCHAR(64),
208 CallingStationId text NOT NULL default ''::text,
209 expiry_time TIMESTAMP(0) without time zone NOT NULL default 'now'::timestamp(0),
210 username text DEFAULT ''::text
213 CREATE INDEX radippool_poolname_ipaadr ON radippool USING btree (pool_name, framedipaddress);
214 CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, expiry_time);
215 CREATE INDEX radippool_nasipaddr_poolkey ON radippool USING btree (nasipaddress, pool_key);
216 CREATE INDEX radippool_nasipaddr_calling ON radippool USING btree (nasipaddress, callingstationid);
219 -- Table structure for table 'dictionary'
220 -- This is not currently used by FreeRADIUS
222 -- CREATE TABLE dictionary (
223 -- id SERIAL PRIMARY KEY,
225 -- Attribute VARCHAR(64),
226 -- Value VARCHAR(64),
227 -- Format VARCHAR(20),
228 -- Vendor VARCHAR(32)
232 * Note: (pnixon: 2003-12-10) The following function should not be required
233 * if you use the PG specific queries in raddb/postgresql.conf
235 * Common utility function for date calculations. This is used in our
236 * alternative account stop query to calculate the start of a session.
238 * This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
240 * Please note that this requires the plpgsql to be available in your
241 * radius database. If it is not available you can register it with
242 * postgres by running this command:
244 * createlang plpgsql <databasename>
246 CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
251 var2 = $2 || '' '' || $3;
253 to_date($1 - var2::interval, ''YYYY-MM-DD'');
255 END;' LANGUAGE 'plpgsql';