Como uso a extensão pgAudit para auditar minha instância de banco de dados do Amazon RDS para PostgreSQL?

Resolução

Há parâmetros diferentes que é possível definir para registrar atividades na sua instância de banco de dados do RDS para PostgreSQL. Depois de ativar a extensão pgAudit, é possível configurar o parâmetro pgaudit.log para auditar bancos de dados, perfis, tabelas e colunas específicos. Primeiro, defina o valor do parâmetro pgaudit.log como nenhum no grupo de parâmetros de um banco de dados, perfil ou tabela:

> show pgaudit.log;+---------------+
| pgaudit.log   |
|---------------|
| none          |
+---------------+
SHOW

Use a extensão pgAudit para auditar bancos de dados

Conclua as seguintes etapas:

1. Execute o comando a seguir para substituir a configuração do sistema para o parâmetro pgaudit.log somente em seu banco de dados:

   ALTER DATABASE test_database set pgaudit.log='All';

   Observação: Substitua test_database pelo nome do seu banco de dados. O comando anterior altera o valor do parâmetro pgaudit.log para All para que o pgAudit audite somente o test_database.
2. Conecte-se ao seu banco de dados e, em seguida, execute a seguinte consulta para auditar o banco de dados:

   select * from aud_table;

   Observação: Substitua aud_table pela tabela que você deseja auditar.
   A saída do log de erros é semelhante à seguinte:

   2019-06-25 19:21:35 UTC:192.0.2.7(39330):testpar@test_database:[21638]:LOG: AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not logged>

Use a extensão pgAudit para auditar perfis

Você também pode modificar os perfis no parâmetro pgaudit.log para que tenham valores diferentes. Para definir valores diferentes para o parâmetro pgaudit.log para seus perfis, execute os seguintes comandos:

ALTER ROLE test1 set pgaudit.log='All';

ALTER ROLE test2 set pgaudit.log='DDL';

Observação: Substitua test1 e test2 pelos nomes de seus perfis.

Para verificar se as modificações foram feitas no nível do perfil, execute a seguinte consulta:

> select rolname,rolconfig from pg_roles where rolname in ('test1','                  test2');
+-----------+----------------------+
| rolname   | rolconfig            |
|-----------+----------------------|
| test1     | [u'pgaudit.log=All'] |
| test2     | [u'pgaudit.log=DDL'] |
+-----------+----------------------+
SELECT 2
Time: 0.010s

Observação: Substitua test1 e test2 pelos nomes de seus perfis.

Para testar o comportamento de log do pgAudit de seus perfis, execute as seguintes consultas para cada perfil:

CREATE TABLE test_table (id int);CREATE TABLE

select * from test_table;id
----
(0 rows)

A saída de log para dois perfis diferentes é semelhante aos exemplos a seguir.

test1:

...2019-06-26 14:51:12 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>

2019-06-26 14:51:18 UTC:192.0.2.7(44754):test1@postgres:[3547]:LOG:  
AUDIT: SESSION,2,1,READ,SELECT,,,select * from test_table;,<not
logged>
...

test2:

...2019-06-26 14:53:54 UTC:192.0.2.7(44772):test2@postgres:[5517]:LOG:  
AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE test_table (id
int);,<not logged>
...

Na saída do exemplo test2, não há uma entrada de auditoria para a consulta SELECT porque o parâmetro pgaudit.log para test2 está definido somente como DDL. Esse teste verifica se o perfil test2 registra somente operações DDL.

Use a extensão pgAudit para auditar tabelas

Observação: A extensão pgAudit pode registrar somente os comandos SELECT, INSERT, UPDATE e DELETE. A extensão não pode auditar TRUNCATE.

Para auditar suas tabelas, execute o seguinte comando para conceder ao perfil rds_pgaudit acesso aos comandos:

grant select, delete on test_table to rds_pgaudit;

Observação: O comando anterior permite que o perfil rds_pgaudit acesse SELECT e DELETE. Substitua select e delete pelos comandos que você deseja auditar.

Para verificar se o log de auditoria está configurado corretamente, execute uma instrução na tabela. O comando de exemplo a seguir executa uma instrução DELETE em test_table:

Time: 0.008s DELETE 1
>delete from test_table where pid=5050;

A saída da instrução DELETE é semelhante à seguinte:

2019-06-25 17:13:02UTC:192.0.2.7(41810):postgresql104saz@postgresql104saz:[24976]:LOG:
AUDIT: OBJECT,3,1,WRITE,DELETE,TABLE,public.t1,delete from test_table where
pid=5050,<not logged>

Use a extensão pgAudit para auditar colunas

Você pode auditar colunas de uma tabela específica, como quando dados sigilosos existem em apenas uma coluna. O comando de exemplo a seguir cria uma tabela de folha de pagamento que tem uma coluna sensível com dados salariais que devem ser auditados:

create table payroll(    
    name text,
    salary text
);

Para auditar a coluna de salário, primeiro, execute o seguinte comando para conceder ao perfil rds_pgaudit acesso a SELECT na coluna salary:

grant select (salary)  on payroll to rds_pgaudit;

Em seguida, execute o comando a seguir de SELECT para todas as colunas na tabela, incluindo a coluna salary:

select * from payroll;

Se a consulta SELECT não contiver a coluna salary, o pgAudit não auditará essa coluna.

No exemplo de saída a seguir, pgAudit audita qualquer SELECT que inclua a coluna salary:

2019-06-25 18:25:02
UTC:192.0.2.7(42056):postgresql104saz@postgresql104saz:[4118]:LOG:
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.payroll,select * from
payroll,<not logged>

Informações relacionadas

Tarefas comuns de DBA para Amazon RDS para PostgreSQL

Usar o pgAudit para registrar a atividade do banco de dados