[RADIATOR] AuthBy SQLTOTP with encrypted secrets (RcryptKey)

Fri Oct 6 13:36:48 UTC 2023

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
tobias.schnurrenberger at id.ethz.ch

Montag, Dienstag, Mittwoch & Freitag
Monday, Tuesday, Wednesday  & Friday
-------------------------------------------------------

> 
> Date: Mon, 25 Sep 2023 19:11:31 +0300
> From: Heikki Vatiainen <hvn at open.com.au>
> To: radiator at lists.open.com.au
> Subject: Re: [RADIATOR] AuthBy SQLTOTP with encrypted secrets
> (RcryptKey)
> Message-ID: <6e1b01fc-8e8d-4cce-9a1f-33b66aeb592f at open.com.au>
> 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
> radiator at lists.open.com.au
> https://lists.open.com.au/mailman/listinfo/radiator
> 
> ------------------------------
> 
> End of radiator Digest, Vol 171, Issue 11
> *****************************************

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4222 bytes
Desc: not available
URL: <https://lists.open.com.au/pipermail/radiator/attachments/20231006/9ed37409/attachment.p7s>