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( 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 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;

-- 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 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 ),
( 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 ),
( 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 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 ),
( 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 ),
( 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 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 )),
(( 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 )),
(( 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() );



\timing

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_table;
SELECT * FROM rls_test_composite;


DO $$
DECLARE
  i int;
  t0 timestamptz;
  t1 INTERVAL;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..100000 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 $$
DECLARE
  i int;
  t0 timestamptz;
  t1 INTERVAL;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..100000 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 $$
DECLARE
  i int;
  t0 timestamptz;
  t1 INTERVAL;
BEGIN
  t0 := clock_timestamp();
  FOR i IN 1..100000 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 $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM rls_guard_out();
  END LOOP;
END $$;


DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM * FROM rls_test_out;
  END LOOP;
END $$;




DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM rls_guard_table();
  END LOOP;
END $$;


DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM * FROM rls_test_table;
  END LOOP;
END $$;




DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM rls_guard_composite();
  END LOOP;
END $$;


DO $$
DECLARE
  i int;
BEGIN
  FOR i IN 1..1000000 LOOP
    PERFORM * FROM rls_test_composite;
  END LOOP;
END $$;


ROLLBACK;

RESET SESSION AUTHORIZATION;

DROP TABLE rls_test_composite;
DROP TABLE rls_test_table;
DROP TABLE rls_test_out;

DROP FUNCTION rls_guard_composite();
DROP FUNCTION rls_guard_table();
DROP FUNCTION rls_guard_out();


ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC
    REVOKE ALL PRIVILEGES ON TABLES FROM test;
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC
    REVOKE ALL PRIVILEGES ON SEQUENCES FROM test;
DROP ROLE test;

/*

psql -d test -U postgres -p 15435 -h 127.0.0.1 -f t/module/db/sql/manual.sql
CREATE ROLE
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
CREATE FUNCTION
CREATE FUNCTION
psql:t/module/db/sql/manual.sql:35: ERROR:  type "tenant_ctx" already exists
CREATE FUNCTION
CREATE TABLE
INSERT 0 7
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE TABLE
INSERT 0 7
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
CREATE TABLE
INSERT 0 7
ALTER TABLE
ALTER TABLE
CREATE POLICY
CREATE POLICY
Timing is on.
BEGIN
Time: 0,029 ms
SET
Time: 0,028 ms
 current_user
--------------
 test
(1 row)

Time: 0,046 ms
 set_config
------------
 3
(1 row)

Time: 0,069 ms
 set_config
------------
 2
(1 row)

Time: 0,022 ms
 set_config
------------
 1
(1 row)

Time: 0,034 ms
 org_id | unit_id | user_id
--------+---------+---------
      1 |       2 |       3
(1 row)

Time: 0,104 ms
 org_id | unit_id | user_id
--------+---------+---------
      1 |       2 |       3
(1 row)

Time: 0,054 ms
 org_id | unit_id | user_id
--------+---------+---------
      1 |       2 |       3
(1 row)

Time: 0,050 ms
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
(1 row)

Time: 0,307 ms
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
(1 row)

Time: 0,143 ms
 id | branch
----+---------
  5 | (1,2,3)
(1 row)

Time: 0,151 ms
DO
Time: 1965,860 ms (00:01,966)
 elapsed_ms
------------
 1964.921
(1 row)

Time: 0,116 ms
DO
Time: 1272,192 ms (00:01,272)
 elapsed_ms
------------
 1271.946
(1 row)

Time: 0,205 ms
DO
Time: 1905,913 ms (00:01,906)
 elapsed_ms
------------
 1905.659
(1 row)

Time: 0,046 ms
DO
Time: 9271,593 ms (00:09,272)
DO
Time: 19391,374 ms (00:19,391)
DO
Time: 9901,212 ms (00:09,901)
DO
Time: 12647,792 ms (00:12,648)
DO
Time: 8970,411 ms (00:08,970)
DO
Time: 18970,746 ms (00:18,971)
ROLLBACK
Time: 0,161 ms
RESET
Time: 0,119 ms
DROP TABLE
Time: 0,970 ms
DROP TABLE
Time: 0,518 ms
DROP TABLE
Time: 0,430 ms
DROP FUNCTION
Time: 0,066 ms
DROP FUNCTION
Time: 0,043 ms
DROP FUNCTION
Time: 0,036 ms
ALTER DEFAULT PRIVILEGES
Time: 0,054 ms
ALTER DEFAULT PRIVILEGES
Time: 0,034 ms
DROP ROLE
Time: 0,075 ms
Time: 0,035 ms


with the increased number of data the time also increases (except for RETURNS TABLE)
Data x3:
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
 12 |      1 |       2 |       3
 19 |      1 |       2 |       3
(3 rows)

Time: 0,280 ms
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
 12 |      1 |       2 |       3
 19 |      1 |       2 |       3
(3 rows)

Time: 0,179 ms
 id | branch
----+---------
  5 | (1,2,3)
 12 | (1,2,3)
 19 | (1,2,3)
(3 rows)

Time: 0,166 ms
DO
Time: 3996,013 ms (00:03,996)
 elapsed_ms
------------
 3995.471
(1 row)

Time: 0,130 ms
DO
Time: 1333,295 ms (00:01,333)
 elapsed_ms
------------
 1333.026
(1 row)

Time: 0,134 ms
DO
Time: 3939,919 ms (00:03,940)
 elapsed_ms
------------
 3939.694
(1 row)

Time: 0,133 ms
DO
Time: 9323,095 ms (00:09,323)
DO
Time: 39369,711 ms (00:39,370)
DO
Time: 9943,009 ms (00:09,943)
DO
Time: 13332,607 ms (00:13,333)
DO
Time: 8993,791 ms (00:08,994)
DO
Time: 39473,836 ms (00:39,474)


Data x6:
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
 12 |      1 |       2 |       3
 19 |      1 |       2 |       3
 26 |      1 |       2 |       3
 33 |      1 |       2 |       3
 40 |      1 |       2 |       3
(6 rows)

Time: 0,404 ms
 id | org_id | unit_id | user_id
----+--------+---------+---------
  5 |      1 |       2 |       3
 12 |      1 |       2 |       3
 19 |      1 |       2 |       3
 26 |      1 |       2 |       3
 33 |      1 |       2 |       3
 40 |      1 |       2 |       3
(6 rows)

Time: 0,185 ms
 id | branch
----+---------
  5 | (1,2,3)
 12 | (1,2,3)
 19 | (1,2,3)
 26 | (1,2,3)
 33 | (1,2,3)
 40 | (1,2,3)
(6 rows)

Time: 0,431 ms
DO
Time: 6941,705 ms (00:06,942)
 elapsed_ms
------------
 6940.442
(1 row)

Time: 0,108 ms
DO
Time: 1414,257 ms (00:01,414)
 elapsed_ms
------------
 1414.028
(1 row)

Time: 0,116 ms
DO
Time: 7009,730 ms (00:07,010)
 elapsed_ms
------------
 7009.457
(1 row)

Time: 0,136 ms
DO
Time: 9369,038 ms (00:09,369)
DO
Time: 69423,001 ms (01:09,423)
DO
Time: 9997,077 ms (00:09,997)
DO
Time: 14179,923 ms (00:14,180)
DO
Time: 9023,941 ms (00:09,024)
DO
Time: 69650,565 ms (01:09,651)

 */