Advice on how to handle multiple roles in JWT

[mkleczek]

In my org we have a standard that requires applications to handle JWT that contain multiple roles (all roles that a particular user has assigned). The application is supposed to provide a user a sum of all permissions assigned to these roles.

How would you handle it in PostgREST?

[wolfgangwalther]

Do those "multiple roles" vary from request to request or would a certain user always list the same roles (until they have been assigned another role or have a role revoked from them)?

Which kind of "roles" can the user have? i.e. is this a pre-defined set? Something like "read api, write api, ..."? If yes, how many of those roles exist?

[mkleczek]

The flow is: user logs in using OAuth2 and gets a JWT. This JWT contains a claim with a list of user roles and a claim with a username. User roles are mapped to database roles (so they represent a set of privileges/grants). The roles assigned to a user are maintained in external system and do not change often but - unfortunately - we do not have a way to provision usernames and assigned roles to PostgreSQL (if we could do that I would not ask the question as this would be a classic impersonation scenario).

Obviously we can have RLS policies (or a layer of views/functions) that check JWT claim containing roles - but this is manual work that needs to be done to re-implement built-in db security.

Another idea is to have a dedicated database role per each subset of possible user roles - but that is unfeasible for larger number of user roles. And there is also a question how to map the list provided in the JWT claim to such a database role.

Another idea would be to have a reverse proxy that lazily provisions user accounts to the database. But that in turn would lessen security as this reverse proxy would need to work with high privileges so that it can create roles.

Any other ideas?

[wolfgangwalther]

Those were exactly the ideas that I had in mind, too:

Another idea is to have a dedicated database role per each subset of possible user roles - but that is unfeasible for larger number of user roles. And there is also a question how to map the list provided in the JWT claim to such a database role.

Algorithm:

• Keep all those roles in a table with a smallint pk
• map the roles in the JWT to a bitfield, where each role corresponds to the pk-th bit.
• turn the bitfield into an int and create database roles for each of those combinations, so for example claimXXX.
• do this in a pre-request function, read the JWT, switch to the resulting role here.

Another idea would be to have a reverse proxy that lazily provisions user accounts to the database. But that in turn would lessen security as this reverse proxy would need to work with high privileges so that it can create roles.

You could do that in a security definer pre-request function granted to a user with those privileges. If the user's role doesn't exist, create it and grant it the respective roles. If it exists, update the granted roles as necessary from the JWT.