PostgreSQL 계정 및 권한 관리 가이드

PostgreSQL 계정 및 권한 관리 가이드

PostgreSQL에서 DB 생성부터 계정 생성, 권한 부여/삭제, 스키마 변경 시 권한 자동 유지까지 실무에서 자주 쓰는 명령어를 정리했습니다.

 

1. 데이터베이스 생성

-- 기본 생성
CREATE DATABASE myapp;

-- 인코딩, 소유자 지정
CREATE DATABASE myapp
    OWNER myuser
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8';

 

2. 계정(Role) 생성

PostgreSQL에서는 사용자와 그룹 모두 ROLE을 사용합니다.

-- 일반 사용자
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass';

-- DB 생성 권한 포함
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass' CREATEDB;

-- Superuser
CREATE ROLE myadmin WITH LOGIN PASSWORD 'adminpass' SUPERUSER;

 

주요 옵션 정리:

옵션	설명
LOGIN	접속 허용 (없으면 그룹 역할만 가능)
SUPERUSER	모든 권한 (주의해서 사용)
CREATEDB	DB 생성 권한
CREATEROLE	역할 생성 권한

 

3. 권한 부여 (GRANT)

DB 접속 권한:

GRANT CONNECT ON DATABASE myapp TO myuser;

 

스키마 사용 권한:

GRANT USAGE ON SCHEMA public TO myuser;

-- 스키마 내 객체 생성까지 허용
GRANT USAGE, CREATE ON SCHEMA public TO myuser;

 

테이블 권한:

-- 특정 테이블
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO myuser;

-- 스키마 내 모든 기존 테이블
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;

-- 읽기 전용
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

 

시퀀스 권한 (INSERT 시 serial/identity 컬럼 사용에 필요):

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

 

4. 스키마 변경 시 권한 자동 유지 (DEFAULT PRIVILEGES)

GRANT ON ALL TABLES는 실행 시점의 기존 테이블에만 적용됩니다. 이후 새로 생성되는 테이블에는 권한이 없습니다. 이 문제를 해결하는 것이 ALTER DEFAULT PRIVILEGES입니다.

-- 앞으로 생성되는 테이블에 자동 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

-- 앞으로 생성되는 시퀀스에 자동 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO myuser;

-- 읽기 전용 계정용
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly_user;

주의: ALTER DEFAULT PRIVILEGES는 실행한 역할(Role)이 생성하는 객체에만 적용된다. 예를 들어, postgres로 실행했으면 postgres가 만드는 테이블에만 적용된다. 다른 역할이 만드는 객체에도 적용하려면 FOR ROLE을 지정해야 한다.

-- deployer 역할이 생성하는 테이블에도 적용
ALTER DEFAULT PRIVILEGES FOR ROLE deployer IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

 

5. 실전: 완전한 권한 설정 스크립트

새 DB와 계정을 만들고, 기존 + 미래 테이블 모두에 권한을 부여하는 전체 흐름입니다.

-- 1) 계정 생성
CREATE ROLE myuser WITH LOGIN PASSWORD 'secretpass';

-- 2) DB 생성
CREATE DATABASE myapp OWNER postgres;

-- 3) DB 접속 (psql -d myapp 또는 \c myapp)

-- 4) 스키마 권한
GRANT CONNECT ON DATABASE myapp TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;

-- 5) 기존 객체 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- 6) 미래 객체 자동 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO myuser;

 

6. 권한 삭제 (REVOKE)

GRANT의 반대다. 문법도 거의 동일합니다.

-- 테이블 권한 삭제
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM myuser;

-- 스키마 권한 삭제
REVOKE USAGE ON SCHEMA public FROM myuser;

-- DB 접속 권한 삭제
REVOKE CONNECT ON DATABASE myapp FROM myuser;

-- DEFAULT PRIVILEGES 삭제
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE USAGE ON SEQUENCES FROM myuser;

 

7. 권한 확인

-- 역할 목록 확인
\du

-- 특정 테이블 권한 확인
\dp users

-- DB 권한 확인
\l

-- DEFAULT PRIVILEGES 확인
\ddp

 

8. 계정 삭제

계정을 삭제하려면 소유 객체와 권한을 먼저 정리해야 합니다.

-- 소유 객체를 다른 역할로 이전
REASSIGN OWNED BY myuser TO postgres;

-- 남은 권한 모두 삭제
DROP OWNED BY myuser;

-- 계정 삭제
DROP ROLE myuser;

 

정리

목적	명령
기존 테이블 권한	GRANT ... ON ALL TABLES
미래 테이블 자동 권한	ALTER DEFAULT PRIVILEGES
권한 회수	REVOKE ... FROM
계정 삭제	REASSIGN OWNED BY + DROP OWNED BY + DROP ROLE

핵심은 GRANT ON ALL TABLES와 ALTER DEFAULT PRIVILEGES를 함께 사용해야 기존 + 미래 테이블 모두 권한이 적용된다는 것 입니다.