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
multi-tenancy_with_rls_poc.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers