04 - Resumo Prático

Veja o artigo:
03 - Abordagem para Administradores (ignorando RLS)

SQLs

sys.apply_tenant_rls

Montar uma FUNCTION que cria as polices de forma dinâmica:

Um ponto iimportante abaixo é que se o usuário para SELECT não atribuir o app.allowed_tenant_ids o sistema irá resultar os registros que forem globais (tenant_id is null)

CREATE OR REPLACE FUNCTION sys.apply_tenant_rls(_schema text, _table text)
RETURNS void
LANGUAGE plpgsql
AS $
DECLARE
    full_table text := format('%I.%I', _schema, _table);
    sql text;
    pol_select_name text := format('rls_%s_select', _table);
    pol_insert_name text := format('rls_%s_insert', _table);
    pol_update_name text := format('rls_%s_update', _table);
    pol_delete_name text := format('rls_%s_delete', _table);
BEGIN
    -- Liga RLS
    EXECUTE format('ALTER TABLE %s ENABLE ROW LEVEL SECURITY', full_table);
    EXECUTE format('ALTER TABLE %s FORCE ROW LEVEL SECURITY', full_table);

    -- Limpa policies antigas (idempotente)
    -- Remove policy antiga '_write' se existir (compatibilidade retroativa)
    EXECUTE format('DROP POLICY IF EXISTS %I ON %s', format('rls_%s_write', _table), full_table);
    EXECUTE format('DROP POLICY IF EXISTS %I ON %s', pol_select_name, full_table);
    EXECUTE format('DROP POLICY IF EXISTS %I ON %s', pol_insert_name, full_table);
    EXECUTE format('DROP POLICY IF EXISTS %I ON %s', pol_update_name, full_table);
    EXECUTE format('DROP POLICY IF EXISTS %I ON %s', pol_delete_name, full_table);

    --------------------------------------------------------------------
    -- POLICY DE SELECT: globais + tenants permitidos
    --------------------------------------------------------------------
    sql := format(
        'CREATE POLICY %I ON %s ' ||
        'FOR SELECT ' ||
        'TO farsoft_app_user ' ||
        'USING (' ||
        '   tenant_id IS NULL ' ||
        '   OR tenant_id = ANY(' ||
        '       string_to_array(' ||        
        '           COALESCE(current_setting(''app.allowed_tenant_ids'', true), ''''),'','' ' ||        
        '       )::bigint[]' ||
        '   )' ||
        ')',
        pol_select_name,
        full_table
    );

    RAISE NOTICE 'SQL SELECT policy: %', sql;
    EXECUTE sql;

    --------------------------------------------------------------------
    -- POLICY DE INSERT: só tenant atual (usa apenas WITH CHECK)
    --------------------------------------------------------------------
    sql := format(
        'CREATE POLICY %I ON %s ' ||
        'FOR INSERT ' ||
        'TO farsoft_app_user ' ||
        'WITH CHECK (' ||
        '   tenant_id = current_setting(''app.current_tenant_id'')::bigint' ||
        ')',
        pol_insert_name,
        full_table
    );

    RAISE NOTICE 'SQL INSERT policy: %', sql;
    EXECUTE sql;

    --------------------------------------------------------------------
    -- POLICY DE UPDATE: só tenant atual (usa USING e WITH CHECK)
    --------------------------------------------------------------------
    sql := format(
        'CREATE POLICY %I ON %s ' ||
        'FOR UPDATE ' ||
        'TO farsoft_app_user ' ||
        'USING (' ||
        '   tenant_id = current_setting(''app.current_tenant_id'')::bigint' ||
        ') ' ||
        'WITH CHECK (' ||
        '   tenant_id = current_setting(''app.current_tenant_id'')::bigint' ||
        ')',
        pol_update_name,
        full_table
    );

    RAISE NOTICE 'SQL UPDATE policy: %', sql;
    EXECUTE sql;

    --------------------------------------------------------------------
    -- POLICY DE DELETE: só tenant atual (usa apenas USING)
    --------------------------------------------------------------------
    sql := format(
        'CREATE POLICY %I ON %s ' ||
        'FOR DELETE ' ||
        'TO farsoft_app_user ' ||
        'USING (' ||
        '   tenant_id = current_setting(''app.current_tenant_id'')::bigint' ||
        ')',
        pol_delete_name,
        full_table
    );

    RAISE NOTICE 'SQL DELETE policy: %', sql;
    EXECUTE sql;
END;
$;

Agora basta rodar:

-- Aplica a política de RLS
-- param 1 = schema
-- param 2 = table

SELECT sys.apply_tenant_rls('global', 'paises');

Privilégios

Criando os Roles

Importante, o Role farsoft_app_user jamais irá fazer login (NOLOGIN) servirá apenas para ser herdado, no caso o farsoft_app herda as config dele.

-- ROLE ADMIN / MIGRATIONS (LOGIN)
CREATE ROLE farsoft_app_admin
    LOGIN
    PASSWORD 'digitar uma senha forte'  -- ajuste aqui ou use secret
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT;

-- ROLE DE APLICAÇÃO (RLS se aplica)
CREATE ROLE farsoft_app_user
    NOLOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT; 

-- OPCIONAL: role de conexão da app que herda app_user
CREATE ROLE farsoft_app
    LOGIN
    PASSWORD 'digitar uma senha forte'
    INHERIT;

-- app (conexão) herda o "perfil" app_user
GRANT farsoft_app_user TO farsoft_app;

Temos que dar privilégios assim que criarmos o schema:

-- criação do schema
CREATE SCHEMA IF NOT EXISTS sys AUTHORIZATION farsoft_app_admin;

-- Concede uso do schema para os roles da aplicação
GRANT USAGE ON SCHEMA sys TO farsoft_app_admin;
GRANT USAGE ON SCHEMA sys TO farsoft_app_user;

-- farsoft_app_admin
ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT ALL ON TABLES TO farsoft_app_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT ALL ON SEQUENCES TO farsoft_app_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT ALL ON FUNCTIONS TO farsoft_app_admin;

-- farsoft_app_user
ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO farsoft_app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT USAGE, SELECT ON SEQUENCES TO farsoft_app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA sys GRANT EXECUTE ON FUNCTIONS TO farsoft_app_user;

-- criação do schema
CREATE SCHEMA IF NOT EXISTS global AUTHORIZATION farsoft_app_admin;
 
-- Concede uso do schema para os roles da aplicação
GRANT USAGE ON SCHEMA global TO farsoft_app_admin;
GRANT USAGE ON SCHEMA global TO farsoft_app_user;

-- farsoft_app_admin
ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT ALL ON TABLES TO farsoft_app_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT ALL ON SEQUENCES TO farsoft_app_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT ALL ON FUNCTIONS TO farsoft_app_admin;

-- farsoft_app_user
ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO farsoft_app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT USAGE, SELECT ON SEQUENCES TO farsoft_app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA global GRANT EXECUTE ON FUNCTIONS TO farsoft_app_user;

Prática

Criando um registro

Conectado com o farsoft_app:

# Teste de cadastro em países
set app.current_tenant_id=1;

INSERT INTO global.paises (tenant_id, pais, codigo_ibge_pais, is_system)
VALUES (1, 'teste', 1111, 'N')
 
# Teste de cadastro em outra tabela
set app.current_tenant_id=1;

INSERT INTO "global".parametros
(public_id, chave, valor, padrao, detalhes)
VALUES(uuidv7(), 'teste2', 'teste2', 'teste2', 'teste2');
importante

Caso seja informado para o app.current_tenant_id = 1 e na hora de incluir ou alterar ser informado outro id, correrá um ERRO, ou seja, tem que ter consistência entre o tenant_id da variável/sessão e com o valor informado na instrução SQL.

Para consultar:


-- Caso queira apenas os registros do tenant_id = 1
SET app.allowed_tenant_ids = '1';

select t.* from global.paises t
where 1=1
order by t.id

-- Caso queira apenas os registros do tenant_id = 1 e 2
SET app.allowed_tenant_ids = '1,2';

select t.* from global.paises t
where 1=1
order by t.id

Como países tem o tenant_id NULL, ou seja, pode ter registros globais, então ele sempre retornará o global + os registros do tenant_id 1 e 2.

O mais interessante é que não há necessidade de informar a condicional tenant_id nas queries.

Funções Importantes

Listar todas as RLS polices do banco

SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
ORDER BY schemaname, tablename, policyname;

Listar polices por tabela

SELECT *
FROM pg_policies
WHERE tablename = 'parametros' AND schemaname = 'global';

Para excluir (drop) as polices

DROP POLICY <nome_da_policy> ON <schema>.<tabela>;

Exemplo real baseado no que você está criando:

DROP POLICY rls_parametros_select ON global.parametros;
DROP POLICY rls_parametros_insert ON global.parametros;
DROP POLICY rls_parametros_update ON global.parametros;
DROP POLICY rls_parametros_delete ON global.parametros;

Melhor forma de excluir todas as policies da tabela de uma vez

DO $
DECLARE p record;
BEGIN
    FOR p IN 
        SELECT policyname 
        FROM pg_policies
        WHERE schemaname = 'global' AND tablename = 'parametros'
    LOOP
        EXECUTE format('DROP POLICY %I ON global.parametros', p.policyname);
    END LOOP;
END;
$;

Isso é útil quando você está ajustando estrutura ou re-gerando policies da tabela.

Como verfiicar se RLS está habilitado na tabela

SELECT relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE oid = 'global.parametros'::regclass;

Se relrowsecurity = t → RLS está ativado.
Se relforcerowsecurity = t → PostgreSQL força RLS mesmo para roles com BYPASS RLS.

Ação Comando
Ver todas as policies SELECT * FROM pg_policies;
Ver policies por tabela SELECT * FROM pg_policies WHERE tablename = 'parametros';
Drop de uma policy específica DROP POLICY rls_parametros_update ON global.parametros;
Remover todas policies da tabela bloco DO com loop

A ideia da sys.reset_tenant_rls é:

  1. Dropar todas as policies da tabela informada.
  2. Chamar a sua sys.apply_tenant_rls(_schema, _table) pra recriar tudo no padrão.
CREATE OR REPLACE FUNCTION sys.reset_tenant_rls(_schema text, _table text)
RETURNS void
LANGUAGE plpgsql
AS $
DECLARE
    full_table text;
    p record;
BEGIN
    -- Monta nome completo schema.tabela com escaping correto
    full_table := format('%I.%I', _schema, _table);

    RAISE NOTICE 'Resetando RLS da tabela: %', full_table;

    ----------------------------------------------------------------
    -- 1) Dropar TODAS as policies existentes na tabela
    ----------------------------------------------------------------
    FOR p IN
        SELECT policyname
        FROM pg_policies
        WHERE schemaname = _schema
          AND tablename = _table
    LOOP
        RAISE NOTICE 'Dropando policy: %', p.policyname;
        EXECUTE format('DROP POLICY IF EXISTS %I ON %s', p.policyname, full_table);
    END LOOP;

    ----------------------------------------------------------------
    -- 2) Reaplicar o padrão multi-tenant
    ----------------------------------------------------------------
    PERFORM sys.apply_tenant_rls(_schema, _table);

    RAISE NOTICE 'RLS reaplicado em %', full_table;
END;
$;

Como usar

Exemplo para a tabela global.parametros:

SELECT sys.reset_tenant_rls('global', 'parametros');