4 ms·

Wow. This is super exciting. What does this mean for multi tenant applications? Does this mean we can push our multi-tenancy logic down to the DB (if not logic,

Wow. This is super exciting. What does this mean for multi tenant applications? Does this mean we can push our multi-tenancy logic down to the DB (if not logic, at least the last line of "data defence"). Can we create one DB user for each app-level account, ensure that all tables have the account identifier, and then enforce (at a DB level), that one account should be unable to access another account's data? What does this mean for DB pooling? Is it possible to maintain a set of open DB connections where the account credentials can be applied, query executed, and the account credentials removed, with the connection going back into the pool?

saurabhnanda 12y ago
Also, what kind of hit does this have on performance?
- Alex3917 12y ago
  Based purely on the syntax they are showing, it seems like in the worst case scenario it should just be the equivalent of having a couple extra where clauses for each potentially matched row, so the impact should be nominal. The real question is whether the optimizer can actually make the queries faster than they would have been before if there are a huge number of rows, but only a small number that the user has permission to access. E.g. can it create an indexed column on the permissions.
masklinn 12y ago
> Is it possible to maintain a set of open DB connections where the account credentials can be applied, query executed, and the account credentials removed, with the connection going back into the pool? SET ROLE/RESET ROLE can be used for that, however for role A to be able to switch to role B, you need to "GRANT B TO A" which will lead to a combinatory explosion. It'd probably be better to have a user with all roles granted as NOINHERIT (so the only thing it can do is SET ROLE) and all connections defaulting to that, then when you get a connection from the pool you "SET ROLE current_user" and you "RESET ROLE" the connection before storing it back. I have not tested it.
- ibotty 12y ago
  i do just that and it works like a charm.

saurabhnanda 12y ago

Also, what kind of hit does this have on performance?

Alex3917 12y ago
Based purely on the syntax they are showing, it seems like in the worst case scenario it should just be the equivalent of having a couple extra where clauses for each potentially matched row, so the impact should be nominal. The real question is whether the optimizer can actually make the queries faster than they would have been before if there are a huge number of rows, but only a small number that the user has permission to access. E.g. can it create an indexed column on the permissions.

masklinn 12y ago

> Is it possible to maintain a set of open DB connections where the account credentials can be applied, query executed, and the account credentials removed, with the connection going back into the pool? SET ROLE/RESET ROLE can be used for that, however for role A to be able to switch to role B, you need to "GRANT B TO A" which will lead to a combinatory explosion. It'd probably be better to have a user with all roles granted as NOINHERIT (so the only thing it can do is SET ROLE) and all connections defaulting to that, then when you get a connection from the pool you "SET ROLE current_user" and you "RESET ROLE" the connection before storing it back. I have not tested it.

ibotty 12y ago
i do just that and it works like a charm.