It doesn't, you can use any variable available. So for example in postgrest you get all variables in a JWT exposed as GUC (Grand Unified Configuration) variables and you can use that in your RLS checks.
The most common way is to create a role though, yeah. I find that creating a role is not a downside, since it is generally just a row in a table that postgresql's permission system also uses.
How does this work in terms of Postgres connections/pools? My understanding of this is that you'd do something like `SET SESSION user_id TO 123;` which sets the variable `user_id` for that connection. Subsequent queries could use that variable to do row-based authorization but they need to use the same connection. Is this how Postgrest does it? (guaranteeing the same connection for the lifecycle of a request that is)
IIRC postgrest starts a transaction for each request, and inside of that sets the variables. I've done some testing to make sure that variables do not leak between requests.
The most common way is to create a role though, yeah. I find that creating a role is not a downside, since it is generally just a row in a table that postgresql's permission system also uses.