[Winsaucerer]

I'm doing something like this. It's an experiment at the moment, so I haven't used it in anger yet. Rather than having a role for each application user, I have my own application's notion of a role/account/user, with its own table in my database. For each transaction:

  SET LOCAL ROLE webuser (used by all transactions that come from the web application)
  SET LOCAL "request.web.sub" = '<internal application's primary key for this specific user'

Then I can in queries check for the current role (where by 'role' I mean my application's user/account/role set via "request.web.sub", not a postgres role) via:

  create or replace function auth.fn_requesting_role()
    RETURNS uuid LANGUAGE sql AS
  $func$
    with crole as (
      select coalesce(
        nullif(current_setting('request.web.sub', true), ''),
        nullif(current_setting('request.jwt.sub', true), '')
      )::uuid as role_id
    )
    select crole.role_id::uuid from crole
    join auth.role on role.role_id = crole.role_id::uuid;
  $func$;

You can then find out the current requesting user/account/role ID in RLS policies and other functions, and apply whatever permissions you like there.

The reason I have 'request.jwt.sub' is just for future if I want to allow requests to come from PostgREST as well and use the same authorisation checks.