Multi-tenancy with RLS - Mailing list pgsql-hackers

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