Proteger leituras e escritas de expositores

Introdução

Fern Studio e Cedar Works estão se preparando para uma pequena feira de artesanato. Cada expositor deve manter seus próprios cadastros de carga em uma tabela compartilhada do PostgreSQL. A equipe da feira ainda precisa ver todos e atribuir estandes. Um filtro em um comando do cliente é útil para consulta, mas um expositor pode omiti-lo; o banco de dados precisa impor esse limite.

Você salvará e aplicará políticas de linha e usará logins comuns separados com senha para concluir cadastros reais. O PostgreSQL e as contas com escopo definido estão preparados. Você precisa saber navegar pelo terminal e conhecer SELECT/WHERE, INSERT, UPDATE e conceitos de tabelas do PostgreSQL. Papéis, segurança em nível de linha, expressões de políticas, transações e a sintaxe necessária do shell são explicados antes do uso. Não é necessário programar em Python. Use Terminal durante todo o lab. A duração de 95 minutos é uma estimativa do autor que inclui leitura e erros comuns, não um tempo medido de conclusão por iniciantes.

Trabalhe em /home/labex/project/exhibitor-desk. Escreva read-access.sql e write-access.sql, altere políticas de linha em fair.registrations e crie ou corrija apenas os dois novos cadastros especificados adiante. Preserve todos os registros originais, a identidade e as definições da tabela, as permissões/contas preparadas, as credenciais, as ferramentas e os serviços. Os arquivos de política devem ser SQL autossuficiente: sem comandos do shell, mudanças de conexão nem inclusões externas. Nomes equivalentes de políticas e arranjos corretos de políticas são aceitos. Você é o operador do curso e pode escolher deliberadamente cada conta preparada de teste; este exercício protege identidades de banco de dados umas das outras, não credenciais contra o operador sudo da VM. Ele não implementa autenticação de site nem promete proteção contra todos os canais laterais de informação.

Limitar a visibilidade sem impedir o trabalho da equipe da feira

Nesta etapa, você dará a cada expositor uma visão completa de seus próprios cadastros e manterá disponível o trabalho do organizador entre expositores. Escritas de expositores continuarão indisponíveis até a próxima etapa adicionar suas políticas de escrita.

Comece em Terminal. cd muda o diretório; este caminho absoluto funciona independentemente do diretório atual:

cd /home/labex/project/exhibitor-desk

./ executa um programa do diretório atual. O inicializador preparado db autentica uma conexão real do PostgreSQL e executa psql, o cliente PostgreSQL. Ele não filtra linhas nem fabrica resultados. Liste seus aliases de conta:

./db accounts

Os aliases selecionam estes logins preparados separadamente:

Um alias é uma conveniência para você como operador, não uma permissão de banco de dados concedida a Fern. Escolher cedar usa deliberadamente as credenciais separadas de Cedar. Dentro de uma conexão de Fern, mudar para Cedar deve continuar proibido.

-c fornece uma string de comando. Aspas duplas mantêm seus espaços juntos para o shell. session_user identifica o login autenticado; current_user identifica o papel cujas permissões a instrução usa atualmente. Eles devem coincidir nestas conexões comuns:

./db fern -c "SELECT session_user, current_user;"

Espere fern_studio nas duas colunas. Consulte primeiro o registro normal conhecido de Fern. O número de caixotes é uma contagem inteira; zero significa um zero conhecido, não um cadastro ausente:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 pertence a Fern, tem contato Fern desk, 2 caixotes, estande A1 e taxa de 1200 centavos. fair é o esquema que agrupa a tabela. Esses fatos originais de negócio devem permanecer inalterados.

Agora omita o filtro opcional do cliente:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Inicialmente, Fern vê R101/R102 e R201/R202 de Cedar. WHERE owner_role = 'fern_studio' os ocultaria naquela consulta, mas não impediria esta consulta sem filtro. O mesmo problema existe para Cedar.

O PostgreSQL tem duas camadas relevantes de permissão. Um GRANT permite uma operação em uma tabela ou em colunas nomeadas; as permissões preparadas já permitem que esses usuários executem suas operações necessárias. A segurança em nível de linha, ou RLS, decide adicionalmente quais linhas eles podem consultar ou alterar. Habilitar RLS sem uma política aplicável nega acesso às linhas por padrão; não inventa automaticamente a regra de propriedade.

O editor é proprietário desta tabela do curso e pode alterar políticas. Proprietários normalmente ignoram RLS, portanto uma consulta bem-sucedida do editor não comprova que um expositor esteja restrito. Use o editor para inspecionar definições e contas comuns para testar o resultado. \d é um comando de descrição do psql, não SQL:

./db editor -c "\d fair.registrations"

As seis colunas são registration_id, owner_role, contact, crates, booth_label e fee_cents. Um novo cadastro tem como padrão o estande Pending e taxa 0. As permissões preparadas de coluna permitem que expositores insiram ID/proprietário/contato/caixotes e atualizem proprietário/contato/caixotes. Eles não podem atualizar IDs, estandes ou taxas, excluir linhas nem truncar a tabela. A política ainda deve recusar uma mudança de proprietário, mesmo que a coluna de proprietário seja acessível por uma permissão de UPDATE.

Inspecione as políticas existentes pela visão de catálogo pg_policies do PostgreSQL:

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access permite acesso a todos. Políticas normalmente são permissivas: políticas aplicáveis se combinam com OR. Adicionar uma política restrita ao lado dessa abrangente ainda permitiria o resultado abrangente. Remova a política legada como parte da alteração.

A expressão USING de uma política decide se uma linha existente é visível. owner_role = current_user compara o proprietário da linha à identidade real do banco de dados. FOR SELECT limita esta política à leitura. O organizador precisa de uma política separada, com escopo TO fair_team, que permita suas próprias operações legítimas em todas as linhas; true significa que todas as linhas passam para esse papel nomeado.

Salve a primeira migração. cat > read-access.sql grava o texto a seguir no arquivo, substituindo uma cópia antiga. <<'SQL' inicia um here-document: o marcador entre aspas mantém o SQL interno literal até SQL aparecer sozinho em uma linha. BEGIN e COMMIT tornam esta alteração de políticas uma transação, para que as instruções sejam confirmadas juntas ou desfeitas em caso de erro. DROP POLICY IF EXISTS também permite corrigir e reexecutar esta migração:

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

Se o shell continuar esperando porque você omitiu o marcador final, digite SQL sozinho. Leia o arquivo salvo antes de executá-lo:

cat read-access.sql

-f instrui o psql a ler comandos de um arquivo. Aplique-o como o proprietário da tabela com escopo definido:

./db editor -f read-access.sql

Espere mensagens BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY e COMMIT. Um aviso de que um dos seus novos nomes de política ainda não existia é inofensivo. O inicializador habilita ON_ERROR_STOP: um erro de sintaxe em um arquivo interrompe a execução, com status 3 do psql. O fechamento da conexão desfaz uma transação não concluída. Corrija o arquivo e execute-o novamente; não reinicialize a VM. Para um erro de nome de arquivo/caminho, use o cd absoluto, execute ls, consulte o nome real e tente novamente. Nenhuma linha original precisa ser excluída nem recriada.

Use cada identidade comum para inspecionar o conjunto completo permitido:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern vê R101/R102, com contagens 2/0. Cedar vê R201/R202, com contagens 3/1. O organizador ainda vê os quatro originais:

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

Este é um estado intermediário intencional: os expositores têm uma política de SELECT, mas ainda não têm política de INSERT/UPDATE. Mantenha suas permissões preparadas inalteradas. Execute a verificação do resultado; sudo permite que o verificador preparado inspecione o estado protegido. Ele consulta os dados reais de negócio e testa o SQL de políticas em dados descartáveis, sem completar seu trabalho ausente:

sudo python3 /opt/labex-exhibitors/verify.py visibility

Espere confirmação de que as duas contas veem seus próprios registros e o organizador continua utilizável. Em seguida, adicione as regras separadas para escritas propostas.

Verificar tanto linhas existentes quanto valores propostos

Nesta etapa, você permitirá que expositores adicionem e atualizem seus próprios cadastros sem criar nem transferir registros para outro proprietário. Comece em Terminal, em /home/labex/project/exhibitor-desk.

Uma política de SELECT sozinha não autoriza um INSERT. Uma política de escrita precisa considerar dois momentos. Para UPDATE, USING seleciona as linhas existentes que o papel pode alterar; WITH CHECK testa a nova linha proposta. Para INSERT, não há linha antiga, portanto sua política usa apenas WITH CHECK. Nos dois casos, o proprietário proposto deve ser igual a current_user.

Salve a migração de escrita usando o mesmo formato de here-document entre aspas. Ela adiciona políticas à tabela já protegida por read-access.sql:

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

Leia-a e aplique-a como editor:

cat write-access.sql

./db editor -f write-access.sql

Espere a confirmação das duas instruções CREATE POLICY. Corrigir o arquivo e reexecutá-lo substitui apenas essas políticas nomeadas de escrita; não reinicializa os cadastros. Se recomeçar a partir dos arquivos salvos de políticas, aplique read-access.sql primeiro e depois write-access.sql.

Arranjos equivalentes de políticas podem funcionar. Uma política ALL ou UPDATE com apenas USING usa implicitamente essa expressão para seu WITH CHECK ausente; omitir essa cláusula não é automaticamente um defeito. Aqui, as políticas separadas por comando tornam explícita cada função. O que importa é o comportamento de cada operação permitida e proibida, não os nomes das políticas nem a grafia exata.

Primeiro, exercite uma escrita legítima sem deixar dados de teste. BEGIN inicia uma transação. RETURNING exibe a linha realmente armazenada por INSERT/UPDATE. ROLLBACK descarta as alterações da transação, deixando os quatro originais intactos:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

Espere TRY-FERN, proprietário fern_studio e 2 caixotes, seguidos de ROLLBACK. A inserção normal aceita um zero real, e a atualização funciona; uma regra que negue toda escrita falharia neste requisito.

Agora teste uma linha existente oculta. Esta instrução deve ter sucesso como comando sem encontrar nenhuma linha permitida para atualizar:

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

Espere nenhuma linha retornada e UPDATE 0. R201 pertence a Cedar. Um resultado de zero linhas é filtragem de linhas, não falha de conexão nem erro do banco de dados.

Os próximos dois comandos são falhas SQL esperadas. Ambos envolvem o teste em uma transação: se uma política errada o aceitasse acidentalmente, o ROLLBACK final ainda o descartaria. Se um erro interromper o comando antes de ROLLBACK, fechar a conexão também descarta sua transação não concluída.

Tente criar um registro de propriedade de Cedar enquanto está autenticado como Fern. Omita RETURNING deliberadamente: uma verificação de política SELECT sobre linhas retornadas não deve substituir a regra de escrita da linha proposta:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

Espere new row violates row-level security policy. $? é o status do comando do shell imediatamente anterior; imprima-o antes de executar qualquer outra coisa:

echo $?

Espere status 1 para este comando SQL -c com falha. Status 2 com um erro de conexão/autenticação é uma falha diferente e não comprova que a política de linha funcionou. O inicializador escolhe as credenciais preparadas em cada execução; use ./db accounts e tente novamente com o alias documentado em vez de editar arquivos de credenciais.

Em seguida, tente alterar o proprietário das linhas visíveis de Fern para Cedar. O UPDATE constante não tem WHERE nem RETURNING que leia uma coluna da tabela. Isso alcança diretamente a verificação da linha proposta; a transação ao redor mantém reversível até um teste aceito acidentalmente:

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

Novamente, espere o erro de segurança de linha e status 1. Não deduza que apenas atualizações com RETURNING precisam de proteção: SQL permitido pode omiti-lo.

Permissões de coluna fornecem um limite diferente. Um expositor pode alterar seu próprio contato/contagem, mas a equipe da feira atribui estandes e taxas. Esta tentativa de alterar o estande deve produzir um erro de permissão de tabela, mesmo para as próprias linhas de Fern:

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

Espere permission denied for table registrations, em vez de um erro de política de linha proposta. Da mesma forma, uma conexão de Fern não pode se tornar Cedar apenas nomeando esse papel:

./db fern -c "SET ROLE cedar_works;"

Espere permission denied to set role. Os expositores preparados são logins comuns sem participação entre contas, propriedade da tabela nem BYPASSRLS. Alterar uma configuração livremente escolhida da aplicação não autenticaria outro login; a política compara o papel real do banco de dados.

Confirme que os testes reversíveis deixaram intactos os fatos originais de negócio:

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

As quatro linhas originais, contatos, contagens, estandes e taxas devem corresponder aos seus valores iniciais. Execute a verificação do limite de escrita:

sudo python3 /opt/labex-exhibitors/verify.py writes

Ela verifica as duas identidades comuns, escritas legítimas, conjuntos de dados diferentes, escritas entre proprietários e acesso do organizador. Uma aprovação completa significa que agora você pode usar a tabela protegida para trabalho normal.

Concluir cadastros pelas contas protegidas

Nesta etapa, você usará as permissões funcionais para preparar dois cadastros reais de carga. Comece em Terminal, em /home/labex/project/exhibitor-desk. Estes dois novos registros são as únicas linhas de negócio que você pode adicionar ou corrigir; mantenha cada linha original.

Fern inicia um novo cadastro com uma contagem conhecida de zero caixotes. Os padrões fornecidos deixam seu estande como Pending e taxa 0 até que o organizador os atribua:

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

Espere R301, fern_studio, Fern loading, 0, Pending, 0. Este é um INSERT real confirmado, diferente dos testes anteriores em transações. Repeti-lo causaria um erro de chave duplicada; não é um comando idempotente de criação.

Defina sua contagem final de caixotes como quatro com uma atualização comum direcionada:

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar cria seu próprio novo cadastro com um caixote:

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

Espere R302, cedar_works e 1. Cada expositor agora tem três registros visíveis, incluindo seus dois originais.

A própria identidade comum do organizador atribui o estande e a taxa acordados a cada novo registro. As taxas são centavos inteiros; estes valores fornecidos são fatos do cenário, não um cálculo fiscal nem contábil:

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

Espere R301/C3/1800 e R302/D2/600. Este trabalho usa fair_team, não o proprietário da tabela; ignorar as regras como proprietário não comprovaria que a política do organizador continua utilizável.

Consulte os registros finais completos por cada expositor:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern vê R101/R102/R301, com o novo R301 na contagem 4, estande C3 e taxa 1800. Cedar vê R201/R202/R302, com o novo R302 na contagem 1, estande D2 e taxa 600. Os registros originais devem permanecer inalterados.

Se repetir acidentalmente um INSERT, inspecione esse novo registro nomeado com a conta correspondente. Se ele já existir, use um UPDATE direcionado de seu contato/contagem em vez de excluí-lo ou reinicializar o banco de dados. Por exemplo, o novo registro de Fern pode ser corrigido com UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';. Corrija apenas os novos registros nomeados; reexecute a atribuição direcionada de estande/taxa do organizador se tiver cometido um erro ali. As migrações salvas também podem ser corrigidas e reaplicadas sem recriar dados.

Execute a verificação final do resultado:

sudo python3 /opt/labex-exhibitors/verify.py business

Espere confirmação de que os dois novos cadastros estão prontos com proprietário, contagens, estandes e taxas corretos. Você tem SQL reutilizável de políticas e um registro compartilhado que expositores comuns e o organizador realmente podem usar. O desafio independente aplica os mesmos limites a um registro diferente de artesãos.

Resumo

Você separou permissões de tabela/coluna da propriedade das linhas, removeu uma política permissiva abrangente e verificou tanto linhas existentes quanto escritas propostas por logins comuns reais. Dois novos cadastros e seus estandes atribuídos pelo organizador agora funcionam sem expor nem alterar registros de outro expositor.

O limite é a identidade autenticada no banco de dados. Proprietários de tabelas e administradores continuam sendo confiáveis, e este exercício não fornece login de site nem garantia contra todo canal lateral do banco de dados.