숏컷 코드

CREATE ROLE app_user LOGIN PASSWORD 'secret';

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;

권한 모델

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

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

CREATE ROLE readonly;
CREATE ROLE app_reader LOGIN PASSWORD 'secret';

GRANT readonly TO app_reader;

운영에서는 개인 계정, 애플리케이션 계정, 권한 묶음 role을 분리하는 편이 관리하기 쉽습니다. 애플리케이션이 owner role로 접속하면 실수로 DDL이나 전체 삭제 권한까지 갖게 됩니다.

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을 만드는가"까지 함께 고정해야 합니다.

운영 기준

주의할 점

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