Read jwt-secret from the database

[carragom]

Environment

• PostgreSQL version: 12.1
• PostgREST version: 6.0.2
• Operating system: MacOS Mojave

Description of issue

Hi, not sure if this is a question or a feature request but here it goes. I'm using SQL User Management as described in the docs and everything is working as expected.

Whenever I deploy my database schema using migrations a random JWT secret gets created with a statement like this:

select set_config('app.jwt.secret', md5(random()::text), true);

Since the app is in development the schema gets re-deployed quite often and a new random secret gets installed on the database. Every time this happens I have to edit my postgrest.conf and set the jwt-secret parameter to the result of the following query:

select current_setting('app.jwt.secret');

If this is not done, the authentication does not work. This is expected of course since the signer (a database function) uses a different secret that PostgREST which is the one that verifies the JWT.

I was wondering if there was a way to tell PostgREST to read the jwt-secret from the database instead of having it hard coded in the config. Or maybe there is some other clever (hopefully simple) way to keep those two values in sync.

Thanks for your time and this great tool.

[steve-chavez]

@carragom Hey there.

From your example I'm not sure how your app.jwt.secret GUC is being persisted since GUCs have a lifetime scoped to transaction or session(it'll be empty if postgrest tries to read it). That's unless you set the GUC to the database like: alter database postgrest_test set app.jwt.secret = 'mysecret'; then the GUC will be attached to the db lifetime.

For the latter case, since postgrest supports reading from a file in the jwt-secret you could do something like:

## Having the postgrest.conf like
jwt-secret = @./mysecret

## Then on deployment
psql postgrest_test -A -t -X -c "select current_setting('app.jwt.secret');" > mysecret

Then you'd have to restart postgrest since there's no unix signal yet for reloading the jwt-secret(#1119).

[carragom]

@steve-chavez that makes a lot of sense. Will try it that way.

[steve-chavez]

That being said, we could add a way to get the jwt-secret directly from the db. There are deployments that have the jwt-secret in a table, like in PostgREST/postgrest-docs#28.

Maybe we can have another config param for this, that could get the secret from a table or view(this one could obtain the secret from a GUC).

[carragom]

That would be awesome

[drorm]

Have the same need. The secret is generated when deploying the app and already needs to be protected in the db.

• Having it in a file, on a different server adds a vulnerability.
• Easy to regenerate it in the db and have everything work correctly without needing to make changes to the postgrest server.

[steve-chavez]

@drorm Good points. I'll reopen for further discussion.

[steve-chavez]

Even if pgrst reads the secret from the db, the secret won't be automatically reloadable because we will need to cache it. So for this to work we'd need to also clear #1119.

[dwagin]

Have the same need. The secret is generated when deploying the app and already needs to be protected in the db.

* Having it in a file, on a different server adds a vulnerability.

* Easy to regenerate it in the db and have everything work correctly without needing to make changes to the postgrest server.

Use asymmetric crypto ed25519 etc, with asymmetric keys postgrest need only public key.

[wolfgangwalther]

#1119 is cleared, but it seems unlikely that a special solution is implemented for just the jwt-secret. #1562 extends this to other config options as well, so closing in favor of that.