참가업체의 읽기와 쓰기 보호하기

소개

Fern Studio와 Cedar Works는 작은 공예 박람회를 준비하고 있습니다. 각 참가업체는 하나의 공유 PostgreSQL 테이블에서 자신의 반입 등록을 관리해야 합니다. 박람회 운영팀은 계속 모든 업체의 기록을 조회하고 부스를 배정할 수 있어야 합니다. 클라이언트 명령의 필터는 조회에 유용하지만 참가업체가 생략할 수 있으므로, 데이터베이스에서 이 경계를 강제해야 합니다.

행 정책을 저장하고 적용한 다음, 서로 다른 일반 계정의 비밀번호 인증 로그인을 사용해 실제 등록을 완료합니다. PostgreSQL과 범위가 제한된 계정은 준비되어 있습니다. 기본적인 터미널 탐색과 PostgreSQL의 SELECT/WHERE, INSERT, UPDATE 및 테이블 개념을 알고 있어야 합니다. 역할, 행 수준 보안, 정책 표현식, 트랜잭션과 필요한 셸 문법은 사용 전에 설명합니다. Python 프로그래밍은 필요하지 않습니다. 전체 과정에서 Terminal을 사용하세요. 95분은 읽기와 일반적인 실수를 포함한 저자의 예상 시간이며, 초보자의 완료 시간을 측정한 값은 아닙니다.

/home/labex/project/exhibitor-desk에서 작업하세요. read-access.sql과 write-access.sql을 작성하고, fair.registrations의 행 정책을 변경하며, 뒤에서 지정하는 두 개의 새 등록만 생성하거나 수정합니다. 모든 원본 기록, 테이블의 정체성과 정의, 준비된 권한 부여와 계정, 자격 증명, 도구 및 서비스를 보존하세요. 정책 파일은 자체 완결적인 SQL이어야 하며 셸 명령, 연결 변경 또는 외부 파일 포함을 사용하면 안 됩니다. 동등한 정책 이름과 올바른 정책 구성도 허용됩니다. 여러분은 교육 환경의 운영자로서 준비된 각 테스트 계정을 의도적으로 선택할 수 있습니다. 이 실습은 데이터베이스 계정을 서로로부터 보호하며, VM에서 sudo를 사용하는 운영자로부터 자격 증명을 숨기지는 않습니다. 웹사이트 인증을 구현하거나 모든 정보 부채널로부터의 보호를 보장하지 않습니다.

박람회 운영팀의 접근을 유지하며 조회 범위 제한하기

이 단계에서는 각 참가업체가 자신의 등록 전체를 조회하게 하고, 운영자의 업체 간 업무도 계속 가능하게 합니다. 다음 단계에서 쓰기 정책을 추가하기 전까지 참가업체의 쓰기는 허용되지 않습니다.

Terminal에서 시작하세요. cd는 디렉터리를 변경합니다. 다음 절대 경로는 현재 디렉터리와 관계없이 사용할 수 있습니다.

cd /home/labex/project/exhibitor-desk

./는 현재 디렉터리에 있는 프로그램을 실행합니다. 준비된 db 실행기는 실제 PostgreSQL 연결을 인증하고 PostgreSQL 클라이언트인 psql을 실행합니다. 행을 필터링하거나 결과를 만들어 내지 않습니다. 계정 별칭을 나열하세요.

./db accounts

별칭은 각각 별도로 준비된 다음 로그인 계정을 선택합니다.

별칭은 운영자인 여러분을 위한 편의 기능이며, Fern에 부여된 데이터베이스 권한이 아닙니다. cedar를 선택하면 의도적으로 Cedar의 별도 자격 증명을 사용합니다. Fern 연결 안에서 Cedar로 전환하는 것은 계속 금지되어야 합니다.

-c는 하나의 명령 문자열을 전달합니다. 큰따옴표는 셸이 공백을 포함한 문자열을 하나로 다루게 합니다. session_user는 인증된 로그인 계정을 나타내고, current_user는 현재 문장이 권한을 사용하는 역할을 나타냅니다. 이러한 일반 연결에서는 두 값이 일치해야 합니다.

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

두 열 모두 fern_studio가 나와야 합니다. 먼저 Fern의 알려진 정상 기록을 읽으세요. 상자 수는 정수 개수이며, 0은 등록 누락이 아니라 개수가 0임을 알고 있다는 뜻입니다.

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

R101은 Fern의 기록이며, 연락처는 Fern desk, 상자 2개, 부스 A1, 요금 1200센트입니다. fair는 테이블을 묶는 스키마입니다. 이러한 원래의 업무 사실을 변경하면 안 됩니다.

이제 선택 사항인 클라이언트 필터를 생략하세요.

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

처음에는 Fern이 R101/R102와 Cedar의 R201/R202를 모두 봅니다. WHERE owner_role = 'fern_studio'를 사용하면 그 쿼리에서만 다른 기록을 숨길 뿐, 필터 없는 이 쿼리는 막지 못합니다. Cedar에도 같은 문제가 있습니다.

PostgreSQL에는 여기서 살펴볼 두 권한 계층이 있습니다. GRANT는 테이블이나 지정된 열에 대한 작업을 허용합니다. 준비된 권한 부여를 통해 사용자는 이미 필요한 작업에 접근할 수 있습니다. 행 수준 보안, 즉 RLS는 여기에 더해 읽거나 변경할 수 있는 행을 결정합니다. 적용 가능한 정책 없이 RLS를 활성화하면 기본적으로 행 접근이 거부됩니다. 소유권 규칙이 자동으로 만들어지는 것은 아닙니다.

editor는 이 교육용 테이블을 소유하며 정책을 변경할 수 있습니다. 소유자는 일반적으로 RLS를 우회하므로, 편집자의 쿼리가 성공했다고 해서 참가업체에 제한이 적용되었음이 입증되지는 않습니다. 정의는 편집자로 확인하고 결과는 일반 계정으로 테스트하세요. \d는 SQL이 아니라 psql의 설명 명령입니다.

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

여섯 열은 registration_id, owner_role, contact, crates, booth_label, fee_cents입니다. 새 등록의 기본값은 부스 Pending과 요금 0입니다. 준비된 열 권한은 참가업체가 ID/소유자/연락처/상자 수를 삽입하고 소유자/연락처/상자 수를 갱신하도록 허용합니다. ID, 부스 또는 요금 갱신, 행 삭제, 테이블 비우기는 허용하지 않습니다. UPDATE 권한으로 소유자 열에 접근할 수 있더라도, 정책은 소유자 변경을 거부해야 합니다.

PostgreSQL의 pg_policies 카탈로그 뷰로 기존 정책을 확인하세요.

./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는 모두의 접근을 허용합니다. 정책은 일반적으로 **허용형(permissive)**입니다. 적용 가능한 정책들이 OR로 결합됩니다. 이 광범위한 정책 옆에 좁은 정책을 추가해도 광범위한 결과는 여전히 허용됩니다. 변경 작업의 일부로 기존 정책을 제거하세요.

정책의 USING 표현식은 기존 행이 보이는지를 결정합니다. owner_role = current_user는 행의 소유자를 실제 데이터베이스 계정과 비교합니다. FOR SELECT는 이 정책을 읽기로 한정합니다. 운영자에게는 TO fair_team으로 범위를 지정한 별도 정책이 필요하며, 이 정책은 모든 행에서 운영자 자신의 정당한 작업을 허용합니다. true는 지정된 그 역할에 대해 모든 행이 통과한다는 뜻입니다.

첫 번째 마이그레이션을 저장하세요. cat > read-access.sql은 다음 텍스트를 파일에 쓰며 이전 사본이 있으면 덮어씁니다. <<'SQL'은 here-document를 시작합니다. 따옴표로 감싼 표식은 SQL이 한 줄에 단독으로 나올 때까지 안쪽 SQL을 문자 그대로 유지합니다. BEGIN과 COMMIT은 이 정책 변경을 하나의 트랜잭션으로 묶으므로 문장들이 함께 커밋되거나 오류 시 롤백됩니다. DROP POLICY IF EXISTS를 사용하면 이 마이그레이션을 수정하여 다시 실행할 수도 있습니다.

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

종료 표식을 빠뜨려 셸이 계속 기다리면 SQL만 한 줄에 입력하세요. 실행 전에 저장한 파일을 읽으세요.

cat read-access.sql

-f는 psql이 파일에서 명령을 읽도록 합니다. 범위가 제한된 테이블 소유자로 적용하세요.

./db editor -f read-access.sql

BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY, COMMIT 메시지가 나와야 합니다. 새 정책 이름 중 하나가 아직 없다는 알림은 문제없습니다. 실행기는 ON_ERROR_STOP을 활성화합니다. 파일에 구문 오류가 있으면 실행을 중단하고 psql 상태 3을 반환합니다. 연결이 닫히면 완료되지 않은 트랜잭션은 롤백됩니다. 파일을 수정하고 다시 실행하세요. VM을 초기화하지 마세요. 파일 이름이나 경로 오류가 있으면 절대 경로의 cd를 사용하고 ls를 실행해 실제 파일 이름을 읽은 뒤 재시도하세요. 원본 행을 삭제하거나 다시 만들 필요는 없습니다.

각 일반 계정으로 허용된 전체 집합을 확인하세요.

./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에는 R101/R102가 보이며 개수는 2/0입니다. Cedar에는 R201/R202가 보이며 개수는 3/1입니다. 운영자는 여전히 원본 네 행을 모두 볼 수 있습니다.

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

이는 의도적인 중간 상태입니다. 참가업체에게 SELECT 정책은 있지만 INSERT/UPDATE 정책은 아직 없습니다. 준비된 권한 부여는 변경하지 마세요. 결과 검사를 실행하세요. sudo는 준비된 검사기가 보호된 상태를 확인할 수 있게 합니다. 검사기는 실제 업무 데이터를 읽고 일회용 데이터로 정책 SQL을 테스트하며, 빠진 작업을 대신 완료하지 않습니다.

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

두 계정이 자신의 기록을 보고 운영자도 계속 작업할 수 있다는 확인이 나와야 합니다. 다음으로 쓰려는 새 값에 대한 별도 규칙을 추가합니다.

기존 행과 쓰려는 값 모두 검사하기

이 단계에서는 참가업체가 다른 소유자의 기록을 만들거나 그 소유자에게 기록을 넘기지 못하게 하면서 자신의 등록을 추가하고 갱신하도록 합니다. Terminal의 /home/labex/project/exhibitor-desk에서 시작하세요.

SELECT 정책만으로는 INSERT가 허용되지 않습니다. 쓰기 정책은 두 시점을 고려해야 합니다. UPDATE에서 USING은 역할이 변경할 수 있는 기존 행을 선택하고, WITH CHECK는 쓰려는 새 행을 검사합니다. INSERT에는 이전 행이 없으므로 정책에서 WITH CHECK만 사용합니다. 두 경우 모두 쓰려는 소유자가 current_user와 같아야 합니다.

앞에서와 같이 따옴표로 감싼 here-document 형식으로 쓰기 마이그레이션을 저장하세요. 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

파일을 읽고 편집자로 적용하세요.

cat write-access.sql

./db editor -f write-access.sql

CREATE POLICY 문장 두 개가 커밋되어야 합니다. 파일을 수정하고 다시 실행하면 이 이름의 쓰기 정책만 대체되며 등록 데이터는 초기화되지 않습니다. 저장한 정책 파일부터 다시 적용한다면 read-access.sql을 먼저 적용한 뒤 write-access.sql을 적용하세요.

동등한 정책 구성도 올바르게 동작할 수 있습니다. USING만 있는 ALL 또는 UPDATE 정책은 생략된 WITH CHECK에 그 표현식을 암묵적으로 사용하므로, 이 절이 없다고 무조건 결함인 것은 아닙니다. 여기서는 명령별 정책을 분리해 각 정책의 용도를 명확히 합니다. 중요한 것은 정책 이름이나 정확한 문법 표기보다 허용할 모든 작업과 금지할 모든 작업의 동작입니다.

먼저 테스트 데이터를 남기지 않는 정당한 쓰기를 실행하세요. BEGIN은 트랜잭션을 시작합니다. RETURNING은 INSERT/UPDATE로 실제 저장한 행을 보여 줍니다. ROLLBACK은 트랜잭션의 변경을 취소하여 원본 네 행을 그대로 남깁니다.

./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;"

TRY-FERN, 소유자 fern_studio, 상자 2개가 나온 다음 ROLLBACK이 나와야 합니다. 정상적인 삽입은 실제 0을 받아들이고 갱신도 작동합니다. 모든 쓰기를 거부하는 규칙은 이 요구를 충족하지 못합니다.

이제 숨겨진 기존 행을 테스트하세요. 이 문장은 명령으로는 성공하되 갱신이 허용된 행을 찾지 못해야 합니다.

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

반환된 행이 없고 UPDATE 0이 나와야 합니다. R201은 Cedar의 기록입니다. 0행 결과는 행 필터링이며, 연결 실패나 데이터베이스 오류가 아닙니다.

다음 두 명령은 예상된 SQL 실패입니다. 두 명령 모두 테스트를 트랜잭션으로 감쌉니다. 잘못된 정책이 실수로 작업을 허용해도 마지막 ROLLBACK이 변경을 취소합니다. 오류 때문에 ROLLBACK 전에 명령이 멈춰도 연결이 닫히면서 완료되지 않은 트랜잭션이 취소됩니다.

Fern으로 인증한 상태에서 Cedar 소유의 기록을 만들어 보세요. 의도적으로 RETURNING을 생략합니다. 반환된 행에 대한 SELECT 정책 검사가 쓰려는 행에 대한 쓰기 규칙을 대신해서는 안 됩니다.

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

new row violates row-level security policy가 나와야 합니다. $?는 바로 직전 셸 명령의 상태입니다. 다른 명령을 실행하기 전에 출력하세요.

echo $?

실패한 이 -c SQL 명령의 상태는 1이어야 합니다. 연결·인증 오류와 함께 나오는 상태 2는 다른 실패이며, 행 정책이 작동했다는 증거가 아닙니다. 실행기는 호출할 때마다 준비된 자격 증명을 선택합니다. 자격 증명 파일을 편집하지 말고 ./db accounts로 확인한 뒤 문서의 별칭으로 재시도하세요.

다음으로 Fern에게 보이는 행의 소유자를 Cedar로 바꿔 보세요. 이 상수 UPDATE에는 테이블 열을 읽는 WHERE나 RETURNING이 없습니다. 따라서 쓰려는 행의 검사에 직접 도달합니다. 바깥쪽 트랜잭션 덕분에 실수로 허용된 테스트도 되돌릴 수 있습니다.

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

echo $?

다시 행 보안 오류와 상태 1이 나와야 합니다. RETURNING이 있는 갱신만 보호하면 된다고 판단하지 마세요. 허용된 SQL은 이를 생략할 수 있습니다.

열 권한은 또 다른 경계를 제공합니다. 참가업체는 자신의 연락처와 개수를 바꿀 수 있지만, 부스와 요금은 박람회 운영팀이 배정합니다. 다음 부스 변경 시도는 Fern 자신의 행에 대해서도 테이블 권한 오류를 일으켜야 합니다.

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

쓰려는 행의 정책 오류가 아니라 permission denied for table registrations가 나와야 합니다. 마찬가지로 Fern 연결은 역할 이름만 지정해서 Cedar가 될 수 없습니다.

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

permission denied to set role이 나와야 합니다. 준비된 참가업체 계정은 다른 계정의 역할 멤버십, 테이블 소유권, BYPASSRLS가 없는 일반 로그인 계정입니다. 임의로 선택할 수 있는 애플리케이션 설정을 바꾼다고 다른 로그인 계정으로 인증되는 것은 아닙니다. 정책은 실제 데이터베이스 역할을 비교합니다.

되돌릴 수 있는 테스트 후에도 원래의 업무 사실이 유지되는지 확인하세요.

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

원본 네 행의 연락처, 개수, 부스와 요금이 모두 초기 값과 일치해야 합니다. 쓰기 경계 검사를 실행하세요.

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

이 검사는 두 일반 계정, 정당한 쓰기, 변경된 데이터 구성, 소유자 간 쓰기와 운영자 접근을 확인합니다. 모두 통과하면 보호된 테이블을 정상 업무에 사용할 수 있습니다.

보호된 계정으로 등록 완료하기

이 단계에서는 제대로 작동하는 권한을 사용해 실제 반입 등록 두 개를 준비합니다. Terminal의 /home/labex/project/exhibitor-desk에서 시작하세요. 업무 행 중 추가하거나 수정할 수 있는 것은 이 두 개의 새 기록뿐입니다. 모든 원본 행을 보존하세요.

Fern은 상자 개수가 0임을 알고 있는 새 등록을 시작합니다. 제공된 기본값에 따라 운영자가 배정하기 전까지 부스는 Pending, 요금은 0으로 남습니다.

./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;"

R301, fern_studio, Fern loading, 0, Pending, 0이 나와야 합니다. 앞의 트랜잭션 테스트와 달리 이것은 실제로 커밋되는 INSERT입니다. 반복하면 중복 키 오류가 발생합니다. 여러 번 실행해도 같은 결과를 유지하는 생성 명령이 아닙니다.

대상을 지정한 일반 갱신으로 최종 상자 수를 4개로 설정하세요.

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

Cedar는 상자 1개로 자신의 새 등록을 만듭니다.

./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;"

R302, cedar_works, 1이 나와야 합니다. 이제 각 참가업체에 원본 두 개를 포함한 세 개의 기록이 보입니다.

운영자 자신의 일반 계정으로 각 새 기록에 합의된 부스와 요금을 배정합니다. 요금은 정수 센트 단위입니다. 제공된 금액은 시나리오의 사실이며 세금이나 회계 계산 결과가 아닙니다.

./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;"

R301/C3/1800과 R302/D2/600이 나와야 합니다. 이 작업은 테이블 소유자가 아닌 fair_team을 사용합니다. 소유자의 우회로는 운영자 정책이 계속 사용 가능하다는 사실을 입증할 수 없습니다.

각 참가업체를 통해 최종 기록 전체를 읽으세요.

./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에는 R101/R102/R301이 보이며, 새 R301의 개수는 4, 부스는 C3, 요금은 1800입니다. Cedar에는 R201/R202/R302가 보이며, 새 R302의 개수는 1, 부스는 D2, 요금은 600입니다. 원본 기록은 변경되지 않아야 합니다.

실수로 INSERT를 반복했다면 해당 계정으로 지정된 새 기록을 확인하세요. 이미 존재하면 삭제하거나 데이터베이스를 초기화하지 말고, 연락처와 개수에 대상을 지정한 UPDATE를 사용하세요. 예를 들어 Fern의 새 기록은 UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';로 수정할 수 있습니다. 지정된 새 기록만 수정하세요. 운영자의 부스·요금 배정에 실수가 있었다면 대상을 지정한 해당 배정을 다시 실행하세요. 저장한 마이그레이션도 데이터를 다시 만들지 않고 수정하여 재적용할 수 있습니다.

최종 결과 검사를 실행하세요.

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

두 새 등록이 올바른 소유권, 개수, 부스와 요금으로 준비되었다는 확인이 나와야 합니다. 이제 재사용 가능한 정책 SQL과 일반 참가업체 및 운영자가 실제로 사용할 수 있는 공유 등록부를 갖추었습니다. 독립 챌린지에서는 같은 경계를 다른 제작자들의 등록부에 적용합니다.

요약

테이블·열 권한과 행 소유권을 구분하고, 광범위한 허용 정책을 제거했으며, 실제 일반 계정으로 로그인하여 기존 행과 쓰려는 새 값을 모두 검사했습니다. 이제 다른 참가업체의 기록을 노출하거나 변경하지 않으면서 두 개의 새 등록과 운영자가 배정한 부스를 사용할 수 있습니다.

보안 경계는 인증된 데이터베이스 계정입니다. 테이블 소유자와 관리자는 계속 신뢰하며, 이 실습은 웹사이트 로그인이나 모든 데이터베이스 부채널에 대한 보호를 보장하지 않습니다.