Использование учетной записи без роли суперпользователя

Если не используется учетная запись с ролью суперпользователя superuser (или с ролью rds_superuser при работе с Amazon Aurora или Amazon RDS), необходимо создать несколько объектов для захвата событий DDL (Data Definition Language ― язык определения данных). Создайте эти объекты в выбранной учетной записи, а затем создайте триггер в основной учетной записи пользователя.

Порядок действий:

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. Если выполнен вход в непривилегированную учетную запись, выйдите из нее и войдите в учетную запись, которой назначена роль суперпользователя superuser (или роль rds_superuser при работе с Amazon Aurora или Amazon RDS).

   Примечание к информации

   Если хранимая процедура attrep_intercept_ddl создается не в схеме по умолчанию, необходимо указать имя схемы в поле Создавать артефакты DDL в схеме в параметрах коннектора PostgreSQL.

   Для получения дополнительной информации о параметрах конфигурации репликации см. справку 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 Database for PostgreSQL, 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>;