행 보안 정책
행 보안 정책 (Row Security Policies)
SQL 표준 권한 시스템(GRANT)은 "테이블 단위"로 접근을 통제해요. 하지만 가끔은 "사용자별로 특정 행에만 접근하게" 하고 싶을 때가 있어요. 예를 들어 직원이 자기 회사의 데이터만 보게 한다든가요. 그럴 때 쓰는 것이 행 보안 정책(row security policy)이고, 이 기능을 **행 수준 보안(Row-Level Security)**이라고도 불러요. 기본적으로 테이블에는 정책이 없어서, SQL 권한 시스템에 따라 접근 권한이 있으면 모든 행을 똑같이 조회·갱신할 수 있어요.
출처: 공식문서
어떻게 동작하는가
테이블에서 행 보안을 활성화하면(ALTER TABLE ... ENABLE ROW LEVEL SECURITY), 그 테이블의 행을 선택·수정하는 모든 일반 접근은 행 보안 정책에 의해 허용되어야 해요. 단, 테이블 소유자는 보통 행 보안 정책의 적용을 받지 않아요. 테이블에 정책이 없으면 기본 거부(default-deny) 정책이 적용돼서 어떤 행도 보이지 않거나 수정할 수 없어요. 테이블 전체에 적용되는 작업(TRUNCATE, REFERENCES 등)은 행 보안의 적용을 받지 않아요.
행 보안 정책은 명령별로, 롤별로, 또는 둘 다로 특정할 수 있어요. 정책이 ALL, SELECT, INSERT, UPDATE, DELETE 중 어떤 명령에 적용될지 지정할 수 있죠. 여러 롤을 한 정책에 배정할 수 있고, 일반적인 롤 멤버십·상속 규칙이 적용돼요.
정책 표현식
정책에 따라 어떤 행이 보이거나 수정 가능한지 지정하려면 Boolean 결과를 반환하는 표현식이 필요해요. 이 표현식은 사용자 쿼리에서 오는 조건이나 함수보다 먼저 각 행에 대해 평가돼요. (예외는 leakproof 함수인데, 이 함수는 정보를 누출하지 않는다고 보장되어서 옵티마이저가 행 보안 검사보다 앞서 적용할 수 있어요.) 표현식이 true를 반환하지 않는 행은 처리되지 않아요. 보이는 행과 수정이 허용되는 행을 독립적으로 통제하도록 별도 표현식을 지정할 수 있어요. 정책 표현식은 쿼리의 일부로, 쿼리를 실행하는 사용자의 권한으로 실행돼요.
우회 규칙
슈퍼유저와 BYPASSRLS 속성을 가진 롤은 테이블에 접근할 때 항상 행 보안 시스템을 우회해요. 테이블 소유자도 보통 행 보안을 우회하지만, ALTER TABLE ... FORCE ROW LEVEL SECURITY로 행 보안의 적용을 받기로 선택할 수 있어요. 행 보안을 켜고 끄는 것과 테이블에 정책을 추가하는 것은 항상 테이블 소유자만의 권한이에요.
정책 만들고 고치기
정책은 CREATE POLICY로 만들고, ALTER POLICY로 바꾸고, DROP POLICY로 지워요. 테이블에 행 보안을 켜고 끄려면 ALTER TABLE을 써요.
각 정책은 이름을 가지며 한 테이블에 여러 정책을 정의할 수 있어요. 정책은 테이블별이므로 한 테이블의 각 정책 이름은 유일해야 해요. 다른 테이블은 같은 이름의 정책을 가질 수 있어요.
여러 정책이 한 쿼리에 적용되면, 허용적(permissive) 정책(기본값)은 OR로, 제한적(restrictive) 정책은 AND로 결합돼요. OR 동작은 "특정 롤은 그것이 멤버인 모든 롤의 권한을 가진다"는 규칙과 비슷해요.
예제: managers 롤만 자기 계정 접근
간단한 예로, account 릴레이션에 managers 롤 멤버만 접근하게 하고 그들 계정의 행만 접근하게 하는 정책을 만들어 볼게요:
CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers
USING (manager = current_user);
위 정책은 암묵적으로 USING 절과 동일한 WITH CHECK 절을 제공해서, 제약이 명령에 의해 선택되는 행과 명령에 의해 수정되는 행 둘 다에 적용돼요. (그래서 매니저는 다른 매니저에 속한 기존 행을 SELECT·UPDATE·DELETE할 수 없고, INSERT·UPDATE로 다른 매니저의 행을 만들 수도 없어요.)
롤을 지정하지 않거나 특수 사용자 이름 PUBLIC을 쓰면 정책이 시스템의 모든 사용자에게 적용돼요. users 테이블에서 모든 사용자가 자기 행에만 접근하게 하려면:
CREATE POLICY user_policy ON users
USING (user_name = current_user);
보이는 행과 추가되는 행에 다른 정책
보이는 행과 테이블에 추가되는 행에 다른 정책을 쓰려면 여러 정책을 결합할 수 있어요. 이 정책 쌍은 모든 사용자가 users 테이블의 모든 행은 보게 하되, 자기 행만 수정하게 해요:
CREATE POLICY user_sel_policy ON users
FOR SELECT
USING (true);
CREATE POLICY user_mod_policy ON users
USING (user_name = current_user);
SELECT 명령에서는 두 정책이 OR로 결합되어 모든 행을 선택할 수 있고, 다른 명령 타입에서는 두 번째 정책만 적용되므로 결과가 이전과 같아요.
끄기
행 보안은 ALTER TABLE 명령으로 끌 수도 있어요. 끈다고 테이블에 정의된 정책이 제거되진 않고, 그냥 무시될 뿐이에요. 그러면 표준 SQL 권한 시스템에 따라 테이블의 모든 행이 보이고 수정 가능해져요.
실무 예제: passwd 테이블
이 기능이 운영 환경에서 어떻게 쓰이는지 큰 예를 볼게요. passwd 테이블이 Unix 비밀번호 파일을 흉내 내요:
-- Simple passwd-file based example
CREATE TABLE passwd (
user_name text UNIQUE NOT NULL,
pwhash text,
uid int PRIMARY KEY,
gid int NOT NULL,
real_name text NOT NULL,
home_phone text,
extra_info text,
home_dir text NOT NULL,
shell text NOT NULL
);
CREATE ROLE admin; -- Administrator
CREATE ROLE bob; -- Normal user
CREATE ROLE alice; -- Normal user
-- Populate the table
INSERT INTO passwd VALUES
('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash');
INSERT INTO passwd VALUES
('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');
INSERT INTO passwd VALUES
('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');
-- Be sure to enable row-level security on the table
ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;
-- Create policies
-- Administrator can see all rows and add any rows
CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);
-- Normal users can view all rows
CREATE POLICY all_view ON passwd FOR SELECT USING (true);
-- Normal users can update their own records, but
-- limit which shells a normal user is allowed to set
CREATE POLICY user_mod ON passwd FOR UPDATE
USING (current_user = user_name)
WITH CHECK (
current_user = user_name AND
shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
);
-- Allow admin all normal rights
GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;
-- Users only get select access on public columns
GRANT SELECT
(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
ON passwd TO public;
-- Allow users to update certain columns
GRANT UPDATE
(pwhash, real_name, home_phone, extra_info, shell)
ON passwd TO public;
보안 설정은 어느 것이든 실제로 예상대로 동작하는지 테스트하고 확인하는 게 중요해요. 위 예제를 쓰면 권한 시스템이 제대로 동작함을 보여줘요:
-- admin can view all rows and fields
postgres=> set role admin;
SET
postgres=> table passwd;
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dash
bob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
-- Test what Alice is able to do
postgres=> set role alice;
SET
postgres=> table passwd;
ERROR: permission denied for table passwd
postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
user_name | real_name | home_phone | extra_info | home_dir | shell
-----------+-----------+--------------+------------+-------------+-----------
admin | Admin | 111-222-3333 | | /root | /bin/dash
bob | Bob | 123-456-7890 | | /home/bob | /bin/zsh
alice | Alice | 098-765-4321 | | /home/alice | /bin/zsh
(3 rows)
postgres=> update passwd set user_name = 'joe';
ERROR: permission denied for table passwd
-- Alice is allowed to change her own real_name, but no others
postgres=> update passwd set real_name = 'Alice Doe';
UPDATE 1
postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0
postgres=> update passwd set shell = '/bin/xx';
ERROR: new row violates WITH CHECK OPTION for "passwd"
postgres=> delete from passwd;
ERROR: permission denied for table passwd
postgres=> insert into passwd (user_name) values ('xxx');
ERROR: permission denied for table passwd
-- Alice can change her own password; RLS silently prevents updating other rows
postgres=> update passwd set pwhash = 'abc';
UPDATE 1
제한적(RESTRICTIVE) 정책
지금까지 만든 정책은 모두 허용적(permissive) 정책이에요. 여러 정책이 적용될 때 OR로 결합된다는 뜻이죠. 허용적 정책만으로 의도한 경우에만 접근을 허용하도록 구성할 수도 있지만, 허용적 정책과 제한적 정책(기록이 통과해야 하며 AND로 결합됨)을 결합하는 게 더 단순할 수 있어요. 위 예제에, 관리자가 passwd 테이블 기록에 접근하려면 로컬 Unix 소켓으로 연결해야 한다는 제한적 정책을 추가해 볼게요:
CREATE POLICY admin_local_only ON passwd AS RESTRICTIVE TO admin
USING (pg_catalog.inet_client_addr() IS NULL);
그러면 네트워크로 연결한 관리자는 제한적 정책 때문에 어떤 기록도 못 볼 거예요:
=> SELECT current_user;
current_user
--------------
admin
(1 row)
=> select inet_client_addr();
inet_client_addr
------------------
127.0.0.1
(1 row)
=> TABLE passwd;
user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell
-----------+--------+-----+-----+-----------+------------+------------+----------+-------
(0 rows)
=> UPDATE passwd set pwhash = NULL;
UPDATE 0
참조 무결성과 시한 채널
unique·primary key 제약 조건과 foreign key 참조 같은 참조 무결성 검사는 데이터 무결성을 유지하기 위해 항상 행 보안을 우회해요. 스키마와 행 수준 정책을 개발할 때는 그런 참조 무결성 검사를 통한 은닉 채널(covert channel) 정보 누출을 피하도록 주의해야 해요.
row_security 파라미터
어떤 상황에서는 행 보안이 적용되지 않는 게 확실해야 할 때가 있어요. 예를 들어 백업을 받을 때 행 보안이 조용히 일부 행을 백업에서 빼버리면 치명적이겠죠. 그럴 때는 row_security 설정 파라미터를 off로 설정할 수 있어요. 이건 행 보안을 우회하는 게 아니라, 어떤 쿼리 결과가 정책에 의해 필터링되면 오류를 던지게 해요. 그래서 오류 원인을 조사하고 고칠 수 있는 거죠.
정책 표현식이 다른 행·테이블을 참조할 때의 경쟁 조건
위 예제들의 정책 표현식은 접근·갱신하려는 행의 현재 값만 고려해요. 이게 가장 단순하고 성능도 가장 좋은 경우라, 가능하면 이렇게 설계하는 게 좋아요. 다른 행이나 다른 테이블을 참조해야 한다면 정책 표현식의 서브쿼리나 SELECT를 포함한 함수로 할 수 있어요. 하지만 그런 접근은 주의하지 않으면 정보 누출로 이어질 수 있는 경쟁 조건(race condition)을 만들 수 있다는 점을 명심하세요.
예를 들어 이런 테이블 설계를 볼게요:
-- definition of privilege groups
CREATE TABLE groups (group_id int PRIMARY KEY,
group_name text NOT NULL);
INSERT INTO groups VALUES
(1, 'low'),
(2, 'medium'),
(5, 'high');
GRANT ALL ON groups TO alice; -- alice is the administrator
GRANT SELECT ON groups TO public;
-- definition of users' privilege levels
CREATE TABLE users (user_name text PRIMARY KEY,
group_id int NOT NULL REFERENCES groups);
INSERT INTO users VALUES
('alice', 5),
('bob', 2),
('mallory', 2);
GRANT ALL ON users TO alice;
GRANT SELECT ON users TO public;
-- table holding the information to be protected
CREATE TABLE information (info text,
group_id int NOT NULL REFERENCES groups);
INSERT INTO information VALUES
('barely secret', 1),
('slightly secret', 2),
('very secret', 5);
ALTER TABLE information ENABLE ROW LEVEL SECURITY;
-- a row should be visible to/updatable by users whose security group_id is
-- greater than or equal to the row's group_id
CREATE POLICY fp_s ON information FOR SELECT
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
-- we rely only on RLS to protect the information table
GRANT ALL ON information TO public;
이제 alice가 "slightly secret" 정보를 바꾸고 싶은데, mallory가 그 행의 새 내용을 신뢰해서는 안 된다고 결정했다고 해볼게요:
BEGIN;
UPDATE users SET group_id = 1 WHERE user_name = 'mallory';
UPDATE information SET info = 'secret from mallory' WHERE group_id = 2;
COMMIT;
안전해 보이죠. mallory가 "secret from mallory" 문자열을 볼 수 있는 틈이 없어 보이는데요. 하지만 여기 경쟁 조건이 있어요. mallory가 동시에 이런 쿼리를 실행 중이라면:
SELECT * FROM information WHERE group_id = 2 FOR UPDATE;
그리고 그녀의 트랜잭션이 READ COMMITTED 모드라면, 그녀가 "secret from mallory"를 볼 수 있어요. alice의 트랜잭션 직후에 information 행에 도달하면 그런 일이 일어나요. 그녀는 alice의 트랜잭션 커밋을 기다리며 블록되고, FOR UPDATE 절 덕분에 갱신된 행 내용을 가져오거든요. 그런데 users에서의 암묵적 SELECT는 갱신된 행을 가져오지 못해요. 그 서브쿼리에는 FOR UPDATE가 없어서, users 행은 쿼리 시작 시점의 스냅샷으로 읽히거든요. 그래서 정책 표현식이 mallory 보안 수준의 옛 값을 검사해, 그녀가 갱신된 행을 보게 허용해버려요.
이 문제를 피하는 방법이 몇 가지 있어요. 한 가지 간단한 답은 행 보안 정책의 서브쿼리에서 SELECT ... FOR SHARE를 쓰는 거예요. 다만 참조된 테이블(여기선 users)에 대한 UPDATE 권한을 관련 사용자에게 부여해야 한다는 점이 바람직하지 않을 수 있죠. (다른 행 보안 정책으로 그 권한을 실제로 행사하지 못하게 막거나, 서브쿼리를 security definer 함수에 넣을 수도 있어요.) 참조된 테이블에 대해 행 공유 잠금을 과하게 동시 사용하면 성능 문제가 될 수도 있어요. 또 다른 해법은, 참조 테이블의 갱신이 드물다면, 갱신할 때 참조된 테이블에 ACCESS EXCLUSIVE 잠금을 걸어 동시 트랜잭션이 예전 행 값을 검사하지 못하게 하는 거예요.
자세한 내용은 CREATE POLICY와 ALTER TABLE 참고 문서를 보세요.