Re: Multi-tenancy with RLS

On 9/14/15 7:38 PM, Haribabu Kommi wrote:
> On Fri, Sep 11, 2015 at 7:50 AM, Joe Conway <mail@joeconway.com> wrote:
>> On 09/01/2015 11:25 PM, Haribabu Kommi wrote:
>>> If any user is granted any permissions on that object then that user
>>> can view it's meta data of that object from the catalog tables.
>>> To check the permissions of the user on the object, instead of
>>> checking each and every available option, I just added a new
>>> privilege check option called "any". If user have any permissions on
>>> the object, the corresponding permission check function returns
>>> true. Patch attached for the same.
>>>
>>> Any thoughts/comments?
>>
>> Thanks for working on this! Overall I like the concept and know of use
>> cases where it is critical and should be supported. Some comments:
>
> Thanks for the review, I will take care of the comments in the next patch.
>
> I didn't find any better approach other than creating policies automatically
> or providing permission to superuser on system catalog tables. If everyone
> feels as this is the best approach, then i will create policies for all catalog
> tables in the next version.

Instead of adding or removing the rules, couldn't they just stay in 
place and be governed by what the field in the database was set to? It 
would also be nice if we could grant full access to roles instead of 
requiring superuser to see everything. Perhaps instead of a boolean 
store a role name in pg_database; anyone granted that role can see the 
full catalogs.

Also, we've faced issues in the past with making catalog changes due to 
fear of breaking user scripts. Instead of doubling down on that with RLS 
on top of catalog tables, would it be better to move the tables to a 
different schema, make them accessible only to superusers and put views 
in pg_catalog?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com