freeradius, mysql and encrypted columns
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
On Apr 13, 2017, at 8:39 AM, petr.linke@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.
On Thu, 13 Apr 2017, Alan DeKok wrote:
On Apr 13, 2017, at 8:39 AM, petr.linke@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
On Apr 13, 2017, at 12:52 PM, <petr.linke@seznam.cz> <petr.linke@seznam.cz> wrote:
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).
Hmm... probably because the SQL query is returning the encrypted value, not the decrypted one. In the end, I'd put it down to MySQL weirdness. I'm not sure there's anything that can be done to FreeRADIUS to fix it. Alan DeKok.
participants (2)
-
Alan DeKok -
petr.linke＠seznam.cz