On Sat, Mar 2, 2019 at 4:09 PM Eugen Stan <eu@xxxxxxxxx> wrote:
Hi,
I would like to advocate forJSON web token support in PostgreSQL.
JWT tokens are used in a lot of web applications and I think there are
some very nice use cases for passing the token down to PostgreSQL.
pgjwt author here. While I do agree with you that a direct integration would simplify a few things, the idea doesn't really bring enough benefit to overcome some downsides.
Some of the first thing that comes to mind is pairing it with row level
security to implement authorization policies and data access directly in DB.
It's possible to do this now, tools like PostgREST and Postgraphile use a pattern where they log in as an Anonymous user but when they get a valid JWT, they SET ROLE to the correct user. It's also possible to inspect jwt claims in RLS policy checks via variables that are set on each query. It's worth noting that neither of these projects use pgjwt but rather use the libraries that come with their runtimes.
But more abstractly how would this integration work? Postgres does not speak HTTP, you'd have to extend the client protocol. That's gonna be a huge hurdle since its a large change to a lot of code outside of postgres itself.
I've did a bit of research and I found some prior work in [pgjwt] and
[postgres-jwt-prototype] .
It seems to be pretty easy to support since most of the work is done:
PostgreSQL has crypto support via pgcrypto and has native JSON handling
functions.
pgcrypto only supports secret key signing with hmac, but it doesn't support public key signing. pgjwt was always just an expedient tool, for me at least, to use as an early adopter of PostgREST. It is nice to be able to generate valid jwts from the db with a secret. But for any more serious use it's going to need not only work to pgcrypto or a gratuitous self-plug for pgsodium (https://github.com/michelp/pgsodium) to support public key signing and it's going to need some eyeballs from real security people. I wrote them and I personally wouldn't use pgjwt (other than trival key generation) or pgsodium for production use without some more buy in from serious security people. That's huge hurdle #2.
JWT is a widely used technology, especially in web applications.
This is why I think PostgreSQL should adopt a JWT extension in contrib.
I believe PostgreSQL will benefit, especially in the web apps use.
What do you think?
What would it take for a JWT extension to be added to postgresql contrib
or the official postgresql extensions ?
I like your enthusiasm for the idea, but it has some serious hurdles noted. In the mean time, if you're idea is to get working quickly with postgres and JWT, I would look at PostgREST and Postgraphile, and I hear Hasura is quite cool too although I don't have any firsthand experience with it.
-Michel
Thanks,
Eugen
[pgjwt] https://github.com/michelp/pgjwt
[postgres-jwt-prototype] https://github.com/larsar/postgres-jwt-prototype
