SQL User management avoid hardcoding secret

[MonkOfTheFeels]

Hey there,

I am following the guidance in the docs to implement the JWT capability but running into a wall.

Say I have the below code in an SQL file, there doesnt seems to be any way that I can find on online documentation to set the reallyreallyreallyreallyverysafe to an environement variable so that the secret isnt copied to git. Currently with this approach the SQL file below would be added to a git repo and the secret would be exposed.

Issue #1062 raises this and there is some links to another repo provided which combines using \set capabilities but I cant seem to get anything in the linked file to work to actual replace reallyreallyreallyreallyverysafe with an environment variable or some safer way.

Any help or guidance would be greatly appreciated by anyone who has tackeled this before. Is it best to instead change the function to take an additional text input and actually pass the secret to the function via the API call?

CREATE TYPE basic_auth.jwt_token AS (
   token TEXT,
);

ALTER DATABASE database SET "app.jwt_secret" TO 'reallyreallyreallyreallyverysafe';


create or replace function
workflow.login(username text, pass TEXT) returns basic_auth.jwt_token as $$
declare
   _role name;
   result basic_auth.jwt_token;
begin

   -- check email and password
   select basic_auth.user_role(username, pass) into _role;
   if _role is null then

      raise invalid_password using message = 'invalid user or password';
   end if;

   SELECT 
      sign(row_to_json(r), current_setting('app.jwt_secret')) as token, 
   FROM 
      (select _role as role, username as username, extract(epoch from now())::integer + 5*60 as exp) r,
   into result;
   
   return result;
end;
$$ language plpgsql security definer;
```

[wolfgangwalther]

Is it best to instead change the function to take an additional text input and actually pass the secret to the function via the API call?

That would be pointless, because then your client would define the secret for authentication... and could authenticate as anyone?

Say I have the below code in an SQL file, there doesnt seems to be any way that I can find on online documentation to set the reallyreallyreallyreallyverysafe to an environement variable so that the secret isnt copied to git.

Are you loading this SQL code via psql? If yes, you can use its \getenv command. Only available in PG 15+.

This would leave the secret in the pg_catalog table, because it's persisted with ALTER DATABASE. Any user can read it.

If you have control of the postgres process at startup (e.g. when you run postgresql via docker or so and not via some cloud service), you could also set PGOPTIONS.

[MonkOfTheFeels]

Thank you for such a fast and detailed response!

Is it best to instead change the function to take an additional text input and actually pass the secret to the function via the API call?
That would be pointless, because then your client would define the secret for authentication... and could authenticate as anyone?

Thanks for confirming I thought that was the case but had saw some examples of pgjwt that made me think I had done something wrong.

I am loading the code via a docker container so I can go about using psql as part of the start up commands. Do you also mean to not user ALTER DATABASE as storing it that way would be accessible to all users?

Instead would it be best to something like the bellow to set the variable?

\getenv secret SECRET

create or replace function
workflow.login(username text, pass TEXT) returns basic_auth.jwt_token as $$
declare
   _role name;
   result basic_auth.jwt_token;
begin

   -- check email and password
   select basic_auth.user_role(username, pass) into _role;
   if _role is null then

      raise invalid_password using message = 'invalid user or password';
   end if;

   SELECT 
      sign(row_to_json(r), :'secret') as token 
   FROM 
      (select _role as role, username as username, extract(epoch from now())::integer + 5*60 as exp) r
   into result;
   
   return result;
end;
$$ language plpgsql security definer;

Would this be any different if hosting on Azure? I thought psql could be used on azure hosted postgresql databases based on what I have read in the documentation.

[wolfgangwalther]

\getenv secret SECRET

This will read the secret from the environment in which psql runs. This could be your own machine.

create or replace function
workflow.login(username text, pass TEXT) returns basic_auth.jwt_token as $$
[...]
      sign(row_to_json(r), :'secret') as token 
[...]
$$ language plpgsql security definer;

This will hardcode the secret in the function definition, because the :'secret' part is evaluated by psql. IMHO, this is strictly worse than the ALTER DATABASE approach - because it would also expose your secret in logs, when you have an error in the code etc.

Would this be any different if hosting on Azure? I thought psql could be used on azure hosted postgresql databases based on what I have read in the documentation.

No experience with Azure, but psql will run on your machine, so can use the above - although it's not secure.

---

An even better approach would be to use e.g. pgsodium to keep the secret encrypted only. But I don't think Azure supports that extension.

[MonkOfTheFeels]

Thanks for the detailed explanation I clearly had missunderstood how psql variables worked. pgsodium seems to be the best way but I have checked and at this stage Azure doesnt support. I will work to try to use what you orginally suggested and use the PGOPTIONS, I am running into some issues with being able to set it via Docker but will try to figure that out myself.

For those who read this later I followed the original guidance by wolfgangwalther and change did the following.

\getenv jwt_secret SECRET

ALTER DATABASE database SET "app.jwt_secret" TO :jwt_secret;

-- login should be on your exposed schema
create or replace function
workflow.login(username text, pass TEXT) returns basic_auth.jwt_token as $$
declare
   _role name;
   result basic_auth.jwt_token;
begin

   -- check email and password
   select basic_auth.user_role(username, pass) into _role;
   if _role is null then
      raise invalid_password using message = 'invalid user or password';
   end if;

   SELECT 
      sign(row_to_json(r), current_setting('app.jwt_secret')) as token,
   FROM 
      (select _role as role, username as username, extract(epoch from now())::integer + 5*60 as exp) r
   into result;
   
   return result;
end;
$$ language plpgsql security definer;