rls.sql

CREATE TABLE events(
tenant_id int,
id int,
type text
);

--- CREATE 2 roles using roles tab as:
-- tenant_1 is the role for tenant_id 1
-- tenant_2 is the role for tenant_id 2

SELECT create_distributed_table('events','tenant_id');
INSERT INTO events VALUES (1,1,'push');
INSERT INTO events VALUES (2,2,'push');

--Enabling RLS on a distributed table.
SET citus.enable_ddl_propagation to off;
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
SET citus.enable_ddl_propagation to on;
SELECT run_command_on_shards('events','ALTER TABLE %s ENABLE ROW LEVEL SECURITY;');

-- Administrator (citus) can see all rows and add any rows
CREATE POLICY admin_all ON events TO citus USING (true) WITH CHECK (true);
SELECT run_command_on_shards('events',$cmd$CREATE POLICY admin_all ON %s TO citus USING (true) WITH CHECK (true);$cmd$);

-- Normal users can select,update,delete only their own records.
CREATE POLICY user_mod ON events 
  USING (current_user = 'tenant_' ||tenant_id::text)
 ;
SELECT run_command_on_shards('events',$cmd$CREATE POLICY user_mod ON %s
  USING (current_user = 'tenant_' ||tenant_id::text)
 $cmd$);
 
 -- Allow users to select,update,delete table.
GRANT UPDATE,SELECT,DELETE
  ON events TO public;
--SELECT run_command_on_shards('events',$cmd$GRANT UPDATE,SELECT,DELETE
--  ON %s TO public$cmd$);

-- Now connect with citus role and try the following commands:
SELECT * from events;
-- tenant_id | id | type
-----------+----+------
--         1 |  1 | push
--         2 |  2 | push
--(2 rows)

-- Now connect with tenant_1 role and try the following commands:
SELECT * from events; -- Can see only it's own rows.
--tenant_id | id | type
-----------+----+------
         1 |  1 | push
--(1 row)
--Try updating rows associated to tenant_id 2
UPDATE events SET type='test_update' WHERE tenant_id=2;
--UPDATE 0
-- RLS prevents updating rows related to another tenant.