freeradius, mysql and encrypted columns

[Alan DeKok]

On Apr 13, 2017, at 8:39 AM, petr.linke at seznam.cz wrote:
> 
> Hi,
> I have very simple question: Is it possible to use encrypted columns in freeradius?

  No.

> I wish to hide 'sensitive' data (cleartext-password in radcheck table, secret in nas table ...) from simple raw reading block device, where the mysql tables are stored. One way is to use LUKS (but here is performance storage impact), second way is encrypting on database level.
> 
> And where is the problem:
> 
> If in freeradius is used nas table and nas query from default, all works perfectly. 
> But if I use encrypted columns - for example modified nas query like:
> 
> nas_query = "SELECT id, nasname, shortname, type, DES_DECRYPT(secret), server FROM ${nas_table}", 
> freeradius stops resolving clients from nas table.
> In column secret in nas table is des_encrypted value given by des_encrypt('some_secret',1), mysql is started with one key slot in
> key-file as a key for encryption.

  That's an issue for MySQL to resolve.

> But when I put into column secret des_encrypted value, and modify the nas query to: 
> "SELECT id, nasname, shortname, type, DES_DECRYPT(secret), server FROM ${nas_table}", 
> freeradius stops resolving correctly the clients from nas table, and in freeradius log appears:
> Ignoring request to authentication address 10.255.246.120 port 1812 from unknown client 10.255.246.120 port 56837

  The debug output should also show it adding the client.  There should be more information there.

> but the result from modified nas query with encrypted column secret is the same as query result with cleartext secret:
> 
> SELECT id, nasname, shortname, type, DES_DECRYPT(secret), server FROM nas;
> +----+-------------------+-------------+-------+---------------------+--------+
> | id | nasname           | shortname   | type  | DES_DECRYPT(secret) | server |
> +----+-------------------+-------------+-------+---------------------+--------+
> |  6 | 10.255.246.120/32 | client test | other | test                | NULL   |
> +----+-------------------+-------------+-------+---------------------+--------+
> 
> The same situation is with encrypted column Value in radcheck or radgroupcheck table. It looks that when some columns are encrypted and in the query is used DES_DECRYPT() function, freeradius has problem to fill the value from encypted columns into internal variables. For example in debug from freeradius is (encrypted column Value in radcheck table):
> [pap] Using clear text password ""
> - it looks that freeradius do not take DES_DECRYPT(Value) from radcheck table.

  DES_DECRYPT(value) is a MySQL function.  MySQL *should* return the decrypted value to FreeRADIUS.

  Alan DeKok.