Row-level-security for PostgreSQL

PGSQL_RLS.sql

-- https://satoricyber.com/postgres-security/postgres-row-level-security/
-- https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql

CREATE TABLE information_under_dataprotection_law
(
     id int not null constraint pk_information_under_dataprotection_law primary key 
	 ,tenant_id int not null
	 ,txt national character varying(4000) not null
	 ,val float not null
); 


GO


CREATE OR REPLACE FUNCTION get_app_current_tenant_id()
RETURNS integer AS
$$
DECLARE
  app_tenant_id integer;
BEGIN
  -- Attempt to retrieve the setting
  BEGIN
    app_tenant_id := current_setting('app.current_tenant_id'::text)::integer;
  EXCEPTION
    WHEN undefined_object THEN
      -- Handle the case where the setting doesn't exist
      app_tenant_id := 0; -- or any other default value you prefer
  END;

  RETURN app_tenant_id;
END;
$$
LANGUAGE plpgsql;



GO 

SET app.current_tenant_id = '1';

-- app.current_tenant_id is never in pg_settings 
SELECT * FROM pg_settings WHERE name = 'app.current_tenant_id'

SELECT
  CASE
    WHEN EXISTS (SELECT 1 FROM pg_settings WHERE name = 'app.current_tenant_id') THEN
      CAST(current_setting('app.current_tenant_id'::text) AS integer)
    ELSE 0 -- or any other default value you prefer
  END AS current_tenant_id
  
  ,get_app_current_tenant_id()
; 


GO



ALTER TABLE information_under_dataprotection_law
ADD CONSTRAINT chk_information_under_dataprotection_law_tenant_id 
CHECK ( tenant_id = get_app_current_tenant_id() ) 
NOT VALID -- same as with nocheck 
;
  
-- ALTER TABLE information_under_dataprotection_law
-- VALIDATE CONSTRAINT chk_information_under_dataprotection_law_tenant_id;


GO

ALTER DATABASE cor_basic SET row_security = on;


GO

CREATE POLICY tenant_rls_policy
ON information_under_dataprotection_law
FOR ALL
USING (tenant_id = get_app_current_tenant_id());


GO


-- DROP POLICY tenant_rls_policy ON information_under_dataprotection_law;


GO


DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.policies WHERE table_name = 'information_under_dataprotection_law' AND policy_name = 'tenant_rls_policy') THEN
        EXECUTE 'DROP POLICY tenant_rls_policy ON information_under_dataprotection_law';
    END IF;
END $$;


GO

-- ============================ DATA ============================ 

SET app.current_tenant_id = '1'; 

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,1 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(10, 19);



SET app.current_tenant_id = '2'; 

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,2 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(20,29);


SET app.current_tenant_id = '3'; 

INSERT INTO information_under_dataprotection_law(id, tenant_id, txt, val) 
SELECT 
     generate_series AS id -- int not null constraint pk_information_under_dataprotection_law primary key 
	 ,2 AS tenant_id -- int not null
	 ,N'' AS txt -- national character varying(4000) not null
	 ,generate_series*5.0 AS float -- not null
FROM generate_series(30,39);





SET app.current_tenant_id = '1'; 
SELECT current_setting('app.current_tenant_id'::text)::integer
 
SELECT * FROM information_under_dataprotection_law; 
DELETE  FROM information_under_dataprotection_law;