[Database] PostgreSQL Commands - DCL

개요

지난 시간에는 PostgreSQL 데이터베이스의 DML 에 대해서 알아보았습니다. 이번 시간에는 PostgreSQL의 DCL 및 권한 관리 부분에 대해서 알아보겠습니다.

---

Privileges (권한)

데이터 개체가 생성되면 해당 개체에는 소유자가 할당됩니다. 소유자는 일반적으로 생성문을 실행한 역할입니다. 대부분의 개체 종류에 대해 초기 상태는 소유자(혹은 슈퍼 어드민) 만 개체에 대한 모든 권한을 가지고 있습니다. 다른 역할에서 개체를 사용할 수 있도록 하려면 권한이 부여되어야 합니다.

 

SELECT, INSERT, UPDATE, DELETE, TRUNKATE, REFERENCE, TRIGER, CREATE, CONNECT, TERIVE, EXECURE, USE, SET, ALTER SYSTEM 등등 다양한 종류의 권한이 존재합니다. 특정 개체에 적용할 수 있는 권한은 개체의 유형 (Table, Function, etc...) 에 따라 달라집니다.

 

개체를 수정하거나 삭제할 권한은 개체의 소유자에게 자동 내재되어 있으며, 그 자체로 부여되거나 취소될 수 없습니다. (디단, 모든 권한과 마찬가지로 그 권리는 소유자 역할의 구성원이 상속할 수 있습니다)

 

개체에 대해 적절한 종류의 ALTER 명령을 사용하여 새 소유자에게 개체를 할당할 수 있습니다.

ALTER TABLE table_name OWNER TO new_owner;

 

슈퍼 어드민은 항상 이 작업을 수행할 수 있습니다. 일반적으로는 개체의 현재 소유자 (또는 소유 역할의 권한을 상속받은 사용자) 가 작업을 수행할 수 있으며, 새 소유 역할에 역할을 설정할 수 있는 경우에만 수행할 수 있습니다.

 

권한을 할당하기 위해서는 GRANT 명령어를 사용합니다.

# Joe 가 기존 역할이고 계정이 기존 테이블인 경우 테이블을 업데이트 할 수 있는 권한을 부여할 수 있습니다
GRANT UPDATE ON accounts TO joe;

 

특정 권한 대신에 ALL 옵션을 사용하면 개체 유형과 관련된 모든 권한이 부여됩니다.

 

특별한 "역할" 이름 PUBLIC 은 시스템의 모든 역할에 권한을 부여하는 데 사용될 수 있습니다.

 

이전에 부여된 권한을 취소하려면 적합한 이름의 REVOKE 명령을 사용합니다.

REVOKE ALL ON accounts FROM PUBLIC;

 

---

Row Security Policies

GRANT 를 통해 사용할 수 있는 SQL 표준 권한 시스템 외에도 테이블에는 일반 쿼리로 어떤 행을 반환하거나 데이터 수정 명령으로 삽입, 업데이트 또는 삭제할 수 있는지 사용자별로 제한하는 행 보안 정책이 있을 수 있습니다. 이 기능을 Row-Level Security 라고도 합니다. 기본적으로 테이블에는 정책이 없으므로 SQL 권한 시스템에 따라 사용자가 테이블에 대한 액세스 권한이 있는 경우 테이블 내의 모든 행이 쿼리 또는 업데이트에 동일하게 사용할 수 있습니다.

 

테이블에서 행 보안을 사용하도록 설정한 경우 (ALTER TABLE ... ENABLE ROW LEVEL SECURITY) 행을 조회하거나 수정하기 위한 테이블에 대한 모든 일반 액세스가 행 보안 정책에 의해 허용되어야 합니다. 그러나 테이블의 소유자는 일반적으로 행 보안 정책의 적용을 받지 않습니다. 테이블에 대한 정책이 없는 경우 기본 거부 정책이 사용되며, 이는 행이 조회되거나 수정될 수 없음을 의미합니다. TRUNCATE 와 REFERENCES 같이 전체 테이블에 적용되는 작업은 행 보안의 적용을 받지 않습니다.

 

행 보안 정책은 명령문이나 역할 혹은 둘 다에 지정할 수 있습니다. 정책은 모든 명령에 적용하거나 SELECT, INSERT, UPDATE 혹은 DELETE 에 적용하도록 지정할 수 있습니다. 지정된 정책에 여러 역할을 할당할 수 있으며 일반 역할 구성원 자격 및 상속 규칙이 적용됩니다.

 

정책에 따라 조회되거나 수정할 수 있는 행을 지정하려면 Boolean 결과를 반환하는 식을 필요로 합니다. 이 식은 사용자의 쿼리에서 나오는 모든 조건이나 함수보다 먼저 각 행에 대해 평가됩니다. (이 규칙의 유일한 예외는 정보가 누출되지 않도록 보장되는 누출 방지 함수입니다. Optimizer 는 행 보안 검사 전에 이러한 함수를 적용하도록 선택할 수 있습니다.) 식을 true 값으로 반환하지 않는 행은 처리되지 않습니다. 표시되는 행과 수정할 수 있는 행에 대한 독립적인 제어를 제공하기 위해 별도의 식을 지정할 수 있습니다. 정책 식은 호출하는 사용자가 사용할 수 없는 데이터에 액세스 하는 데 보안 정의자 함수를 사용할 수 있지만, 쿼리의 일부와 쿼리를 실행하는 사용자의 권한으로 실행됩니다.

 

BYPASSRLS 특성을 가진 슈퍼 유저 및 역할은 테이블에 액세스할 때 항상 행 보안 시스템을 우회합니다. 테이블 소유자는 일반적으로 행 보안을 우회하지만 테이블 소유자는 ALTER TABLE ... FORCE ROW LEVEL SECURITY 명령문을 통해 행 보안을 적용받도록 선택할 수 있습니다.

 

행 보안을 활성화 및 비활성화하고 테이블에 정책을 추가하는 것은 항상 테이블 소유자의 권한입니다.

 

CREATE POLICY 명령을 사용하여 정책을 만들고 ALTER POLICY 명령을 사용하여 변경하며, DROP POLICY 명령을 사용하여 정책을 삭제합니다. 지정된 테이블의 행 보안을 활성화하고 비활성화하려면 ALTER TABLE 명령을 사용합니다.

 

각 정책에는 이름이 있으며 테이블에 대해 여러 정책을 정의할 수 있습니다. 정책은 테이블에 따라 다르므로 테이블에 대한 각 정책에는 고유한 이름이 있어야 합니다. 다른 테이블에는 동일한 이름의 정책이 있을 수 있습니다.

 

여러 정책이 주어진 쿼리에 적용되면 OR (기본값인 허용 정책의 경우) 또는 AND (제한 정책의 경우)를 사용하여 결합됩니다. 이는 주어진 역할이 구성원인 모든 역할의 권한을 갖는다는 규칙과 유사합니다. 허용 대 제한 정책에 대해서는 아래에서 자세히 설명합니다.

 

간단한 예제로, 다음은 관리자 역할의 구성원만 행에 액세스하고, 본인 계정의 행에만 액세스할 수 있도록 계정 관계에 대한 정책을 만드는 방법입니다.

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 구문을 암묵적으로 제공하므로 제약 조건은 명령에 의해 선택된 행과 명령에 의해 수정된 행에 모두 적용됩니다.

 

역할이 지정되지 않았거나 특수 사용자 이름 PUBLIC 을 사용하는 경우 정책은 시스템의 모든 사용자에게 적용됩니다. 모든 사용자가 사용자 테이블에서 자신의 행에만 액세스할 수 있도록 하려면 다음과 같이 설정할 수 있습니다.

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

 

위 작업은 이전의 예제와 유사하게 동작합니다.

 

테이블에 추가되는 행에 대해 보이는 행과 비교하여 다른 정책을 사용하려면 이러한 정책을 조합할 수 있습니다. 이 정책 쌍을 사용하면 모든 사용자가 사용자 테이블의 모든 행을 볼 수 있지만 자신의 행만 수정할 수 있습니다.

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 권한 시스템에 따라 달라집니다.

 

아래는 이 기능이 프로덕션 환경에서 어떻게 사용될 수 있는지에 대한 더 큰 예입니다. 테이블 암호는 유닉스 암호 파일을 가상화 시킵니다.

 

-- 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

 

지금까지 구축된 모든 정책은 허용 정책이었으며, 이는 여러 정책이 적용될 때 "OR" Bool 연산자를 사용하여 결합된다는 것을 의미합니다. 허용 정책은 의도된 경우 행에 대한 액세스만 허용하도록 구축될 수 있지만, 허용 정책을 제한 정책과 결합하는 것이 더 간단할 수 있습니다. 위의 예제를 기반으로, 관리자가 암호 테이블의 레코드에 액세스하기 위해 로컬 유닉스 소켓을 통해 연결해야 하는 제한 정책을 추가해보겠습니다.

 

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

 

고유 또는 기본 키 제약 조건 및 외부 키 참조와 같은 참조 무결성 검사는 데이터 무결성이 유지되도록 항상 행 보안을 우회합니다. 이러한 참조 무결성 검사를 통해 정보의 "COVERT CHANNEL" 유출을 방지하기 위해 스키마 및 행 수준 정책을 개발할 때 주의해야 합니다.

 

일부 상황에서는 행 보안이 적용되지 않는지 확인하는 것이 중요합니다. 예를 들어, 백업을 수행할 때 행 보안 때문에 백업에서 일부 행이 자동으로 누락되는 경우가 발생할 수 있습니다. 이러한 상황에서는 row_security 구성 매개 변수를 off 로 설정할 수 있습니다. 이는 그 자체로 행 보안을 우회하는 것이 아니라, 쿼리 결과가 정책에 의해 필터링될 경우 오류를 던지는 것입니다. 이후에 오류의 원인을 디버깅할 수 있습니다.

 

위의 예제에서 정책 식은 액세스하거나 업데이트할 행의 현재 값만 고려합니다. 이는 가장 간단하고 성능이 가장 우수한 경우이며, 가능하면 이러한 방식으로 작동할 행 보안 응용 프로그램을 설계하는 것이 가장 좋습니다. 정책 결정을 위해 다른 행 또는 다른 테이블을 참조해야 하는 경우, 정책 식에서 하위 SELECT 또는 SELECT를 포함하는 함수를 사용하여 수행할 수 있습니다. 그러나 이러한 접근은 주의를 기울이지 않으면 정보 유출을 허용할 수 있는 취약 지점을 만들 수 있습니다.

---

여기까지 PostgreSQL 만의 Policy 에 대해 알아보았습니다.

 

출처

https://www.postgresql.org/docs/current/ddl-rowsecurity.html