db-pre-request / current_setting() variable replacement in select is safe?

[SupaFuzz]

Hello everyone, my pl/sql is rusty indeed, and I'm totally new to how it works in postgres, and I need some help.
I'm following the very helpful sql user management guide.

I've got it all working but I'd like to add a enable boolean column to the basic_auth.users table such that I can set the value false on a user record and invalidate any existing JWTs for that user.

It seems like using db-pre-request would be the way to go, and to set up a stored procedure like the one from the tutorial, but instead of having it check a string literal for equivalency to a known bad actor email address, have it select the enabled flag on the basic_auth.users table where email = current_setting('request.jwt.claims', true)::json->>'email'

so I started trying to google around to see how to do a variable replacement like that, then it occurred to me that this could potentially be unsafe? Like if someone spoofs a token with some nasty stuff in the email claim and does an sql injection or something? Am I seeing ghosts? Would that even be possible? (sorry, I am a big n00b in this arena -- I've consumed many APIs in my time, but never built one before).

I realize this is a question beyond just postgrest, but ... what would be the safe way to do this check?

surely I'm not the first to think of doing this with postgrest. Has anyone implemented something like this before and wouldn't mind sharing?

thanks everyone!
-Amy

edit: after many more hours messing around with this ...

ok, it seems like db-pre-request is just ignored entirely?
Like I gave up on checking the bool on my user record for the moment, and literally pasted the exact code from the immediate revoction tutorial, changing out the email string literal for the one I'm testing with. Just to be sure it's there, I decoded the token on jwt.io to be sure the email claim was in the token. Set up db-pre-request = "auth.check_token" in the conf file. Set log-level to "info" as well. It just keeps accepting the token and gives no indication at all that the db-pre-request function is even being called?!

surely I'm missing something here.

edit 2: and that thing I was missing was the postgresql version-dependent syntax for checking the current_setting(...) vars.
A very important point I missed from the docs:

• For PostgreSQL server version >= 14

  current_setting('request.jwt.claims', true)::json->>'email';

• For PostgreSQL server version < 14

  current_setting('request.jwt.claim.email', true);

my Fedora box installed version 12.6 from the distro ... I was using the >=14 version syntax. It is troubling though, that absolutely no error was thrown, anywhere even in the postres server log .. and postgrest allowed the transaction regardless. So I mean yeah ... watch out for errors in db-pre-request functions, I guess.

anyhow back to my original question.

I've got a version of what I want working. This is my db-pre-request function and it does appear to function as expected.

create or replace function basic_auth.check_token() returns void
  language plpgsql
  as $$
declare en bool;
begin
    select enabled from basic_auth.users where email = current_setting('request.jwt.claim.email', true) into en;
    if en = false then
      raise insufficient_privilege
      using hint = 'user disabled';
    end if;
end
$$ security definer;

I can set enabled = false on a row in basic_auth.users and existing valid keys will bounce an error on transactions, I can flip it back to true and it works again. Which is super good stuff. However here's what I'm asking is this safe? or is there a more legit way? Should I be concerned about sanitizing data found in token claims before using it in SQL statements?

[steve-chavez]

Hey Amy,

Yes, using current_setting is safe as it only returns text so if you got something like DROP BOBBY TABLES as an input it would only be a false WHERE condition.

Should I be concerned about sanitizing data found in token claims before using it in SQL statements?

Not for this case. You should only be concerned about this is if you are doing Dynamic SQL inside your function(which is a rare need). It is possible to make that safe as well though(example here).

[SupaFuzz]

thanks for the response!
Sorry, I'm quite new to this ecosystem.

so just to be clear, someone having a claim like this in the token wouldn't be an issue?

{
     "user": "select * from (drop table basic_auth.users)" 
}

Of course, I'm presuming that postgrest wouldn't even parse the token if it's not properly signed, but presuming someone got the key and spoofed an arbitrary token ... sorry I can't stop worrying ... I'm considering putting a postgrest service behind a reverse proxy on a site that will be exposed to the public internet, I get paranoid like that ... mostly because again I'm so new to this set of tools. I want to be sure I don't leave a massive security hole with this approach

[SupaFuzz]

actually I think I missed what you meant by current_setting only returning text.
you're saying its interpreted as quoted text only with escape chars disabled or what have you. Basically I'm worrying about nothing. Thanks! :-)