Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE ROLE test
	NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN
	PASSWORD 'test'
;

-- SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALL PRIVILEGES
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO test;

-- USAGE, SELECT, UPDATE, ALTER, ALL PRIVILEGES
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO test;

CREATE OR REPLACE FUNCTION rls_guard( OUT org_id INT, OUT unit_id INT, OUT user_id INT )
AS $$
SELECT
  current_setting( 'app.org_id',  true )::int,
  current_setting( 'app.unit_id', true )::int,
  current_setting( 'app.user_id', true )::int;
$$ LANGUAGE sql STABLE;

CREATE TABLE rls_test (
	id SERIAL,
	org_id  INT NOT NULL DEFAULT current_setting( 'app.org_id',   true )::int,
	unit_id INT NOT NULL DEFAULT current_setting( 'app.unit_id',  true )::int,
	user_id INT NOT NULL DEFAULT current_setting( 'app.user_id',  true )::int
);
-- TODO: add uniq constraint

INSERT INTO rls_test ( org_id, unit_id, user_id ) VALUES
( 1, 1, 1 ),
( 1, 1, 2 ),
( 1, 1, 3 ),
( 1, 2, 1 ),
( 1, 2, 3 ),
( 1, 3, 2 ),
( 2, 1, 1 );

ALTER TABLE rls_test FORCE  ROW LEVEL SECURITY;
ALTER TABLE rls_test ENABLE ROW LEVEL SECURITY;

-- If table has RESTRICTIVE rules it must have at least one permissive rule
-- https://www.postgresql.org/docs/current/sql-createpolicy.html#id-1.9.3.75.6
CREATE POLICY db_tenant_allow_any ON rls_test USING( true );

CREATE POLICY db_tenant_by ON rls_test AS RESTRICTIVE
USING( (org_id, unit_id, user_id) = (select (rls_guard()).*) );

CREATE ROLE

ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES

CREATE FUNCTION

CREATE TABLE

INSERT 0 7

ALTER TABLE

ALTER TABLE

CREATE POLICY

CREATE POLICY

begin;
SET SESSION AUTHORIZATION test;
select current_user;
select set_config( 'app.user_id', '3', true );
select set_config( 'app.unit_id', '2', true );
select set_config( 'app.org_id', '1', true );

select * from rls_test;

BEGIN

SET

current_user

test

SELECT 1

set_config

3

SELECT 1

set_config

2

SELECT 1

set_config

1

SELECT 1

id
org_id
unit_id
user_id

5
1
2
3

SELECT 1

DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 interval;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..100000 LOOP
    PERFORM rls_guard();
  END LOOP;
  t1 := clock_timestamp() - t0;

  -- Return the elapsed ms as a one-row result
  PERFORM set_config('app.elapsed_ms', (EXTRACT(MILLISECOND FROM t1))::text, true);
END $$;

SELECT current_setting('app.elapsed_ms') AS elapsed_ms;

DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 interval;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..100000 LOOP
    PERFORM * from rls_test;
  END LOOP;
  t1 := clock_timestamp() - t0;

  -- Return the elapsed ms as a one-row result
  PERFORM set_config('app.elapsed_ms', (EXTRACT(MILLISECOND FROM t1))::text, true);
END $$;

SELECT current_setting('app.elapsed_ms') AS elapsed_ms;

DO

elapsed_ms

4450.994

SELECT 1

DO

elapsed_ms

13893.853

SELECT 1