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_table()
RETURNS TABLE( org_id INT, unit_id INT, 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 OR REPLACE FUNCTION rls_guard_out( 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;

-- define a composite type once
CREATE TYPE tenant_ctx AS (
  org_id int,
  unit_id int,
  user_id int
);

-- function that always returns a single composite row
CREATE OR REPLACE FUNCTION rls_guard_composite()
RETURNS tenant_ctx
LANGUAGE sql STABLE AS $$
  SELECT
    current_setting('app.org_id',  true)::int,
    current_setting('app.unit_id', true)::int,
    current_setting('app.user_id', true)::int;
$$;

CREATE ROLE

ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES

CREATE FUNCTION

CREATE FUNCTION

CREATE TYPE

CREATE FUNCTION

CREATE TABLE rls_test_out (
	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
);

INSERT INTO rls_test_out ( 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 ),
( 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_out FORCE  ROW LEVEL SECURITY;
ALTER TABLE rls_test_out 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_out USING( true );

CREATE POLICY db_tenant_by ON rls_test_out AS RESTRICTIVE
USING( (org_id, unit_id, user_id) = rls_guard_out() );

CREATE TABLE

INSERT 0 14

ALTER TABLE

ALTER TABLE

CREATE POLICY

CREATE POLICY

CREATE TABLE rls_test_table (
  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
);

INSERT INTO rls_test_table ( 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 ),
( 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_table FORCE  ROW LEVEL SECURITY;
ALTER TABLE rls_test_table 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_table USING( true );

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

CREATE TABLE

INSERT 0 14

ALTER TABLE

ALTER TABLE

CREATE POLICY

CREATE POLICY

CREATE TABLE rls_test_composite (
	id SERIAL,
    branch tenant_ctx
);

INSERT INTO rls_test_composite ( branch ) VALUES
(( 1, 1, 1 )),
(( 1, 1, 2 )),
(( 1, 1, 3 )),
(( 1, 2, 1 )),
(( 1, 2, 3 )),
(( 1, 3, 2 )),
(( 2, 1, 1 )),
(( 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_composite FORCE  ROW LEVEL SECURITY;
ALTER TABLE rls_test_composite 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_composite USING( true );

CREATE POLICY db_tenant_by ON rls_test_composite AS RESTRICTIVE
USING( branch = rls_guard_composite() );

CREATE TABLE

INSERT 0 14

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_guard_out();
select * from rls_guard_table();
select * from rls_guard_composite();

select * from rls_test_out;
select * From rls_test_composite;

BEGIN

SET

current_user

test

SELECT 1

set_config

3

SELECT 1

set_config

2

SELECT 1

set_config

1

SELECT 1

org_id
unit_id
user_id

1
2
3

SELECT 1

org_id
unit_id
user_id

1
2
3

SELECT 1

org_id
unit_id
user_id

1
2
3

SELECT 1

id
org_id
unit_id
user_id

5
1
2
3

12
1
2
3

SELECT 2

id
branch

5
(1,2,3)

12
(1,2,3)

SELECT 2

DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 interval;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..10000 LOOP
    PERFORM * from rls_test_out;
  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

1127.307

SELECT 1

DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 interval;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..10000 LOOP
    PERFORM * from rls_test_table;
  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

625.765

SELECT 1

DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 interval;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..10000 LOOP
    PERFORM * from rls_test_composite;
  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

1131.528

SELECT 1