Re: Multi-tenancy with RLS

On Thu, Jan 7, 2016 at 4:31 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> I applied all the patches. I have a basic question. Sorry though if I've
> entirely missed the point (and/or scope) of your proposal. I wonder if
> something like the following should not have failed with the patch:
>
> postgres=# CREATE POLICY class_policy ON pg_class TO PUBLIC USING
> (relowner = current_user);
> ERROR: permission denied: "pg_class" is a system catalog
>
> Is there no support yet for user-defined catalog policies?

Currently the patches don't have the support of allowing user to
create policies on catalog tables. The policies similar like you
specified are prepared for all eligible catalog tables and those
will be used when the user enables the catalog security.

Presently, default policies are used to provide proper multi-tenancy
behavior. May be we can add the support of user to update the
existing policies and add new policies on the catalog tables
without dropping the creation of default polices, as these are
required for supporting multi-tenancy by default without any
user policies.

Example usage:

postgres=# create role test_user1;
CREATE ROLE
postgres=# create role test_user2;
CREATE ROLE
postgres=# alter database postgres with catalog security true;
ALTER DATABASE
postgres=# set session authorization test_user1;
SET
postgres=> create table tbl1(f1 int);
CREATE TABLE
postgres=> set session authorization test_user2;
SET
postgres=> create table tbl2(f2 int);
CREATE TABLE
postgres=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+------------
public | tbl2 | table | test_user2
(1 row)

postgres=> select attrelid, attname from pg_attribute where attname
like 'f%' and attrelid > 16345;
attrelid | attname
----------+---------
16389 | f2
(1 row)

postgres=> set session authorization test_user1;
SET
postgres=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+------------
public | tbl1 | table | test_user1
(1 row)

postgres=> select attrelid, attname from pg_attribute where attname
like 'f%' and attrelid > 16345;
attrelid | attname
----------+---------
16386 | f1
(1 row)

Without multi-tenancy patches, both users can see two tables
and columns that are created.

Turning off catalog security is not working in earlier patches,
because of a using a wrong tuple. updated patch is attached.

Regards,
Hari Babu
Fujitsu Australia