freeradius, mysql and encrypted columns

[petr.linke at seznam.cz]

On Thu, 13 Apr 2017, Alan DeKok wrote:   

> 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.

Ok, that's an answer :).

> > 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.$
> > 
> > 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.

Maybe I gave bad explanation, the main is, than in the column secret of nas table is encrypted value, and to receive the decrypted value is used MySQL function DES_DECRYPT(). And it works fine - in described case the shared NAS secret is string 'test', and DES_DECRYPT(secret) return 'test' - as was described in my previous mail.

> > 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.
   
Freeradius is started in debug mode (freeradius -X), but on stdout appears only:
Ignoring request to authentication address 10.255.246.120 port 1812 from unknown client 10.255.246.120 port 54318
Ready to process requests.

> > 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$
> > [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.

Of course, you can see the result of query, and DESC_DECRYPT(secret) return correct value (in described case from previous mail return string 'test'). When in the column secret of nas table is plaintext, all is OK. But when in the column secret of nas table is value given via MySQL function DES_ENCRYPT(1,'some_NAS_secret'), and the DES_DECRYPT(secret) returns correct value ('some_NAS_secret'), why freeradius do not accept the correct value given by DES_DECRYPT(secret).

You can test it very simple:
 
create file stored in <path_to_mysql_secret> put 1 row:
1 some_mysql_secret

start mysql mith parameter --des-key-file=<path_to_mysql_secret>

modify nas table:
alter table nas modify column secret varbinary(253) not null  

set freeradius to mysql support, and set the nas_query to "SELECT id, nasname, shortname, type, DES_DECRYPT(secret), server FROM ${nas_table}"

fill the nas table with some reasonable values, the column 'secret' from nas table must be filled via mysql function DES_ENCRYPT(1,'some_NAS_secret');

and now when freeradius is started in debug mode (freeradius -X), no more debug on stdout, only info about unknown client.

Thank you for fast response, Petr Linke