3

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

InsatiableTraveller
  • 1,545
  • 2
  • 10
  • 9

1 Answers1

0

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!

Community
  • 1
  • 1
Robins Tharakan
  • 2,209
  • 19
  • 17
  • Thanks @Robins , that helps. What you have mentioned in the end is in fact true. But what exactly do you mean by "uncontrolled environment ". How to put controls ? – InsatiableTraveller Feb 12 '16 at 05:28
  • Since you mention this `this tenant user can himself do a "set session authorization some_other_tenant"`, I am assuming that this isn't entirely a controlled environment and so one has to be maximally paranoid. – Robins Tharakan Feb 12 '16 at 08:18
  • A `Controlled Environment` is one where you can be sure that 0% of the users would do this kind of user escalation 0% of times. Since you can't be sure... then this is an uncontrolled environment (I always picture Netflix's Chaos Monkey playing havoc with my database:) (http://techblog.netflix.com/2012/07/chaos-monkey-released-into-wild.html) :) – Robins Tharakan Feb 12 '16 at 08:26