PostgreSQL에서 사용자, 역할 및 권한 관리를 위한 필수 명령어

PostgreSQL의 사용자, 역할 및 권한은 누가 연결할 수 있는지, 무엇을 읽을 수 있는지, 무엇을 변경할 수 있는지를 결정합니다. 이러한 권한이 추측에 의해 관리되면 데이터베이스를 감사하기 어렵고 과도하게 노출되기 쉽습니다.

PostgreSQL은 로그인 사용자와 그룹 모두에 대해 역할(role)이라는 하나의 개념을 사용합니다. LOGIN 속성이 있는 역할은 연결할 수 있습니다. LOGIN 속성이 없는 역할은 공유 권한을 전달하는 그룹으로 자주 사용됩니다.

역할 생성 및 변경

비밀번호가 있는 애플리케이션 로그인 역할을 생성합니다:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

공유 읽기 전용 액세스를 위한 그룹 역할을 생성합니다:

CREATE ROLE reporting_readonly NOLOGIN;

일반적인 역할 속성은 다음과 같습니다:

속성	허용되는 작업
LOGIN	데이터베이스 서버에 연결합니다.
CREATEDB	데이터베이스를 생성합니다.
CREATEROLE	많은 역할을 생성, 변경 및 삭제합니다(슈퍼유저 역할에는 중요한 제한이 있음).
REPLICATION	복제 연결을 사용합니다.
SUPERUSER	일반 권한 검사를 우회합니다. 엄격하게 통제된 관리자 역할에만 사용하세요.
INHERIT	멤버로 속한 역할의 권한을 사용합니다. 기본값입니다.

ALTER ROLE을 사용하여 비밀번호를 변경하거나 로그인을 비활성화합니다:

ALTER ROLE app_user WITH PASSWORD 'new_strong_password';
ALTER ROLE old_employee NOLOGIN;

역할을 삭제하기 전에 해당 역할이 소유한 모든 항목과 부여된 권한을 처리합니다:

REASSIGN OWNED BY old_employee TO postgres;
DROP OWNED BY old_employee;
DROP ROLE old_employee;

REASSIGN OWNED는 소유한 객체를 이전합니다. DROP OWNED는 역할에 부여된 권한을 제거하고 현재 데이터베이스에서 역할이 여전히 소유한 객체를 삭제할 수 있으므로 실행하기 전에 신중히 검토하세요.

더 깔끔한 액세스를 위한 그룹 역할 사용

그룹 역할에 권한을 부여한 다음 사람이나 애플리케이션 역할을 해당 그룹에 추가합니다.

GRANT reporting_readonly TO alice, bob;
REVOKE reporting_readonly FROM bob;

WITH ADMIN OPTION을 사용하면 멤버가 다른 사람에게 해당 역할 멤버십을 부여하고 취소할 수 있습니다. 이것만으로 멤버가 슈퍼유저가 되거나 데이터베이스에 대한 무제한 권한을 갖는 것은 아닙니다.

GRANT reporting_readonly TO team_lead WITH ADMIN OPTION;

이 패턴은 액세스를 감사하기 쉽게 유지합니다. 50명의 사용자에 대한 테이블 권한을 확인하는 대신 그룹 역할을 확인할 수 있습니다.

객체 권한 부여

GRANT를 사용하여 특정 객체에 대한 특정 작업을 허용합니다.

GRANT SELECT ON TABLE production.orders TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.orders TO app_user;

일반적인 테이블 권한은 SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES 및 TRIGGER입니다.

스키마 권한은 테이블 권한과 별개입니다. 역할은 종종 스키마에 대한 USAGE와 그 안에 있는 테이블에 대한 권한이 필요합니다:

GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_user;

시퀀스를 사용하는 삽입의 경우 시퀀스 액세스도 부여합니다:

GRANT USAGE, SELECT ON SEQUENCE app.orders_id_seq TO app_user;

함수의 경우 명시적으로 실행 권한을 부여합니다:

GRANT EXECUTE ON FUNCTION app.audit_log_insert(text) TO app_user;

권한 취소

REVOKE는 직접 부여를 제거합니다.

REVOKE INSERT ON TABLE production.orders FROM app_user;
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM reporting_readonly;

취소 후에도 사용자가 여전히 액세스 권한이 있다면 그룹 멤버십을 확인하세요. 사용자가 다른 역할을 통해 동일한 권한을 상속받을 수 있습니다.

향후 테이블에 대한 권한 설정

GRANT ON ALL TABLES는 기존 테이블에만 영향을 미칩니다. 나중에 생성되는 테이블의 경우 해당 테이블을 생성할 역할에서 ALTER DEFAULT PRIVILEGES를 사용합니다.

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO reporting_readonly;

이는 PostgreSQL 권한 설정에서 가장 흔히 누락되는 단계 중 하나입니다. 사용자는 오늘의 테이블을 읽을 수 있지만 기본 권한이 구성되지 않았기 때문에 내일의 테이블에서는 거부됩니다.

현재 액세스 감사

psql에서 다음 명령은 빠른 확인에 유용합니다:

\du
\du+ app_user
\dp app.orders
\dn+

카탈로그 뷰는 반복 가능한 보고서에 더 좋습니다:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

테이블 권한의 경우:

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY grantee, table_name, privilege_type;

효과적인 보안 습관

인간 DBA 계정 대신 애플리케이션 전용 역할을 사용하세요. 애플리케이션에 필요한 최소한의 권한 집합을 부여하세요. 일상적인 작업에 SUPERUSER 및 광범위한 ALL PRIVILEGES 부여를 피하세요.

공유 액세스에는 그룹 역할을 사용하고 정기적으로 멤버십을 검토하세요. 팀원이 변경될 때 하나의 그룹 멤버십을 제거하는 것이 테이블 수준 권한을 찾아 제거하는 것보다 안전합니다.

실용적인 다음 단계는 하나의 프로덕션 스키마를 선택하고 해당 역할, 멤버십, 테이블 권한 및 기본 권한을 나열하는 것입니다. 그곳의 격차를 해결한 후 동일한 패턴을 나머지 데이터베이스로 확장하세요.