sqlippool additional information

classic Classic list List threaded Threaded
5 messages Options
| Threaded
Open this post in threaded view
|

sqlippool additional information

Nathan Ward
Hi,

I’m doing some testing of sqlippool in a fairly large scale environment, where we have lots of different IPv4 prefixes to select addresses from.

We have many prefixes in each pool, and direct customers to pools based on some details.

Depending on the prefix the customer gets assigned an address from we want to return different information in RADIUS - such as setting attributes to pass DHCP options like prefix length, router, DNS server, etc.
In my testing, I’ve been setting these manually, but in production these can change, and I don’t want to be reconfiguring and reloading the RADIUS server each time we move things about.
Customers could get addresses from any prefix, so we can’t provide these values from our user database unfortunately.

sqlippool returns only a single value. As does sql xlat.

Is there a good way to pull these from a DB? We’ve got both LDAP and SQL at our disposal.

We’re running Postgres, so can easily do a longest prefix match to find the prefix that an address is in - but this means one query per attribute value we want, which I figure means about 4 or 5 extra queries per auth request so isn’t really ideal.
Before I go down that road, I figured I’d reach out and see if anyone else has an elegant solution I haven’t considered, as it seems like it’d be a fairly common problem.

--
Nathan Ward


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlippool additional information

Alan DeKok-2
On Dec 5, 2018, at 8:24 PM, Nathan Ward <[hidden email]> wrote:
>
> I’m doing some testing of sqlippool in a fairly large scale environment, where we have lots of different IPv4 prefixes to select addresses from.
>
> We have many prefixes in each pool, and direct customers to pools based on some details.
>
> Depending on the prefix the customer gets assigned an address from we want to return different information in RADIUS - such as setting attributes to pass DHCP options like prefix length, router, DNS server, etc.

  That's a common use-case.  It's not really well supported by the existing sqlippool module.

> In my testing, I’ve been setting these manually, but in production these can change, and I don’t want to be reconfiguring and reloading the RADIUS server each time we move things about.
> Customers could get addresses from any prefix, so we can’t provide these values from our user database unfortunately.
>
> sqlippool returns only a single value. As does sql xlat.

  Sure.

> Is there a good way to pull these from a DB? We’ve got both LDAP and SQL at our disposal.

   I've seen good system using redis for IP pools, and LDAP for everything else.  The idea is that you treat the IP pools as "grab bags" which just return some IP.  Once you have an IP, you look up the prefix in LDAP to find out DNS, router, DHCP options, etc.

  I'm not sure that this is what you want.

> We’re running Postgres, so can easily do a longest prefix match to find the prefix that an address is in - but this means one query per attribute value we want, which I figure means about 4 or 5 extra queries per auth request so isn’t really ideal.

  That may be a good time for a stored procedure.

  But why are you assigning multiple IPs?

> Before I go down that road, I figured I’d reach out and see if anyone else has an elegant solution I haven’t considered, as it seems like it’d be a fairly common problem.

  Less common in the past, more common now.  As people try to abstract their IP configuration, it becomes more important to create the right DB schema.   And the sqlippool schema is what, 10 years old?

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlippool additional information

Nathan Ward

> On 6/12/2018, at 3:38 PM, Alan DeKok <[hidden email]> wrote:
>
> On Dec 5, 2018, at 8:24 PM, Nathan Ward <[hidden email]> wrote:
>>
>> I’m doing some testing of sqlippool in a fairly large scale environment, where we have lots of different IPv4 prefixes to select addresses from.
>>
>> We have many prefixes in each pool, and direct customers to pools based on some details.
>>
>> Depending on the prefix the customer gets assigned an address from we want to return different information in RADIUS - such as setting attributes to pass DHCP options like prefix length, router, DNS server, etc.
>
>  That's a common use-case.  It's not really well supported by the existing sqlippool module.
>
>> In my testing, I’ve been setting these manually, but in production these can change, and I don’t want to be reconfiguring and reloading the RADIUS server each time we move things about.
>> Customers could get addresses from any prefix, so we can’t provide these values from our user database unfortunately.
>>
>> sqlippool returns only a single value. As does sql xlat.
>
>  Sure.
>
>> Is there a good way to pull these from a DB? We’ve got both LDAP and SQL at our disposal.
>
>   I've seen good system using redis for IP pools, and LDAP for everything else.  The idea is that you treat the IP pools as "grab bags" which just return some IP.  Once you have an IP, you look up the prefix in LDAP to find out DNS, router, DHCP options, etc.
>
>  I'm not sure that this is what you want.

Yeah. The LDAP thing would be good - we’d need a way to do longest prefix match though. Both redis and SQL are just going to tell us an IP, not a prefix (or, some other label like a prefix name or group or something). I don’t think LDAP can do that itself.
We could create an LDAP entry per IP, but, that’s a drag to maintain..

How have the systems you’ve seen handle this?

I’m staying away from redis right now - it doesn’t give me much advantage over SQL, I don’t think, and is a bit of a learning curve for the rest of the team. Both just give me an IP, and work like a grab bag, yeah?

>> We’re running Postgres, so can easily do a longest prefix match to find the prefix that an address is in - but this means one query per attribute value we want, which I figure means about 4 or 5 extra queries per auth request so isn’t really ideal.
>
>  That may be a good time for a stored procedure.
>
>  But why are you assigning multiple IPs?

We’re not (necessarily*), sorry if that was implied in some way. Based on IP from sqlippool we need to get:
- Router
- Subnet Mask
- Primary DNS
- Secondary DNS
- Domain name (ick. for now.)

My understanding is that the sql xlat can get one of those at a time, so, we’d do something like:
update reply {
  Router-Attribute-Whatever = “%{sql:SELECT router FROM prefixes WHERE prefix >>= '%{reply:Framed-IP-Address}';"
  Framed-IP-Netmask = “%{sql:SELECT netmask FROM prefixes WHERE prefix >>= '%{reply:Framed-IP-Address}’;”
  .. etc ..
}

We actually don’t assign them to those reply attributes directly - we have some internal control attributes, and then later we turn the control attributes in to vendor/BNG specific attributes - but that’s just an example.

I can optimise the above in to the query cache, aside for one query, by doing (pseudocode):
- control:Internal-Attr-Prefix = SELECT prefix FROM prefixes WHERE prefix >>= ‘%{reply:Framed-IP-Address}’;
- reply:Attribute-1 = SELECT attribute1 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;
- reply:Attribute-2 = SELECT attribute2 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;
- reply:Attribute-3 = SELECT attribute2 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;

But, it’s still not ideal - I’d rather avoid the round trip to the DB over and over, and just get all the attributes in one hit. I suppose I could just stick an encoded DHCP options string directly in to the DB, pull it out in one query then unpack it with the dhcp_options xlat.. but it’s pretty ugly.

I could also do the above first query, then call LDAP for the attributes based on the prefix - but, data in 2 DBs seems like a design bug.

* I say necessarily - we assign IPv4, IPv6-NA, and IPv6-PD addresses from different sqlippool instances. Juniper BNGs mean we do that in two different requests (IPv4 in one, the IPv6 ones in another), and Cisco BNGs want it all at once.

>> Before I go down that road, I figured I’d reach out and see if anyone else has an elegant solution I haven’t considered, as it seems like it’d be a fairly common problem.
>
>  Less common in the past, more common now.  As people try to abstract their IP configuration, it becomes more important to create the right DB schema.   And the sqlippool schema is what, 10 years old?

Yep. I’ve done quite a lot of experimentation with it in the last few weeks, too, and got a pretty significant performance improvement over the stock queries, in my environment. RAND() really slows down large pools, and some modern SELECT parameters can really help concurrency (SELECT .. FOR UPDATE SKIP LOCKED). MariaDB doesn’t support SKIP LOCKED, recent MySQL does, Postgres does. Without it, FreeRADIUS effectively locks all the rows in the pool, as the radippool_poolname_expire index record is locked, even with innodb, even with postgres - add the RAND() sort in there and your request latency goes through the roof for high request rates and pools with lots of unused addresses (think /16 RFC6498 pools, for example).

I’ll write some of this up when I’ve got this production proven, and maybe submit a PR or two.

--
Nathan Ward


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlippool additional information

Alan DeKok-2
On Dec 5, 2018, at 10:35 PM, Nathan Ward <[hidden email]> wrote:
> Yeah. The LDAP thing would be good - we’d need a way to do longest prefix match though. Both redis and SQL are just going to tell us an IP, not a prefix (or, some other label like a prefix name or group or something). I don’t think LDAP can do that itself.
> We could create an LDAP entry per IP, but, that’s a drag to maintain..

  You don't want to do that.

> How have the systems you’ve seen handle this?

  It should just be putting an IP prefix into LDAP, and then selecting a subtree based on that.

> I’m staying away from redis right now - it doesn’t give me much advantage over SQL, I don’t think, and is a bit of a learning curve for the rest of the team. Both just give me an IP, and work like a grab bag, yeah?

  Yes.  The main difference is that Redis is faster.  Much, much, faster.

> We’re not (necessarily*), sorry if that was implied in some way. Based on IP from sqlippool we need to get:
> - Router
> - Subnet Mask
> - Primary DNS
> - Secondary DNS
> - Domain name (ick. for now.)

  Yeah... those should all be keyed off of the prefix.  It should be:

- get IP
- look up IP to get prefix
 - prefix table contains routing, etc. information

> My understanding is that the sql xlat can get one of those at a time, so, we’d do something like:
> update reply {
>  Router-Attribute-Whatever = “%{sql:SELECT router FROM prefixes WHERE prefix >>= '%{reply:Framed-IP-Address}';"
>  Framed-IP-Netmask = “%{sql:SELECT netmask FROM prefixes WHERE prefix >>= '%{reply:Framed-IP-Address}’;”
>  .. etc ..
> }

  You could get them all as one string, and then parse them in FreeRADIUS.  But that's ugly.

> We actually don’t assign them to those reply attributes directly - we have some internal control attributes, and then later we turn the control attributes in to vendor/BNG specific attributes - but that’s just an example.
>
> I can optimise the above in to the query cache, aside for one query, by doing (pseudocode):
> - control:Internal-Attr-Prefix = SELECT prefix FROM prefixes WHERE prefix >>= ‘%{reply:Framed-IP-Address}’;
> - reply:Attribute-1 = SELECT attribute1 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;
> - reply:Attribute-2 = SELECT attribute2 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;
> - reply:Attribute-3 = SELECT attribute2 FROM prefixes WHERE prefix = ‘%{control:Internal-Attr-Prefix}’;
>
> But, it’s still not ideal - I’d rather avoid the round trip to the DB over and over, and just get all the attributes in one hit. I suppose I could just stick an encoded DHCP options string directly in to the DB, pull it out in one query then unpack it with the dhcp_options xlat.. but it’s pretty ugly.

  Yeah.  That's why we added "map" in v4:

        map sql select {
                radius-attribute = sql-column
                ...
        }

  Which does multiple things in one go.

> I could also do the above first query, then call LDAP for the attributes based on the prefix - but, data in 2 DBs seems like a design bug.

  Yeah.

> Yep. I’ve done quite a lot of experimentation with it in the last few weeks, too, and got a pretty significant performance improvement over the stock queries, in my environment. RAND() really slows down large pools, and some modern SELECT parameters can really help concurrency (SELECT .. FOR UPDATE SKIP LOCKED). MariaDB doesn’t support SKIP LOCKED, recent MySQL does, Postgres does. Without it, FreeRADIUS effectively locks all the rows in the pool, as the radippool_poolname_expire index record is locked, even with innodb, even with postgres - add the RAND() sort in there and your request latency goes through the roof for high request rates and pools with lots of unused addresses (think /16 RFC6498 pools, for example).

  Sounds good.

> I’ll write some of this up when I’ve got this production proven, and maybe submit a PR or two.

  Thanks.  Performance improvements are always nice. :)

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: sqlippool additional information

Matthew Newton-3
On Thu, 2018-12-06 at 09:00 -0500, Alan DeKok wrote:
> On Dec 5, 2018, at 10:35 PM, Nathan Ward <[hidden email]>
> wrote:
> > I can optimise the above in to the query cache, aside for one
> > query, by doing (pseudocode):
> > - control:Internal-Attr-Prefix = SELECT prefix FROM prefixes WHERE
> > prefix >>= ‘%{reply:Framed-IP-Address}’;
> > - reply:Attribute-1 = SELECT attribute1 FROM prefixes WHERE prefix
> > = ‘%{control:Internal-Attr-Prefix}’;
...

> >
> > But, it’s still not ideal - I’d rather avoid the round trip to the
> > DB over and over, and just get all the attributes in one hit.
> >
>   Yeah.  That's why we added "map" in v4:
>
> map sql select {
> radius-attribute = sql-column
> ...
> }
>
>   Which does multiple things in one go.

Can do that with rlm_ldap in v3 - use the ldap config update{} section
to map radius attrs to LDAP attrs.

Redis (or maybe SQL) for IPs, LDAP for network options is probably a
reasonable place.

Or Redis for all of it, if you want to go fast.

--
Matthew

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html