doc/examples/postgresql.sql

   1 /*
   2  * $Id$
   3  *
   4  * Postgresql schema for FreeRADIUS
   5  *
   6  * All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13
   7  *
   8  */
   9
  10 /*
  11  * Table structure for table 'radacct'
  12  *
  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
  15  */
  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),
  22         Realm                   VARCHAR(64),
  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         FramedProtocol          VARCHAR(32),
  39         FramedIPAddress         INET,
  40         AcctStartDelay          BIGINT,
  41         AcctStopDelay           BIGINT
  42 );
  43 -- This index may be usefull..
  44 -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
  45
  46 -- For use by onoff-, update-, stop- and simul_* queries
  47 CREATE INDEX radacct_active_user_idx ON radacct (UserName, NASIPAddress, AcctSessionId) WHERE AcctStopTime IS NULL;
  48 -- and for common statistic queries:
  49 CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
  50 -- and, optionally
  51 -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
  52
  53 /*
  54  * There was WAAAY too many indexes previously. This combo index
  55  * should take care of the most common searches.
  56  * I have commented out all the old indexes, but left them in case
  57  * someone wants them. I don't recomend anywone use them all at once
  58  * as they will slow down your DB too much.
  59  *  - pnixon 2003-07-13
  60  */
  61
  62 /*
  63  * create index radacct_UserName on radacct (UserName);
  64  * create index radacct_AcctSessionId on radacct (AcctSessionId);
  65  * create index radacct_AcctUniqueId on radacct (AcctUniqueId);
  66  * create index radacct_FramedIPAddress on radacct (FramedIPAddress);
  67  * create index radacct_NASIPAddress on radacct (NASIPAddress);
  68  * create index radacct_AcctStartTime on radacct (AcctStartTime);
  69  * create index radacct_AcctStopTime on radacct (AcctStopTime);
  70 */
  71
  72
  73
  74 /*
  75  * Table structure for table 'radcheck'
  76  */
  77 CREATE TABLE radcheck (
  78         id              SERIAL PRIMARY KEY,
  79         UserName        VARCHAR(64) NOT NULL DEFAULT '',
  80         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
  81         op              VARCHAR(2) NOT NULL DEFAULT '==',
  82         Value           VARCHAR(253) NOT NULL DEFAULT ''
  83 );
  84 create index radcheck_UserName on radcheck (UserName,Attribute);
  85 /*
  86  * Use this index if you use case insensitive queries
  87  */
  88 -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
  89
  90 /*
  91  * Table structure for table 'radgroupcheck'
  92  */
  93 CREATE TABLE radgroupcheck (
  94         id              SERIAL PRIMARY KEY,
  95         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
  96         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
  97         op              VARCHAR(2) NOT NULL DEFAULT '==',
  98         Value           VARCHAR(253) NOT NULL DEFAULT ''
  99 );
 100 create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
 101
 102 /*
 103  * Table structure for table 'radgroupreply'
 104  */
 105 CREATE TABLE radgroupreply (
 106         id              SERIAL PRIMARY KEY,
 107         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
 108         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
 109         op              VARCHAR(2) NOT NULL DEFAULT '=',
 110         Value           VARCHAR(253) NOT NULL DEFAULT ''
 111 );
 112 create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
 113
 114 /*
 115  * Table structure for table 'radreply'
 116  */
 117 CREATE TABLE radreply (
 118         id              SERIAL PRIMARY KEY,
 119         UserName        VARCHAR(64) NOT NULL DEFAULT '',
 120         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
 121         op              VARCHAR(2) NOT NULL DEFAULT '=',
 122         Value           VARCHAR(253) NOT NULL DEFAULT ''
 123 );
 124 create index radreply_UserName on radreply (UserName,Attribute);
 125 /*
 126  * Use this index if you use case insensitive queries
 127  */
 128 -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
 129
 130 /*
 131  * Table structure for table 'radusergroup'
 132  */
 133 CREATE TABLE radusergroup (
 134         UserName        VARCHAR(64) NOT NULL DEFAULT '',
 135         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
 136         priority        INTEGER NOT NULL DEFAULT 0
 137 );
 138 create index radusergroup_UserName on radusergroup (UserName);
 139 /*
 140  * Use this index if you use case insensitive queries
 141  */
 142 -- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
 143
 144 /*
 145  * Table structure for table 'realmgroup'
 146  * Commented out because currently not used
 147  */
 148 --CREATE TABLE realmgroup (
 149 --      id              SERIAL PRIMARY KEY,
 150 --      RealmName       VARCHAR(30) DEFAULT '' NOT NULL,
 151 --      GroupName       VARCHAR(30)
 152 --);
 153 --create index realmgroup_RealmName on realmgroup (RealmName);
 154
 155 /*
 156  * Table structure for table 'realms'
 157  * This is not yet used by FreeRADIUS
 158  */
 159 --CREATE TABLE realms (
 160 --      id              SERIAL PRIMARY KEY,
 161 --      realmname       VARCHAR(64),
 162 --      nas             VARCHAR(128),
 163 --      authport        int4,
 164 --      options         VARCHAR(128) DEFAULT ''
 165 --);
 166
 167 /*
 168  * Table structure for table 'nas'
 169  */
 170 CREATE TABLE nas (
 171         id              SERIAL PRIMARY KEY,
 172         nasname         VARCHAR(128) NOT NULL,
 173         shortname       VARCHAR(32) NOT NULL,
 174         type            VARCHAR(30) NOT NULL DEFAULT 'other',
 175         ports           int4,
 176         secret          VARCHAR(60) NOT NULL,
 177         community       VARCHAR(50),
 178         description     VARCHAR(200)
 179 );
 180 create index nas_nasname on nas (nasname);
 181
 182 --
 183 -- Table structure for table 'radpostauth'
 184 --
 185
 186 CREATE TABLE radpostauth (
 187         id                      BIGSERIAL PRIMARY KEY,
 188         username                VARCHAR(253) NOT NULL,
 189         pass                    VARCHAR(128),
 190         reply                   VARCHAR(32),
 191         CalledStationId         VARCHAR(50),
 192         CallingStationId        VARCHAR(50),
 193         authdate                TIMESTAMP with time zone NOT NULL default 'now'
 194 );
 195
 196 --
 197 -- Table structure for table 'radippool'
 198 --
 199
 200 CREATE TABLE radippool (
 201         id                      BIGSERIAL PRIMARY KEY,
 202         pool_name               varchar(64) NOT NULL,
 203         FramedIPAddress         INET NOT NULL,
 204         NASIPAddress            VARCHAR(16) NOT NULL default '',
 205         pool_key                VARCHAR(64) NOT NULL default 0,
 206         CalledStationId         VARCHAR(64),
 207         CallingStationId        text DEFAULT ''::text NOT NULL default '',
 208         expiry_time             TIMESTAMP(0) without time zone NOT NULL default 'now'::timestamp(0),
 209         username                text DEFAULT ''::text
 210 );
 211
 212 CREATE INDEX radippool_poolname_ipaadr ON radippool USING btree (pool_name, framedipaddress);
 213 CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, expiry_time);
 214 CREATE INDEX radippool_nasipaddr_poolkey ON radippool USING btree (nasipaddress, pool_key);
 215 CREATE INDEX radippool_nasipaddr_calling ON radippool USING btree (nasipaddress, callingstationid);
 216
 217 --
 218 -- Table structure for table 'dictionary'
 219 -- This is not currently used by FreeRADIUS
 220 --
 221 -- CREATE TABLE dictionary (
 222 --     id              SERIAL PRIMARY KEY,
 223 --     Type            VARCHAR(30),
 224 --     Attribute       VARCHAR(64),
 225 --     Value           VARCHAR(64),
 226 --     Format          VARCHAR(20),
 227 --     Vendor          VARCHAR(32)
 228 -- );
 229
 230 /*
 231  * Note: (pnixon: 2003-12-10) The following function should not be required
 232  * if you use the PG specific queries in raddb/postgresql.conf
 233  *
 234  * Common utility function for date calculations. This is used in our
 235  * alternative account stop query to calculate the start of a session.
 236  *
 237  * This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
 238  *
 239  * Please note that this requires the plpgsql to be available in your
 240  * radius database. If it is not available you can register it with
 241  * postgres by running this command:
 242  *
 243  *   createlang plpgsql <databasename>
 244  */
 245 CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
 246 DECLARE
 247         var1 date;
 248         var2 text;
 249 BEGIN
 250         var2 = $2 || '' '' || $3;
 251         SELECT INTO var1
 252                 to_date($1 - var2::interval, ''YYYY-MM-DD'');
 253 RETURN var1;
 254 END;' LANGUAGE 'plpgsql';