Can I be in charge of verifying JWT, no setting of PGRST_JWT_SECRET (jwt-secret)?

[dnk8n]

For example, I have the following code which works (and it uses the latest public signing keys:

CREATE OR REPLACE FUNCTION auth.decode_role (jwt text)
  RETURNS text
AS $$
  import asyncio
  from guardpost.jwts import JWTValidator
  async def main():
    validator = JWTValidator(
      authority="https://login.microsoftonline.com/{TENANT_ID}/",
      valid_issuers=["https://login.microsoftonline.com/{TENANT_ID}/v2.0"],
      valid_audiences=["{CLIENT_ID}"]
    )
    return await validator.validate_jwt(jwt)
  decoded = asyncio.run(main())
  return decoded["preferred_username"]
$$ LANGUAGE plpython3u;

Running this in psql:

> select auth.decode_role('{ID_TOKEN}') as role;

Returns:

               role               
----------------------------------
 [email protected]
(1 row)

I want to know if I can then be in charge of having appropriate functions run a variabilized form of :

SET LOCAL ROLE '[email protected]';

To create an authenticated request, I would just pass -H "Authorization: Bearer {ID_TOKEN}" in a curl request.

Is it possible to define a flow such as this without setting jwt-secret ?

P.S. Additionally in the future I might like to also include an additional header with the access token passed through to Microsoft Graph.

[wolfgangwalther]

No, such a manual verification of the JWT is not possible. You will need to provide PostgREST with the key somehow to validate the JWT itself.

I looked at guardpost, which you use in your snippet, for a moment and I realized they are downloading the required JWKS automatically: https://github.com/Neoteroi/GuardPost#functions-to-validate-jwts

That's actually quite nice. I wonder whether that could be implemented in PostgREST, too...

[dnk8n]

Yes currently I am dreading the task of key rotation and am putting it off (potentially it isn’t as big a task as I have bigged up).

but if postgREST could be in charge of syncing the latest keys (it has all the access in order to do so), or to at least allow deferring to a function that can interact with the internet and update postgrest’s configuration (without environment variables taking effect again in the case containers being recreated) that would be great.

[wolfgangwalther]

Hm. Maybe it's already possible to do this right now. You can set the JWKS via in-database-configuration. That means you can update it at will as well and then trigger a config reload for PostgREST. So a simple function to load the keys from the external source and do just that, which is then run periodically via pg_cron, could work?

[dnk8n]

I was going to look at that way of doing things, thanks for confirming the path.

Is there some overlapping period where 2 keys are valid? Else there could still be a time of invalid keys until cron is run (even if it is 2min, that is still bad enough).

I was thinking an alternative could be an error handling mechanism which updates the keys just in time in a retry loop, for the first user... thus updating the key for all (not needed if I can confirm that two keys are valid for a few hours, then an hourly cronjob is simpler to maintain)

[wolfgangwalther]

A JWKS is an array of keys, so it can contain multiple keys. PostgREST will already try all of them, if you provide multiple.

If the service that you are fetching your keys from does not provide the current and latest key automatically in a JWKS, you could still construct your own JWKS by keeping older keys around for a while. It's just a JSON array anyway, so that should be easy.

[dnk8n]

Oh yes, multiple keys do exist - https://learn.microsoft.com/en-us/azure/active-directory/develop/active-directory-signing-key-rollover

There's always more than one valid key available in the OpenID Connect discovery document and the federation metadata document. Your application should be prepared to use any and all of the keys specified in the document, since one key may be rolled soon, another may be its replacement, and so forth

So a 24 hour cronjob to fetch all the latest keys would be the correct way. Better to have this running in the database directly as it is a living environment change.

My job seems much less challenging now. Appreciate your insights @wolfgangwalther

[blockchaind]

@dnk8n, checkout https://github.com/edgeflare/pgo. It's not yet as robust and reliable (please do give your feedback to make it so) as PostgREST, but enhances postgrest in a few ways:

• fixes JWKS: load public keys from a well known endpoint #1130 (JWSError JWSInvalidSignature)
• supports basic-auth (along-side OIDC auth and anonymous/public access

    rest:
      listenAddr: ":8080"
      pg:
        connString: "host=localhost port=5432 user=postgrest password=secret dbname=testdb"
      oidc:
        issuer: https://iam.example.org
        clientID: example-client-id
        clientSecret: example-client-secret
        roleClaimKey: .policies.pgrole
      basicAuth:
        admin: adminpw
        user1: user1pw
      anonRole: anon