[PostgreSQL] Password(패스워드) 만료, 테이블 권한 부여, 권한 분리 설정

(DBA 입장에서 바라 본)
PostgreSQL 패스워드 관리와 테이블 권한부여 및 권한 분리 대한 정리

OS환경 : Rocky 8.10 (64bit)
DB 환경 : PostgreSQL 17.4

 

 

 

Oracle DB 처럼 개발자 계정, 서비스 계정 분리를 하기 위해 다음과 같이 방법을 고려해서 작성하였습니다.

해당 내용을 이해하기 위해서는 Schema, Role, User, search_path 개념을 먼저 익히면 좋을 것 같습니다.

 

 

예시) A 개발 업체의 특정 서비스 개발로 여러 개의 계정생성이 필요한 상황이며 불필요한 DDL권한은 DBA에게 요청하여  관리할 목적임

 

 

🎯 개발 프로젝트 용도 계정생성 

유저 이름	사번
대상 DB	mydb
사용 시작일	2025-03-31
사용 기간(계정 만료일)	1년 (~ 2026-03-30)
권한 범위	테이블 DML만 (SELECT, INSERT, UPDATE, DELETE)
권한 적용 범위	스키마 dev
권한 부여 방식	그룹 ROLE을 통해 간접 위임

 

😀 1. 사용자 생성

##영어 대문자로 계정을 만들어도, 소문자로 생성됨
CREATE ROLE A12345678 WITH LOGIN PASSWORD'1q2w3e4r!'VALID UNTIL '2026-03-31 23:59:59';

 

😢 2. 현재 계정 만료일 확인

SELECT usename, valuntil FROM pg_user WHERE usename = 'a12345678'

 

⭐오라클처럼 패스워드를 변경한다고 계정 만료일이 자동으로 연장되지 않음.

 

 

👍 계정 자동 연장 스크립트 (30일 이내 만료 계정, 자동 1년 연장)  

DO $$
DECLARE
    r record;
BEGIN
  FOR r IN
    SELECT usename FROM pg_user
    WHERE valuntil < now() + interval '30 days'
      AND valuntil IS NOT NULL
  LOOP
    EXECUTE format('ALTER ROLE %I VALID UNTIL %L', r.usename, (now() + interval '1 year')::text);
  END LOOP;
END$$;

 

 

 

 

🧷 1. DML 전용 ROLE 생성 

CREATE ROLE mydb_dml_role;

 

 

🛡️ 2. DML 전용 ROLE에 권한부여 

-- 현재 있는 테이블들에 대해
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA dev
  TO mydb_dml_role;

-- Sequences부여
GRANT USAGE ON ALL SEQUENCES IN SCHEMA dev TO mydb_dml_role;

-- 앞으로 생성되는 테이블에도 자동으로 권한 부여
-- 새로운 테이블 생성 시 해당 테이블 접근 불가
ALTER DEFAULT PRIVILEGES IN SCHEMA dev
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO mydb_dml_role;

-- 자동으로 시퀀스 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA dev grant usage on sequences TO mydb_dml_role;

  
-- 계정 DB 접속 권한 조회
SELECT datname,
       has_database_privilege('a12345678', datname, 'connect') AS can_connect
FROM pg_database;

 

 

※ 만약 can_connect가 t로 되어 있다면 public schema revoke 진행, template0는 원래 접속이 불가능 하여 revoke 할 필요가 없음.  ( datallowconn = false ) 

-- 초기 DB 설치 시 Public schema는 revoke처리 
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE khg FROM PUBLIC;

 

 

🔗 3. 개발자 계정에  DML ROLE 권한 부여 

GRANT mydb_dml_role TO a12345678;

 

 

📦 4. 개발자 계정 접속 권한 부여 및 스키마 권한 부여

GRANT CONNECT ON DATABASE mydb TO a12345678;
CREATE SCHEMA DEV;
--해당 권한이 없으면 테이블 접근 불가
GRANT USAGE ON SCHEMA DEV TO mydb_dml_role;

--SEARCH PATH 설정
ALTER DATABASE mydb SET SERACH_PATH TO dev, public, "$user";

 

Serach Path 확인

 

 

 

dev schema 테이블 리스트 조회 

 

 

 

PUBLIC 스키마 테이블 조회 시도 시  불가능 확인. 목록 리스트는 확인 가능함  

 

 

 

 

 

TIP :

1. VALID UNTIL은 패스워드 만료일이지만, 사실상 계정 만료일과 다름 없다.
   (사용자가 패스워드 변경해도 유효기간은 변함없음)  
2. 테이블 접근 권한 부여를 하지만, 새로운 테이블 생성 시  ALTER DEFAULT PRIVILEGES IN 권한을 부여해줘야
   여러 번  작업을 안 할 수 있음.

 

 

 

내용적으로 추가로 궁금한 부분이나 새로운 아이디어 질문해주시면 답변 드리겠습니다.  

 

 

참조 : 없음