What kind of data each freeradius table store in database?

Question

i got a project to create a freeradius like application, but i don't understand what kind of data each table store in the database. Anyone care to explain ? here's the list of those tables:

1. nas
2. radacct
3. radcheck
4. radgroupcheck
5. radgroupreply
6. radpostauth
7. radreply
8. radusergroup

any help will be very appreciated, and sorry for my english

Answer 1

First you need to read up on how the user's file works, as the logic of the SQL module is based on that.

The tables in the default schema are used for the following:

• nas - Definitions of RADIUS clients. These are the IP addresses and shared secrets of NAS (Network Access Servers) that will be talking to FreeRADIUS. This contains the same basic information as the clients.conf file. These are read in from the SQL database on startup, and provide a convenient way of sharing client definitions across a cluster of FreeRADIUS instances.

• radpostauth - This contains a record of the result of an authentication attempt. Usually it will hold the username, when the authentication attempt was made, and whether authentication was successful or not. Many people customize this to record extra information. Some even record incorrect passwords to help helpdesk users (though this is not recommended).

• radacct - This stores RADIUS accounting data. RADIUS accounting data describes a service session. The session usually starts immediately after authentication, and ends when the user either disconnects from the network service (disassociates from the wireless access point, pulls out the ethernet cable etc...), or is administratively terminated via a PoD Packet of Disconnect, or a session timer (see the Session-Timeout attribute). This table stores the username, connection point, and traffic stats for the session, as well as many other fields. Not all columns will be useful for all deployments, so its a good idea to remove ones that you're not using, and update the queries appropriately. Only one row is created per session, and is updated over the lifetime of that session.

• radcheck - This has identical logic to the first line of a user's file entry. It has check and control pairs which are differentiated by the operator used. If the check pairs match then radreply is queried for reply pairs to add to the reply list. Both radcheck and radreply are indexed by user by default (it can be altered). Only one set of radcheck/radreply items is permitted per index value.

• radreply - Contains reply pairs to add if the radcheck check items matched.

• radusergroup - Contains mappings between users and groups. If a mapping exists for a user, the radgroupcheck and radgroupreply tables will be queried with the same logic as radcheck and radreply, the main difference being that check and reply items are being looked up for the group and not the user. This is also used when the SQL-Group attribute is used in a condition. SQL-Group is a magic attribute which results in radusergroup being queried, evaluating to true if the user is a member of a group, and false if they are not.

• radgroupcheck - Identical to radcheck but indexed by group instead of user.

• radgroupreply - Identical to radreply but indexed by group instead of user.

The configuration files for rlm_sql are split. There's the main config file in mods-available/sql, and then the SQL dialect specific one in mods-config/sql/main/<dialect>/queries.conf

• mods-available/sql is where you set connection parameters (IP address of the server, port, credentials, database flavour).
• queries.conf is where you can alter the default queries and index values.