Using JWT to establish a trusted context for Row Level Security

Summary

Row-level security (RLS) is a great feature. It allows restricting access to rows by applying filters defined by a policy. It’s a tool useful for cases when the data set can’t be split into separate databases.Sadly, using RLS may be quite cumbersome. RLS requires some sort of “trusted context” for the RLS policies. The policies need to filter using data the user can’t change. If the filter uses some sort of “tenant ID”, and the user can change it to an arbitrary value, that would break the RLS concept.This is why solutions like using GUCs are flawed, because the access control for GUC is very limited. The traditional solution is to use roles, which derives the trust from authentication.It occurred to me it should be possible to build a trusted context on cryptography, independently of authentication. I’ll explain the basic idea, and discuss a couple interesting variations. I’ve also published an experimental extension jwt_context, implementing this using JWT.I’m interested in all kinds of feedback. Is it a good idea to use JWT this way, as a basis for RLS context? Did I miss some fundamental issue? Are there interesting improvements?Note: Whenever you see “context,” imagine a set of key/value pairs. The RLS policies can “query” the context for a key, and use the value in RLS policies to filter rows.RLS vs. rolesRLS requires a trusted context - set of values that may be referenced in policies. The values need to be set in a safe way, and the user must not be able to modify them arbitrarily. This is why most RLS examples rely on current_user. It identifies the user, and it’s authenticated, so the user can’t change the value easily.But it also means there has to be a role per application user. And if you have managed systems doing that, you probably see it may be challenging. Roles are database objects and need to be managed at that level (it’s more a task for a DBA than an app developer).Roles also complicate pooling. The current_user role for a connection is set duri...