My goal is to have clients connect to PostgreSQL,
and call a function that return a JWT token.
The JWT is supposed to capture the user (login role),
and the current_role (which has meaning in our app),
and sign it using a secret corresponding to a mid-tier
service the client will connect to later.
perfect for my use case, but I'm struggling with something.
On the one hand, I want a INVOKER security function,
to be able to capture the login and current ROLEs.
On the other hand, I want a DEFINER security function,
to be able to access the secret to sign the JWT with.
That secret will be in a table that regular users of our DB
do NOT have access to, of course. But that the function
doing the JWT signing does need access to, of course (again).
I thought I'd have two layers of functions, one INVOKER
that captures the ROLEs, which then calls the DEFINER one,
passing the ROLEs captured, but since the INVOKER function
must also be able to call the DEFINER function, what prevents
the client from calling it directly, with different (spoofed) ROLEs?
Is there a way out of that conundrum?
I also thought about pg_stat_activity.usename with pg_backend_pid(),
but there's no current_role in there, and also, I'm not sure how that
would play with connection pooling!?!?!?
So Is there a way to somehow mix INVOKER and DEFINER
to achieve the stated goal?
Or more broadly, mix information from the session and
"private" information (JWT secret, part of the "app") in a
server-side SQL function/procedure?
Thanks, --DD