CREATE POLICY

CREATE POLICY

테이블을 위한 새 행 수준 보안(row-level security) 정책을 정의하는 명령이에요. 어떤 사용자가 어떤 행을 보고 수정할 수 있는지를 세밀하게 제어합니다.

출처: PostgreSQL 문서

본문

문법 (Synopsis)

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

설명 (Description)

CREATE POLICY 명령은 테이블을 위한 새 행 수준 보안 정책을 정의해요. 만들어진 정책이 적용되려면 테이블에서 행 수준 보안이 켜져 있어야 한다는 점에 주의하세요(ALTER TABLE ... ENABLE ROW LEVEL SECURITY 사용).

정책은 관련 정책 표현식과 일치하는 행을 선택, 삽입, 갱신, 삭제할 권한을 부여합니다. 기존 테이블 행은 USING에 지정된 표현식으로 검사되고, INSERT나 UPDATE로 만들어질 새 행은 WITH CHECK에 지정된 표현식으로 검사됩니다. USING 표현식이 주어진 행에 대해 true를 반환하면 그 행이 사용자에게 보이고, false나 null이 반환되면 그 행은 보이지 않습니다. 보통 행이 보이지 않을 때 오류는 발생하지 않지만, 예외는 표 300을 참고하세요. WITH CHECK 표현식이 행에 대해 true를 반환하면 그 행이 삽입·갱신되고, false나 null이 반환되면 오류가 발생합니다.

INSERT, UPDATE, MERGE 문에 대해 WITH CHECK 표현식은 BEFORE 트리거가 발화된 뒤, 실제 데이터 수정이 이루어지기 전에 적용됩니다. 따라서 BEFORE ROW 트리거가 삽입될 데이터를 수정해 보안 정책 검사 결과에 영향을 줄 수 있어요. WITH CHECK 표현식은 다른 어떤 제약보다 먼저 적용됩니다.

정책 이름은 테이블마다 고유합니다. 따라서 하나의 정책 이름을 여러 다른 테이블에 쓰고, 각 테이블에 그 테이블에 적절한 정의를 가질 수 있어요.

정책은 특정 명령이나 특정 역할에 대해 적용될 수 있습니다. 새로 만든 정책의 기본은 달리 지정하지 않는 한 모든 명령과 모든 역할에 적용된다는 것입니다. 여러 정책이 단일 명령에 적용될 수 있어요(자세한 내용은 아래 참고). 표 300은 서로 다른 유형의 정책이 특정 명령에 어떻게 적용되는지 요약합니다.

USING과 WITH CHECK 표현식을 둘 다 가질 수 있는 정책(ALL과 UPDATE)에서는 WITH CHECK 표현식이 정의되지 않으면, USING 표현식이 어떤 행이 보이는지를 결정하는(일반 USING 경우) 것과 어떤 새 행이 추가되도록 허용될지를 결정하는(WITH CHECK 경우) 데 둘 다 쓰입니다.

테이블에 행 수준 보안이 켜져 있지만 적용 가능한 정책이 없으면 '기본 거부(default deny)' 정책이 가정되어 어떤 행도 보이거나 갱신되지 않습니다.

파라미터 (Parameters)

  • name — 만들 정책의 이름. 테이블의 다른 어떤 정책 이름과도 달라야 해요.
  • table_name — 정책이 적용되는 테이블의 이름(스키마 한정 가능).
  • PERMISSIVE — 정책을 허용적(permissive) 정책으로 만들도록 지정. 주어진 쿼리에 적용 가능한 모든 허용적 정책은 Boolean "OR" 연산자로 결합됩니다. 허용적 정책을 만들어 관리자는 접근할 수 있는 레코드 집합을 늘릴 수 있어요. 정책은 기본적으로 허용적입니다.
  • RESTRICTIVE — 정책을 제한적(restrictive) 정책으로 만들도록 지정. 주어진 쿼리에 적용 가능한 모든 제한적 정책은 Boolean "AND" 연산자로 결합됩니다. 제한적 정책을 만들어 관리자는 접근할 수 있는 레코드 집합을 줄일 수 있어요. 모든 레코드가 모든 제한적 정책을 통과해야 하기 때문입니다. 레코드 접근 권한을 부여하는 허용적 정책이 최소한 하나 있어야 제한적 정책으로 그 접근을 줄이는 게 유용해집니다. 제한적 정책만 존재하면 어떤 레코드도 접근할 수 없어요. 허용적·제한적 정책이 섞여 있으면, 레코드는 모든 제한적 정책을 통과하는 데 더해 허용적 정책 중 적어도 하나를 통과할 때만 접근 가능합니다.
  • command — 정책이 적용되는 명령. 유효한 옵션은 ALL, SELECT, INSERT, UPDATE, DELETE이며 기본은 ALL입니다. 어떻게 적용되는지 구체적인 내용은 아래를 참고하세요.
  • role_name — 정책이 적용될 역할(들). 기본은 PUBLIC으로 모든 역할에 정책이 적용됩니다.
  • using_expression — boolean을 반환하는 임의의 SQL 조건식. 조건식은 어떤 집계 함수나 윈도우 함수도 포함할 수 없어요. 이 표현식은 행 수준 보안이 켜져 있으면 테이블을 참조하는 쿼리에 추가됩니다. 표현식이 true를 반환하는 행은 보입니다. false나 null을 반환하는 행은 (SELECT에서) 사용자에게 보이지 않고, (UPDATE나 DELETE에서) 수정에 사용할 수 없습니다. 보통 그런 행은 조용히 억제되고 오류가 보고되지 않습니다(하지만 표 300의 예외 참고).
  • check_expression — boolean을 반환하는 임의의 SQL 조건식. 조건식은 어떤 집계 함수나 윈도우 함수도 포함할 수 없어요. 이 표현식은 행 수준 보안이 켜져 있으면 테이블에 대한 INSERT와 UPDATE 쿼리에 사용됩니다. 표현식이 true로 평가되는 행만 허용됩니다. 삽입된 레코드나 갱신 결과로 생기는 레코드 중 어느 것에 대해 표현식이 false나 null로 평가되면 오류가 던져집니다. check_expression은 행의 원래 내용이 아니라 제안된 새 내용에 대해 평가된다는 점을 참고하세요.

명령별 정책 (Per-Command Policies)

  • ALL — 정책에 ALL을 쓰면 명령 유형에 관계없이 모든 명령에 적용됨을 뜻해요. ALL 정책이 있고 더 구체적인 정책도 있으면, ALL 정책과 더 구체적인 정책(들)이 둘 다 적용됩니다. 추가로 ALL 정책은 쿼리의 선택 쪽과 수정 쪽 모두에 적용되는데, USING 표현식만 정의된 경우 두 경우 모두에 그 표현식을 씁니다. 예를 들어 UPDATE가 발행되면 ALL 정책은 UPDATE가 갱신할 행으로 선택할 수 있는 것(USING 표현식 적용)과, 결과로 생긴 갱신된 행이 테이블에 추가되도록 허용되는지 확인하는 것(WITH CHECK 표현식이 정의되어 있으면 그것, 아니면 USING 표현식 적용) 양쪽에 적용됩니다. INSERT나 UPDATE 명령이 ALL 정책의 WITH CHECK 표현식(또는 WITH CHECK 표현식이 없으면 그것의 USING 표현식)을 통과하지 못하는 행을 테이블에 추가하려 하면 전체 명령이 중단됩니다.
  • SELECT — 정책에 SELECT를 쓰면 SELECT 쿼리와, 정책이 정의된 릴레이션에 SELECT 권한이 요구될 때마다 적용됨을 뜻해요. 그 결과 SELECT 쿼리 중에는 SELECT 정책을 통과한 릴레이션의 레코드만 반환되고, UPDATE, DELETE, MERGE처럼 SELECT 권한이 필요한 쿼리도 SELECT 정책이 허용한 레코드만 볼 수 있습니다. SELECT 정책은 아래에 설명한 경우를 제외하면 릴레이션에서 레코드를 가져올 때만 적용되므로 WITH CHECK 표현식을 가질 수 없어요. 데이터 수정 쿼리에 RETURNING 절이 있으면 릴레이션에 SELECT 권한이 필요하고, 릴레이션의 새로 삽입·갱신된 행은 RETURNING 절에 사용 가능해지려면 릴레이션의 SELECT 정책을 만족해야 합니다. 새로 삽입·갱신된 행이 릴레이션의 SELECT 정책을 만족하지 않으면 오류가 던져집니다(반환될 삽입·갱신 행은 절대 조용히 무시되지 않습니다). INSERT에 ON CONFLICT DO UPDATE 절이 있거나, arbiter 인덱스나 제약 지정이 있는 ON CONFLICT DO NOTHING 절이 있으면 릴레이션에 SELECT 권한이 필요하고, 삽입 제안된 행이 릴레이션의 SELECT 정책으로 검사됩니다. 삽입 제안된 행이 릴레이션의 SELECT 정책을 만족하지 않으면 오류가 던져집니다(INSERT는 절대 조용히 회피되지 않습니다). 게다가 UPDATE 경로가 취해지면 갱신할 행과 새로 갱신된 행이 릴레이션의 SELECT 정책에 대해 검사되고, 만족하지 않으면 오류가 던져집니다(보조 UPDATE는 절대 조용히 회피되지 않습니다). MERGE 명령은 소스와 대상 릴레이션 양쪽에 SELECT 권한을 요구하므로, 각 릴레이션의 SELECT 정책이 조인되기 전에 적용되고 MERGE 동작은 그 정책들이 허용한 레코드만 볼 수 있습니다. 또한 UPDATE 동작이 실행되면 독립형 UPDATE에서처럼 갱신된 행에 대상 릴레이션의 SELECT 정책이 적용되는데, 만족하지 않으면 오류가 던져진다는 점이 다릅니다.
  • INSERT — 정책에 INSERT를 쓰면 INSERT 명령과 INSERT 동작을 포함한 MERGE 명령에 적용됨을 뜻해요. 이 정책을 통과하지 못하는 삽입되는 행은 정책 위반 오류를 만들고 전체 INSERT 명령이 중단됩니다. INSERT 정책은 레코드가 릴레이션에 추가될 때만 적용되므로 USING 표현식을 가질 수 없어요. ON CONFLICT DO NOTHING/UPDATE 절이 있는 INSERT는 끝에 실제로 삽입되는지와 무관하게 삽입 제안된 모든 행에 대해 INSERT 정책의 WITH CHECK 표현식을 검사한다는 점을 참고하세요.
  • UPDATE — 정책에 UPDATE를 쓰면 UPDATE, SELECT FOR UPDATE, SELECT FOR SHARE 명령과, INSERT 명령의 보조 ON CONFLICT DO UPDATE 절, UPDATE 동작을 포함한 MERGE 명령에 적용됨을 뜻해요. UPDATE 명령은 기존 레코드를 가져와 새로 수정된 레코드로 교체하는 것을 포함하므로, UPDATE 정책은 USING 표현식과 WITH CHECK 표현식 둘 다 받아들입니다. USING 표현식은 UPDATE 명령이 반대에 대해 동작할 레코드를 결정하고, WITH CHECK 표현식은 어떤 수정된 행이 릴레이션에 다시 저장될 수 있는지를 정의해요. 갱신된 값이 WITH CHECK 표현식을 통과하지 못하는 행은 오류를 만들고 전체 명령이 중단됩니다. USING 절만 지정하면 그 절이 USING과 WITH CHECK 두 경우 모두에 쓰입니다. 보통 UPDATE 명령은 갱신되는 릴레이션의 컬럼 데이터도 읽어야 합니다(예: WHERE 절, RETURNING 절, SET 절 오른쪽의 표현식). 이 경우 갱신되는 릴레이션에 SELECT 권한도 필요하고, UPDATE 정책에 더해 적절한 SELECT 또는 ALL 정책도 적용됩니다. 따라서 사용자는 UPDATE 또는 ALL 정책으로 행 갱신 권한을 부여받는 것 외에, SELECT 또는 ALL 정책으로 갱신되는 행(들)에 접근할 수 있어야 합니다. INSERT 명령에 보조 ON CONFLICT DO UPDATE 절이 있을 때 UPDATE 경로가 취해지면, 갱신할 행이 먼저 어떤 UPDATE 정책의 USING 표현식에 대해 검사된 다음 새로 갱신된 행이 WITH CHECK 표현식에 대해 검사됩니다. 단, 독립형 UPDATE 명령과 달리 기존 행이 USING 표현식을 통과하지 못하면 오류가 던져집니다(UPDATE 경로는 절대 조용히 회피되지 않습니다). 같은 것이 MERGE 명령의 UPDATE 동작에도 적용됩니다.
  • DELETE — 정책에 DELETE를 쓰면 DELETE 명령과 DELETE 동작을 포함한 MERGE 명령에 적용됨을 뜻해요. DELETE 명령의 경우 이 정책을 통과한 행만 DELETE 명령이 봅니다. SELECT 정책을 통해 보이지만 DELETE 정책의 USING 표현식을 통과하지 못하면 삭제할 수 없는 행이 있을 수 있어요. 단, MERGE 명령의 DELETE 동작은 SELECT 정책을 통해 보이는 행을 보게 되고, 그런 행에 대해 DELETE 정책이 통과하지 않으면 오류가 던져집니다. 대부분의 경우 DELETE 명령도 삭제하는 릴레이션의 컬럼 데이터를 읽어야 합니다(예: WHERE 절, RETURNING 절). 이 경우 릴레이션에 SELECT 권한도 필요하고, DELETE 정책에 더해 적절한 SELECT 또는 ALL 정책도 적용됩니다. 따라서 사용자는 DELETE 또는 ALL 정책으로 행 삭제 권한을 부여받는 것 외에, SELECT 또는 ALL 정책으로 삭제되는 행(들)에 접근할 수 있어야 합니다. DELETE 정책은 레코드가 릴레이션에서 삭제될 때만 적용되어 검사할 새 행이 없으므로 WITH CHECK 표현식을 가질 수 없어요.

표 300은 서로 다른 유형의 정책이 특정 명령에 어떻게 적용되는지 요약합니다. 표에서 "check"는 정책 표현식이 검사되고 false나 null을 반환하면 오류가 던져짐을 의미하며, "filter"는 정책 표현식이 false나 null을 반환하면 행이 조용히 무시됨을 의미합니다.

표 300. 명령 유형별 적용되는 정책

Command SELECT/ALL policy USING expression SELECT/ALL policy WITH CHECK expression INSERT/ALL policy WITH CHECK expression UPDATE/ALL policy USING expression UPDATE/ALL policy WITH CHECK expression DELETE/ALL policy USING expression
SELECT / COPY ... TO Filter existing row — — — — —
SELECT FOR UPDATE/SHARE Filter existing row — — Filter existing row — —
INSERT Check new row [a] — Check new row — — —
UPDATE Filter existing row [a] & check new row [a] — — Filter existing row Check new row —
DELETE Filter existing row [a] — — — — Filter existing row
INSERT ... ON CONFLICT Check new row [b][c] — Check new row [c] — — —
ON CONFLICT DO UPDATE Check existing & new rows [d] — — Check existing row Check new row [d] —
MERGE Filter source & target rows — — — — —
MERGE ... THEN INSERT Check new row [a] — Check new row — — —
MERGE ... THEN UPDATE Check new row — — Check existing row Check new row —
MERGE ... THEN DELETE — — — — — Check existing row

[a] 기존이나 새 행에 대한 읽기 접근이 필요할 때(예: 릴레이션 컬럼을 참조하는 WHERE나 RETURNING 절). [b] arbiter 인덱스나 제약이 지정된 경우. [c] 삽입 제안된 행은 충돌이 발생하는지와 무관하게 검사됨. [d] 원래 INSERT 명령의 새 행과 다를 수 있는 보조 UPDATE 명령의 새 행.

여러 정책의 적용 (Application of Multiple Policies)

서로 다른 명령 유형의 여러 정책이 같은 명령에 적용될 때(예: UPDATE 명령에 SELECT와 UPDATE 정책이 적용) 사용자는 두 유형의 권한을 모두 가져야 합니다(예: 릴레이션에서 행을 선택할 권한과 갱신할 권한). 따라서 한 유형 정책의 표현식들은 다른 유형 정책의 표현식들과 AND 연산자로 결합됩니다.

같은 명령 유형의 여러 정책이 같은 명령에 적용될 때는 릴레이션 접근을 부여하는 허용적(PERMISSIVE) 정책이 최소한 하나 있어야 하고, 모든 제한적(RESTRICTIVE) 정책이 통과해야 합니다. 따라서 모든 허용적 정책 표현식은 OR로 결합되고, 모든 제한적 정책 표현식은 AND로 결합되며, 그 결과들은 AND로 결합됩니다. 허용적 정책이 없으면 접근이 거부됩니다.

여러 정책을 결합하는 목적에서 ALL 정책은 적용되는 다른 유형의 정책과 같은 유형으로 취급된다는 점을 참고하세요.

예를 들어 SELECT와 UPDATE 권한을 모두 요구하는 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
  ...
)

주의 사항 (Notes)

테이블에 대한 정책을 만들거나 바꾸려면 그 테이블의 소유자여야 해요.

정책은 데이터베이스의 테이블에 대한 명시적 쿼리에 적용되지만, 시스템이 내부 참조 무결성 검사를 수행하거나 제약을 검증할 때는 적용되지 않습니다. 이는 어떤 값이 존재하는지 알아내는 간접적인 방법이 있음을 의미해요. 그 예로, 기본 키이거나 유일 제약이 있는 컬럼에 중복 값을 삽입하려 시도하는 경우가 있어요. 삽입이 실패하면 사용자는 그 값이 이미 존재한다고 추론할 수 있습니다. (이 예는 정책이 사용자에게 보이지 않는 레코드의 삽입을 허용한다고 가정합니다.) 또 다른 예는 사용자가 다른, 그렇지 않으면 숨겨진 테이블을 참조하는 테이블에 삽입하는 것이 허용된 경우입니다. 참조 테이블에 값을 삽입해 존재 여부를 알 수 있는데, 성공하면 참조되는 테이블에 그 값이 존재함을 나타내요. 이런 문제는, 사용자가 달리 볼 수 없는 값을 암시할 수 있는 레코드를 삽입·삭제·갱신하지 못하게 정책을 신중하게 설계하거나, 외부 의미가 있는 키 대신 생성된 값(예: 대리 키)을 써서 해결할 수 있습니다.

일반적으로 시스템은 신뢰할 수 없을 수 있는 사용자 정의 함수에 보호된 데이터가 우발적으로 노출되는 것을 막기 위해, 사용자 쿼리에 나타나는 자격(qualification)보다 먼저 보안 정책으로 부과된 필터 조건을 적용합니다. 하지만 시스템(또는 시스템 관리자)이 LEAKPROOF로 표시한 함수와 연산자는 신뢰할 수 있다고 가정되므로 정책 표현식보다 먼저 평가될 수 있어요.

정책 표현식은 사용자의 쿼리에 직접 추가되므로 전체 쿼리를 실행하는 사용자의 권한으로 실행됩니다. 따라서 주어진 정책을 사용하는 사용자는 그 표현식이 참조하는 어떤 테이블이나 함수에 접근할 수 있어야 하며, 그렇지 않으면 행 수준 보안이 켜진 테이블을 쿼리하려 할 때 권한 거부 오류를 받을 뿐입니다. 다만 이는 뷰가 동작하는 방식은 바꾸지 않아요. 일반 쿼리와 뷰에서처럼, 뷰가 참조하는 테이블에 대한 권한 검사와 정책은 뷰가 security_invoker 옵션으로 정의된 경우(see CREATE VIEW)를 제외하면 뷰 소유자의 권한과 뷰 소유자에게 적용되는 정책을 사용합니다.

MERGE를 위한 별도 정책은 없어요. 대신 SELECT, INSERT, UPDATE, DELETE에 대해 정의된 정책이 수행되는 동작에 따라 MERGE 실행 중에 적용됩니다.

추가 논의와 실용적 예는 5.9절에서 찾을 수 있습니다.

호환성 (Compatibility)

CREATE POLICY는 PostgreSQL 확장 기능이에요.

함께 보기 (See Also)

ALTER POLICY, DROP POLICY, ALTER TABLE

더 알아보기 (Learn more)

  • ALTER TABLE ... ENABLE ROW LEVEL SECURITY: 행 수준 보안을 켜서 정책이 적용되게 하는 명령.
  • ALTER POLICY: 기존 정책을 변경하는 명령.
  • DROP POLICY: 정책을 제거하는 명령.