PostgreSQL에서 사용자를 잘 생성하고, 테이블에 대한 SELECT 권한을 할당했음에도 잘 동작하지 않았던 문제 상황을 겪었습니다.
추후 같은 문제를 겪지 않기 위하여 겪었던 문제와 그에 대한 해결방법을 정리하여 기록합니다.

증상 1: Permission Denied

원인

사용자를 생성하고, 테이블에 대한 SELECT 권한을 부여했습니다. 그리고 SELECT 명령을 실행했는데, Permission Denied -> 권한 문제가 발생했습니다.

그래서 테이블에 컬럼들을 살펴본 결과, COLUMN_DEFAULT를 통해 설정되는 기본값에 nextval이라는 시퀀스가 사용되고 있었습니다.

시퀀스는 보통 유일한 값을 생성하기 위해 사용되며 DBMS마다 조금은 다르지만 다양한 기능을 제공하는데요. 시퀀스를 사용하기 위해 스키마에 요구되는 권한(USAGE)이 있고, 이게 SELECT할 때는 쓰이지 않지만 할당된 이상 요구되는 그 권한을 할당해줘야 하는 문제였습니다.

해결

테이블이 속해 있는 스키마에 대한 USAGE 권한을 생성한 사용자에게 할당해 해결할 수 있습니다.

GRANT USAGE ON SCHEMA '스키마 이름' TO '사용자 이름';

증상 2: 오류 없는 조회 실패

원인

증상 1의 해결 이후 더 이상 조회할 때 오류는 발생하지 않으나, 관리자가 조회할 때는 모든 값이 잘 조회되는 테이블이 마치 비어있는 테이블처럼 보이는 문제가 발생했습니다.

여기서는 ChatGPT의 도움을 받아 이런 상황에 대해 물어보니 RLS의 문제일 수도 있겠다는 힌트를 주어, 관련 내용을 찾아봤습니다.

찾아보니, RLS(Row Level Security)는 행 단위에 보안을 위해 정책을 설정할 수 있는 기능으로, pg_policies 테이블에 확인해보니 문제가 발생하는 테이블에 실제로 정책이 설정되어 있는 것을 확인할 수 있었습니다.

해결

기존 정책은 사내 보안사항이기 때문에 설명하지 않겠지만, 유지가 필요한 정책이어서 충돌되지 않는 새 정책을 추가하여 문제를 해결했습니다.

아래는 실행한 명령어로 생성한 사용자가 SELECT를 시도하는 경우 허용하도록 하는 정책입니다. USING은 WHERE 절처럼 사용하여, 해당 조건에 부합하는 경우만 정책이 적용되도록 하는 것인데, true로 설정하면 모든 상황에 적용됩니다.

CREATE POLICY "Allow SELECT by 사용자 이름"
ON '스키마.테이블' FOR SELECT TO '사용자 이름'
USING (true);