michelp
commented
2 years ago Looking up secrets in an unlogged table is a partial approach. You can also SET LOCAL log_statements = 'off' to turn off logging before accessing the secret (this is in the README).
Unfortunately pgcrypto works with raw byte keys, it is not secure by nature. There really isn't much pgjwt can do about that.
I'm curious if you have considered techniques which could hide the secret so that it doesn't leak into queries using the pgjwt functions that use it as a parameter. Perhaps if this extension had a pgjwt.conf file, we might be able to set a secretfile setting that only postgres user should be able to access. The secret file might have key=value entries per line which allow us to name our secrets (if we wish to use multiple).
Optionally, the secret.file can have a single-line entry to represent a global secret. Once specified in pgjwt.conf, and the corresponding secret.file found as per the path provided in pgjwt.con, at startup when the UNLOGGED table has not yet been found to not exist, the extension could load the named secret(s) into an UNLOGGED table owned by the SUPERUSER who created the extension. In this way, the secret.file and it's contents loaded into the UNLOGGED table should(?) only be accessible by the postgres and SUPERUSER(s) respectively. The requires a table-existence check at every call.
The pgjwt functions requiring the secret parameter ( as SECURITY DEFINER ), would be able to lookup the UNLOGGED secrets table, matching either by name (or no name as null). If the name is not found in lookup then the function can assume the string text provided by the parameter is the actual secret. If it finds a matching name, then it can use the corresponding value in the UNLOGGED table. If NULL is passed as the secret parameter, then the matching secret value with NULL key is used. If no NULL key is found in the UNLOGGED table, then we raise exception because i don't think you can encrypt a token with some string value as the secret.
I'm curious about your thoughts on this suggestion as Im keen to implement pgjwt in my database system to offer end-to-end authentication/authorization using JWT (it's a beautiful thing). Thanks in advance :)