Roles, groups and set session authorization in postgres

Question

I am mucking around with postgres row level security for multi tenant applications. I want achieve this via a policy which separates rows based on the tenant_name which is a column in my tables. I have roles for each tenant. However, I am maintaining a connection pool by connection via a superuser. This is so that i can have only one connection pool. Once i have a query from a tenant, i want to drop privileges to that tenant first and then execute the query.

So I connect to the database as a superuser, and then i do "set session authorization tenant_role". This sets the session_user and current_user variables. However, the problem is that this tenant user can himself do a "set session authorization some_other_tenant" and then Row security doesn't matter. I am guessing that this happens because the DB login context is the superuser.

So how do i achieve this ? Once i do "set session authorization" or "set role" to some user , that user should not be able to run the same thing again.

Thanks

Answer 1

You may want to read this, for your answer.

Essentially, create a no-privilege user and login to the pool via that (instead of connection pooling as a supervisor role). Once logged in, then escalate the role to the actually connected user. The URL above tells you how to do that.

What I am unable to understand is that if this is an uncontrolled environment, then even with this solution, a user can escalate to someone else's role and still play havoc. So although even if this answers the question, it may still not be what you want to eventually do!