Then you can get as fancy as you want with RLS policies automatically attached to each table and validate the user based on a user table with capabilities. I have a feeling you could instead have the RLS rules automatically insert data instead, but I never tried it. It would have to be multiple updates though, perhaps a trigger/function.
The problem is SET applies to a session, and pgbouncer doesn't make guarantees about the session for a particular statement. Your statement might receive an existing session with another user's ID, or it might receive no ID at all.
Through AWS's RDS Proxy (beefed up pgbouncer AFAIK), the situation is slightly better, in that AWS will detect the modification to the session and 'pin' it to the current connection. Your data will be consistent, but pinned sessions can no longer be multiplexed, so you've basically removed the whole point of connection pooling to begin with.
Ultimately, its probably safer to just manually add the user into your queries. Its not that much overhead.