슈퍼 사용자 역할이 없는 계정 사용

슈퍼 사용자 역할(또는 Amazon Aurora 또는 Amazon RDS에서 작업하는 경우 rds_superuser 역할)이 있는 계정을 사용하지 않는 경우 DDL(데이터 정의 언어) 이벤트를 캡처하기 위해 여러 개체를 만들어야 합니다. 선택한 계정에서 이러한 개체를 만든 다음 마스터 사용자 계정에서 트리거를 만듭니다.

수행할 작업:

1. 개체를 만들 스키마를 선택합니다. 기본 스키마는 공개입니다. 스키마가 존재하고 권한이 없는 계정에서 액세스할 수 있는지 확인합니다.

2. 다음 명령을 실행하여 attrep_ddl_audit 테이블을 만듭니다.

   create table <objects_schema>.attrep_ddl_audit
   (
   c_key    bigserial primary key,
   c_time   timestamp,    -- Informational
   c_user   varchar(64),  -- Informational: Current_user
   c_txn    varchar(16),  -- Informational: Current transaction
   c_tag    varchar(24),  -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
   c_oid    integer,      -- For future use - TG_OBJECTID
   c_name   varchar(64),  -- For future use - TG_OBJECTNAME
   c_schema varchar(64),  -- For future use - TG_SCHEMANAME. For now, holds the current_schema
   c_ddlqry  text         -- The DDL query associated with the current DDL event
   );

3. 다음 명령을 실행하여 attrep_intercept_ddl 함수를 만듭니다.

   CREATE OR REPLACE FUNCTION <objects_schema>.attrep_intercept_ddl()
     RETURNS event_trigger
   LANGUAGE plpgsql
     AS $$
     declare _qry text;
   BEGIN
     if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
            SELECT current_query() into _qry;
            insert into <objects_schema>.attrep_ddl_audit
            values
            (
            default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
            );
            delete from <objects_schema>.attrep_ddl_audit;
   end if;
   END;
   $$;
   

4. 권한이 없는 계정으로 로그인한 경우 권한이 없는 계정에서 로그아웃하고 슈퍼 사용자 역할(Amazon Aurora 또는 Amazon RDS를 사용하는 경우 rds_superuser 역할)이 할당된 계정으로 로그인합니다.

   정보 메모

   기본 스키마에 attrep_intercept_ddl 저장 절차가 만들어지지 않는 경우 PostgreSQL 커넥터 설정의 스키마에 DDL 아티팩트 만들기 필드에 스키마 이름을 지정해야 합니다.

   복제 구성 매개 변수에 대한 자세한 내용은 PostgreSQL 도움말을 참조하십시오.

5. 다음 명령을 실행하여 이벤트 트리거 attrep_intercept_ddl을 만듭니다.

   CREATE EVENT TRIGGER attrep_intercept_ddl ON ddl_command_end

   EXECUTE PROCEDURE <objects_schema>.attrep_intercept_ddl();

6. 권한이 없는 계정에 다음 권한을 부여합니다.

   • GRANT INSERT ON attrep_ddl_audit to <non-privileged-user>;
   • GRANT DELETE ON attrep_ddl_audit to <non-privileged-user>;

   • GRANT USAGE ON attrep_ddl_audit_c_key_seq TO <non-privileged-user>;

   • PostgreSQL 온프레미스, Microsoft Azure for PostgreSQL Database, Google Cloud SQL for PostgreSQL 또는 Google Cloud AlloyDB for PostgreSQL을 사용하는 경우 다음 권한을 부여합니다.

     ALTER ROLE <non-privileged-user> WITH REPLICATION;

   • Amazon RDS for PostgreSQL 또는 Amazon Aurora를 사용하는 경우 다음 권한을 부여합니다.

     GRANT rds_replication to <non-privileged-user>;

7. 사용자에게 SELECT 권한을 부여합니다.

   GRANT SELECT ON ALL TABLES IN SCHEMA <SCHEMA _NAME> TO <non-privileged-user>;

8. 스키마에 대한 사용 권한을 사용자에게 부여합니다.

   GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO <non-privileged-user>;