sqlippool additional information

[Alan DeKok]

On Dec 5, 2018, at 8:24 PM, Nathan Ward <lists+freeradius at daork.net> 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.