SQL 표준에 추가로PostgreSQL : 문서 : 11 : 5.6. 토토 캔다음을 통해 사용 가능부여, 테이블에는행 보안 무지개 토토이는 사용자별로 일반 쿼리로 반환할 수 있거나 데이터 수정 명령으로 삽입, 업데이트 또는 삭제할 수 있는 행을 제한합니다. 이 기능은 다음과 같이 알려져 있습니다.행 수준 보안. 기본적으로 테이블에는 무지개 토토이 없으므로 사용자가 SQL 권한 시스템에 따라 테이블에 대한 액세스 권한을 가지고 있는 경우 테이블 내의 모든 행을 쿼리 또는 업데이트에 동일하게 사용할 수 있습니다.
테이블에서 행 보안이 활성화된 경우(스포츠 토토 PostgreSQL : 문서), 행을 선택하거나 행을 수정하기 위한 테이블에 대한 모든 일반적인 액세스는 행 보안 무지개 토토에 의해 허용되어야 합니다. (그러나 테이블 소유자는 일반적으로 행 보안 무지개 토토의 적용을 받지 않습니다.) 테이블에 대한 무지개 토토이 없으면 기본 거부 무지개 토토이 사용됩니다. 즉, 행이 표시되지 않거나 수정할 수 없음을 의미합니다. 다음과 같이 전체 테이블에 적용되는 작업잘라내기그리고참조, 행 보안이 적용되지 않습니다.
행 보안 무지개 토토은 명령이나 역할 또는 둘 다에 특정할 수 있습니다. 적용할 무지개 토토을 지정할 수 있습니다.전체명령, 또는선택, 삽입, 업데이트, 또는삭제. 특정 무지개 토토에 여러 역할을 할당할 수 있으며 일반적인 역할 멤버십 및 상속 규칙이 적용됩니다.
무지개 토토에 따라 표시되거나 수정 가능한 행을 지정하려면 부울 결과를 반환하는 식이 필요합니다. 이 표현식은 사용자 쿼리에서 나오는 조건이나 함수 이전에 각 행에 대해 평가됩니다. (이 규칙의 유일한 예외는 다음과 같습니다.누출 방지정보 유출이 보장되는 함수; 최적화 프로그램은 행 보안 검사 전에 이러한 함수를 적용하도록 선택할 수 있습니다.) 표현식이 반환되지 않는 행참처리되지 않습니다. 표시되는 행과 수정이 허용되는 행에 대한 독립적인 제어를 제공하기 위해 별도의 표현식을 지정할 수 있습니다. 무지개 토토 표현식은 쿼리를 실행하는 사용자의 권한으로 쿼리의 일부로 실행되지만 보안 정의자 기능을 사용하면 호출하는 사용자가 사용할 수 없는 데이터에 액세스할 수 있습니다.
수퍼유저 및 역할BYPASSRLS속성은 테이블에 액세스할 때 항상 행 보안 시스템을 우회합니다. 테이블 소유자는 일반적으로 행 보안도 우회하지만 테이블 소유자는 다음을 사용하여 행 보안을 적용하도록 선택할 수 있습니다.스포츠 토토 PostgreSQL : 문서.
행 보안을 활성화 및 비활성화하고 테이블에 무지개 토토을 추가하는 것은 항상 테이블 소유자만의 권한입니다.
무지개 토토은 다음을 사용하여 생성됩니다.무지개 토토 생성명령, 다음을 사용하여 변경됨무지개 토토 변경명령을 수행하고를 사용하여 삭제했습니다.삭제 무지개 토토명령. 특정 테이블에 대한 행 보안을 활성화 및 비활성화하려면테이블 변경명령.
각 무지개 토토에는 이름이 있으며 테이블에 대해 여러 무지개 토토을 정의할 수 있습니다. 무지개 토토은 테이블마다 다르므로 테이블의 각 무지개 토토에는 고유한 이름이 있어야 합니다. 서로 다른 테이블에 동일한 이름의 무지개 토토이 있을 수 있습니다.
특정 쿼리에 여러 무지개 토토이 적용되면 둘 중 하나를 사용하여 결합됩니다.또는(기본값인 허용 무지개 토토의 경우) 또는 사용그리고(제한적인 무지개 토토의 경우). 이는 특정 역할이 자신이 속한 모든 역할의 권한을 갖는 규칙과 유사합니다. 허용 무지개 토토과 제한 무지개 토토은 아래에서 자세히 설명합니다.
간단한 예로 다음은 무지개 토토을 생성하는 방법입니다.계정관계는 의 구성원만 허용합니다.관리자행에 액세스하는 역할 및 계정의 행에만 액세스:
CREATE TABLE 계정(관리자 텍스트, 회사 텍스트, contact_email 텍스트);
ALTER TABLE 계정은 행 수준 보안을 활성화합니다.
관리자에게 계정에 대한 무지개 토토 account_managers 만들기
사용 중(관리자 = current_user);
위 무지개 토토은 암시적으로 다음을 제공합니다.확인 포함동일한 절사용 중절, 제약 조건이 명령에 의해 선택된 행 모두에 적용되도록 합니다(따라서 관리자는 다음을 수행할 수 없습니다.선택, 업데이트또는삭제다른 관리자에 속한 기존 행) 및 명령으로 수정된 행(따라서 다른 관리자에 속한 행은 다음을 통해 생성될 수 없음)삽입또는업데이트).
역할이 지정되지 않거나 특수 사용자 이름이 있는 경우공개을 사용하면 무지개 토토이 시스템의 모든 사용자에게 적용됩니다. 모든 사용자가 a에서 자신의 행에만 액세스할 수 있도록 하려면사용자테이블에서 간단한 무지개 토토을 사용할 수 있습니다.
사용자에 대한 무지개 토토 user_policy 만들기
USING (user_name = 현재_user);
이것은 이전 예와 유사하게 작동합니다.
표시되는 행과 비교하여 테이블에 추가되는 행에 대해 다른 무지개 토토을 사용하려면 여러 무지개 토토을 결합할 수 있습니다. 이 무지개 토토 쌍을 사용하면 모든 사용자가 다음의 모든 행을 볼 수 있습니다.사용자테이블이지만 테이블 자체만 수정:
사용자에 대한 무지개 토토 user_sel_policy 만들기
선택을 위해
사용 (사실);
사용자에 대한 무지개 토토 user_mod_policy 생성
USING (user_name = 현재_user);
에선택명령, 이 두 무지개 토토은 다음을 사용하여 결합됩니다.또는, 결과적으로 모든 행을 선택할 수 있습니다. 다른 명령 유형에서는 두 번째 무지개 토토만 적용되므로 효과는 이전과 동일합니다.
행 보안은 다음을 통해 비활성화할 수도 있습니다.테이블 변경명령. 행 보안을 비활성화해도 테이블에 정의된 무지개 토토은 제거되지 않습니다. 그들은 단순히 무시됩니다. 그러면 표준 SQL 권한 시스템에 따라 테이블의 모든 행이 표시되고 수정 가능해집니다.
다음은 이 기능이 프로덕션 환경에서 어떻게 사용될 수 있는지에 대한 더 큰 예입니다. 테이블비밀번호Unix 비밀번호 파일을 에뮬레이트합니다:
-- 간단한 비밀번호 파일 기반 예
CREATE TABLE 비밀번호(
user_name 텍스트 고유 NULL이 아님,
문자 메시지,
uid int 기본 키,
gid int는 NULL이 아닙니다.
real_name 텍스트는 NULL이 아닙니다.
집_전화 문자,
extra_info 텍스트,
home_dir 텍스트는 NULL이 아닙니다.
쉘 텍스트는 NULL이 아닙니다
);
역할 관리자 만들기; -- 관리자
역할 만들기 bob; -- 일반 사용자
역할 만들기 앨리스; -- 일반 사용자
-- 테이블 채우기
비밀번호 값에 삽입
('관리자','xxx',0,0,'관리자','111-222-3333',null,'/root','/bin/dash');
비밀번호 값에 삽입
('밥','xxx',1,1,'밥','123-456-7890',null,'/홈/밥','/bin/zsh');
비밀번호 값에 삽입
('앨리스','xxx',2,1,'앨리스','098-765-4321',null,'/home/alice','/bin/zsh');
-- 테이블에서 행 수준 보안을 활성화해야 합니다.
ALTER TABLE passwd 행 수준 보안 활성화;
-- 무지개 토토 생성
-- 관리자는 모든 행을 보고 행을 추가할 수 있습니다.
무지개 토토 생성 admin_all ON passwd TO admin USING(true) WITH CHECK(true);
-- 일반 사용자는 모든 행을 볼 수 있습니다.
무지개 토토 만들기 all_view ON passwd FOR SELECT USING(true);
-- 일반 사용자는 자신의 기록을 업데이트할 수 있지만
-- 일반 사용자가 설정할 수 있는 쉘을 제한합니다.
업데이트를 위한 비밀번호 ON 무지개 토토 user_mod 생성
사용 중(현재_사용자 = 사용자_이름)
수표 포함(
current_user = user_name 그리고
쉘 IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
);
-- 관리자에게 모든 일반 권한을 허용합니다.
관리자에게 비밀번호 선택, 삽입, 업데이트, 삭제 권한 부여;
-- 사용자는 공개 열에 대해서만 선택 액세스 권한을 가집니다.
그랜트 선택
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
ON 비밀번호를 공개로;
-- 사용자가 특정 열을 업데이트하도록 허용
보조금 업데이트
(pwhash, real_name, home_phone, extra_info, 쉘)
ON 비밀번호를 공개로;
모든 보안 설정과 마찬가지로 시스템이 예상대로 작동하는지 테스트하고 확인하는 것이 중요합니다. 위의 예를 사용하면 권한 시스템이 제대로 작동하고 있음을 알 수 있습니다.
-- 관리자는 모든 행과 필드를 볼 수 있습니다.
postgres= 관리자 역할 설정;
세트
postgres= 테이블 비밀번호;
사용자 이름 | 으악 | UID | 기드 | 실명 | 집 전화 | 엑스트라_정보 | home_dir | 껍질
-----------+---------+-----+------+------------+------------+------------+------------+-------------+------------
관리자 | xxx | 0 | 0 | 관리자 | 111-222-3333 | | /루트 | /빈/대시
밥 | xxx | 1 | 1 | 밥 | 123-456-7890 | | /집/밥 | /bin/zsh
앨리스 | xxx | 2 | 1 | 앨리스 | 098-765-4321 | | /홈/앨리스 | /bin/zsh
(3열)
-- Alice가 무엇을 할 수 있는지 테스트
postgres= 앨리스 역할 설정;
세트
postgres= 테이블 비밀번호;
오류: passwd 테이블에 대한 권한이 거부되었습니다.
postgres= passwd에서 user_name,real_name,home_phone,extra_info,home_dir,shell을 선택합니다.
사용자 이름 | 실명 | 집 전화 | 엑스트라_정보 | home_dir | 껍질
------------+------------+---------------+------------+------------+------------+------------
관리자 | 관리자 | 111-222-3333 | | /루트 | /빈/대시
밥 | 밥 | 123-456-7890 | | /집/밥 | /bin/zsh
앨리스 | 앨리스 | 098-765-4321 | | /홈/앨리스 | /bin/zsh
(3열)
postgres= 비밀번호 업데이트 set user_name = 'joe';
오류: passwd 테이블에 대한 권한이 거부되었습니다.
-- Alice는 자신의 real_name을 변경할 수 있지만 다른 사람은 변경할 수 없습니다.
postgres= 업데이트 비밀번호 설정 real_name = 'Alice Doe';
업데이트 1
postgres= 비밀번호 업데이트 set real_name = 'John Doe' 여기서 user_name = 'admin';
업데이트 0
postgres= update passwd set shell = '/bin/xx';
오류: 새 행이 "passwd"에 대한 WITH CHECK OPTION을 위반합니다.
postgres= 비밀번호에서 삭제;
오류: passwd 테이블에 대한 권한이 거부되었습니다.
postgres= passwd(user_name) 값('xxx')에 삽입;
오류: passwd 테이블에 대한 권한이 거부되었습니다.
-- Alice는 자신의 비밀번호를 변경할 수 있습니다. RLS는 자동으로 다른 행 업데이트를 방지합니다.
postgres= 비밀번호 업데이트 set pwhash = 'abc';
업데이트 1
지금까지 구축된 모든 무지개 토토은 허용 무지개 토토이었습니다. 즉, 여러 무지개 토토이 적용될 때 다음을 사용하여 결합됩니다.“또는”부울 연산자. 허용 무지개 토토은 의도된 경우에만 행에 대한 액세스를 허용하도록 구성할 수 있지만 허용 무지개 토토과 제한 무지개 토토을 결합하는 것이 더 간단할 수 있습니다(레코드가 통과해야 하며 다음을 사용하여 결합됨).“그리고”부울 연산자). 위의 예를 바탕으로 관리자가 로컬 Unix 소켓을 통해 연결하여 레코드에 액세스하도록 요구하는 제한적인 무지개 토토을 추가합니다.비밀번호테이블:
관리자에게 제한적인 비밀번호로 무지개 토토 admin_local_only ON 무지개 토토 만들기
USING(pg_catalog.inet_client_addr()은 NULL입니다);
그러면 제한 무지개 토토으로 인해 네트워크를 통해 연결하는 관리자가 어떤 기록도 볼 수 없음을 알 수 있습니다.
= 현재_사용자 선택; 현재_사용자 -------------- 관리자 (1줄) = inet_client_addr()을 선택합니다. inet_client_addr ------------------ 127.0.0.1 (1줄) = 현재_사용자 선택; 현재_사용자 -------------- 관리자 (1줄) = 테이블 비밀번호; 사용자 이름 | 으악 | UID | 기드 | 실명 | 집 전화 | 엑스트라_정보 | home_dir | 껍질 -----------+---------+------+------+------------+------------+------------+------------+----------+--------- (0행) = UPDATE passwd set pwhash = NULL; 업데이트 0
고유 키 또는 기본 키 제약 조건 및 외래 키 참조와 같은 참조 무결성 검사는 항상 행 보안을 우회하여 데이터 무결성이 유지되는지 확인합니다. 방지하기 위해 스키마 및 행 수준 무지개 토토을 개발할 때 주의를 기울여야 합니다.“비밀 채널”그러한 참조 무결성 검사를 통한 정보 유출.
일부 상황에서는 행 보안이 적용되지 않는지 확인하는 것이 중요합니다. 예를 들어, 백업을 수행할 때 행 보안으로 인해 자동으로 일부 행이 백업에서 생략되면 재앙이 될 수 있습니다. 이러한 상황에서는 다음을 설정할 수 있습니다.row_security구성 매개변수꺼짐. 이는 그 자체로 행 보안을 우회하지 않습니다. 쿼리 결과가 무지개 토토에 의해 필터링되면 오류가 발생합니다. 그런 다음 오류의 원인을 조사하고 수정할 수 있습니다.
위의 예에서 무지개 토토 표현식은 액세스하거나 업데이트할 행의 현재 값만 고려합니다. 이는 가장 간단하고 성능이 가장 뛰어난 사례입니다. 가능하다면 이러한 방식으로 작동하도록 행 보안 애플리케이션을 설계하는 것이 가장 좋습니다. 무지개 토토 결정을 내리기 위해 다른 행이나 다른 테이블을 참조해야 하는 경우 sub-를 사용하여 수행할 수 있습니다.선택s 또는 다음을 포함하는 함수선택s, 무지개 토토 표현식에서. 그러나 이러한 액세스는 주의를 기울이지 않으면 정보 유출을 허용할 수 있는 경쟁 조건을 만들 수 있다는 점에 유의하십시오. 예를 들어 다음 테이블 디자인을 고려해보세요.
-- 권한 그룹 정의
CREATE TABLE 그룹(group_id int PRIMARY KEY,
그룹_이름 텍스트는 NULL이 아님);
그룹 값에 삽입
(1, '낮음'),
(2, '중간'),
(5, '높음');
그룹에 모든 권한을 alice에게 부여합니다. -- Alice는 관리자입니다.
그룹에 대한 선택을 공개로 부여합니다.
-- 사용자의 권한 수준 정의
CREATE TABLE 사용자(user_name 텍스트 PRIMARY KEY,
group_id int NOT NULL REFERENCES 그룹);
사용자 값에 삽입
('앨리스', 5),
('밥', 2),
('말로리', 2);
모든 사용자에게 앨리스에게 부여;
사용자에게 공개 선택 권한을 부여합니다.
-- 보호할 정보가 들어 있는 테이블
CREATE TABLE 정보(정보 텍스트,
group_id int NOT NULL REFERENCES 그룹);
정보 가치에 삽입
('거의 비밀', 1),
('약간 비밀', 2),
('매우 비밀', 5);
ALTER TABLE 정보 ENABLE ROW LEVEL SECURITY;
-- 보안 그룹 ID가 다음과 같은 사용자가 행을 볼 수 있거나 업데이트할 수 있어야 합니다.
-- 행의 group_id보다 크거나 같음
SELECT에 대한 정보에 대한 무지개 토토 fp_s 생성
USING (group_id <= (사용자 WHERE user_name = 현재_user에서 group_id 선택));
업데이트에 대한 정보에 대한 무지개 토토 fp_u 만들기
USING (group_id <= (사용자 WHERE user_name = 현재_user에서 group_id 선택));
-- 정보 테이블을 보호하기 위해 RLS에만 의존합니다.
모든 정보를 공개적으로 부여합니다.
이제 가정해 보겠습니다.앨리스다음을 변경하고 싶습니다“약간의 비밀”정보이지만 결정합니다말로리해당 행의 새로운 내용을 신뢰해서는 안 됩니다. 그래서 그녀는 다음과 같이 합니다:
시작; 사용자 업데이트 SET group_id = 1 WHERE user_name = 'mallory'; 업데이트 정보 SET 정보 = 'malory의 비밀' WHERE group_id = 2; 커밋;
안전해 보이는군요. 창문이 없습니다.말로리다음을 볼 수 있어야 합니다“말로리의 비밀”문자열. 그러나 여기에는 경쟁 조건이 있습니다. 만일말로리동시에 다음을 수행하고 있습니다.
SELECT * FROM 정보 WHERE group_id = 2 FOR UPDATE;
그리고 그녀의 거래가 진행 중입니다읽어 커밋됨모드, 그녀가 볼 수 있습니다“말로리의 비밀”. 그녀의 거래가 다음 단계에 도달하면 이런 일이 발생합니다.정보바로 뒤의 행앨리스이 그렇습니다. 기다리는 동안 차단됩니다.앨리스의 트랜잭션을 커밋한 다음, 다음 덕분에 업데이트된 행 내용을 가져옵니다.업데이트용절. 그러나 그렇습니다.아님암시적에 대한 업데이트된 행을 가져옵니다선택에서사용자, 왜냐하면 그 하위-SELECT없었습니다업데이트용; 대신에사용자행은 쿼리 시작 시 생성된 스냅샷으로 읽혀집니다. 따라서 무지개 토토 표현식은의 이전 값을 테스트합니다.말로리의 권한 수준을 확인하고 업데이트된 행을 볼 수 있도록 허용합니다.
이 문제를 해결하는 방법에는 여러 가지가 있습니다. 간단한 대답 중 하나는 다음을 사용하는 것입니다.선택 ... 공유용하위-선택26676_26738업데이트참조 테이블에 대한 권한(여기사용자)가 영향을 받는 사용자에게 전달되며 이는 바람직하지 않을 수 있습니다. (그러나 실제로 해당 권한을 행사하는 것을 방지하기 위해 다른 행 보안 무지개 토토을 적용할 수도 있습니다. 또는 하위-SELECT보안 정의자 함수에 내장될 수 있습니다.) 또한 참조 테이블에서 행 공유 잠금을 과도하게 동시에 사용하면 성능 문제가 발생할 수 있으며, 특히 업데이트가 자주 발생하는 경우에는 더욱 그렇습니다. 참조된 테이블의 업데이트가 자주 발생하지 않는 경우 실용적인 또 다른 솔루션은 다음을 수행하는 것입니다.접속 독점업데이트 시 참조 테이블을 잠그므로 동시 트랜잭션이 이전 행 값을 검사할 수 없습니다. 또는 참조 테이블의 업데이트를 커밋한 후 새로운 보안 상황에 따라 변경을 수행하기 전에 모든 동시 트랜잭션이 종료될 때까지 기다릴 수도 있습니다.