최소 권한 롤 계층 구성(GRANT + ALTER DEFAULT PRIVILEGES)

명령어

-- Group roles (NOLOGIN): object owner, read-only, read-write
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
GRANT app_readonly TO app_readwrite;
CREATE SCHEMA app AUTHORIZATION app_owner;

-- Restrict PUBLIC's default privileges
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Existing objects
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA app TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

-- Objects app_owner will create IN THE FUTURE
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

-- Login users get privileges only through group membership
CREATE ROLE web_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readwrite;
CREATE ROLE report_app LOGIN PASSWORD 'Change#2026' IN ROLE app_readonly;

-- Configured default privileges (psql: \ddp)
SELECT pg_get_userbyid(d.defaclrole) AS owner_role,
       n.nspname AS schema_name,
       d.defaclobjtype AS object_type,
       d.defaclacl
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;

하는 일

owner, 읽기 전용, 읽기-쓰기 그룹 롤의 계층을 만들고, 기존 및 향후 테이블/시퀀스에 올바른 권한을 부여하며, 애플리케이션 사용자에게는 그룹 멤버십으로만 권한을 준다.

사용 시점

새 애플리케이션 스키마를 구성할 때, superuser나 테이블 소유자로 연결하는 애플리케이션을 바로잡을 때, 감사 전 권한을 단순화할 때.

주의

ALTER DEFAULT PRIVILEGES는 FOR ROLE에 지정한 롤이 이후에 생성하는 객체에만 적용된다. 마이그레이션 도구가 다른 사용자로 테이블을 만들면 권한이 적용되지 않으므로 SET ROLE app_owner로 DDL을 실행한다. GRANT ... ON ALL TABLES는 그 시점에 존재하는 객체만 포함한다. PG15+에서 생성된 데이터베이스는 이미 public 스키마의 CREATE를 PUBLIC에 부여하지 않지만, 이전 버전에서 업그레이드된 데이터베이스는 REVOKE가 필요하다. REVOKE ALL ON DATABASE ... FROM PUBLIC은 CONNECT와 TEMP도 제거하므로 필요한 다른 롤에 별도로 부여한다. 비밀번호를 명령줄에 넣는 대신 psql \password를 사용한다(로그와 history에 남지 않는다).