Quick Reference

CREATE ROLE app_user LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE posts TO app_user;

REVOKE DELETE ON TABLE posts FROM app_user;

runtime role은 owner·superuser가 아닌 login role로 만들고 필요한 database, schema, table, sequence 권한만 줍니다. SERIAL·identity의 자동 번호는 sequence USAGE가 필요할 수 있으므로 INSERT grant만으로 끝나지 않는지 확인합니다. 새 object 권한은 생성 role별 ALTER DEFAULT PRIVILEGES로 따로 정하며, PUBLIC에 이미 열려 있는 권한도 현재 ACL에서 점검합니다.

권한 모델

role은 사용자이기도 하고 권한 묶음이기도 하다

PostgreSQL에서 role은 login 가능한 사용자 계정일 수도 있고, 여러 사용자에게 부여하는 권한 묶음일 수도 있습니다. LOGIN 속성이 있으면 접속 계정으로 쓸 수 있고, 없으면 그룹처럼 권한을 모으는 용도로 쓸 수 있습니다.

CREATE ROLE readonly;
CREATE ROLE app_reader LOGIN;

GRANT readonly TO app_reader;

운영에서는 개인 계정, 애플리케이션 계정, 권한 묶음 role을 분리하는 편이 관리하기 쉽습니다. 애플리케이션이 owner role로 접속하면 실수로 DDL이나 전체 삭제 권한까지 갖게 됩니다. 비밀번호나 인증서 같은 자격 증명은 SQL 예제와 저장소 이력에 넣지 말고 배포 환경의 secret 관리 경로에서 설정합니다.

object별 privilege를 따로 부여한다

데이터베이스 접속 권한, schema 사용 권한, table 접근 권한은 서로 다릅니다. CONNECT만 있어도 테이블을 읽을 수 있는 것은 아니고, table privilege만 있어도 schema USAGE가 없으면 접근이 막힐 수 있습니다.

GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON TABLE posts TO app_user;

권한 문제를 볼 때는 "DB에 접속 가능한가", "schema 이름을 해석할 수 있는가", "table에 해당 작업 권한이 있는가"를 단계별로 나눠 확인합니다.

기본 권한은 새 object에 자동 적용할 때 쓴다

기존 table에 GRANT를 해도 나중에 새로 만드는 table에는 자동으로 같은 권한이 붙지 않습니다. 새 object에도 같은 권한을 부여하려면 ALTER DEFAULT PRIVILEGES를 사용합니다. 이 설정은 object를 생성하는 role 기준으로 적용됩니다.

ALTER DEFAULT PRIVILEGES
  IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

마이그레이션을 실행하는 role과 object owner가 다르면 기본 권한이 예상과 다르게 적용될 수 있습니다. 권한 자동화는 "누가 table을 만드는가"까지 함께 고정해야 합니다.

기본 권한은 기존 object를 바꾸지 않고, 다른 role이 만든 object에 상속되지도 않습니다. PUBLIC에 부여된 schema·function 등의 권한은 모든 role에 적용될 수 있으므로, 새 database template의 기본값을 가정하지 말고 \\dn+, \\dp, information_schema 또는 ACL 조회로 현재 상태를 확인합니다.

INSERT 권한과 sequence 권한은 자동 증가 키에서 함께 확인한다

SERIAL 또는 identity column의 자동 값은 내부 sequence의 nextval()을 사용합니다. 대상 table의 INSERT 권한만 있고 sequence USAGE가 없으면 insert가 실패할 수 있습니다. 반대로 sequence privilege만 넓게 주면 번호를 직접 소비할 수 있으므로 필요한 sequence에만 부여합니다.

GRANT INSERT ON TABLE users TO app_user;
GRANT USAGE ON SEQUENCE users_id_seq TO app_user;

운영 기준

주의할 점

GRANT ALL은 편하지만 운영 권한 설계에서는 범위가 넓습니다. 특히 DELETE, TRUNCATE, REFERENCES, TRIGGER 같은 권한은 읽기/쓰기 업무와 다른 위험을 갖습니다. 권한은 "이 role이 실제로 수행해야 하는 SQL"을 기준으로 좁게 부여합니다.