Re: Multi-tenancy with RLS - Mailing list pgsql-hackers

On Sat, Oct 10, 2015 at 1:54 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Haribabu Kommi (kommi.haribabu@gmail.com) wrote:
>> On Fri, Oct 9, 2015 at 2:04 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> > * Robert Haas (robertmhaas@gmail.com) wrote:
>> >> We've got one reloption for views already - security_barrier.  Maybe
>> >> we could have another one that effectively changes a particular view
>> >> from "security definer" as it is today to "security invoker".
>> >
>> > As I recall, there was a previous suggestion (honestly, I thought it was
>> > your idea) to have a reloption which made views "fully" security
>> > definer, in that functions in the view definition would run as the view
>> > owner instead of the view invoker.
>> >
>> > I liked that idea, though we would need to have a function to say "who
>> > is the 'outer' user?" (CURRENT_USER always being the owner with the
>> > above described reloption).
>> >
>> > I'm less sure about the idea of having a view which runs entirely as the
>> > view invoker, but I'm not against it either.
>>
>> I changed in function check_enable_rls to use the invoker id instead of owner id
>> for all the system objects, the catalog table policies are getting
>> applied and it is
>> working fine till now in my multi-tenancy testing.
>>
>> Currently I am writing tests to validate it against all user objects also.
>> If this change works for all user objects also, then we may not needed
>> the security invoker
>> reloption.
>
> The reloption would be to allow the user to decide which behavior they
> wanted, as there are use-cases for both.


Any_privilege_option:
Patch that adds 'any' type as a privilege option to verify whether the user
is having any privileges on the object, instead of specifying each and every
privilege type that object supports. Using of this option at grant and revoke
commands throw an error.

View_security_definer:
Patch that adds "security_definer" as a view option to specify whether the
view owner needs to be used for all operations on the view, otherwise the
current user is used.

Currently by default the view owner is used to check against all privileges,
so changing it as invoker instead of owner leads to backward compatibility
problems as permission denied on the base relation and etc. To minimize
the impact, currently the invoker id is used only when the view is rewritten
to base relation for 1) updatable views 2) while applying the row security
policies to the base relations.

Instead of the above change, if we treat all the views by default as security
definer, then to support multi-tenancy we need to change all the system views
as security_definer=false.

comments?

shared_catalog_tenancy:
Patch adds an initdb option -C or --shared-catalog-security to add row level
security policies on shared catalog tables that are eligible for tenancy.
With this option, user gets the tenancy at database level, means user can
get the database list that he has some privileges, but not all. It is
not possible
to disable the shared catalog security once it is set at initdb time.


database_catalog_tenancy:
Patch that adds an database option of "catalog security". This can be used
with alter database only not possible with create database command.
With this option, user gets the tenancy at table level. Once user enables
the catalog security at database level, row level security policies are created
on catalog tables that are eligible. User can disable catalog security if wants.

Known issues:
1. If user (U1) grants permissions on object (tbl1) to user (U2), the user U2
    can get the information that there exists an user (U1) in the system, but
    U2 cannot get the details of U1.

2. If user (U2) executes a query on an object (tbl2) which the user
(U2) don't have
    permissions, as he cannot able to see that object from catalog views/tables,
    but the query returns an error message as "permission denied", but in case
    if multi-tenancy is enabled, the error message should be "relation
doesn't exist".

Pending items:
1. Need to add some more tests to verify all database catalog tables.
2. Documentation changes for database catalog tenancy.

Regards,
Hari Babu
Fujitsu Australia