Dear Heikki Based on the postgres encrypt function I was able to implement encrypt and decrypt routines. Well, actually it is a bit more advanced, since we have an Oracle database as single source of truth and copy the relevant user data to a postgres database. So the setup looks like this:
IAM System <-> Oracle -> PostgreSQL <-> RADIATOR
So I had to implement 1) encryption routine on Oracle and 2) decryption routine
on PostgreSQL. I found something on StackOverflow[1] and adapted it to this.
Unfortunately it is only encrypted in aes128, it would be better in aes25.
encryption in Oracle:
---
create FUNCTION encrypt_totp_secret(shared_secret IN VARCHAR2, crypt_key in
VARCHAR2)
RETURN VARCHAR2
IS
v_encrypted RAW(2000);
v_encryption PLS_INTEGER := 6+256+4096; /*DBMS_CRYPTO.ENCRYPT_AES128 +
DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5*/
BEGIN
v_encrypted := DBMS_CRYPTO.encrypt (
UTL_I18N.STRING_TO_RAW (shared_secret, 'AL32UTF8'),
v_encryption,
UTL_RAW.cast_to_raw (RPAD (crypt_key, (128/8))));
RETURN CONCAT ('\x', RAWTOHEX (v_encrypted ));
END;
/
---
decryption in PostgreSQL:
---
create function decrypt_totp_secret(secret character varying, key character
varying) returns character varying
language plpgsql
as
$$
DECLARE
v_decoded_bytes text;
BEGIN
RETURN convert_from(decrypt(secret::bytea, rpad(key, 128/8)::bytea,
'aes'),'SQL_ASCII');
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Error decrypting secret % with key %: %', secret, key, SQLERRM;
END;
$$;
---
In RADIATOR I load the encryption key from a local file into the global
variable TotpKey und use it in my SELECT statement inside the <AuthBy SQLTOTP>
like this:
---
AuthSelect SELECT
base32_decode_to_hex(decrypt_totp_secret(secret_encrypted, ?::varchar)) as
secret, active, pin, digits, bad_logins, accessed, last_timestep, algorithm,
timestep, timestep_origin from subscribers WHERE username=?
AuthSelectParam %{GlobalVar:TotpKey}
AuthSelectParam %{X-PRIVATE-USER}
---
So thanks Heikki for your support.
If anyone is interested in the source code of the base32_decode_to_hex()
routine, which I need because RADIATOR cannot read the BASE32 Shared Secret
created by our IAM directly, please let me know and I will put it onto the
list, too.
Best regards,
Tobias
[1]
https://stackoverflow.com/questions/53495073/encryption-and-decryption-from-oracle-to-postgresql
-------------------------------------------------------
ETH Zürich
Tobias Schnurrenberger
ITS Network Applications
OCT G 19
Binzmühlestrasse 130
8092 Zürich
Telefon +41 44 632 45 00
[email protected]
Montag, Dienstag, Mittwoch & Freitag
Monday, Tuesday, Wednesday & Friday
-------------------------------------------------------
>
> Date: Mon, 25 Sep 2023 19:11:31 +0300
> From: Heikki Vatiainen <[email protected]>
> To: [email protected]
> Subject: Re: [RADIATOR] AuthBy SQLTOTP with encrypted secrets
> (RcryptKey)
> Message-ID: <[email protected]>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> 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
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> radiator mailing list
> [email protected]
> https://lists.open.com.au/mailman/listinfo/radiator
>
> ------------------------------
>
> End of radiator Digest, Vol 171, Issue 11
> *****************************************
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ radiator mailing list [email protected] https://lists.open.com.au/mailman/listinfo/radiator
