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 system 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. The database design process should be driven by your specific requirements to ensure data integrity and optimal performance.
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. Your design strategy should account for how the data is stored and retrieved over the long term.
Key database design principles for RADIUS systems
Number of users and access control
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. Each user should have a unique identifier to reduce redundancies and maintain access control. 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 organisations that 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 organisations 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 organisation. See our design blueprint for universities, and our design strategy for multi-site ISPs.
Retaining historical data in a separate table
Many ISPs and telecoms are required to maintain historical data to comply with regulatory requirements. Keeping current session data in a separate table 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 isn'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 organisations 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 service multiple locations, and retain historical information. The result is that RADIUS system design requires expertise, and a deep understanding of RADIUS and database design principles. It is just not possible to pick “a good design” and use it everywhere.
Need more help?
InkBridge Networks has been at the forefront of network security for over two decades, tackling complex challenges across various protocols and infrastructures. Our team of seasoned experts has encountered and solved nearly every conceivable network security issue. If you're looking for insights from the architects behind some of the internet's most foundational authentication systems, you can request a quote for network security solutions here.
Related Articles
Separating Authentication from your RADIUS Accounting server
Many ISP networks and enterprise environments handle both Authentication and Accounting functions through the same RADIUS servers and databases. While this configuration works well for small and low-load systems, there are compelling reasons to separate these critical network security functions.
Why you should separate historical data from live data
ISPs and telecoms are often legally required to keep user session data for long periods of time. However, keeping these records can result in enormous databases tables which significantly affect the performance of your RADIUS system. This article explores how to optimize database performance so that you can maintain operational efficiency while storing years of user activity data.