PostgreSQL 권한 관리 완전 가이드 — 롤·GRANT·REVOKE·스키마 권한

-- 로그인 계정 (애플리케이션용)
CREATE ROLE app_user LOGIN PASSWORD 'S3cr3t!23';

-- 권한 묶음(그룹) — 로그인 불가
CREATE ROLE readonly;
CREATE ROLE readwrite;

-- 관리용 속성
CREATE ROLE analyst LOGIN PASSWORD '...' 
  CONNECTION LIMIT 5       -- 동시 접속 제한
  VALID UNTIL '2027-01-01'; -- 만료일

ALTER ROLE app_user CONNECTION LIMIT 20;   -- 속성 변경
ALTER ROLE app_user PASSWORD 'new_pw';     -- 비밀번호 변경

-- 1. 데이터베이스 접속 허용
GRANT CONNECT ON DATABASE appdb TO readonly;

-- 2. 스키마 사용 허용 (이걸 빼먹는 실수가 가장 많다)
GRANT USAGE ON SCHEMA public TO readonly;

-- 3. 기존 테이블 전체에 SELECT
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 4. 시퀀스 조회(필요 시)
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite;  -- SERIAL/자동증가용

REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;

-- 권한은 그룹에
GRANT readonly TO analyst;     -- analyst가 readonly의 모든 권한 상속
GRANT readwrite TO app_user;

-- 멤버십 확인 / 회수
REVOKE readwrite FROM app_user;

-- 앞으로 app_owner가 public에 만드는 모든 테이블에 readonly가 SELECT 갖도록
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON SEQUENCES TO readonly;

-- 아무나 public에 객체 생성하지 못하게
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- (선택) 새 DB 자동 접속도 차단
REVOKE ALL ON DATABASE appdb FROM PUBLIC;

\du                    -- 롤 목록과 속성
\du+ app_user          -- 특정 롤 상세(멤버십 포함)
\dp public.*           -- 테이블별 권한(access privileges)
\dn+                   -- 스키마 권한

SELECT has_table_privilege('readonly', 'public.orders', 'SELECT');  -- t / f