Matteo Scandolo | 48d3d2d | 2017-08-08 13:05:27 -0700 | [diff] [blame^] | 1 | |
| 2 | # Copyright 2017-present Open Networking Foundation |
| 3 | # |
| 4 | # Licensed under the Apache License, Version 2.0 (the "License"); |
| 5 | # you may not use this file except in compliance with the License. |
| 6 | # You may obtain a copy of the License at |
| 7 | # |
| 8 | # http://www.apache.org/licenses/LICENSE-2.0 |
| 9 | # |
| 10 | # Unless required by applicable law or agreed to in writing, software |
| 11 | # distributed under the License is distributed on an "AS IS" BASIS, |
| 12 | # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 13 | # See the License for the specific language governing permissions and |
| 14 | # limitations under the License. |
| 15 | |
| 16 | |
Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame] | 17 | /* |
| 18 | * $Id: 5ab9d29306cbef460fe310aafd5d2046267cbbdc $ |
| 19 | * |
| 20 | * Postgresql schema for FreeRADIUS |
| 21 | * |
| 22 | * All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13 |
| 23 | * |
| 24 | */ |
| 25 | |
| 26 | /* |
| 27 | * Table structure for table 'radacct' |
| 28 | * |
| 29 | * Note: Column type bigserial does not exist prior to Postgres 7.2 |
| 30 | * If you run an older version you need to change this to serial |
| 31 | */ |
| 32 | CREATE TABLE radacct ( |
| 33 | RadAcctId bigserial PRIMARY KEY, |
| 34 | AcctSessionId text NOT NULL, |
| 35 | AcctUniqueId text NOT NULL UNIQUE, |
| 36 | UserName text, |
| 37 | GroupName text, |
| 38 | Realm text, |
| 39 | NASIPAddress inet NOT NULL, |
| 40 | NASPortId text, |
| 41 | NASPortType text, |
| 42 | AcctStartTime timestamp with time zone, |
| 43 | AcctUpdateTime timestamp with time zone, |
| 44 | AcctStopTime timestamp with time zone, |
| 45 | AcctInterval bigint, |
| 46 | AcctSessionTime bigint, |
| 47 | AcctAuthentic text, |
| 48 | ConnectInfo_start text, |
| 49 | ConnectInfo_stop text, |
| 50 | AcctInputOctets bigint, |
| 51 | AcctOutputOctets bigint, |
| 52 | CalledStationId text, |
| 53 | CallingStationId text, |
| 54 | AcctTerminateCause text, |
| 55 | ServiceType text, |
| 56 | FramedProtocol text, |
| 57 | FramedIPAddress inet |
| 58 | ); |
| 59 | -- This index may be useful.. |
| 60 | -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress); |
| 61 | |
| 62 | -- For use by update-, stop- and simul_* queries |
| 63 | CREATE INDEX radacct_active_user_idx ON radacct (AcctSessionId, UserName, NASIPAddress) WHERE AcctStopTime IS NULL; |
| 64 | |
| 65 | -- For use by onoff- |
| 66 | create INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL; |
| 67 | |
| 68 | -- and for common statistic queries: |
| 69 | CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName); |
| 70 | |
| 71 | -- and, optionally |
| 72 | -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName); |
| 73 | |
| 74 | /* |
| 75 | * There was WAAAY too many indexes previously. This combo index |
| 76 | * should take care of the most common searches. |
| 77 | * I have commented out all the old indexes, but left them in case |
| 78 | * someone wants them. I don't recomend anywone use them all at once |
| 79 | * as they will slow down your DB too much. |
| 80 | * - pnixon 2003-07-13 |
| 81 | */ |
| 82 | |
| 83 | /* |
| 84 | * create index radacct_UserName on radacct (UserName); |
| 85 | * create index radacct_AcctSessionId on radacct (AcctSessionId); |
| 86 | * create index radacct_AcctUniqueId on radacct (AcctUniqueId); |
| 87 | * create index radacct_FramedIPAddress on radacct (FramedIPAddress); |
| 88 | * create index radacct_NASIPAddress on radacct (NASIPAddress); |
| 89 | * create index radacct_AcctStartTime on radacct (AcctStartTime); |
| 90 | * create index radacct_AcctStopTime on radacct (AcctStopTime); |
| 91 | */ |
| 92 | |
| 93 | |
| 94 | |
| 95 | /* |
| 96 | * Table structure for table 'radcheck' |
| 97 | */ |
| 98 | CREATE TABLE radcheck ( |
| 99 | id serial PRIMARY KEY, |
| 100 | UserName text NOT NULL DEFAULT '', |
| 101 | Attribute text NOT NULL DEFAULT '', |
| 102 | op VARCHAR(2) NOT NULL DEFAULT '==', |
| 103 | Value text NOT NULL DEFAULT '' |
| 104 | ); |
| 105 | create index radcheck_UserName on radcheck (UserName,Attribute); |
| 106 | /* |
| 107 | * Use this index if you use case insensitive queries |
| 108 | */ |
| 109 | -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute); |
| 110 | |
| 111 | /* |
| 112 | * Table structure for table 'radgroupcheck' |
| 113 | */ |
| 114 | CREATE TABLE radgroupcheck ( |
| 115 | id serial PRIMARY KEY, |
| 116 | GroupName text NOT NULL DEFAULT '', |
| 117 | Attribute text NOT NULL DEFAULT '', |
| 118 | op VARCHAR(2) NOT NULL DEFAULT '==', |
| 119 | Value text NOT NULL DEFAULT '' |
| 120 | ); |
| 121 | create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute); |
| 122 | |
| 123 | /* |
| 124 | * Table structure for table 'radgroupreply' |
| 125 | */ |
| 126 | CREATE TABLE radgroupreply ( |
| 127 | id serial PRIMARY KEY, |
| 128 | GroupName text NOT NULL DEFAULT '', |
| 129 | Attribute text NOT NULL DEFAULT '', |
| 130 | op VARCHAR(2) NOT NULL DEFAULT '=', |
| 131 | Value text NOT NULL DEFAULT '' |
| 132 | ); |
| 133 | create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute); |
| 134 | |
| 135 | /* |
| 136 | * Table structure for table 'radreply' |
| 137 | */ |
| 138 | CREATE TABLE radreply ( |
| 139 | id serial PRIMARY KEY, |
| 140 | UserName text NOT NULL DEFAULT '', |
| 141 | Attribute text NOT NULL DEFAULT '', |
| 142 | op VARCHAR(2) NOT NULL DEFAULT '=', |
| 143 | Value text NOT NULL DEFAULT '' |
| 144 | ); |
| 145 | create index radreply_UserName on radreply (UserName,Attribute); |
| 146 | /* |
| 147 | * Use this index if you use case insensitive queries |
| 148 | */ |
| 149 | -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute); |
| 150 | |
| 151 | /* |
| 152 | * Table structure for table 'radusergroup' |
| 153 | */ |
| 154 | CREATE TABLE radusergroup ( |
| 155 | id serial PRIMARY KEY, |
| 156 | UserName text NOT NULL DEFAULT '', |
| 157 | GroupName text NOT NULL DEFAULT '', |
| 158 | priority integer NOT NULL DEFAULT 0 |
| 159 | ); |
| 160 | create index radusergroup_UserName on radusergroup (UserName); |
| 161 | /* |
| 162 | * Use this index if you use case insensitive queries |
| 163 | */ |
| 164 | -- create index radusergroup_UserName_lower on radusergroup (lower(UserName)); |
| 165 | |
| 166 | -- |
| 167 | -- Table structure for table 'radpostauth' |
| 168 | -- |
| 169 | |
| 170 | CREATE TABLE radpostauth ( |
| 171 | id bigserial PRIMARY KEY, |
| 172 | username text NOT NULL, |
| 173 | pass text, |
| 174 | reply text, |
| 175 | CalledStationId text, |
| 176 | CallingStationId text, |
| 177 | authdate timestamp with time zone NOT NULL default now() |
| 178 | ); |
| 179 | |
| 180 | /* |
| 181 | * Table structure for table 'nas' |
| 182 | */ |
| 183 | CREATE TABLE nas ( |
| 184 | id serial PRIMARY KEY, |
| 185 | nasname text NOT NULL, |
| 186 | shortname text NOT NULL, |
| 187 | type text NOT NULL DEFAULT 'other', |
| 188 | ports integer, |
| 189 | secret text NOT NULL, |
| 190 | server text, |
| 191 | community text, |
| 192 | description text |
| 193 | ); |
| 194 | create index nas_nasname on nas (nasname); |