PostgreSQL: DEFAULT PRIVILEGES

※ PostgreSQL: DEFAULT PRIVILEGES.
 
안녕하세요. 듀스트림입니다.
 
PostgreSQL의 권한 설계는 다른 DBMS(MySQL/MariaDB 등)에 비해 상대적으로 복잡한 편입니다. (Oracle과는 비슷합니다.)
 
그 이유로는 먼저, PostgreSQL 철학에서 USER는 LOGIN 가능한 ROLE 일뿐입니다.
 
다음으로 권한 모델 구조를 보면, 다음과 같이 계층적 구조가 아닌 분리형 구조로 되어 있습니다.

Database
 └─ Schema
     └─ Table / View / Sequence / Function / Type …

→ 여기서 중요한 점은 상위 객체에 권한을 부여해도, 하위 객체 권한이 자동으로 따라오지 않는다는 점입니다.
 
Database에 CONNECT 권한을 부여하고 해당 Table에 SELECT 권한이 있어도, Schema에 USAGE 권한이 없으면 접근 불가합니다.
 
PostgreSQL 객체별 권한

Object	주요 권한
DATABASE	CONNECT, CREATE, TEMP
SCHEMA	USAGE, CREATE
TABLE	SELECT, INSERT, UPDATE, DELETE, TRUNCATE
SEQUENCE	USAGE, SELECT, UPDATE
FUNCTION	EXECUTE

→ 테이블에 INSERT 권한이 있어도, 시퀀스 USAGE 권한이 없으면 INSERT 실패
 
생성되는 객체의 수가 많지 않은 환경이라면, 개별 객체에 대해 GRANT / REVOKE로 권한을 관리하는 방식만으로도 충분합니다.
그러나 파티션 테이블이 다수 존재하거나, 서비스가 지속적으로 개발되어 새로운 객체가 계속 추가되는 환경에서 이런 방식은 매우 높은 피로도로 이어질 수 있습니다.
 

❗위와 같은 이유로 운영 피로도를 줄이기 위해서는 DEFAULT PRIVILEGES에 대한 이해가 반드시 필요합니다.

 

ALTER DEFAULT PRIVILEGES

---

1. DEFAULT PRIVILEGES ?

ALTER DEFAULT PRIVILEGES는 앞으로 생성될 객체에 적용할 기본 권한 템플릿을 설정하는 명령입니다.

 
가장 중요한 규칙은,

• DEFAULT PRIVILEGES는 이미 존재하는 객체에는 영향을 주지 않습니다.
• 오직 미래에 생성되는 객체에만 적용됩니다. 

 
권한 관점에서 보면,

• 기존 객체 정리: GRANT / REVOKE
• 미래 생성분 정책화: ALTER DEFAULT PRIVILEGES

---

2. DEFAULT PRIVILEGES 핵심 축 3가지

DEFAULT PRIVILEGES는 "스키마 기본 권한"처럼 느껴지지만, 실제 동작은 더 엄격합니다.

2.1 누가(ROLE) 객체를 생성하는가?

DEFAULT PRIVILEGES는 객체 소유자(owner)가 아니라, 객체를 생성 시점의 생성하는 ROLE을 기준으로 적용됩니다.
 
같은 스키마라도

• postgres가 만들면 postgres의 default privileges가 적용되고,
• app_owner가 만들면 app_owner의 default privileges가 적용됩니다.

"DEFAULT PRIVILEGES를 설정했는데 왜 권한이 없지?"의 최빈 원인입니다.

---

2.2 범위는 현재 접속 중인 데이터베이스 내에서 "전역 또는 특정 스키마"

ALTER DEFAULT PRIVILEGES는 현재 데이터베이스 내부에서만 의미가 있고, 전역(해당 DB 전체) 또는 스키마 단위로 적용할 수 있습니다.

---

2.3 권한은 객체 타입별로 완전히 분리

기본 권한은 객체 타입(테이블/시퀀스/함수/타입 등)마다 별도 규칙입니다.

 
객체 목록

• ON TABLE
• ON SEQUENCE
• ON FUNCTION
• ON TYPE
• ...

테이블 권한을 설정했다고 해서 시퀀스 권한이 자동으로 따라가지 않습니다.

---

3. 테이블에 권한을 부여하면 시퀀스, 인덱스에도 자동 적용될까?

 

3.1 시퀀스(sequence)

시퀀스는 테이블에 종속적으로 보이지만 권한은 독립적입니다.

 
테이블에 INSERT 권한을 줘도, 시퀀스 권한은 자동으로 부여되지 않습니다.

permission denied for sequence ...가 나는 이유가 이것입니다.

 
SERIAL/BIGSERIAL/IDENTITY 컬럼은 내부적으로 별도의 시퀀스 객체를 사용합니다. 따라서 INSERT가 동작할 때 nextval()을 호출하게 됩니다.

이때 필요한 권한이 바로 시퀀스의 USAGE 권한입니다.

USAGE 권한은 nextval() / currval() 사용과 연결됩니다.

---

3.2 인덱스(index)

인덱스는 권한 부여 대상이 아닙니다. (GRANT 대상이 아님)

 
인덱스는 사용자가 직접 접근하는 객체가 아니라, 테이블 접근 시 PostgreSQL이 내부적으로 사용하는 성능 최적화용 객체입니다.

사용자는 테이블에 대한 권한(SELECT, UPDATE 등)을 통해 데이터에 접근하며, 어떤 인덱스를 사용할지는 권한과 무관하게 쿼리 플래너가 결정합니다.

---

4. 그렇다면 INSERT 사용자에게만 시퀀스 USAGE를 주면 되나?

원칙적으로는 그렇습니다.

 
시퀀스 권한

• USAGE: nextval() 호출(=새 ID 생성) 
• SELECT: currval() 등 조회(운영/진단 목적 외에는 흔치 않음) 
• UPDATE: setval() (Admin/마이그레이션) 

 
일반 애플리케이션 계정 기준으로 보면,

• INSERT가 필요하면 → 시퀀스 USAGE 필요
• SELECT만 필요하면 → 시퀀스 권한 불필요
• setval()이 필요한 Admin/마이그레이션 계정만 → 시퀀스 UPDATE 권한 부여 고려

---

5. 슈퍼유저로 객체 생성 후 OWNER 변경하면 DEFAULT PRIVILEGES는?

DEFAULT PRIVILEGES는 객체 생성 시점에 생성한 ROLE을 기준으로 한 번만 동작하고 끝입니다.

 

1. 슈퍼유저(postgres)로 테이블 생성
2. ALTER TABLE ... OWNER TO app_owner;

위와 같은 순서로 하면 app_owner의 DEFAULT PRIVILEGES는 적용되지 않습니다.

OWNER 변경은 소유자만 바꾸고, 생성 시점에 복사된 ACL은 자동 재계산되지 않습니다.
(기존 객체는 영향을 받지 않는다는 원칙과 동일한 맥락입니다.)

---

6. pg_default_acl

DEFAULT PRIVILEGES 설정은 시스템 카탈로그 pg_default_acl에 저장됩니다.

 

52.17. pg_default_acl

 
공식 문서 핵심 요약

• defaclnamespace = 0 인 row는 전역(global)
• 스키마 OID를 가지는 row는 스키마 한정(per-schema)
• global이 있으면 하드코딩 디폴트를 override
• per-schema는 global/하드코딩 디폴트에 추가(add) 

 

 
컬럼 의미

• defaclrole: 이 규칙이 적용되는 생성자 ROLE (누가 만들 때 적용?) 
• defaclnamespace: 0이면 전역, 아니면 특정 스키마 OID 
• defaclobjtype: 객체 타입 (테이블/시퀀스/함수/타입 등) 
• defaclacl: 실제 ACL (누구에게 어떤 권한을 기본으로 줄지) 

 
+ 아래 쿼리 사용하세요.

/*************************************************
*** 1. creator_role: 누가 만들 때 적용?
*** 2. schema_name: 전역인가, 특정 스키마인가?
*** 3. object_type: 테이블/시퀀스/함수 중 무엇인가?
*** 4. default_acl: 누구에게 어떤 권한을 줄 것인가?
*************************************************/
SELECT
  defaclrole::regrole AS creator_role,
  COALESCE(n.nspname, 'ALL_SCHEMAS') AS schema_name,
  CASE defaclobjtype
    WHEN 'r' THEN 'TABLES'
    WHEN 'S' THEN 'SEQUENCES'
    WHEN 'f' THEN 'FUNCTIONS'
    WHEN 'T' THEN 'TYPES'
    ELSE defaclobjtype::text
  END AS object_type,
  defaclacl AS default_acl
FROM pg_default_acl d
LEFT JOIN pg_namespace n
  ON n.oid = d.defaclnamespace
ORDER BY 1,2,3;

 

52.17. pg_default_acl

---

오늘은 2026년의 마지막 날입니다.
다가오는 새해에는 하시는 모든 일이 더 잘 풀리고, 늘 건강과 행복이 함께하시길 진심으로 기원합니다.
새해 복 많이 받으세요.
 
오늘은 여기까지~