Quantcast

Multi-tenancy with RLS

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
63 messages Options
1234
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Multi-tenancy with RLS

Haribabu Kommi-2
This is regarding supporting of multi-tenancy in a single PostgreSQL instance
using the row level security feature. The main idea is to have the
"row level security"
enabled on system catalog tables, thus the user can get only the rows that are
either system objects or the user objects, where the user is the owner.


Example:

postgres=# create role test login;
postgres=# create role test1 login;

postgres=# \c postgres test
postgres=> create table test(f1 int);
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | test
(1 row)

postgres=> \c postgres test1
postgres=> create table test1(f1 int);
postgres=> \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | test1 | table | test1
(1 row)

postgres=# \c postgres test
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | test
(1 row)


To enable row level security on system catalog tables, currently I
added a new database option to create/alter database. The syntax can
be changed later. Adding an option to database makes it easier for
users to enable/disable the row level security on system catalog
tables.

CREATE DATABASE USERDB WITH ROW LEVEL SECURITY = TRUE;
ALTER DATBASE USERDB WITH ROW LEVEL SECURITY = FALSE;

A new boolean column "datrowlevelsecurity" is added to pg_database
system catalog table to display the status of row level security on
that database.

Currently I just implemented the row level security is enabled only
for pg_class system table as a proof of concept. whenever the row
level security on the database is enabled/disabled, it internally
fires the create policy/remove policy commands using SPI interfaces.

Here I attached the proof concept patch.

Pending items:
1. Supporting of RLS on all system catalog tables
2. Instead of SPI interfaces, any better way to create/remove policies.

Any comments/suggestions regarding the way to achieve multi-tenancy in
PostgreSQL?

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

multi-tenancy_with_rls_poc.patch (20K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Fri, Aug 14, 2015 at 12:00 PM, Haribabu Kommi
<[hidden email]> wrote:
>
> Here I attached the proof concept patch.

Here I attached an updated patch by adding policies to the most of the
system catalog tables, except the following.
AggregateRelationId

AccessMethodRelationId
AccessMethodOperatorRelationId
AccessMethodProcedureRelationId

AuthMemRelationId
CastRelationId
EnumRelationId
EventTriggerRelationId
ExtensionRelationId

LargeObjectRelationId
LargeObjectMetadataRelationId

PLTemplateRelationId
RangeRelationId
RewriteRelationId
TransformRelationId

TSConfigRelationId
TSConfigMapRelationId
TSDictionaryRelationId
TSParserRelationId
TSTemplateRelationId

Following catalog tables needs to create the policy based on the
class, so currently didn't added any policy for the same.

SecLabelRelationId
SharedDependRelationId
SharedDescriptionRelationId
SharedSecLabelRelationId


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?

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

multi-tenancy_with_rls_poc_2.patch (37K) Download Attachment
any_privilege_check_option.patch (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Joe Conway
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:

1.) "... WITH ROW LEVEL SECURITY ...": name is not really accurate. This
feature does not, for example automatically add row security to all
tables in the database. It is really a specific set of RLS policies on
system tables to accomplish one very specific goal -- restrict metadata
access to those objects on which the user has some access. So maybe
something like one of these?
  ... WITH METADATA SECURITY ...
  ... WITH CATALOG SECURITY ...
  ... WITH METADATA RESTRICT ...
  ... WITH CATALOG RESTRICT ...

2.) Related to #1, I wonder if we should even provide a set of policies,
or should we just allow RLS on system tables and let people create their
own policies to suit their own needs. Maybe that could be dangerous, but
so are a lot of things we allow superusers do.

3.) In RelationBuildRowSecurity:

8<--------------
+ /* */
+ if (!criticalRelcachesBuilt || !criticalSharedRelcachesBuilt ||
relation->rd_id == PolicyRelationId)
+ return;
8<--------------

Bypassing RelationBuildRowSecurity() for relation->rd_id ==
PolicyRelationId causes a segfault for me and prevents me from even
logging into the database. Additionally I would want row security to
apply to pg_policy as well. Maybe not the best approach, but I solved
the infinite recursion issue like this:

8<--------------
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 45326a3..49db767 100644
*** a/src/backend/commands/policy.c
--- b/src/backend/commands/policy.c
*************** static char parse_policy_command(const c
*** 52,57 ****
--- 105,116 ----
  static Datum *policy_role_list_to_array(List *roles, int *num_roles);

  /*
+  * used to prevent infinite recursion when building
+  * row security for pg_policy itself
+  */
+ int rdepth = 0;
+
+ /*
   * Callback to RangeVarGetRelidExtended().
   *
   * Checks the following:
*************** RelationBuildRowSecurity(Relation relati
*** 197,202 ****
--- 257,273 ----
        MemoryContext oldcxt = CurrentMemoryContext;
        RowSecurityDesc *volatile rsdesc = NULL;

+       /* */
+       if (!criticalRelcachesBuilt || !criticalSharedRelcachesBuilt)
+               return;
+       if (relation->rd_id == PolicyRelationId && rdepth > 0)
+       {
+               rdepth = 0;
+               return;
+       }
+       if (relation->rd_id == PolicyRelationId)
+               rdepth = 1;
+
        /*
         * Create a memory context to hold everything associated with this
         * relation's row security policy.  This makes it easy to clean
up during
*************** RelationBuildRowSecurity(Relation relati
*** 366,377 ****
--- 437,450 ----
                /* Delete rscxt, first making sure it isn't active */
                MemoryContextSwitchTo(oldcxt);
                MemoryContextDelete(rscxt);
+               rdepth = 0;
                PG_RE_THROW();
        }
        PG_END_TRY();

        /* Success --- attach the policy descriptor to the relcache entry */
        relation->rd_rsdesc = rsdesc;
+       rdepth = 0;
  }

  /*
8<--------------

4.) Currently, policies are always installed in the current database
rather than the target database. Didn't try to figure out how to fix:
8<--------------
postgres=# create database test with ROW LEVEL SECURITY = true;
CREATE DATABASE
postgres=# select count(1) from pg_policy;
 count
-------
    30
(1 row)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select count(1) from pg_policy;
 count
-------
     0
(1 row)
8<--------------

5.) I'm not thrilled with all the additional included headers added to
policy.c for this, but I don't offhand see a better way either.

6.) I would like to see this work in conjunction with sepgsql (and/or
other security label providers) as well. That could be accomplished
either as mentioned in #2 above (let me create custom policies to suit
my needs), or by providing security label hooks somewhere that they
affect the output of the has_<obj>_privilege*() functions.

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Fri, Sep 11, 2015 at 7:50 AM, Joe Conway <[hidden email]> 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.

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Jim Nasby-5
On 9/14/15 7:38 PM, Haribabu Kommi wrote:

> On Fri, Sep 11, 2015 at 7:50 AM, Joe Conway <[hidden email]> 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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Robert Haas
On Tue, Sep 15, 2015 at 9:18 AM, Jim Nasby <[hidden email]> wrote:
> 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?

Uggh.  -1 on that option from me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Stephen Frost
* Robert Haas ([hidden email]) wrote:
> On Tue, Sep 15, 2015 at 9:18 AM, Jim Nasby <[hidden email]> wrote:
> > 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?
>
> Uggh.  -1 on that option from me.

Yeah, -1 from here too...  That way leads to madness (note that we still
haven't managed to get rid of the pg_user, et al,
backwards-compatibility views from, uh, 8.2?).

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
In reply to this post by Joe Conway
On Fri, Sep 11, 2015 at 7:50 AM, Joe Conway <[hidden email]> 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:
Here I attached an updated version of the patch with the following changes.

Two options to the user to create catalog security on system catalog tables.

./initdb -C -D data

With the above option during initdb, the catalog security is enabled
on all shared system catalog
tables. With this way the user can achieve the catalog security at
database level. For some users
this may be enough. Currently enabling catalog security is supported
only at initdb.

ALTER DATABASE <database> WITH CATALOG SECURITY=true;
ALTER DATABASE <database> WITH CATALOG SECURITY=false;

With the above commands, user can enable/disable catalog security on a
database system catalog
tables if multi-tenancy requires at table level.

Currently setting catalog security at create database command is not
supported. And also with
alter database command to enable/disable to database where the backend
is not connected.
This is because of a restriction to execute the policy commands
without connecting to a database.


Pending things needs to be taken care:

1. select * from tenancy_user1_tbl1;
    ERROR:  permission denied for relation tenancy_user1_tbl1

As we are not able to see the above user table in any catalog relation
because of the multi-tenancy policies,
but if user tries to select the data of the table directly, The error
message comes as permission denied, I feel
instead of the permission denied error, in case of multi-tenancy is
enabled, the error message should be
"relation doesn't exist".

2. Correct all catalog relation policies
3. Add regression tests for all system catalog relations and views.
4. Documentation changes

Any comments?

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

multi-tenancy_with_rls_poc_3.patch (108K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Tue, Oct 6, 2015 at 10:56 AM, Haribabu Kommi
<[hidden email]> wrote:
> Here I attached an updated version of the patch with the following changes.

I found some problems related to providing multi-tenancy on a system
catalog view.
This is because, system catalog view uses the owner that is created
the user instead
of the current user by storing the user information in "checkAsUser"
field in RangeTblEntry
structure.

The same "checkAsUser" is used in check_enable_rls function before
getting the policies for the table. All the system catalog views are
created by the super user, so no row level security policies
are applied to the views.

Ex-
SET SESSION ROLE tenancy_user1;

select relname from pg_class where relname = 'tenancy_user2_tbl1';
 relname
---------
(0 rows)

select schemaname, relname from pg_stat_all_tables where relname =
'tenancy_user2_tbl1';
 schemaname |      relname
------------+--------------------
 public     | tenancy_user2_tbl1
(1 row)

The policy that is created on pg_class system catalog table is, get
all the objects that current
user have permissions. Permissions can be anything.

To fix the problem, I thought of using current session id instead of
"checkAsUser" while applying
row level security policies to system catalog objects. This doesn't
affect the normal objects. But this solution has given some problems
for foreign_data.sql while running the regress tests as the planner is
generating targetlist as NULL.

Is the above specified solution is the correct approach to handle this
problem? If it is i will check the foreign_data.sql problem, otherwise
is there any good approach to handle the same?


Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Stephen Frost
* Haribabu Kommi ([hidden email]) wrote:

> On Tue, Oct 6, 2015 at 10:56 AM, Haribabu Kommi
> <[hidden email]> wrote:
> > Here I attached an updated version of the patch with the following changes.
>
> I found some problems related to providing multi-tenancy on a system
> catalog view.
> This is because, system catalog view uses the owner that is created
> the user instead
> of the current user by storing the user information in "checkAsUser"
> field in RangeTblEntry
> structure.
Right, when querying through a view to tables underneath, we use the
permissions of the view owner.  View creators should be generally aware
of this already.

I agree that it adds complications to the multi-tenancy idea since the
system views, today, allow viewing of all objects.  There are two ways
to address that:

Modify the system catalog views to include the same constraints that the
policies on the tables do

or

Allow RLS policies against views and then create the necessary policies
on the views in the catalog.

My inclination is to work towards the latter as that's a capability we'd
like to have anyway.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Tue, Oct 6, 2015 at 10:29 PM, Stephen Frost <[hidden email]> wrote:

> * Haribabu Kommi ([hidden email]) wrote:
>> On Tue, Oct 6, 2015 at 10:56 AM, Haribabu Kommi
>> <[hidden email]> wrote:
>> > Here I attached an updated version of the patch with the following changes.
>>
>> I found some problems related to providing multi-tenancy on a system
>> catalog view.
>> This is because, system catalog view uses the owner that is created
>> the user instead
>> of the current user by storing the user information in "checkAsUser"
>> field in RangeTblEntry
>> structure.
>
> Right, when querying through a view to tables underneath, we use the
> permissions of the view owner.  View creators should be generally aware
> of this already.
>
> I agree that it adds complications to the multi-tenancy idea since the
> system views, today, allow viewing of all objects.  There are two ways
> to address that:
>
> Modify the system catalog views to include the same constraints that the
> policies on the tables do
>
> or
>
> Allow RLS policies against views and then create the necessary policies
> on the views in the catalog.
>
> My inclination is to work towards the latter as that's a capability we'd
> like to have anyway.

Thanks for the solutions to handle the problem.

Currently I thought of providing two multi-tenancy solutions to the user.
They are:

1. Tenancy at shared system catalog tables level
2. Tenancy at database system catalog tables.

User can create views on system catalog tables, even though I want to provide
tenancy on those views also. I will do further analysis and provide
details of which
solution gives the benefit of two tenancy levels and then I can proceed for
implementation after discussion.

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Robert Haas
In reply to this post by Stephen Frost
On Tue, Oct 6, 2015 at 7:29 AM, Stephen Frost <[hidden email]> wrote:

> * Haribabu Kommi ([hidden email]) wrote:
>> On Tue, Oct 6, 2015 at 10:56 AM, Haribabu Kommi
>> <[hidden email]> wrote:
>> > Here I attached an updated version of the patch with the following changes.
>>
>> I found some problems related to providing multi-tenancy on a system
>> catalog view.
>> This is because, system catalog view uses the owner that is created
>> the user instead
>> of the current user by storing the user information in "checkAsUser"
>> field in RangeTblEntry
>> structure.
>
> Right, when querying through a view to tables underneath, we use the
> permissions of the view owner.  View creators should be generally aware
> of this already.
>
> I agree that it adds complications to the multi-tenancy idea since the
> system views, today, allow viewing of all objects.  There are two ways
> to address that:
>
> Modify the system catalog views to include the same constraints that the
> policies on the tables do
>
> or
>
> Allow RLS policies against views and then create the necessary policies
> on the views in the catalog.
>
> My inclination is to work towards the latter as that's a capability we'd
> like to have anyway.

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".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Stephen Frost
* Robert Haas ([hidden email]) 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 do think both of those are independent of supporting policies for
views and foreign tables though, which we'd want even if we had
reloptions for the above ideas.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Fri, Oct 9, 2015 at 2:04 PM, Stephen Frost <[hidden email]> wrote:

> * Robert Haas ([hidden email]) 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.

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Joe Conway
In reply to this post by Stephen Frost
On 10/08/2015 11:04 PM, Stephen Frost wrote:
> * Robert Haas ([hidden email]) 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'd love to see a way for views to behave in an entirely view definer or
entirely view invoker way. The current mixed mode is bad/confusing IMHO,
although I guess we need some backward compatibility mode?

> 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 do think both of those are independent of supporting policies for
> views and foreign tables though, which we'd want even if we had
> reloptions for the above ideas.
Agreed

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


signature.asc (853 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Stephen Frost
In reply to this post by Haribabu Kommi-2
* Haribabu Kommi ([hidden email]) wrote:

> On Fri, Oct 9, 2015 at 2:04 PM, Stephen Frost <[hidden email]> wrote:
> > * Robert Haas ([hidden email]) 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.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Sat, Oct 10, 2015 at 1:54 AM, Stephen Frost <[hidden email]> wrote:

> * Haribabu Kommi ([hidden email]) wrote:
>> On Fri, Oct 9, 2015 at 2:04 PM, Stephen Frost <[hidden email]> wrote:
>> > * Robert Haas ([hidden email]) 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


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

1_any_privilege_option_v1.patch (8K) Download Attachment
2_view_security_definer_v1.patch (22K) Download Attachment
3_shared_catalog_tenancy_v1.patch (11K) Download Attachment
4_database_catalog_tenancy_v1.patch (91K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Wed, Oct 21, 2015 at 2:42 PM, Haribabu Kommi
<[hidden email]> wrote:
> Pending items:
> 1. Need to add some more tests to verify all database catalog tables.
> 2. Documentation changes for database catalog tenancy.

Here I attached the updated database-catalog-security with more tests
including system views,
information schema views and documentation.

>Known issues:
>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".

To handle the above problem, we can add a check to verify whether the
corresponding
catalog relation has the row level security is enabled or not? in all
*_aclmask or similar
functions. Based on the ACL result, if the row security is enabled,
through an error as
"object does not exist", instead of permission denied by the
aclcheck_error function.
This will increase the extra processing time for queries irrespective
of whether the
multi-tenancy is enabled or not?

comments?

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

4_database_catalog_tenancy_v2.patch (135K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
Rebased patch is attached as it is having an OID conflict with the
latest set of changes
in the master branch.

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

4_database_catalog_tenancy_v3.patch (135K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multi-tenancy with RLS

Haribabu Kommi-2
On Thu, Dec 17, 2015 at 12:46 PM, Haribabu Kommi
<[hidden email]> wrote:
> Rebased patch is attached as it is having an OID conflict with the
> latest set of changes
> in the master branch.

Here I attached new series of patches with a slightly different approach.
Instead of creating the policies on the system catalog tables whenever
the catalog security command is executed, just enable row level security
on the system catalog tables. During the relation build, in
RelationBuildRowSecurity function, if it is a system relation, frame the
policy using the policy query which we earlier used to create by parsing it.

With the above approach, in case of any problems in the policy, to use
the corrected policy, user just needs to replace the binaries. whereas in
earlier approach, either pg_upgrade or disabling and enabling of catalog
security is required.

Currently it is changed only for shared system catalog tables and also the
way of enabling catalog security on shared system catalog tables is through
initdb only. This also can be changed later. I will do similar changes for
remaining catalog tables.

Any comments on the approach?

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

3_shared_catalog_tenancy_v2.patch (25K) Download Attachment
1_any_privilege_option_v2.patch (7K) Download Attachment
2_view_security_definer_v2.patch (17K) Download Attachment
1234
Previous Thread Next Thread
Loading...