freeradius, mysql and encrypted columns

[petr.linke at seznam.cz]

Hi,
I have very simple question: Is it possible to use encrypted columns in freeradius?

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.

example:

cleartext column secret in the nas table, nas_query is set to:
"SELECT id, nasname, shortname, type, secret, server FROM  ${nas_table}"

result executing the nas query:
SELECT id, nasname, shortname, type, secret, server FROM nas;
+----+-------------------+-------------+-------+--------+--------+
| id | nasname           | shortname   | type  | secret | server |
+----+-------------------+-------------+-------+--------+--------+
|  6 | 10.255.246.120/32 | client test | other | test   | NULL   |
+----+-------------------+-------------+-------+--------+--------+

- all works works fine - result from radtest:

radtest user password 10.255.246.120 1 test
Sending Access-Request of id 113 to 10.255.246.120 port 1812
        User-Name = "user"
        User-Password = "password"
        NAS-IP-Address = 10.255.246.120
        NAS-Port = 1
        Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 10.255.246.120 port 1812, id=113, length=37
        Tunnel-Type:0 = VLAN
        Tunnel-Medium-Type:0 = IEEE-802
        Tunnel-Private-Group-Id:0 = "123"


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

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.

Thank you for answer, Petr Linke