How to use the environment variables inside tracked custom functions

[SameerChorge94]

Hii

for encryption decryption with hasura i created the custom function in hasura.
inside which i used the encryption key for decrypting the data using postgres custom functions,

Here's the discord message link for explaining the apporach i used
https://discord.com/channels/407792526867693568/428469959530643466/904962954800279562

with slight changes in discord message example, i referred the environment variable name, but the tracked function not able to get the value from environment variable

CREATE OR REPLACE FUNCTION decryptedTestsWithEnv(hasura_session json)
			RETURNS SETOF test AS $$
				   SELECT
				ROW( q.name, q.id ::INTEGER ) :: test
					FROM ( SELECT id, PGP_SYM_DECRYPT(name :: bytea, hasura_session ->> 'hasura_encrypt_decrypt_secret_key') AS name FROM test ) AS q
		$$ LANGUAGE sql STABLE;
	

Can anyone suggest how to properly refer the env values inside custom function.

Note: As per the docs i added env variable key in all small letters hasura_encrypt_decrypt_secret_key and in env key is actually in all capital letters like HASURA_ENCRYPT_DECRPT_SECRET_KEY does this effect on referring the env values

[SameerChorge94]

Hii
Any update on this question?
can anyone suggest the proper workaround or solution on this

I tried the postgres set config and current settings but it does not work as expected

SELECT set_config('app.HASURA_ENCRYPT_DECRPT_SECRET_KEY', '$UPER_$ECRET_KEY', true)
SELECT current_setting('app.HASURA_ENCRYPT_DECRPT_SECRET_KEY', true) AS ekey

i.e current_settings response only getting when running it along with set_config query

[SameerChorge94]

I solved my own problem by referring the doc note section
https://hasura.io/docs/1.0/graphql/core/deployment/graphql-engine-flags/reference.html#server-config
as per one reference i found
#3737 (comment)

we can send the custom postgres configs from postgres options
Steps followed:

1. First finalize the key to add which can be used in custom functions
   e.g.

   custom.HASURA_ENCRYPT_DECRPT_SECRET_KEY=$UPER_$ECRET_KEY 
   

2. Add it as the option with -c like below

   ?options=-c custom.HASURA_ENCRYPT_DECRPT_SECRET_KEY=$UPER_$ECRET_KEY
   

3. Then Encode only that 2nd step part of

You can use use this URL for url encode decode

• https://www.url-encode-decode.com/
  After URL encode It should look like below

   ?options=-c%20custom.HASURA_ENCRYPT_DECRPT_SECRET_KEY%3D%24UPER_%24ECRET_KEY
  

4. Then concat it with the postgres database connection URL which is passed to the hasura like below

   postgresql://username:password@host:port/dbname?options=-c%20custom.HASURA_ENCRYPT_DECRPT_SECRET_KEY%3D%24UPER_%24ECRET_KEY
   

   and deploy the docker
5. To check if the passed key is accessible from hasura execute the below command in sql section of hasura
   Syntax:

   SELECT current_setting('<KEY_PASSED_ALONG_WITH_CONNECTION_URL>')
   

Example:
SELECT current_setting('custom.HASURA_ENCRYPT_DECRPT_SECRET_KEY')