I've never used it, but something like "SET SESSION AUTHORIZATION" looks like it would do what you want, mostly:
You'd set up a "superuser" in config/database.yml and then change to the lower-privileged specific user per-request.
Some potential problems:
* the lower-privileged users *must* not have sufficient permissions to call "SET SESSION AUTHORIZATION" themselves or the security is an illusion
* you'll want to make 100% certain that connections get a "RESET SESSION AUTHORIZATION", or a selected user's authorization will leak into the next request (and fail, see the previous point)
* you'll need to somehow sanitize the incoming SQL to remove queries like "RESET SESSION AUTHORIZATION; DROP TABLE all_the_things" or the security is an illusion
I noticed you mentioned "their own tables" above; if you're already committed to solutions where adding users is complex, you might want to think about separating things further. You could use a tool like pglogical:
To replicate only the large table to per-user Postgres DBs. Definitely NOT an appropriate solution for multi-tenancy with lots of users, but neither is table-per-user.
--Matt Jones