postgresql view를 생성해서 column 권한을 줘 봅시다.

 postgresql에서 특정 컬럼을 못 보게 하는 role은 어떻게 설정해야 할까요? 요약하면 view를 생성해서 권한을 주는 것입니다.

 

---

 먼저, 특정 테이블을 볼 수 있는 유저 viewer를 하나 생성하겠습니다.

 

 뭔가 상당히 복잡해 보이는데요. 사실 어려운 것은 없습니다. viewer가 LOGIN이 가능하고, public.app_user에 대해 select 권한이 있다는 것을 의미합니다.

 

 

 dbeaver에서는 viewer의 권한을 app_user 테이블을 선택한 다음에 Permission을 SELECT만 선택하고 적용을 누르시면 됩니다.

 

 

 권한 설정을 다 하면, app_user에 있는 데이터들을 모두 볼 수 있습니다.

 

 

 그런데, auth_group에 대해서는 접근할 수 없습니다. 왜냐하면, 제가 'viewer'에게 auth_group에 대한 권한을 주지 않았기 때문입니다. 이제, 이런 요구 사항이 들어왔습니다. 'viewer'에게는 유저 이름에 대한 정보와 닉네임에 대한 정보만 나타나게 하고 싶습니다. 그럴만 합니다. 민감한 데이터가 노출되면 안 되는 경우에 해당 요구 사항이 들어올 수도 있습니다.

 

 테이블 단위의 권한을 주는 경우, 민감한 필드까지 같이 노출될 수 있습니다. 어떻게 postgres에서 column 단위의 권한을 줘야 할까요?

 

---

 이럴 때, view를 쓰게 됩니다. 가상 relation view인데요. 입맛에 맞는 정보들만 사용자에게 보여주는 역할을 합니다. 입맛에 맞는 정보들이라. 민감한 정보들, 예를 들자면 휴대폰 번호를 숨긴다던지, 연봉에 대한 정보를 숨긴다던지 하는 것들이 있을 겁니다. 혹은, 컴퓨터 공학과의 4학년 수업만 얻어올 수도 있습니다.

 

 뷰를 생성해 보겠습니다.

 

 create new view를 클릭하겠습니다.

 

 그러면, Properties가 나옵니다. 이름을 UserView로 하겠습니다. 밑의 탭에 소스가 있는데요. 소스를 보시면, Create Or Replace View 부분이 있습니다. public.userview라는 이름으로 view를 생성한다는 의미입니다. 그 다음에 AS 절이 나오는데요. AS 절 뒤에 붙은 것이 select 절입니다?

 

 보면, username, first_name, last_name, nickname을 app_user로부터 선택하는 쿼리임을 알 수 있는데요. 이 결과가 userview, 가상의 뷰에 보여지는 것입니다.

 

  

 userview를 생성한 후에, select * from userview를 입력해 보면, username, first_name, last_name, nickname만 나오는 것을 볼 수 있습니다. password와 같은 민감한 데이터는 안 보입니다. 이제, userview를 볼 수 있는 권한을 viewer에게 주면 되겠습니다.

 

 viewer를 다시 생성한 후에, 권한 탭을 눌러봅시다. Views의 userview만 SELECT 권한을 주겠습니다.

 

 

 GRANT 문에서, public.app_user 대신에 public.userview에 대해 SELECT 권한만 준 것을 보시면 됩니다.

 

 

 확인해 볼까요? app_user에 있는 record들을 조회해 봅시다. 그랬더니 접근 권한이 없다고 떴습니다. 이는 제가 app_user에 대한 권한을 주지 않았기 때문입니다.

 

 

 대신에, userview라는 가상의 view를 통해서, "viewer"는 user 데이터에 접근할 수 있습니다. 요약하면 테이블 A에 대해 컬럼 단위의 권한을 주려면, 테이블 A에 대한 (제외할 필드들을 select 하지 않은) view를 생성한 후에, 해당 view에 대한 권한을 주면 됩니다.