Amazon RDS for PostgreSQL의 일반적인 DBA 태스크 - Amazon Relational Database Service

Amazon RDS for PostgreSQL의 일반적인 DBA 태스크

데이터베이스 관리자(DBA)는 Amazon RDS for PostgreSQL DB 인스턴스를 관리할 때 다양한 작업을 수행합니다. PostgreSQL을 이미 익숙하게 사용하는 DBA라면 하드웨어에서 PostgreSQL을 실행하는 것과 RDS for PostgreSQL을 실행하는 것 사이의 중요한 차이점을 알고 있어야 합니다. 예를 들어 이는 관리형 서비스이므로, Amazon RDS에서 DB 인스턴스에 대한 shell 액세스를 허용하지 않습니다. 즉, pg_hba.conf 및 다른 구성 파일에 직접 액세스할 수 없으며, 온프레미스 PostgreSQL 인스턴스와 동일한 방식으로 로그 파일을 사용할 수 없습니다. 로깅에 대해 자세히 알아보려면 PostgreSQL 데이터베이스 로그 파일 섹션을 참조하세요.

다른 예로, PostgreSQL superuser 계정에 액세스할 권한이 없습니다. RDS for PostgreSQL에서 rds_superuser 역할은 가장 높은 권한을 가진 역할이며 설정 시 postgres에 부여됩니다. PostgreSQL 온프레미스 사용에 익숙하든, RDS for PostgreSQL을 처음 사용하든 관계없이 rds_superuser 역할 및 역할, 사용자, 그룹, 권한으로 작업하는 방식을 이해하는 것이 좋습니다. 자세한 정보는 PostgreSQL 역할 및 권한 이해을 참조하십시오.

다음은 RDS for PostgreSQL의 일반적인 DBA 태스크 일부입니다.

PostgreSQL 역할 및 권한 이해

AWS Management Console을 사용하여 RDS for PostgreSQL DB 인스턴스를 만들면 관리자 계정이 동시에 생성됩니다. 기본적으로 해당 이름은 다음 스크린샷에 나와 있는 것처럼 postgres입니다.


        데이터베이스 생성(Create database) 페이지의 보안 인증에 대한 기본 로그인 ID는 postgres입니다.

기본값(postgres)을 그대로 사용하지 않고 다른 이름을 선택할 수 있습니다. 이 경우 선택한 이름은 문자로 시작해야 하며 영숫자 1~16자 사이어야 합니다. 단순하게 하기 위해 이 안내서 전체에서 기본 사용자 계정을 기본값(postgres)으로 참조합니다.

AWS Management Console 대신 create-db-instance AWS CLI를 사용하는 경우 명령에서 master-username 파라미터로 이름을 전달하여 이름을 생성합니다. 자세한 내용은 Amazon RDS DB 인스턴스 생성 섹션을 참조하세요.

AWS Management Console, AWS CLI 또는 Amazon RDS API를 사용하든, 기본 postgres 이름을 사용하든, 다른 이름을 선택하든 관계없이 첫 번째 데이터베이스 사용자 계정은 rds_superuser 그룹의 멤버이며 rds_superuser 권한을 가집니다.

rds_superuser 역할 이해

PostgreSQL에서는 역할로 데이터베이스의 다양한 객체에 대해 그룹 또는 사용자에게 부여된 사용자, 그룹 또는 특정 권한 집합을 정의할 수 있습니다. CREATE USERCREATE GROUP에 대한 PostgreSQL 명령은 데이터베이스 사용자를 구분하기 위한 특정 속성을 가진 보다 일반적인 CREATE ROLE로 대체되었습니다. 데이터베이스 사용자는 LOGIN 권한을 가진 역할로 간주할 수 있습니다.

참고

CREATE USERCREATE GROUP 명령을 계속 사용할 수 있습니다. 자세한 내용은 PostgreSQL 설명서에서 데이터베이스 역할을 참조하세요.

postgres 사용자는 RDS for PostgreSQL DB 인스턴스에서 가장 높은 권한을 지닌 데이터베이스 사용자입니다. 이는 다음 CREATE ROLE 문으로 정의되는 특성을 가지고 있습니다.

CREATE ROLE postgres WITH LOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity'

속성 NOSUPERUSER, NOREPLICATION, INHERIT, VALID UNTIL 'infinity'는 별도로 지정하지 않는 한 CREATE ROLE의 기본 옵션입니다.

기본적으로 postgres에는 rds_superuser 역할이 부여된 권한이 있습니다. rds_superuser 역할이 있으면 postgres 사용자는 다음과 같은 작업을 할 수 있습니다.

  • Amazon RDS에서 사용할 수 있는 확장 기능을 추가합니다. 자세한 내용은 Amazon RDS for PostgreSQL에서 지원되는 PostgreSQL 기능 작업

  • 사용자에 대한 역할을 생성하고 사용자에게 권한을 부여합니다. 자세한 내용은 PostgreSQL 설명서에서 CREATE ROLEGRANT를 참조하세요.

  • 데이터베이스를 생성합니다. 자세한 내용은 PostgreSQL 설명서에서 CREATE DATABASE를 참조하세요.

  • 이러한 권한이 없는 다른 사용자 역할에 rds_superuser 권한을 부여하고 필요에 따라 해당 권한을 회수합니다. 필요에 따라서만 이 역할을 다른 사람에게 부여할 수 있는 것이 좋습니다. 즉, DBA 또는 시스템 관리자가 아닌 사용자에게 이 역할을 부여하지 마세요.

  • rds_superuser 역할이 없는 데이터베이스 사용자에게 rds_replication 역할을 부여(회수)합니다.

  • rds_superuser 역할이 없는 데이터베이스 사용자에게 rds_password 역할을 부여(회수)합니다.

  • pg_stat_activity 보기를 사용하여 모든 데이터베이스 연결에 대한 상태 정보를 가져옵니다. 필요한 경우 rds_superuserpg_terminate_backend 또는 pg_cancel_backend를 사용하여 연결을 중지할 수 있습니다.

CREATE ROLE postgres... 문에서 postgres 사용자 역할이 특히 PostgreSQL superuser 권한을 허용하지 않음을 알 수 있습니다. RDS for PostgreSQL은 관리형 서비스이므로 호스트 OS에 액세스할 수 없으며, PostgreSQL superuser 계정을 사용하여 연결할 수 없습니다. 독립 실행형 PostgreSQL에 대한 superuser 액세스 권한이 필요한 대부분의 태스크는 Amazon RDS에서 자동으로 관리됩니다.

권한 부여에 대한 자세한 내용은 PostgreSQL 설명서에서 GRANT를 참조하세요.

rds_superuser 역할은 에서 미리 정의된 여러 역할 중 하나입니다. RDS for PostgreSQL DB 인스턴스

참고

PostgreSQL 13 및 이전 릴리스에서는 미리 정의된 역할을 기본 역할이라고 합니다.

다음 목록에서 새 에 대해 자동으로 생성되는 미리 정의된 다른 역할 중 일부를 확인할 수 있습니다. RDS for PostgreSQL DB 인스턴스 미리 정의된 역할 및 권한은 변경할 수 없습니다. 이러한 미리 정의된 역할에 대한 권한은 삭제 또는 수정하거나 이름을 변경할 수 없습니다. 이렇게 하려고 하면 오류가 발생합니다.

  • rds_password - 데이터베이스 사용자를 대상으로 암호를 변경하고 암호 제약 조건을 설정할 수 있는 역할입니다. rds_superuser 역할에는 기본적으로 이 역할이 부여되며, 이 역할을 통해 데이터베이스 사용자에게 역할을 부여할 수 있습니다. 자세한 정보는 PostgreSQL 데이터베이스에 대한 사용자 액세스 제어 섹션을 참조하세요.

  • rdsadmin - superuser 권한이 있는 관리자가 독립 실행형 PostgreSQL 데이터베이스에서 수행할 수 있는 많은 관리 태스크를 처리하기 위해 생성된 역할입니다. 이 역할은 RDS for PostgreSQL에서 다양한 관리 태스크에 내부적으로 사용됩니다.

  • rdstopmgr - 다중 AZ 배포를 지원하기 위해 Amazon RDS에서 내부적으로 사용하는 역할입니다.

미리 정의된 모든 역할을 보려면 RDS for PostgreSQL DB 인스턴스의 프라이머리 인스턴스에 연결하여 psq1 \du 메타 명령을 사용하면 됩니다. 출력값은 다음과 같습니다.

List of roles Role name | Attributes | Member of --------------+-----------------------------------+------------------------------------ postgres | Create role, Create DB +| {rds_superuser} | Password valid until infinity | rds_superuser | Cannot login | {pg_monitor,pg_signal_backend, | +| rds_replication,rds_password} ...

출력에서 rds_superuser는 데이터베이스 사용자 역할은 아니지만(로그인할 수 없음), 다른 많은 역할의 권한을 가집니다. 데이터베이스 사용자 postgresrds_superuser 역할의 멤버임을 알 수도 있습니다. 앞서 언급한 바와 같이 postgres는 Amazon RDS 콘솔 데이터베이스 생성(Create database) 페이지의 기본값입니다. 다른 이름을 선택한 경우 해당 이름이 대신 역할 목록에 표시됩니다.

PostgreSQL 데이터베이스에 대한 사용자 액세스 제어

PostgreSQL의 새 데이터베이스는 항상 모든 데이터베이스 사용자와 역할이 객체를 만들 수 있도록 허용하는 데이터베이스 public 스키마의 기본 권한 집합으로 생성됩니다. 이러한 권한을 통해 데이터베이스 사용자는 데이터베이스에 연결하고, 가령 연결된 동안 임시 테이블을 만들 수 있습니다.

RDS for PostgreSQL DB 인스턴스 에 생성한 데이터베이스 인스턴스에 액세스할 수 있는 사용자 권한을 보다 효과적으로 제어하려면 이러한 기본 public 권한을 회수하는 것이 좋습니다. 이렇게 하고 나서 다음 절차에 표시된 것처럼 데이터베이스 사용자에게 보다 세분화된 구체적인 권한을 부여하면 됩니다.

새 데이터베이스 인스턴스에 대한 역할 및 권한을 설정하는 방법

새로 생성한 RDS for PostgreSQL DB 인스턴스에 데이터베이스를 설정하여 여러 연구자가 사용할 수 있도록 하고, 이들 모두 데이터베이스에 대한 읽기-쓰기 액세스 권한이 필요하다고 가정합니다.

  1. 다음과 같이 psql 또는 pgAdmin을 사용하여 RDS for PostgreSQL DB 인스턴스에 연결합니다.

    psql --host=your-db-instance.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password

    메시지가 표시되면 암호를 입력합니다. psql 클라이언트는 기본 관리 연결 데이터베이스인 postgres=>를 연결하고 프롬프트로 표시합니다.

  2. 데이터베이스 사용자가 public 스키마에서 객체를 생성하지 못하도록 하려면 다음을 수행합니다.

    postgres=> REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE
  3. 다음으로 새 데이터베이스 인스턴스를 생성합니다.

    postgres=> CREATE DATABASE lab_db; CREATE DATABASE
  4. 새 데이터베이스의 PUBLIC 스키마에서 모든 권한을 회수합니다.

    postgres=> REVOKE ALL ON DATABASE lab_db FROM public; REVOKE
  5. 데이터베이스 사용자를 위한 역할을 생성합니다.

    postgres=> CREATE ROLE lab_tech; CREATE ROLE
  6. 이 역할이 있는 데이터베이스 사용자에게 데이터베이스 연결 기능을 제공합니다.

    postgres=> GRANT CONNECT ON DATABASE lab_db TO lab_tech; GRANT
  7. lab_tech 역할이 있는 모든 사용자에게 이 데이터베이스에 대한 모든 권한을 부여합니다.

    postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_db TO lab_tech; GRANT
  8. 다음과 같이 데이터베이스 사용자를 생성합니다.

    postgres=> CREATE ROLE lab_user1 LOGIN PASSWORD 'change_me'; CREATE ROLE postgres=> CREATE ROLE lab_user2 LOGIN PASSWORD 'change_me'; CREATE ROLE
  9. 다음과 같이 두 사용자에게 lab_tech 역할과 관련된 권한을 부여합니다.

    postgres=> GRANT lab_tech TO lab_user1; GRANT ROLE postgres=> GRANT lab_tech TO lab_user2; GRANT ROLE

이 시점에서 lab_user1lab_user2lab_db 데이터베이스에 연결할 수 있습니다. 이 예제에서는 여러 데이터베이스 인스턴스와 다양한 스키마 생성, 제한된 권한 부여를 포함할 수 있는 엔터프라이즈 사용에 대한 모범 사례를 따르지 않습니다. 전체 정보 및 추가 시나리오를 알아보려면 PostgreSQL 사용자 및 역할 관리를 참조하세요.

PostgreSQL 데이터베이스의 권한에 대한 자세한 내용은 PostgreSQL 설명서에서 GRANT 명령을 참조하세요.

사용자 암호 관리 위임 및 제어

DBA로서 사용자 암호 관리를 위임할 수 있습니다. 또는 데이터베이스 사용자가 암호를 변경하거나 암호 사용 주기와 같은 암호 제약 조건을 재구성하지 못하도록 할 수 있습니다. 선택한 데이터베이스 사용자만 암호 설정을 변경할 수 있도록 하려면 제한된 암호 관리 기능을 켜면 됩니다. 이 기능을 활성화하면 rds_password 역할이 부여된 데이터베이스 사용자만 암호를 관리할 수 있습니다.

참고

제한된 암호 관리를 사용하려면 RDS for PostgreSQL DB 인스턴스가 PostgreSQL 10.6 이상을 실행해야 합니다.

기본적으로 이 기능은 다음과 같이 off입니다.

postgres=> SHOW rds.restrict_password_commands; rds.restrict_password_commands -------------------------------- off (1 row)

이 기능을 켜려면 사용자 정의 파라미터 그룹을 사용하고 rds.restrict_password_commands에 대한 설정을 1로 변경합니다. RDS for PostgreSQL DB 인스턴스를 재부팅해야 설정이 적용됩니다.

이 기능을 활성화하면 다음 SQL 명령에 대한 rds_password 권한이 필요합니다.

CREATE ROLE myrole WITH PASSWORD 'mypassword'; CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01'; ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2023-01-01'; ALTER ROLE myrole WITH PASSWORD 'mypassword'; ALTER ROLE myrole VALID UNTIL '2023-01-01'; ALTER ROLE myrole RENAME TO myrole2;

암호에서 MD5 해시 알고리즘을 사용하는 경우에도 역할 이름 변경(ALTER ROLE myrole RENAME TO newname)이 제한됩니다.

이 기능이 활성화된 상태에서 rds_password 역할 권한 없이 이러한 SQL 명령을 시도하면 다음 오류가 발생합니다.

ERROR: must be a member of rds_password to alter passwords

암호 관리에만 사용하는 몇 가지 역할에만 rds_password 권한을 부여하는 것이 좋습니다. rds_superuser 권한이 없는 데이터베이스 사용자에게 rds_password 권한을 부여할 경우 CREATEROLE 속성도 부여해야 합니다.

만료 및 클라이언트 측에 필요한 복잡성 등의 암호 요구 사항을 확인해야 합니다. 암호 관련 변경 사항에 대해 자체 클라이언트 측 유틸리티를 사용하는 경우 유틸리티가 rds_password의 멤버여야 하며 CREATE ROLE 권한을 가져야 합니다.

RDS for PostgreSQL에서 지원되는 로깅 메커니즘 작업

PostgreSQL DB 인스턴스에 발생하는 기록 활동을 설정할 수 있는 파라미터, 확장 및 기타 구성 항목이 몇 가지 있습니다. 다음을 포함합니다.

  • log_statement 파라미터는 PostgreSQL 데이터베이스에서 사용자 활동 로그에 사용할 수 있습니다. RDS for PostgreSQL 로깅 및 로그를 모니터링하는 방법에 대한 자세한 내용은 PostgreSQL 데이터베이스 로그 파일 섹션을 참조하세요.

  • rds.force_admin_logging_level 파라미터는 Amazon RDS 내부 사용자(rdsadmin)가 DB 인스턴스의 데이터베이스에서 수행한 작업을 기록합니다. PostgreSQL 오류 로그에 출력을 기록합니다. 허용되는 값은 disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, 로그, fatalpanic입니다. 기본값은 disabled입니다.

  • PostgreSQL 오류 로그에서 다양한 autovacuum 작업을 캡처하도록 rds.force_autovacuum_logging_level 파라미터를 설정할 수 있습니다. 자세한 정보는 autovacuum 및 vacuum 활동 로그을 참조하십시오.

  • PostgreSQL Audit(pgAudit) 확장 프로그램은 세션 수준 또는 객체 수준에서 활동을 캡처하도록 설치 및 구성할 수 있습니다. 자세한 정보는 pgAudit 확장 프로그램을 사용하여 세션 및 객체 수준에서 로깅을 참조하십시오.

  • log_fdw 확장을 통해 SQL을 사용하여 데이터베이스 엔진 로그에 액세스할 수 있습니다. 자세한 정보는 log_fdw 확장으로 SQL을 사용하여 DB 로그에 액세스의 내용을 참조하세요.

  • pg_stat_statements 라이브러리가 RDS for PostgreSQL 버전 10 이상의 shared_preload_libraries 파라미터에 대한 기본값으로 지정됩니다. 실행 중인 쿼리를 분석할 때 사용할 수 있는 라이브러리입니다. pg_stat_statements가 DB 파라미터 그룹에 설정되는 것을 확인하세요. 이 라이브러리에서 제공하는 정보를 사용하여 RDS for PostgreSQL DB 인스턴스를 모니터링하는 방법에 대한 자세한 내용은 RDS PostgreSQL에 대한 SQL 통계 섹션을 참조하세요.

일반적으로 로깅의 목적은 DBA가 성능을 모니터링하고 튜닝하고 문제를 해결할 수 있도록 하는 것입니다. 대부분의 로그가 Amazon CloudWatch 또는 성능 개선 도우미로 자동 업로드됩니다. 여기서 로그는 DB 인스턴스에 대한 전체 지표를 제공하기 위해 정렬되고 그룹화됩니다. Amazon RDS 모니터링 및 지표에 대한 자세한 내용은 Amazon RDS 인스턴스에서 지표 모니터링 섹션을 참조하세요.

pgBadger를 사용한 PostgreSQL의 로그 분석

pgBadger 등의 로그 분석기를 사용하여 PostgreSQL 로그를 분석할 수 있습니다. pgBadger 문서에는 %l 패턴(세션 또는 프로세스의 로그 라인)은 접두사에 포함되어야 한다고 나와 있습니다. 그러나 현재 RDS log_line_prefix를 pgBadger에 파라미터로 사용하더라도 여전히 보고서를 생성해야 합니다.

예를 들어 다음 명령은 pgBadger를 사용하여 2014-02-04 일자의 Amazon RDS for PostgreSQL 로그 파일을 정확한 형식으로 나타내고 있습니다.

./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00