sql howto.htm

802.1x认证的认证服务器freeradius的howto文档

<UL>
  <LI>Edit /etc/raddb/radiusd.conf and change as needed. You may wish to change 
  the default port to run on 1645 (old port) if you are replacing a legacy 
  RADIUS server. </LI></UL>
<P>At this point you should be able to manually fire up /usr/sbin/radiusd. You 
should do this with the debug turned on so you can see what happens: </P><PRE>     /usr/sbin/radiusd -X
</PRE>
<P><BR>Lots of stuff will scroll to the screen, and it should tell you it's 
ready to accept requests. If you get an error, READ THE DEBUG, then check the 
docs, check the above and try again. </P>
<P>You should now be able to use FreeRadius. You can use radtest to test an 
account from the command line: </P><PRE>     radtest username password servername port secret
</PRE>
<P><BR>So, if your example user is 'fred' with password 'wilma', your server is 
called 'radius.domain.com', is using port 1645, and you put localhost (or your 
localhost's IP) in clients.conf with a secret of 'mysecret', you should use: 
</P><PRE>     radtest fred wilma radius.domain.com 1645 mysecret
</PRE>
<P><BR>And you should get back something like: </P><PRE>     Sending Access-Request of id 226 to 127.0.0.1:1645
           User-Name = 'fred'
           User-Password = '\304\2323\326B\017\376\322?K\332\350Z;}'
           NAS-IP-Address = radius.domain.com
           NAS-Port = 1645
</PRE><PRE>     rad_recv&nbsp;: Access-Accept packet from host 127.0.0.1:1645,id=226, length=56
           Framed-IP-Address = 80.84.161.1
           Framed-Protocol = PPP
           Service-Type = Framed-User
           Framed-Compression = Van-Jacobson-TCP-IP
           Framed-IP- Netmask = 255.255.255.255
</PRE>
<P><BR>You should get an 'Access Accept' response. If you don't, do not pass Go, 
do not collect £200. Go back and check everything. Read the docs, READ THE 
DEBUG!! </P>
<P>If you have a Windows PC handy you may also wish to use NTradPing 
(downloadable from MasterSoft) to send test packets instead of radtest. If you 
do this, or test from any other machine, remember your PC (or other machine) 
needs to be in your NAS list in clients.conf too! OK, so at this point you 
should have text-file authentication working in FreeRadius... </P>
<DIV class=editsection style="FLOAT: right; MARGIN-LEFT: 5px">[<A 
title="Edit section: Setting up the RADIUS database" 
href="http://wiki.freeradius.org/index.php?title=SQL_HOWTO&amp;action=edit&amp;section=5">edit</A>]</DIV><A 
name=Setting_up_the_RADIUS_database></A>
<H2>Setting up the RADIUS database</H2>
<P>First, you should create a new empty 'radius' database in SQL and a database 
user with permissions to that database. You could of course call the database 
and the user anything you like but you probably should stick with 'radius' for 
both to keep things simple. </P>
<P>Next up, you need to create the schema for your database. There is an SQL 
script file for each SQL type in doc/examples/ in your operating system's doc 
directory (or where you untar'd FreeRADIUS). On SUSE this is under 
/usr/share/doc/packages/freeradius/ </P>
<DIV class=editsection style="FLOAT: right; MARGIN-LEFT: 5px">[<A 
title="Edit section: Create MySQL Database" 
href="http://wiki.freeradius.org/index.php?title=SQL_HOWTO&amp;action=edit&amp;section=6">edit</A>]</DIV><A 
name=Create_MySQL_Database></A>
<H3>Create MySQL Database</H3><PRE>mysql -uroot -p
  CREATE DATABASE radius;
  GRANT ALL ON radius.* TO radius@localhost IDENTIFIED BY "radpass";
  exit
</PRE>
<P><B>Note:</B> use a more secure password that "radpass" in the above example 
</P><PRE>cd /usr/share/doc/packages/freeradius/doc/examples/
mysql -uroot -p radius &lt; mysql.sql
</PRE>
<DIV class=editsection style="FLOAT: right; MARGIN-LEFT: 5px">[<A 
title="Edit section: Create PostgreSQL Database" 
href="http://wiki.freeradius.org/index.php?title=SQL_HOWTO&amp;action=edit&amp;section=7">edit</A>]</DIV><A 
name=Create_PostgreSQL_Database></A>
<H3>Create PostgreSQL Database</H3><PRE>su - postgres
createuser radius --no-superuser --no-createdb --no-createrole -P
createdb radius --owner=radius
exit
</PRE>
<P><B>Note:</B> choose a secure password when prompted for one by the 
<I>createuser</I> command. </P><PRE>cd /usr/share/doc/packages/freeradius/doc/examples/
psql -U radius radius &lt; postgresql.sql
</PRE>
<DIV class=editsection style="FLOAT: right; MARGIN-LEFT: 5px">[<A 
title="Edit section: Configuring FreeRadius to use SQL" 
href="http://wiki.freeradius.org/index.php?title=SQL_HOWTO&amp;action=edit&amp;section=8">edit</A>]</DIV><A 
name=Configuring_FreeRadius_to_use_SQL></A>
<H2>Configuring FreeRadius to use SQL</H2>
<P>Edit either /etc/raddb/sql.conf or /etc/raddb/postgresql.conf and enter the 
server, name and password details to connect to your SQL server and the RADIUS 
database. The database and table names should be left at the defaults if you 
used the default schema. For testing/debug purposes, switch on sqltrace if you 
wish - FreeRadius will dump all SQL commands to the debug output with this on. 
</P>
<P>If you are using PostgreSQL, in /etc/raddb/radiusd.conf change: </P><PRE>$INCLUDE  ${confdir}/sql.conf
</PRE>
<P>to </P><PRE>$INCLUDE  ${confdir}/postgresql.conf
</PRE>
<P>If you're stripping all realm names (i.e. you want user [e-mail] to 
authenticate as just 'joe'), then in sql.conf (or postgresql.conf), under the 
'query config: username' section, you MAY need to adjust the line(s) referring 
to sql_user_name. I needed to do this originally because we want to dump all 
realms, but you probably won't need to do this with the latest FreeRadius. For 
example, in our case I needed to uncomment the line: </P><PRE>            sql_user_name = '%{Stripped-User-Name}'
</PRE>
<P><BR>...and comment out the following line referring to just User-Name. If you 
want to see what's happening here, switch on all the logging options in 
radiusd.conf and run radiusd in debug mode (-X) to see what's happening&nbsp;: 
you'll see " user@domain" being passed to SQL when using User-Name, but just 
"user" when using Stripped-User-Name. Using the latter, realms worked for me 
(basically, I strip everything, as all user names are unique on the server 
anyway). Of course, set all your other SQL options as needed (database login 
details, etc) </P>
<P>Edit /etc/raddb/radiusd.conf and add a line saying 'sql' to the authorize{} 
section (which is towards the end of the file). The best place to put it is just 
after the 'files' entry. Indeed, if you'll just be using SQL, and not falling 
back to text files, you could comment out or delete the 'files' entry 
altogether. </P>
<P>Also add a line saying 'sql' to the accounting{} section to tell FreeRADIUS 
to store accounting records in SQL as well. </P>
<P>Optionally add 'sql' to the session{} section if you want to do 
Simultaneous-Use detection. </P>
<P>Optionally add 'sql' to the post-auth{} section if you want to log all 
Authentication attempts to SQL. </P>
<P><B>You should not change/delete any other lines in the config file without 
reading and understanding the comments!</B> </P>
<P>Your radiusd.conf should then look something like this: </P><PRE>authorise {
       preprocess
       chap
       mschap
       suffix
       eap
       # We leave "files" enabled to allow creation of test users in /etc/raddb/users
       files
       sql
       pap
}

accounting {
       # We leave "detail" enabled to _additionally_ log accounting to /var/log/radius/radacct
       detail
       sql
}
</PRE>
<DIV class=editsection style="FLOAT: right; MARGIN-LEFT: 5px">[<A 
title="Edit section: Populating SQL" 
href="http://wiki.freeradius.org/index.php?title=SQL_HOWTO&amp;action=edit&amp;section=9">edit</A>]</DIV><A 
name=Populating_SQL></A>
<H2>Populating SQL</H2>
<P>You should now created some dummy data in the database to test against. It 
goes something like this: </P>
<UL>
  <LI>In usergroup, put entries matching a user account name to a group name. 
  <LI>In radcheck, put an entry for each user account name with a 
  'Cleartext-Password' attribute with a value of their password. 
  <LI>In radreply, create entries for each user-specific radius reply attribute 
  against their username 
  <LI>In radgroupreply, create attributes to be returned to all group members 
  </LI></UL>
<P>Here's a dump of some example 'radius' tables from a MySQL database (With 
PostgreSQL the formating will look slightly different but it uses exactly the 
same content). </P>
<P>This example includes three users, one with a dynamically assigned IP by the 
NAS (fredf), one assigned a static IP (barney), and one representing a dial-up 
routed connection (dialrouter): </P><PRE>     mysql&gt; select * from usergroup;
     +----+---------------+-----------+
     | id | UserName      | GroupName |
     +----+---------------+-----------+
     |  1 | fredf         | dynamic   |
     |  2 | barney        | static    |
     |  2 | dialrouter    | netdial   |
     +----+---------------+-----------+
     3 rows in set (0.01 sec)

     mysql&gt; select * from radcheck;
     +----+----------------+--------------------+------------------+------+
     | id | UserName       | Attribute          | Value            | Op   | 
     +----+----------------+--------------------+------------------+------+
     |  1 | fredf          | Cleartext-Password | wilma            |&nbsp;:=   |
     |  2 | barney         | Cleartext-Password | betty            |&nbsp;:=   |
     |  2 | dialrouter     | Cleartext-Password | dialup           |&nbsp;:=   |
     +----+----------------+--------------------+------------------+------+
     3 rows in set (0.01 sec)

     mysql&gt; select * from radreply;

     +----+------------+-------------------+---------------------------------+------+
     | id | UserName   | Attribute         | Value                           | Op   |
     +----+------------+-------------------+---------------------------------+------+
     |  1 | barney     | Framed-IP-Address | 1.2.3.4                         |&nbsp;:=   |
     |  2 | dialrouter | Framed-IP-Address | 2.3.4.1                         |&nbsp;:=   |
     |  3 | dialrouter | Framed-IP-Netmask | 255.255.255.255                 |&nbsp;:=   |
     |  4 | dialrouter | Framed-Routing    | Broadcast-Listen                |&nbsp;:=   |
     |  5 | dialrouter | Framed-Route      | 2.3.4.0 255.255.255.248         |&nbsp;:=   |
     |  6 | dialrouter | Idle-Timeout      | 900                             |&nbsp;:=   |
     +----+------------+-------------------+---------------------------------+------+
     6 rows in set (0.01 sec)

     mysql&gt; select * from radgroupreply;
     +----+-----------+--------------------+---------------------+------+
     | id | GroupName | Attribute          | Value               | Op   |
     +----+-----------+--------------------+---------------------+------+
     | 34 | dynamic   | Framed-Compression | Van-Jacobsen-TCP-IP |&nbsp;:=   |
     | 33 | dynamic   | Framed-Protocol    | PPP                 |&nbsp;:=   |
     | 32 | dynamic   | Service-Type       | Framed-User         |&nbsp;:=   |
     | 35 | dynamic   | Framed-MTU         | 1500                |&nbsp;:=   |