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
_______________________________________________
radiator mailing list
[email protected]
https://lists.open.com.au/mailman/listinfo/radiator
