[Database] PostgreSQL - 권한, 스키마, 상속, 파티셔닝

개요

이전 게시글에서는 PostgreSQL 테이블의 기본 사항, 제약 조건을 통한 데이터 무결성 보장 방법, 기존 테이블 구조 수정 방법을 살펴보았습니다. 이번 게시글에서는 데이터베이스 관리 및 설계를 더욱 향상시키는 PostgreSQL의 고급 데이터 정의 기능을 더 깊이 탐구합니다. 여기에는 사용자 접근을 제어하는 권한, 세분화된 접근 제어를 위한 행 수준 보안 정책, 데이터베이스 객체 구성을 위한 스키마, 코드 재사용을 위한 테이블 상속, 대규모 테이블 관리를 위한 파티셔닝, 외부 데이터 소스 접근, 그리고 객체 간의 종속성 추적 등이 포함됩니다.
 

---

섹션 1: 권한을 통한 접근 관리

데이터베이스 보안의 핵심은 사용자가 수행할 수 있는 작업을 제어하는 것입니다. PostgreSQL은 강력한 권한 시스템을 통해 이를 관리합니다.
 

1.1 권한 개념 및 관리

PostgreSQL에서 권한은 특정 데이터베이스 객체(테이블, 스키마, 함수 등)에 대해 특정 역할(사용자 또는 그룹)이 수행할 수 있는 작업을 정의합니다. 객체 소유자(일반적으로 객체를 생성한 사용자) 또는 슈퍼유저는 이러한 권한을 부여하거나 취소할 수 있습니다.

• GRANT 명령: 특정 객체에 대한 권한을 역할에 부여하는데 사용됩니다.

-- joe 역할에게 accounts 테이블에 대한 UPDATE 권한 부여
GRANT UPDATE ON accounts TO joe;

-- 모든 역할(PUBLIC)에게 특정 스키마 사용 권한 부여
GRANT USAGE ON SCHEMA my_schema TO PUBLIC;

• REVOKE 명령: 이전에 부여된 권한을 역할에서 제거하는 데 사용됩니다.

-- joe 역할로부터 accounts 테이블에 대한 UPDATE 권한 취소
REVOKE UPDATE ON accounts FROM joe;

-- 특정 역할로부터 특정 테이블에 대한 모든 권한 취소
REVOKE ALL ON film FROM jim;

 

1.2 주요 권한 유형

PostgreSQL은 다양한 객체 유형에 적용되는 여러 권한 유형을 제공합니다. 주요 권한 유형은 다음과 같습니다:

권한	약어 (ACL)	적용 대상 예시	설명
SELECT	r ("read")	테이블, 뷰, 시퀀스, 열	객체에서 데이터를 읽을 수 있는 권한 (예: SELECT 명령)
INSERT	a ("append")	테이블, 열	테이블에 새 행을 삽입할 수 있는 권한 (예: INSERT 명령)
UPDATE	w ("write")	테이블, 열	테이블의 기존 행에 있는 열 값을 업데이트할 수 있는 권한 (예: UPDATE 명령)
DELETE	d	테이블	테이블에서 행을 삭제할 수 있는 권한 (예: DELETE 명령)
TRUNCATE	t	테이블	테이블의 모든 행을 빠르게 제거할 수 있는 권한 (예: TRUNCATE 명령)
REFERENCES	x	테이블, 열	외래 키 제약 조건을 생성할 수 있는 권한. 열 수준 또는 테이블 수준에서 부여해야 함.
TRIGGER	t	테이블	테이블에 트리거를 생성할 수 있는 권한.
CREATE	C	데이터베이스, 스키마, 테이블스페이스	데이터베이스의 경우 새 스키마 생성을 허용. 스키마의 경우 스키마 내에 새 객체 생성을 허용. 테이블스페이스의 경우 테이블, 인덱스 등을 생성할 수 있음.
CONNECT	c	데이터베이스	데이터베이스에 연결할 수 있는 권한.
TEMPORARY / TEMP	T	데이터베이스	데이터베이스 사용 시 임시 테이블을 생성할 수 있는 권한.
EXECUTE	X	함수, 프로시저	함수 또는 프로시저를 실행하거나 정의에 지정된 연산자를 사용할 수 있는 권한.
USAGE	U	스키마, 시퀀스, 데이터 타입, 외부 데이터 래퍼, 외부 서버, 언어	스키마의 경우 스키마 내 객체 접근 허용. 시퀀스의 경우 currval, nextval 사용 허용. 데이터 타입의 경우 타입 사용 허용.

 

1.3 PUBLIC 역할 및 기본 권한

PUBLIC은 시스템의 모든 역할을 나타내는 특수 이름입니다. 객체 생성 시 일부 유형의 객체에는 기본적으로 PUBLIC에 특정 권한이 부여됩니다 (예: 데이터베이스에 대한 CONNECT 및 TEMPORARY, 함수에 대한 EXECUTE, 타입 및 언어에 대한 USAGE). 테이블, 열, 스키마 등에는 기본적으로 PUBLIC에 권한이 부여되지 않습니다. 객체 소유자는 REVOKE를 사용하여 이러한 기본 권한을 포함한 모든 권한을 취소할 수 있습니다. 보안을 강화하려면 객체 생성과 동일한 트랜잭션 내에서 불필요한 PUBLIC 권한을 취소하는 것이 좋습니다. ALTER DEFAULT PRIVILEGES 명령을 사용하여 기본 권한 설정을 변경할 수도 있습니다.
 

1.4 그랜트 옵션 (WITH GRANT OPTION)

GRANT 명령에 WITH GRANT OPTION을 지정하면 권한을 받은 역할이 해당 권한을 다른 역할에게 다시 부여할 수 있습니다. 이 옵션이 없으면 권한을 받은 역할은 권한을 재부여할 수 없습니다. PUBLIC에는 그랜트 옵션을 부여할 수 없습니다. 만약 그랜트 옵션이 나중에 취소되면, 해당 역할을 통해 권한을 부여받은 모든 역할(직간접적으로)도 해당 권한을 잃게 됩니다 (CASCADE 옵션 사용 시).
 

1.5 소유권

객체를 수정하거나 삭제할 권리는 객체 소유자에게 내재되어 있으며, 일반적인 권한처럼 부여하거나 취소할 수 없습니다. 소유자는 항상 모든 그랜트 옵션을 보유한 것으로 간주되므로 자신의 권한을 취소했더라도 다시 부여할 수 있습니다. 객체 소유권은 ALTER... OWNER TO 명령을 통해 변경할 수 있습니다.
 

---

섹션 2: 행 수준 보안 정책을 통한 세분화된 접근 제어

표준 GRANT/REVOKE 권한 시스템은 객체 수준에서 접근을 제어하지만, 때로는 테이블 내의 특정 행에 대한 접근을 제어해야 할 필요가 있습니다. PostgreSQL은 이를 위해 행 수준 보안(Row-Level Security, RLS) 기능을 제공합니다.
 

2.1 행 수준 보안 (RLS) 소개

RLS는 사용자가 테이블에 대해 쿼리를 실행할 때, 쿼리를 실행하는 사용자를 기반으로 반환되거나 수정될 수 있는 행을 제한하는 기능입니다. 이는 테이블에 적용되는 정책(Policy)을 통해 구현됩니다. 정책은 특정 조건(SQL 표현식)을 정의하며, 이 조건을 만족하는 행만 사용자에게 보이거나 수정 가능하게 됩니다.
 

2.2 RLS 활성화 및 정책 생성

RLS를 사용하려면 먼저 대상 테이블에 대해 RLS를 활성화해야 합니다.

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

 
그런 다음 CREATE POLICY 명령을 사용하여 하나 이상의 정책을 생성합니다.

CREATE POLICY policy_name ON table_name
    -- 정책 유형 (기본값: PERMISSIVE)
    -- 적용될 명령 (기본값: ALL)
    -- 적용될 역할 (기본값: PUBLIC)
    -- 기존 행에 대한 가시성/수정 가능성 조건
   ; -- 새로 삽입/수정될 행에 대한 유효성 조건

 
 

• USING (expression): 이 표현식은 기존 행에 대해 평가됩니다. SELECT, UPDATE, DELETE 명령 시 적용되며, 표현식이 true를 반환하는 행만 사용자에게 보이거나 수정/삭제 가능합니다. false 또는 null을 반환하는 행은 조용히 제외됩니다.
• WITH CHECK (expression): 이 표현식은 INSERT 또는 UPDATE 명령으로 생성되거나 수정될 행에 대해 평가됩니다. 표현식이 true를 반환하는 행만 삽입/수정이 허용되며, false 또는 null을 반환하면 오류가 발생합니다.
• ALL 또는 UPDATE 정책에서 WITH CHECK가 생략되면 USING 표현식이 WITH CHECK 조건으로도 사용됩니다.

 

2.3 허용(Permissive) vs 제한(Restrictive) 정책

정책은 두 가지 유형으로 나뉩니다:

• PERMISSIVE (기본값): 허용 정책은 접근을 허용하는 조건을 정의합니다. 여러 허용 정책이 있는 경우, 행은 하나라도 만족하면 접근이 허용됩니다 (논리적 OR).
• RESTRICTIVE: 제한 정책은 접근을 제한하는 조건을 정의합니다. 행에 접근하려면 모든 제한 정책을 통과해야 합니다 (논리적 AND). 제한 정책은 최소한 하나의 허용 정책이 접근을 허용한 후에 추가적인 제한을 가하는 데 사용됩니다.

 

2.4 RLS 우회 및 강제 적용

기본적으로 슈퍼유저, BYPASSRLS 속성을 가진 역할, 그리고 테이블 소유자는 RLS 정책을 우회합니다. 테이블 소유자에게도 RLS를 강제로 적용하려면 다음 명령을 사용합니다.
 

2.5 RLS 예시: 사용자별 데이터 접근

관리자만 자신의 부서 정보를 볼 수 있도록 하는 정책을 예로 들어 보겠습니다.

-- 샘플 테이블 및 역할 생성 (간략화)
CREATE TABLE departments (
    id serial primary key,
    name VARCHAR(255) NOT NULL UNIQUE,
    manager VARCHAR(255) NOT NULL
);
INSERT INTO departments(name, manager) VALUES('Sales', 'alice'), ('Marketing', 'bob');
CREATE ROLE managers;
CREATE ROLE alice WITH LOGIN PASSWORD 'password' IN ROLE managers;
CREATE ROLE bob WITH LOGIN PASSWORD 'password' IN ROLE managers;
GRANT SELECT ON departments TO managers;

-- RLS 활성화
ALTER TABLE departments ENABLE ROW LEVEL SECURITY;

-- 정책 생성: 현재 사용자가 manager 열의 값과 일치하는 행만 볼 수 있도록 허용
CREATE POLICY department_managers ON departments
    FOR SELECT -- SELECT 명령에만 적용
    TO managers -- managers 역할에 적용
    USING (manager = current_user); -- 현재 사용자와 manager가 같아야 함

-- 이제 'alice' 사용자로 로그인하여 SELECT * FROM departments; 를 실행하면
-- Sales 부서 정보만 보이고, 'bob' 사용자로 실행하면 Marketing 부서 정보만 보입니다.

 
 

---

섹션 3: 스키마를 이용한 객체 구성

데이터베이스가 커지면서 테이블, 뷰, 함수 등의 객체를 효율적으로 관리하는 것이 중요해집니다. PostgreSQL 스키마는 이러한 객체들을 논리적으로 그룹화하는 방법을 제공합니다.
 

3.1 스키마란 무엇인가?

스키마는 데이터베이스 객체(테이블, 데이터 타입, 함수, 연산자 등)를 담는 네임스페이스입니다. 하나의 데이터베이스는 여러 스키마를 가질 수 있으며, 서로 다른 스키마 내에서는 동일한 이름의 객체를 가질 수 있습니다. 스키마는 다음과 같은 이점을 제공합니다:
 

• 사용자 격리: 여러 사용자가 서로 간섭 없이 동일한 데이터베이스를 사용할 수 있습니다.
• 효율적인 데이터 구성: 관련 객체들을 논리적 그룹으로 묶어 관리 용이성을 높입니다.
• 이름 충돌 방지: 특히 서드파티 애플리케이션을 통합할 때 객체 이름 충돌을 피할 수 있습니다.

3.2 스키마 생성 (CREATE SCHEMA)

CREATE SCHEMA 명령을 사용하여 새 스키마를 생성합니다.

-- 'myschema'라는 이름의 스키마 생성 (현재 사용자가 소유)
CREATE SCHEMA myschema;

-- 'joe' 사용자가 소유하는 'joe' 스키마 생성
CREATE SCHEMA AUTHORIZATION joe;

-- 'joe' 사용자가 소유하는 'test' 스키마 생성 (이미 존재하지 않는 경우에만)
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

-- 스키마 생성과 동시에 객체 생성
CREATE SCHEMA hollywood
    CREATE TABLE films (title text, release date, awards text)
    CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL;

 
 
스키마를 생성하려면 현재 데이터베이스에 대한 CREATE 권한이 필요합니다.
 

3.3 public 스키마

새 데이터베이스를 생성하면 기본적으로 public이라는 스키마가 함께 생성됩니다. 스키마 이름을 명시하지 않고 객체를 생성하면 해당 객체는 public 스키마에 저장됩니다.
 

3.4 스키마 검색 경로 (search_path)

스키마 이름을 명시하지 않고 객체(예: 테이블)를 참조할 때, PostgreSQL은 search_path 설정에 지정된 순서대로 스키마를 검색하여 해당 이름의 객체를 찾습니다. search_path는 쉼표로 구분된 스키마 이름 목록입니다.

• 현재 검색 경로 확인:

SHOW search_path;
-- 기본값 예시: "$user", public

 

• $user: 현재 사용자와 동일한 이름의 스키마를 의미합니다. 기본적으로 가장 먼저 검색됩니다.
• pg_catalog: 시스템 카탈로그 스키마는 항상 검색되며, 명시적으로 지정되지 않으면 목록의 스키마들보다 먼저 검색됩니다.
• 임시 테이블 스키마 (pg_temp): 현재 세션의 임시 테이블 스키마도 존재하면 항상 검색되며, 명시적으로 지정되지 않으면 pg_catalog보다도 먼저 검색됩니다.
• 검색 경로 설정:

-- 현재 세션의 검색 경로를 'sales', 'public' 순으로 설정
SET search_path TO sales, public;

 
 
객체를 생성할 때 스키마를 지정하지 않으면 search_path의 첫 번째 유효한 스키마에 생성됩니다.
 

3.5 스키마와 권한

스키마 내의 객체에 접근하거나 스키마 내에 새 객체를 생성하려면 해당 스키마에 대한 특정 권한이 필요합니다.

• USAGE 권한: 스키마 내에 포함된 객체에 접근(조회)할 수 있게 합니다.
• CREATE 권한: 스키마 내에 새 객체를 생성할 수 있게 합니다.

기본적으로 모든 사용자는 public 스키마에 대해 CREATE 및 USAGE 권한을 갖습니다. 보안을 위해 이 기본 권한을 변경하는 것이 권장될 수 있습니다.
 

3.6 시스템 카탈로그 스키마 (pg_catalog)

pg_catalog 스키마는 PostgreSQL 시스템이 내부적으로 사용하는 특별한 스키마입니다. 여기에는 데이터베이스의 메타데이터(테이블, 열, 인덱스, 함수, 사용자, 권한 등에 대한 정보)를 저장하는 시스템 카탈로그 테이블과 뷰가 포함되어 있습니다.

• 역할: 데이터베이스 구조와 상태에 대한 정보를 저장하고 관리합니다.
• 접근: 일반적으로 사용자는 SQL 명령(예: CREATE TABLE, ALTER USER)을 통해 간접적으로 시스템 카탈로그와 상호작용하며, 직접 수정하는 것은 권장되지 않습니다.
• information_schema와의 관계: information_schema는 SQL 표준에 정의된 뷰 집합으로, pg_catalog에 저장된 메타데이터에 대한 표준화되고 이식성 있는 인터페이스를 제공합니다. pg_catalog은 PostgreSQL 특정 정보를 포함하지만, information_schema는 보다 일반적인 정보를 제공합니다.

 

3.7 사용 패턴 및 이식성

스키마를 효과적으로 사용하는 몇 가지 패턴이 있습니다:

• 사용자별 개인 스키마: 각 사용자에게 자신의 이름과 동일한 스키마를 생성해주고 public 스키마의 CREATE 권한을 제거합니다. 사용자는 기본적으로 자신의 스키마에 객체를 생성하고 접근하게 됩니다. (PostgreSQL 15 이상 기본값)
• public 스키마 제거: search_path에서 public을 제거하고, 객체 참조 시 항상 스키마 이름을 명시하도록 합니다.
• public 스키마 공유: 모든 사용자가 public 스키마를 공유합니다. 스키마가 없는 것처럼 동작하지만, 보안상 취약할 수 있습니다. (PostgreSQL 14 이하 기본값)

이식성을 고려할 때, information_schema를 사용하여 메타데이터를 조회하는 것이 좋습니다.
 

---

섹션 4: 테이블 상속을 통한 코드 재사용

PostgreSQL은 객체 지향 개념인 상속을 테이블 수준에서 지원합니다. 이를 통해 테이블 간에 구조(열)와 일부 제약 조건을 공유할 수 있습니다.
 

4.1 테이블 상속 개념

테이블 상속을 사용하면 하나 이상의 "부모" 테이블로부터 열과 특정 제약 조건을 상속받는 "자식" 테이블을 생성할 수 있습니다. 자식 테이블은 부모 테이블의 모든 열을 가지며, 자체적으로 추가 열을 정의할 수도 있습니다.
 
예시: 도시와 수도

-- 부모 테이블: 도시 정보
CREATE TABLE cities (
    name text,
    population real,
    elevation int -- (in ft)
);

-- 자식 테이블: 수도 정보 (도시 정보 상속 + 주 정보 추가)
CREATE TABLE capitals (
    state char(2) UNIQUE NOT NULL
) INHERITS (cities); -- cities 테이블 상속

 
이 경우 capitals 테이블은 name, population, elevation 열을 cities로부터 상속받고, 추가로 state 열을 갖습니다.
 

4.2 상속 작동 방식

 

• 열: 자식 테이블은 모든 부모 테이블의 열을 포함합니다. 여러 부모로부터 동일한 이름의 열을 상속받는 경우, 데이터 유형이 일치해야 하며 해당 열은 하나로 병합됩니다.
• 제약 조건: CHECK 제약 조건과 NOT NULL 제약 조건은 자식 테이블로 상속됩니다. 동일한 이름의 CHECK 제약 조건은 병합되며, 조건이 다르면 오류가 발생합니다.
• 쿼리: 기본적으로 부모 테이블을 쿼리하면 부모 테이블 자체의 행과 모든 자식 테이블의 행이 함께 반환됩니다. 부모 테이블만 쿼리하려면 ONLY 키워드를 사용합니다.

-- cities 테이블과 모든 자식 테이블(capitals)의 모든 행 조회
SELECT name, elevation FROM cities WHERE elevation > 500;

-- cities 테이블 자체의 행만 조회
SELECT name, elevation FROM ONLY cities WHERE elevation > 500;

 
 

4.3 사용 사례

 

• 데이터 모델링: 공통 속성을 가진 객체 계층을 모델링하는 데 사용될 수 있습니다 (예: 도시/수도, 다양한 유형의 장비).
• 데이터 파티셔닝 (레거시): 과거에는 데이터를 작은 테이블로 나누는 파티셔닝 기법으로 사용되었습니다. 하지만 현재는 선언적 파티셔닝이 더 권장됩니다.

 

4.4 주의 사항 및 제한 사항 (Caveats)

테이블 상속은 유용할 수 있지만 몇 가지 중요한 제한 사항이 있습니다:

• 인덱스 및 고유/기본 키 제약 조건: 인덱스(UNIQUE, PRIMARY KEY 포함)는 자식 테이블로 상속되지 않습니다. 즉, 부모 테이블에 기본 키가 있어도 자식 테이블 간 또는 부모-자식 간의 고유성이 보장되지 않습니다. 필요한 경우 각 자식 테이블에 별도로 제약 조건을 추가해야 합니다.
• 외래 키 제약 조건: 외래 키 제약 조건 역시 상속되지 않습니다. 부모 테이블을 참조하는 외래 키는 자식 테이블의 행을 참조하지 않으며, 그 반대도 마찬가지입니다.
• 데이터 라우팅: INSERT 또는 COPY 명령은 지정된 테이블에만 데이터를 삽입하며, 상속 계층 구조에 따라 자동으로 데이터를 자식 테이블로 분배하지 않습니다.
• ALTER TABLE 제한: 부모 테이블에서 상속된 열이나 제약 조건은 자식 테이블에서 직접 삭제하거나 변경할 수 없습니다. RENAME은 자식 테이블에 적용할 수 없습니다. 부모 테이블에서 DROP COLUMN을 사용해도 자식 테이블의 열은 특정 조건 하에서만 제거됩니다.
• 권한: 상속 계층을 쿼리할 때 접근 권한은 부모 테이블에 대해서만 확인됩니다.

이러한 제한 사항 때문에, 특히 파티셔닝 목적으로는 PostgreSQL 10부터 도입된 선언적 파티셔닝 기능이 일반적으로 더 선호됩니다.
 

---

섹션 5: 대규모 테이블 관리를 위한 파티셔닝

매우 큰 테이블은 관리 및 쿼리 성능 측면에서 어려움을 겪을 수 있습니다. PostgreSQL의 테이블 파티셔닝은 큰 논리적 테이블을 파티션이라는 더 작고 관리하기 쉬운 물리적 테이블 조각으로 나누는 기술입니다.
 

5.1 파티셔닝 개요 및 이점

파티셔닝은 큰 테이블을 지정된 규칙(파티션 키 기준)에 따라 여러 개의 작은 테이블(파티션)로 분할합니다. 애플리케이션은 여전히 단일 논리 테이블로 상호작용할 수 있습니다.
 
주요 이점은 다음과 같습니다:

• 쿼리 성능 향상: 쿼리 플래너가 관련 없는 파티션을 검색에서 제외(파티션 프루닝)하여 스캔 범위를 줄일 수 있습니다.
• 대량 데이터 로드/삭제 용이성: 파티션 단위로 데이터를 빠르게 추가하거나 삭제할 수 있습니다 (예: 오래된 월별 데이터 파티션 삭제).
• 유지 관리 용이성: 작은 파티션 단위로 인덱스 재구성, VACUUM 등의 작업을 수행할 수 있습니다.
• 비용 효율적인 스토리지: 사용 빈도에 따라 파티션을 다른 스토리지 매체(예: 느리고 저렴한 스토리지)에 배치할 수 있습니다.

 

5.2 선언적 파티셔닝 (PostgreSQL 10+)

PostgreSQL 10부터 도입된 선언적 파티셔닝은 파티션된 테이블을 생성하고 관리하는 권장 방법입니다.

• 파티션된 테이블 생성: CREATE TABLE 문에 PARTITION BY 절을 사용하여 파티셔닝 방법과 파티션 키(분할 기준이 되는 열 또는 표현식)를 지정합니다.

-- logdate 열을 기준으로 범위 파티셔닝 설정
CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

 
 

• 파티셔닝 방법:
  • RANGE: 파티션 키 값의 범위를 기준으로 분할합니다. 각 파티션은 FOR VALUES FROM (하한값) TO (상한값)으로 정의되며, 하한은 포함되고 상한은 제외됩니다. 날짜나 연속적인 ID 범위에 적합합니다.
  • LIST: 파티션 키 값이 각 파티션에 명시적으로 나열된 값 목록에 속하는지에 따라 분할합니다. FOR VALUES IN (값1, 값2,...)으로 정의됩니다. 지역 코드나 상태 코드처럼 불연속적인 값에 적합합니다.
  • HASH: 파티션 키의 해시 값을 기준으로 분할합니다. 각 파티션은 FOR VALUES WITH (MODULUS 모듈러스, REMAINDER 나머지)로 정의됩니다. 데이터를 파티션 간에 균등하게 분배하고자 할 때 유용합니다.
• 파티션 생성: CREATE TABLE... PARTITION OF 문을 사용하여 각 파티션을 생성하고 해당 파티션이 포함할 값의 범위를 지정합니다.

-- measurement 테이블의 2006년 2월 데이터 파티션 생성 (Range)
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

-- customers 테이블의 'ACTIVE' 상태 파티션 생성 (List)
CREATE TABLE cust_active PARTITION OF customers
    FOR VALUES IN ('ACTIVE');

-- emp 테이블의 해시 파티션 생성 (Hash)
CREATE TABLE emp_0 PARTITION OF emp
    FOR VALUES WITH (MODULUS 3, REMAINDER 0);

 
 
 
파티션 경계 조건에 대한 제약 조건은 자동으로 생성됩니다.

• 하위 파티셔닝 (Sub-partitioning): 파티션 자체를 다시 파티션할 수 있습니다. 파티션 생성 시 PARTITION BY 절을 추가로 지정합니다.
• 인덱스: 파티션된 테이블에 인덱스를 생성하면 모든 파티션에 자동으로 해당 인덱스가 생성됩니다.
• 파티션 프루닝: 쿼리 플래너가 쿼리의 WHERE 절을 분석하여 관련 없는 파티션을 스캔 대상에서 제외하는 최적화 기법입니다. enable_partition_pruning 설정이 활성화되어 있어야 합니다.

 

5.3 파티셔닝 제한 사항

선언적 파티셔닝에도 몇 가지 제한 사항이 있습니다:

• 파티션된 테이블의 UNIQUE 또는 PRIMARY KEY 제약 조건은 반드시 파티션 키 열을 포함해야 합니다. (대안: 각 파티션에 개별적으로 고유 제약 조건 생성)
• 파티션된 테이블에는 BEFORE ROW 트리거를 정의할 수 없습니다. (대안: 각 파티션에 개별적으로 트리거 정의)

 

5.4 상속을 이용한 파티셔닝 (레거시)

PostgreSQL 10 이전에는 테이블 상속과 CHECK 제약 조건, 트리거를 조합하여 파티셔닝을 구현했습니다. 이 방법은 설정이 더 복잡하고 선언적 파티셔닝만큼 성능이 좋지 않거나 기능이 부족할 수 있어 현재는 잘 사용되지 않습니다.
 

---

섹션 6: 외부 데이터 접근 (Foreign Data Wrappers - FDW)

PostgreSQL은 데이터베이스 외부(다른 PostgreSQL 서버, 다른 종류의 데이터베이스, 파일 등)에 저장된 데이터에 SQL을 통해 접근할 수 있는 기능을 제공합니다. 이를 Foreign Data Wrapper(FDW)라고 합니다.
 

6.1 FDW 개념

외부 데이터에 접근하기 위한 일반적인 단계는 다음과 같습니다:

• FDW 확장 설치/생성 (CREATE EXTENSION / CREATE FOREIGN DATA WRAPPER): 사용할 FDW 확장을 설치하고(CREATE EXTENSION postgres_fdw;), 필요하다면 CREATE FOREIGN DATA WRAPPER 명령으로 FDW 객체를 정의합니다.
• 외부 서버 생성 (CREATE SERVER): 외부 데이터 소스에 대한 연결 정보를 정의하는 서버 객체를 생성합니다. FDW별 옵션(호스트, 포트, 데이터베이스 이름 등)을 지정합니다.

-- 다른 PostgreSQL 서버를 위한 외부 서버 정의
CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

 

• 사용자 매핑 생성 (CREATE USER MAPPING): 로컬 PostgreSQL 역할이 외부 서버에 인증하는 데 사용할 자격 증명(예: 사용자 이름, 비밀번호)을 정의합니다.

-- 로컬 사용자 'local_user'가 'foreign_server'에 연결할 때 사용할 원격 사용자 및 비밀번호 매핑
CREATE USER MAPPING FOR local_user
    SERVER foreign_server
    OPTIONS (user 'foreign_user', password 'password');

• 외부 테이블 생성 (CREATE FOREIGN TABLE): 로컬 PostgreSQL에서 외부 데이터의 구조를 정의하는 외부 테이블을 생성합니다. 일반 CREATE TABLE과 유사하지만, 데이터는 로컬에 저장되지 않습니다.

-- 'foreign_server'의 'some_schema.some_table'을 참조하는 외부 테이블 생성
CREATE FOREIGN TABLE foreign_table (
    id serial NOT NULL,
    data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');

 

6.3 외부 테이블 사용 및 제약 조건

외부 테이블은 생성 후 일반 테이블처럼 쿼리(SELECT, INSERT, UPDATE, DELETE)할 수 있습니다. PostgreSQL은 해당 작업을 FDW를 통해 외부 소스로 전달합니다.
 
외부 테이블에 CHECK 또는 NOT NULL과 같은 제약 조건을 정의할 수는 있지만, PostgreSQL 코어나 대부분의 FDW는 이러한 제약 조건을 강제로 적용하지 않습니다. 제약 조건은 단지 외부 데이터가 해당 조건을 만족한다고 가정하는 데 사용됩니다.
 

---

섹션 7: 객체 종속성 이해

데이터베이스 내의 객체들은 서로 연결되어 종속성을 형성합니다. 예를 들어, 뷰는 기반 테이블에 종속되고, 외래 키 제약 조건은 참조되는 테이블에 종속됩니다. PostgreSQL은 이러한 종속성을 추적하여 데이터베이스 구조의 무결성을 유지합니다.
 

7.1 종속성 개념 및 중요성

객체 종속성은 한 객체가 다른 객체의 존재나 정의에 의존하는 관계를 의미합니다. 예를 들어, 함수가 특정 사용자 정의 타입을 인수로 사용한다면, 해당 함수는 그 타입에 종속됩니다.
 
종속성 추적은 객체를 삭제(DROP)하거나 수정(ALTER)할 때 중요합니다. PostgreSQL은 다른 객체가 여전히 의존하고 있는 객체를 실수로 삭제하는 것을 방지합니다.
 

7.2 pg_depend 시스템 카탈로그

PostgreSQL은 pg_depend 시스템 카탈로그 테이블에 객체 간의 종속성 정보를 기록합니다. 이 테이블에는 의존하는 객체와 참조되는 객체의 식별자(OID), 그리고 종속성 유형을 나타내는 정보가 저장됩니다.
 
주요 pg_depend 열:

• classid, objid, objsubid: 의존하는 객체의 카탈로그 OID, 객체 OID, 하위 ID (열 번호 등).
• refclassid, refobjid, refobjsubid: 참조되는 객체의 카탈로그 OID, 객체 OID, 하위 ID.
• deptype: 종속성 유형을 나타내는 코드.

주요 종속성 유형 (deptype):

• DEPENDENCY_NORMAL ('n'): 일반적인 종속성. 의존 객체는 독립적으로 삭제될 수 있지만, 참조된 객체를 삭제하려면 CASCADE 옵션이 필요하며, 이 경우 의존 객체도 함께 삭제됩니다 (예: 열과 해당 데이터 타입).
• DEPENDENCY_AUTO ('a'): 자동 종속성. 의존 객체는 독립적으로 삭제될 수 있으며, 참조된 객체가 삭제되면 RESTRICT나 CASCADE에 관계없이 자동으로 함께 삭제됩니다 (예: 테이블과 해당 테이블의 명명된 제약 조건).
• DEPENDENCY_INTERNAL ('i'): 내부 종속성. 의존 객체는 참조된 객체의 내부 구현 일부이며 직접 삭제할 수 없습니다. 참조된 객체를 삭제하면 자동으로 함께 삭제됩니다 (예: 뷰와 해당 뷰의 ON SELECT 규칙).

 

7.3 DROP 명령과 종속성 (RESTRICT vs CASCADE)

객체를 삭제하려고 할 때 다른 객체가 해당 객체에 의존하고 있다면, 기본적으로(RESTRICT 동작) PostgreSQL은 오류를 발생시키며 삭제를 거부합니다.

DROP TABLE products;
-- ERROR: cannot drop table products because other objects depend on it
-- DETAIL: constraint orders_product_no_fkey on table orders depends on table products
-- HINT: Use DROP... CASCADE to drop the dependent objects too.

 
오류 메시지의 DETAIL과 HINT는 어떤 객체가 의존하고 있으며 어떻게 처리해야 하는지 알려줍니다. 의존하는 객체들을 먼저 수동으로 삭제하거나, CASCADE 옵션을 사용하여 의존하는 객체들까지 재귀적으로 함께 삭제할 수 있습니다.

DROP TABLE products CASCADE;

 
CASCADE 옵션은 매우 강력하므로 사용 시 주의가 필요합니다. 의도치 않은 객체까지 삭제될 수 있으므로, 실행 전에 어떤 객체들이 영향을 받을지 확인하는 것이 좋습니다 (DROP을 CASCADE 없이 실행하여 DETAIL 확인).
 

---

섹션 8: 결론

PostgreSQL의 고급 데이터 정의 기능들을 살펴보았습니다. 권한 시스템을 통한 접근 제어, 행 수준 보안 정책을 이용한 세밀한 데이터 접근 관리, 스키마를 활용한 효율적인 객체 구성, 테이블 상속의 개념과 한계, 대규모 데이터 관리를 위한 강력한 선언적 파티셔닝, 외부 데이터 소스와의 연동을 가능하게 하는 FDW, 그리고 데이터베이스 무결성 유지에 필수적인 객체 종속성 추적까지 다루었습니다.