Hi all, this is a "theoretical" post. As written in the object: LDAP or mySQL in the backend for a remote access control system? I've seen that that for remote access control, e.g. Wi-Fi prepaid access solution, it is widely used freeRADIUS+captive portal+mySQL. There are several implementation of capitve portals, but they all do the same thing: act as NAS towards RADIUS providing AAA attributes. User data are stored in the backend in a database, but for usage characteristics this database should be a directory server (LDAP server); such a solution, in fact, requires features typical for a directory server: optimization for read operation, distributed model for storage of information, advanced search capabilities. So the best solution should be LDAP (openLDAP) for users' data and SQL (mySQL) for accounting data (these data are overwritten so number of write operations are almost equal to number of read operations). Despite this, I've seen that LDAP is not widely used. Is this for its complexity or are there deeper reasons that suggest to use SQL database for both (user data, accounting) purposes? Does anybody have links that might help to build a system made using this architecture? Regards, Carlo
Carlo Prestopino wrote:
Hi all,
this is a “theoretical” post.
As written in the object: LDAP or mySQL in the backend for a remote access control system?
I’ve seen that that for remote access control, e.g. Wi-Fi prepaid access solution, it is widely used freeRADIUS+captive portal+mySQL.
There are several implementation of capitve portals, but they all do the same thing: act as NAS towards RADIUS providing AAA attributes.
User data are stored in the backend in a database, but for usage characteristics this database should be a directory server (LDAP server); such a solution, in fact, requires features typical for a directory server: optimization for read operation, distributed model for storage of information, advanced search capabilities. So the best solution should be LDAP (openLDAP) for users’ data and SQL (mySQL) for accounting data (these data are overwritten so number of write operations are almost equal to number of read operations).
Despite this, I’ve seen that LDAP is not widely used. Is this for its complexity or are there deeper reasons that suggest to use SQL database for both (user data, accounting) purposes? Does anybody have links that might help to build a system made using this architecture?
I would suspect that the vast majority of the world won't deploy a system large enough to tell the difference in performance between LDAP and SQL so most make the decision in favor of the one they are most comfortable with managing. -- Lewis Bergman Texas Communications 4309 Maple St. Abilene, TX 79602-8044 Off. 325-691-1301 Cell 325-439-0533 fax 325-695-6841
Despite this, I've seen that LDAP is not widely used. Is this for its complexity or are there deeper reasons that suggest to use SQL database for both (user data, accounting) purposes? Does anybody have links that might help to build a system made using this architecture?
I use ldap for users and sql for accounting for the same reasons you listed. Installing and using openldap has a much higher learning curve than mysql, which is why I would guess more users use mysql. There is an old doc in the source under doc/ldap_howto.txt. I wrote that several years ago and keep promising a new version. Well, I was finally given a week at the end of this month or early next month to stop all my projects and start documenting. So, at that time, I will be re-writing that doc to be more current. I agree that ldap is a perfect place to store user objects. For example, I have it setup like this. ou=users uid=someuser,ou=users... radiusgroupname: dial radiusgroupname: adsl accountNumber: 11111 uid=anotheruser,ou=users... radiusgroupname: adsl accountNumber: 11111 ou=accounts accountNumber=11111,ou=accounts... radiusgroupname: wifi Using ldap, I can specify the services the user has access to as an attribute of that user. I can also do account level groups as well. In the users above, with my freeradius configuration, I can assure that someuser has access to adsl and dial, while another user can only login to adsl. Any user in the 11111 account can login to wifi, which would be both of those users. Now, you can easily do the same thing in mysql as well. But, I feel that ldap is a better model for this data. Also, with openldap it is very easy to replicate and setup distribution of these users. Also, since ldap is a standard protocol, my provisioning system can write to it whether its from windows, solaris, linux, etc... It just needs to understand the ldap protocol. Using mysql, means your provisioning system must understand mysql syntax, although that isn't usually a difficult task to get setup. Finally, as you said, ldap is optimized for reads and that's exactly what I'm doing. I've never experienced any issues with the ldap servers being overloaded. Then again I don't get a whole lot of traffic maybe 60k-80k logins a day. The only downside I can think of with openldap is that it doesn't support multi-master setups. There are workarounds I've heard of people using with some kind of heartbeat setup and a shared IP, but I don't have the need for a multi-master setup. As long as my master is replicating to my slaves and freeradius is hitting my slaves, I can assure that no users are ever denied access because of an ldap server going down. If the master goes down, the only effect is on provisioning (such as adding new users or changing passwords). In this case we take a slave server and manually upgrade it to a master while we fix the master server. Freeradius with its configurable_failover solution, will allow us to point to all of our slave servers and it takes care of any slave servers going down automatically for us, without the need for a load balancer. As an alternative for non-freeradius ldap queries, I've also setup two ldap proxy servers that point back to my three slaves. The two proxy servers share an IP, so one is always master (on the ip). If it goes down, the slave takes over that IP. In that scenario, you can point applications that don't do failover to the proxyldap shared IP and it will take care of the failover for you. I really like ldap, but its taken me some time to become comfortable with it. I hope that doc helps you with your setup, if you need more help, post questions to the list. Be sure to explain what you are trying to do and show debug info (radiusd -X) so we can see the difference between what its doing and what you want it to do. Take care.
Sometimes, the performance is not important, what you have to remember is to use a system that you know well. You can also learn an other, but when a failure is becoming, you're faster to repair it. Mathieu. Dusty Doris wrote:
Despite this, I've seen that LDAP is not widely used. Is this for its complexity or are there deeper reasons that suggest to use SQL database for both (user data, accounting) purposes? Does anybody have links that might help to build a system made using this architecture?
I use ldap for users and sql for accounting for the same reasons you listed. Installing and using openldap has a much higher learning curve than mysql, which is why I would guess more users use mysql.
There is an old doc in the source under doc/ldap_howto.txt. I wrote that several years ago and keep promising a new version. Well, I was finally given a week at the end of this month or early next month to stop all my projects and start documenting. So, at that time, I will be re-writing that doc to be more current.
I agree that ldap is a perfect place to store user objects. For example, I have it setup like this.
ou=users uid=someuser,ou=users... radiusgroupname: dial radiusgroupname: adsl accountNumber: 11111
uid=anotheruser,ou=users... radiusgroupname: adsl accountNumber: 11111
ou=accounts accountNumber=11111,ou=accounts... radiusgroupname: wifi
Using ldap, I can specify the services the user has access to as an attribute of that user. I can also do account level groups as well. In the users above, with my freeradius configuration, I can assure that someuser has access to adsl and dial, while another user can only login to adsl. Any user in the 11111 account can login to wifi, which would be both of those users.
Now, you can easily do the same thing in mysql as well. But, I feel that ldap is a better model for this data. Also, with openldap it is very easy to replicate and setup distribution of these users. Also, since ldap is a standard protocol, my provisioning system can write to it whether its from windows, solaris, linux, etc... It just needs to understand the ldap protocol. Using mysql, means your provisioning system must understand mysql syntax, although that isn't usually a difficult task to get setup. Finally, as you said, ldap is optimized for reads and that's exactly what I'm doing. I've never experienced any issues with the ldap servers being overloaded. Then again I don't get a whole lot of traffic maybe 60k-80k logins a day.
The only downside I can think of with openldap is that it doesn't support multi-master setups. There are workarounds I've heard of people using with some kind of heartbeat setup and a shared IP, but I don't have the need for a multi-master setup.
As long as my master is replicating to my slaves and freeradius is hitting my slaves, I can assure that no users are ever denied access because of an ldap server going down. If the master goes down, the only effect is on provisioning (such as adding new users or changing passwords). In this case we take a slave server and manually upgrade it to a master while we fix the master server.
Freeradius with its configurable_failover solution, will allow us to point to all of our slave servers and it takes care of any slave servers going down automatically for us, without the need for a load balancer.
As an alternative for non-freeradius ldap queries, I've also setup two ldap proxy servers that point back to my three slaves. The two proxy servers share an IP, so one is always master (on the ip). If it goes down, the slave takes over that IP. In that scenario, you can point applications that don't do failover to the proxyldap shared IP and it will take care of the failover for you.
I really like ldap, but its taken me some time to become comfortable with it.
I hope that doc helps you with your setup, if you need more help, post questions to the list. Be sure to explain what you are trying to do and show debug info (radiusd -X) so we can see the difference between what its doing and what you want it to do.
Take care. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thank you Dusty, could you please provide complete URL for your "doc/ldap_howto.txt", I'd a look to http://www.freeradius.org/doc/ , but didn't find it. My interest in LDAP is not related to performance but to the scalability (distributed directories, referrals, etc...) that it provides compared to SQL. The system is already working with LDAP as backend database, but only as free access. In these day we are developing the prepaid solution for Wi-Fi access (scratch cards), but completely with mySQL, because it is simpler to use (tables radacct, radcheck, etc... are already built) and develop. As we arrive to a stable solution it is my intention to move the authentication process to openLDAP. Best regards Carlo -----Messaggio originale----- Da: freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org [mailto:freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius. org] Per conto di Dusty Doris Inviato: sabato 7 gennaio 2006 17.56 A: FreeRadius users mailing list Oggetto: Re: openLDAP vs.mySQL
Despite this, I've seen that LDAP is not widely used. Is this for its
complexity or are there deeper reasons that suggest to use SQL database for
both (user data, accounting) purposes? Does anybody have links that might
help to build a system made using this architecture?
I use ldap for users and sql for accounting for the same reasons you listed. Installing and using openldap has a much higher learning curve than mysql, which is why I would guess more users use mysql. There is an old doc in the source under doc/ldap_howto.txt. I wrote that several years ago and keep promising a new version. Well, I was finally given a week at the end of this month or early next month to stop all my projects and start documenting. So, at that time, I will be re-writing that doc to be more current. I agree that ldap is a perfect place to store user objects. For example, I have it setup like this. ou=users uid=someuser,ou=users... radiusgroupname: dial radiusgroupname: adsl accountNumber: 11111 uid=anotheruser,ou=users... radiusgroupname: adsl accountNumber: 11111 ou=accounts accountNumber=11111,ou=accounts... radiusgroupname: wifi Using ldap, I can specify the services the user has access to as an attribute of that user. I can also do account level groups as well. In the users above, with my freeradius configuration, I can assure that someuser has access to adsl and dial, while another user can only login to adsl. Any user in the 11111 account can login to wifi, which would be both of those users. Now, you can easily do the same thing in mysql as well. But, I feel that ldap is a better model for this data. Also, with openldap it is very easy to replicate and setup distribution of these users. Also, since ldap is a standard protocol, my provisioning system can write to it whether its from windows, solaris, linux, etc... It just needs to understand the ldap protocol. Using mysql, means your provisioning system must understand mysql syntax, although that isn't usually a difficult task to get setup. Finally, as you said, ldap is optimized for reads and that's exactly what I'm doing. I've never experienced any issues with the ldap servers being overloaded. Then again I don't get a whole lot of traffic maybe 60k-80k logins a day. The only downside I can think of with openldap is that it doesn't support multi-master setups. There are workarounds I've heard of people using with some kind of heartbeat setup and a shared IP, but I don't have the need for a multi-master setup. As long as my master is replicating to my slaves and freeradius is hitting my slaves, I can assure that no users are ever denied access because of an ldap server going down. If the master goes down, the only effect is on provisioning (such as adding new users or changing passwords). In this case we take a slave server and manually upgrade it to a master while we fix the master server. Freeradius with its configurable_failover solution, will allow us to point to all of our slave servers and it takes care of any slave servers going down automatically for us, without the need for a load balancer. As an alternative for non-freeradius ldap queries, I've also setup two ldap proxy servers that point back to my three slaves. The two proxy servers share an IP, so one is always master (on the ip). If it goes down, the slave takes over that IP. In that scenario, you can point applications that don't do failover to the proxyldap shared IP and it will take care of the failover for you. I really like ldap, but its taken me some time to become comfortable with it. I hope that doc helps you with your setup, if you need more help, post questions to the list. Be sure to explain what you are trying to do and show debug info (radiusd -X) so we can see the difference between what its doing and what you want it to do. Take care. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html __________ NOD32 1.1355 (20060106) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com
Thank you Dusty,
could you please provide complete URL for your "doc/ldap_howto.txt", I'd a look to http://www.freeradius.org/doc/ , but didn't find it.
Hmm, used to be in the doc directory when you untarred the source. Maybe its too old now. Its still up at http://doris.name/radius. I'm not actually using distributed directories, its a pretty simple setup here for me. Perhaps you can contribute some documentation of your setup when you've completed it.
My interest in LDAP is not related to performance but to the scalability (distributed directories, referrals, etc...) that it provides compared to SQL. The system is already working with LDAP as backend database, but only as free access. In these day we are developing the prepaid solution for Wi-Fi access (scratch cards), but completely with mySQL, because it is simpler to use (tables radacct, radcheck, etc... are already built) and develop. As we arrive to a stable solution it is my intention to move the authentication process to openLDAP.
I'm doing wifi prepaid w/ mysql right now too and I'm in the process of moving that to ldap. Once I get it built and documented I'll let you know how I did it.
Carlo Prestopino wrote:
In these day we are developing the prepaid solution for Wi-Fi access (scratch cards), but completely with mySQL, because it is simpler to use (tables radacct, radcheck, etc... are already built) and develop. As we arrive to a stable solution it is my intention to move the authentication process to openLDAP.
The only statement that matters is whether you are familiar with either MySQL or LDAP. I don't agree with the other assertions about performance and distributed model of storage. The MySQL database of users can be replicated so each RADIUS server query a local slave database. There is no bottleneck on a single database. (exactly like in a LDAP setup) http://dev.mysql.com/doc/refman/4.1/en/replication.html For example, the RADIUS servers on my site received yesterday 6 millions requests (auth + acct), and the servers were always under 5% of CPU usage. (Pentium 4 2.4GHz) So unless you have good reasons, I think you should keep the MySQL solution if it works for you. -- Nicolas Baradakis
The only statement that matters is whether you are familiar with either MySQL or LDAP. I don't agree with the other assertions about performance and distributed model of storage.
The MySQL database of users can be replicated so each RADIUS server query a local slave database. There is no bottleneck on a single database. (exactly like in a LDAP setup)
http://dev.mysql.com/doc/refman/4.1/en/replication.html
For example, the RADIUS servers on my site received yesterday 6 millions requests (auth + acct), and the servers were always under 5% of CPU usage. (Pentium 4 2.4GHz)
So unless you have good reasons, I think you should keep the MySQL solution if it works for you.
-- Nicolas Baradakis
I agree with that. I was previously stating what I liked about ldap. The most important thing though is being able to support it.
Thank you to all of you for comments, Dusty, could you please provide complete URL for your "doc/ldap_howto.txt", I'd a look to http://www.freeradius.org/doc/ , but didnt find it. Carlo -----Messaggio originale----- Da: freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org [mailto:freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius. org] Per conto di Dusty Doris Inviato: martedì 10 gennaio 2006 17.43 A: FreeRadius users mailing list Oggetto: Re: openLDAP vs.mySQL
The only statement that matters is whether you are familiar with
either MySQL or LDAP. I don't agree with the other assertions about
performance and distributed model of storage.
The MySQL database of users can be replicated so each RADIUS server
query a local slave database. There is no bottleneck on a single
database. (exactly like in a LDAP setup)
For example, the RADIUS servers on my site received yesterday 6 millions
requests (auth + acct), and the servers were always under 5% of CPU
usage. (Pentium 4 2.4GHz)
So unless you have good reasons, I think you should keep the MySQL
solution if it works for you.
--
Nicolas Baradakis
I agree with that. I was previously stating what I liked about ldap. The most important thing though is being able to support it. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html __________ NOD32 1.1358 (20060110) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com
Carlo Prestopino wrote:
Dusty, could you please provide complete URL for your "doc/ldap_howto.txt", I'd a look to http://www.freeradius.org/doc/ , but didnt find it.
The howto written by Dusty Doris: http://freeradius.org/radiusd/doc/ldap_howto.txt And the wiki page: http://wiki.freeradius.org/index.php/Rlm_ldap -- Nicolas Baradakis
Thank you Nicolas. Is it normal that I obtain "Forbidden Access" on the link http://freeradius.org/radiusd/doc/ ? Suppose to be general directory for all documents... Carlo -----Messaggio originale----- Da: freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius.org [mailto:freeradius-users-bounces+c.prestopino=waitalia.com@lists.freeradius. org] Per conto di Nicolas Baradakis Inviato: sabato 14 gennaio 2006 13.39 A: FreeRadius users mailing list Oggetto: Re: openLDAP vs.mySQL Carlo Prestopino wrote:
Dusty, could you please provide complete URL for your "doc/ldap_howto.txt",
I'd a look to http://www.freeradius.org/doc/ , but didn't find it.
The howto written by Dusty Doris: http://freeradius.org/radiusd/doc/ldap_howto.txt And the wiki page: http://wiki.freeradius.org/index.php/Rlm_ldap -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html __________ NOD32 1.1364 (20060113) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com
participants (5)
-
Carlo Prestopino -
Dusty Doris -
Lewis Bergman -
Mathieu Clément -
Nicolas Baradakis