[RADIATOR] AuthBy SQLTOTP with encrypted secrets (RcryptKey)

[Heikki Vatiainen]

On 22.9.2023 11.50, Schnurrenberger Tobias (ID) via radiator wrote:

> Thanks for your answer and confirming it cannot be done inside Radiator.
> 
> I will look into proprietary DB functions, first of all PostgreSQLs pgcrypto package with functions like pgp_sym_decrypt(). Actually I prefer the decryption key to be stored on the Radiator machine rather than inside the database. If the decryption key would be stored at the same place like the encrypted secrets it would not be a security advantage.

If it's acceptable that the encryption key is visible in Radiator's 
configuration, then based on a quick look at PostgreSQL's docs, it 
appears you can embed the decryption/encryption key in the SQL query 
when using 'Raw Encryption Functions'.

It likely sends the key to the DB in order to do the decryption, as 
opposed to the local driver doing the decryption, but it seems this 
gives a possibility of not permanently storing the key within the DB.

Here's an example of what I tested. I strongly suggest a closer look at 
the parameters and PostgreSQL's documentation to see if this a 
reasonable and usable idea. For example, equal passwords get the same 
encrypted value with encrypt() and decrypt() functions.


radius=# insert into subscribers_encrypted (username, password) values 
('jdoe', encrypt('somepw', 'foo', 'aes'));
INSERT 0 1

radius=# insert into subscribers_encrypted (username, password) values 
('jdoe2', encrypt('somepw', 'foo', 'aes'));
INSERT 0 1

radius=# select username,password from subscribers_encrypted;
  username |              password
----------+------------------------------------
  mikem    | \xd7f9631505f0f30451ac9b64c9f11529
  jdoe     | \x700a74263027f8bfc9b0bf10a2b5ffdd
  jdoe2    | \x700a74263027f8bfc9b0bf10a2b5ffdd
(3 rows)

radius=# select username,convert_from(decrypt(password::bytea, 'foo', 
'aes'), 'SQL_ASCII') as password from subscribers_encrypted;
  username | password
----------+----------
  mikem    | fred
  jdoe     | somepw
  jdoe2    | somepw
(3 rows)


Please let us know if you find the above useful.

Thanks,
Heikki

-- 
Heikki Vatiainen
OSC, makers of Radiator
Visit radiatorsoftware.com for Radiator AAA server software