CREATE POLICY
이전 위로SQL 명령어처음 다음

CREATE POLICY

CREATE POLICY — 새 로우 수준 보안 정책 정의

요약

CREATE POLICY 이름 ON 테이블이름
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { 롤이름 | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( 사용식 ) ]
    [ WITH CHECK ( 검사식 ) ]

설명

CREATE POLICY 명령은 한 테이블에 새 로우 수준 보안 정책을 설정한다. 이 명령을 사용하려면, 먼저 그 해당 테이블 속성 가운데 로우 단위 보안 정책 사용이 활성화 되어 있어야 한다. (ALTER TABLE ... ENABLE ROW LEVEL SECURITY 명령으로 활성화 한다)

하나의 정책이란 정책식 검사로 해당되는 로우에 한해서 select, insert, update, delete 작업을 허용하는 것을 말한다. 이미 테이블에 있는 자료에 대한 검사식은 USING 옵션으로 지정하는 표현식을 사용하며, INSERT 명령이나, UPDATE 명령으로 새로 저장될 자료에 대한 검사는 WITH CHECK 옵션으로 지정한는 표현식을 사용한다. USING 옵션의 표현식 결과가 true 값이면, 해당 사용자에게 그 로우를 보여주고, false나 null 이면, 보여주지 않는다. WITH CHECK 옵션의 표현식 결과가 true 값이면, 해당 로우가 insert 또는 update 되며, false나 null 이면, 해당 작업을 오류로 처리한다.

INSERT 또는 UPDATE 구문 처리는 BEFORE 트리거 처리 다음에, 실 자료가 변경 되기 전에, WITH CHECK 검사식이 수행된다. 즉, BEFORE ROW 트리거에서 해당 자료를 조작한다면, 검사식 결과가 달라질 수 있다. WITH CHECK 검사식은 모든 제약 조건 검사보다 먼저 수행된다.

정책 이름은 테이블 단위로 지정한다. 같은 정책 이름이 여러 테이블에 사용할 수 있으며, 이들은 각기 그 테이블과 관련된 정책을 설정한다.

정책은 특정 명령이나, 특정 롤을 대상으로 정의할 수 있다. 특별하게 지정하지 않으면, 모든 명령, 모든 롤 대상이다. 하나의 명령으로 여러 정책을 한번에 정의할 수 있다. 아래에서 자세히 설명한다. 정책 종류와 특정 명령 사이 관계는 표 240 에서 요약하고 있다.

하나의 정책에서 ALL 또는 UPDATE 명령에 대해 USING과 WITH CHECK 구문을 함께 지정할 수 있기 때문에, WITH CHECK 구문이 없는 경우에는, USING 에서 지정한 조건식이 자료를 조회할 때도, 자료를 조작 할 때도 같이 사용된다.

아무런 정책도 정의된 것이 없는 로우 단위 보안 기능이 활성화된 테이블이 있다면, 그 테이블의 기본 보안 정책은 “모두 금지”다. 조회할 수도, 자료를 조작할 수도 없다.

매개 변수

이름

새로 만들 정책 이름. 해당 테이블 내에서 유일해야 한다.

테이블이름

새 정책을 정의할 테이블 이름(스키마 이름 포함).

PERMISSIVE

새 정책을 허용 정책으로 지정한다. 여러 허용 정책이 함께 있다면, “OR” 연산으로 판단한다. 검사식을 통과하는 로우에 대해서 작업을 허용한다. 정책의 기본값이 허용 정책이다.

RESTRICTIVE

새 정책을 제한 정책으로 지정한다. 여러 제한 정책이 함께 있다면, “AND” 연산으로 판단한다. 검사식을 통과하는 로우에 대해서 작업을 허용하지 않는다.

접근을 제한하는 제한 정책을 유용하게 쓰기 위해서는 먼저 적어도 하나 이상의 허용 정책이 만들어 두는 것이 좋다. 제한 정책만 있으면, 접근 할 수 있는 자료가 없게 된다. 두 정책 모두 사용하면, 두 정책 가운데, 하나라도 통과하면, 그 자료를 사용할 수 있게 된다.

명령

정책을 반영할 명령. 사용할 수 있는 값은 ALL, SELECT, INSERT, UPDATE, DELETE이다. ALL이 기본값이다. 각 명령이 어떻게 반영되는지에 대한 설명은 아래에서 한다.

롤이름

정책을 반영할 해당 롤. 기본값은 PUBLIC, 모든 사용자다.

사용식

SQL 구문으로 된 조건식 (반환값은 boolean 형) 이 조건식에 집계 함수나 원도우 함수는 사용할 수 없다. 해당 테이블의 로우 수준 보안 속성이 활성화 되어 있다면, 작업 쿼리에 이 조건식이 포함되어 수행된다. 작업이 SELECT 인 경우, 이 식 계산 결과가 true 면 보이고, false 또는 null 이면 보이지 않는다. 한편, UPDATE 또는 DELETE 이면 true면 자료 조작을 하고, false 또는 null 이면 자료 조작을 하지 않는다. 오류로 처리하지 않고, 그냥 무시한다.

검사식

SQL 구문으로 된 조건식 (반환값은 boolean 형) 이 조건식에 집계 함수나 원도우 함수는 사용할 수 없다. 해당 테이블의 로우 수준 보안 속성이 활성화 되어 있다면, INSERT 및 UPDATE 쿼리에 대해서 이 조건식을 계산해 true 면 작업을 진행하고, false 또는 null 이며 오류로 처리한다. 검사식 대상은 기존 있는 자료가 아니라, 새로 처리될 자료가 대상임을 기억해야 한다.

명령별 정책

ALL

ALL은 모든 명령을 뜻한다. ALL 대상 정책도 있고, 특정 명령 대상 정책이 함께 있는 경우도 모든 정책을 반영한다. 추가로, ALL인 경우는 USING 구문만 사용했다면, 그 USING 구문에서 지정한 조건식이 자료 조작 작업 허용 여부를 결정하는데 사용된다.

예를 들어, UPDATE 작업인 경우, WITH CHECK 조건절이 없는 ALL 정책은 대상 자료를 찾는 작업에서도, 그 자료를 갱신하는 작업에서도 USING 조건식이 사용된다. WITH CHECK 조건절이 있으면 자료를 찾는데는 USING을 갱신할 때는 WITH CHECK 조건절로 검사한다. WITH CHECK 조건절이 있는 ALL 정책이 있는데, 이 조건에 만족하지 않는 INSERT, UPDATE 작업은 중지 된다.

SELECT

SELECT는 SELECT 쿼리 작업에 대해서만 정책을 반영한다. 정확하게 표현하면, SELECT 명령 쿼리만 아니라, 자료를 조회하는 모든 작업을 의미한다. UPDATE 작업에서 대상 자료를 찾을 때, 이 정책이 있다면, 이 정책을 반영해 통과한 자료를 대상으로 자료 갱신 작업이 진행된다. DELETE 작업도 마찬가지다. SELECT 명령 정책은 자료 조회 작업에 대해서만 관여함으로 WITH CHECK 조건절을 사용할 수 없다.

INSERT

INSERT는 INSERT 명령 작업시 반영 되는 정책이다. 이 정책에 위배되면 INSERT 작업은 오류로 처리해서 중지된다. INSERT 명령 정책은 자료 입력 작업에 대해서만 관여함으로 USING 조건절을 사용할 수 없다.

ON CONFLICT DO UPDATE 구문을 사용하는 INSERT 작업에서도 이 정책은 오직 INSERT 작업에 대해서만 검사한다. (insert 정책만 있고, update 정책이 없다면(모든 update 작업 금지), 자료 충돌 뒤 update 작업을 진행할 때, 정책 위반으로 작업은 실패한다. - 옮긴이)

UPDATE

UPDATE는 UPDATE, SELECT FOR UPDATE, SELECT FOR SHARE, INSERT ... ON CONFLICT DO UPDATE 명령에 관여하는 정책이다. UPDATE 명령 정책은 USING과 WITH CHECK 구문을 함께 지정할 수 있고, USING 조건식은 갱신할 자료를 찾는데 사용되고, WITH CHECK 조건식은 그 자료를 갱신 하는데 사용된다.

WITH CHECK 조건식 검사에 통과하지 못하면, 오류로 처리하고 작업을 중지한다. WITH CHECK 구문이 없는 경우에는, USING 에서 지정한 조건식이 자료를 조회할 때도, 자료를 조작 할 때도 같이 사용된다.

일반적으로 UPDATE 명령은 그 작업 대상을 뽑거나 결과를 출력하기 위해 (WHERE이나, RETURNING 구문 사용), 또는 값을 할 당하기 위한 SET 절의 오른쪽 값을 먼저 읽어야하기 때문에, 먼저 SELECT 정책을 통과해야 한다. 즉 의도하는 대로 정상 처리 되려면, 이 정책과 함께, SELECT 또는 ALL 명령 정책도 함께 지정해야 한다.

또한, INSERT ... ON CONFLICT DO UPDATE 작업인 경우에도 UPDATE 명령 정책이 필요하다. 조심해야 할 것은 USING 조건절만 지정했다면, 해당 정책에 위배되어 자료가 갱신되지 못할 상황에서도 오류 없이 그냥 무시해 버린다는 점이다.

DELETE

DELETE는 DELETE 명령 작업 시 적용되는 정책이다. 이 정책에 통과된 자료만 DELETE 작업 대상이 된다. SELECT 명령 정책을 통과했다 하더라도, DELETE 명령 정책의 USING 조건절 검사에 통과 못해서 자료가 삭제 되지 않을 수도 있다.

DELETE 작업은 대부분 자료 읽기 작업이 선행됨으로 (예, WHERE절 또는 RETURNING절), 먼저 SELECT 또는 ALL 명령 정책을 할당해야 의도한 작업이 진행될 것이다.

DELETE 명령 정책은 기존 자료의 조회만 관여하기 때문에, WITH CHECK 조건절 구문을 사용할 수 없다.

표 240. 명령 종류에 따른 정책 반영

명령SELECT/ALL 정책INSERT/ALL 정책UPDATE/ALL 정책DELETE/ALL 정책
USING 사용절WITH CHECK 검사절USING 사용절WITH CHECK 검사절USING 사용절
SELECT기존 로우————
SELECT FOR UPDATE/SHARE기존 로우—기존 로우——
INSERT—새 로우———
INSERT ... RETURNING 새 로우 [a] 새 로우———
UPDATE 기존 & 새 로우 [a] —기존 로우새 로우—
DELETE 기존 로우 [a] ———기존 로우
ON CONFLICT DO UPDATE기존 & 새 로우—기존 로우새 로우—

[a] 해당 테이블의 특정 칼럼을 읽는 WHERE 또는 RETURNING 절에 의해 선택되는 기존 로우 또는 새 로우


다중 정책 적용

하나의 작업(명령)에 여러 명령 정책이 있는 경우(예, UPDATE 명령인데, SELECT 정책과 UPDATE 정책을 동시에 적용해야 하는 경우), 모든 정책이 통과 되어야 작업이 정상적으로 수행된다. 즉, 이 두 정책의 조건식이 AND 연산을 하고, 그 결과가 참이여야 한다.

같은 명령에 적용되는 같은 명령 정책이 여러 개 있는 경우, 적어도 하나는 PERMISSIVE 정책이어야 하며, 모든 RESTRICTIVE 정책은 통과 되어야 한다. 즉, 모든 PERMISSIVE 정책은 OR 연산으로 묶기고, 모든 RESTRICTIVE 정책은 AND 연산으로 묶기고, 이들의 결과를 AND 연산으로 최종 통과를 판별한다. PERMISSIVE 정책이 하나도 없으면, 통과된 자료가 하나도 없음으로 간주한다.

ALL 명령 정책은 각 명령 작업에 필요한 정책으로 간주한다.

한 예로, UPDATE 작업 처럼 SELECT 명령 정책과 UPDATE 명령 정책 둘 다 쓰이고, 같은 명령 종류 정책도 여러개 있는 경우라면, 다음과 같이 그 조건식을 계산한다: 

expression from RESTRICTIVE SELECT/ALL policy 1
AND
expression from RESTRICTIVE SELECT/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE SELECT/ALL policy 1
  OR
  expression from PERMISSIVE SELECT/ALL policy 2
  OR
  ...
)
AND
expression from RESTRICTIVE UPDATE/ALL policy 1
AND
expression from RESTRICTIVE UPDATE/ALL policy 2
AND
...
AND
(
  expression from PERMISSIVE UPDATE/ALL policy 1
  OR
  expression from PERMISSIVE UPDATE/ALL policy 2
  OR
  ...
)

참고

정책은 테이블 소유주가 만들거나 바꿀 수 있다.

정책은 해당 테이블에 적용하면, 해당 테이블을 사용하는 쿼리가 실행 될 때 그 정책 검사를 수행하지만, 서버가 내부적으로 무결성 검사나, 유효성 제약 조건 검사를 할 때는 이 정책 검사는 반영되지 않는다. 이것은 정책에 위배되지만, 입력된 값이 있는지 조사하는 우회적인 방법이다. 대표적인 경우가, 특정 칼럼이 기본키나, 유니크 제약조건이 있는 경우다. 자료를 입력하고 했는데, 이 제약 조건에 위배했다는 오류가 발생하며, 이미 그 자료가 있다는 판단 근거가 되어버린다. 이 자료 볼 수 없는 정책을 적용해 놓았다고 하더라도! 또 다른 예는 참조키 제약조건에서 이런 문제점이 있는데, 어느 테이블에 자료를 입력하는데, 자료가 잘 입력되면, 그 입력된 칼럼이 다른 테이블을 참조키였고, 그 다른 테이블은 해당 사용자가 볼 수 없다하더라도, 참조키가 있는 테이블에 자료가 입력될 수 있다는 것만으로도 볼 수 없는 테이블에 자료가 있음을 간접적으로 알 수 있게된다. 이러한 문제는 사용자가 레코드를 삽입, 삭제 또는 업데이트 할 수 없도록 신중하게 정책을 세워 해결할 수 있다. 그 정책의 조건식에서 아무 의미 없는 일련번호 형태의 값(대리키)을 참조하게 하거나, 다른 방법으로 알아낼 수 없는 값을 사용한다.

일반적으로 신뢰할 수 없는 사용자 정의 함수를 이용해 의도되지 않게, 보호 된 데이터가 노출될 수 있어, 이를 방지하기 위해 사용자 쿼리에 딸린 보안 정책을 사용하여 자료 제한 조건을 적용한다. 그런데, LEAKPROOF 속성이 있는 연산자나, 함수를 사용하면, 해당 자료를 볼 수 없게 이 정책으로 막았다 하더라도, LEAKPROOF 속성이 우선 적용되어, 연산자나, 함수가 그 자료를 볼 수 있다면, 보여준다.

정책에서 사용하는 표현식은 사용 쿼리에 추가됨으로, 그 표현식 검사 작업은 그 쿼리를 실행하는 사용자 권한으로 진행된다. 그러므로, 해당 테이블에 로우 수준 보안 기능이 활성화 되어있고, 그 테이블을 사용하는 쿼리를 실행해, 보안 정책의 검사 작업을 진행하면, 이 표현식에서 사용하는 함수나 참조 하는 테이블 등에 대해서 사용할 수 있는 권한이 있어야한다. 없으면 권한 없음 오류가 발생한다. 한편 뷰인 경우는 조심해야 한다. 그 뷰에 정의된 쿼리는 그 뷰의 소유주 권한으로 실행된다. 자료를 볼 수 있는 사용자가 해당 테이블 대상 뷰를 만들고, 그 뷰 대상 읽기 권한을 원래 테이블에서 자료를 볼 수 없는 사용자에게 부여하게 되면, 그 뷰에 정의된 쿼리는 자료를 볼 수 있는 사용자 권한의 정책이 반영되어 자료를 볼 수 있게 된다.

추가 정보와 연습용 예제는 5.7절에서 다룬다.

호환성

CREATE POLICY 구문은 PostgreSQL 확장 기능이다.

관련 항목

ALTER POLICY, DROP POLICY, ALTER TABLE
이전 위로 다음
CREATE OPERATOR FAMILY 처음 CREATE PUBLICATION