Database design principles

Database design is often overlooked as a critical element of a RADIUS ecosystem. In practice, when we work with our clients, we usually spend the bulk of our time optimizing the database architecture. Our time spent on FreeRADIUS is small in comparison. Why? Because the difference between a fast, reliable RADIUS server and a slow, unstable one, is almost always the database.

It is important to remember that there is no “one size fits all” approach to database architecture for RADIUS. Rather, it is helpful to start with how you plan on using the database, and then design a system which supports those use-cases.

Here are some of the most common considerations:

• How many users are authenticating on your network?

• What kind of authentication protocols are they using?

• How are passwords stored in the DB? Encrypted? Cleartext?

• Do you need to keep track of accounting information?

• Are you billing users for their usage?

• Do you have multiple locations?

  • If so, how do you synchronize the database between the locations?

• Do you need to keep track of historical data for legal reasons?

• Do you need to detect and/or stop credential sharing?

• Do users tend to authenticate once during the day or do they authenticate multiple times throughout the day?

• Are you doing IP address assignment?

  • How many IP addresses need to be managed?

All of these factors should be considered when designing your RADIUS database ecosystem. Some of these might seem obvious, but others are less intuitively connected to database implications.

Number of users

This is probably the most intuitively understood factor for database design. Very large databases can be slower to respond than smaller ones, especially if the indexes are not carefully designed. One way to address these issues is to add more databases, either in a “cross bar” design, or as load-balanced shards. See our discussion on scaling RADIUS infrastructure .

Tracking accounting information

If your business needs to bill customers for their network usage, you should consider splitting the authentication and accounting functionality of your RADIUS system into separate databases.

Separating the two capabilities into independent databases means that users can still be authenticated if the accounting database is slow or unavailable. As RADIUS clients will retransmit accounting packets, these retransmits can work around an accounting database being down for a short period of time, and often no accounting information will be lost. On the other hand, if the authentication database is down, no user can get onto the network, and many users will call support.

For organizations which don’t need to track accounting information, such as typical enterprises or universities, this design is not necessary. However, for ISPs and telecoms, it is usually foundational to our RADIUS ecosystem design. See our article about separating Accounting from Authentication functionality.

It is also possible to split the accounting database into “live” and “historical” systems. The RADIUS server will write to the “live” system, and the “historical” system can be used for complex end of month billing queries. These queries may take significant resources on the database, which could negatively affect the RADIUS server if the queries were done on the “live” system.

Servicing multiple locations

Many large organizations have multiple locations. From universities with multiple campuses in the same general area, to ISPs with locations all over the country, to enterprises with locations that span the globe. To support multiple locations, we typically recommend cloning the primary RADIUS server and its related database(s). There are definitely nuances to this approach depending on the specific requirements of the organization. See our design blueprint for universities, and our design strategy for multi-site ISPs.

Retaining historical data

Many ISPs and telecoms are required to maintain historical data to comply with regulatory requirements. Keeping current session data in a separate database from historical session data is strongly recommended in this situation. The “live” accounting database is small and needs to be fast, whereas the “historical” database can be many terabytes in size and has fewer requirements for speed. See our article on separating historical data.

Preventing credential sharing

In some cases,credential sharing can be a major issue for ISPs, resulting in significant losses in revenue. To prevent credential sharing, the RADIUS infrastructure must check all new sessions against all current live sessions to ensure that there aren’t more than one session at a time for a given user. The database design needs to support more than just a simple authentication query for each new session. See our design strategy for multi-site ISPs for a detailed discussion.

Supporting authentication “peaks”

In most university settings, authentication requests tend to come in waves at the beginning of each class period. This places unique demands on the RADIUS infrastructure. See our design blueprint for universities for a detailed discussion.

The bottom line

Many organizations need to support more than one of these considerations. For example, a university might have multiple locations and authentication peaks. Or an ISP might have millions of users, and must track accounting information, and services multiple locations, and must retain historical information. The result is that RADIUS system design requires expertise, and a deep understanding of RADIUS and databases. It is just not possible to pick “a good design” and use it everywhere.

Need more help?

Network RADIUS has been helping clients around the world design and deploy their RADIUS infrastructure for 20 years. We specialize in complex systems and have seen pretty much every variation and problem out there. If you want help from the people who wrote FreeRADIUS, visit our quote page to contact us for a consultation.