Como usar a extensão pgaudit para auditar minha instância de banco de dados do Amazon RDS que está executando o PostgreSQL?

Resolução

Há parâmetros diferentes que você pode definir para registrar atividades na sua instância de banco de dados do PostgreSQL. Para auditar diferentes bancos de dados, perfis, tabelas ou colunas, você pode usar a extensão pgaudit. Depois de ativar a extensão pgaudit, você pode configurar o parâmetro pgaudit.log para auditar bancos de dados, perfis, tabelas e colunas específicos.

Ativar a extensão pgaudit em uma instância do Amazon RDS executando o PostgreSQL

1.    Crie um perfil de banco de dados específico chamada rds_pgaudit executando o seguinte comando:

CREATE ROLE rds_pgaudit;
CREATE ROLE

2.    Modifique os parâmetros abaixo no seu grupo de parâmetros de banco de dados personalizado associado à sua instância de banco de dados:
Adicione ou anexe pgaudit a shared_preload_libraries
Configure pgaudit.role para rds_pgaudit, o perfil criado na etapa 1

3.    Reinicialize a instância para que as alterações no grupo de parâmetros sejam aplicadas a ela.

4.    Confirme se pgaudit foi inicializada, executando o seguinte comando:

show shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)

5.    Crie a extensão pgaudit executando o seguinte comando:

CREATE EXTENSION pgaudit;
CREATE EXTENSION

6.    Confirme se pgaudit.role está definido como rds_pgaudit, executando o seguinte comando:

show pgaudit.role;
pgaudit.role
------------------
rds_pgaudit

7.    Configure o parâmetro pgaudit.log para auditar qualquer um dos seguintes:

• ALL audita os seguintes comandos.
• MISC audita comandos diversificados, como DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
• DDL audita todas as linguagens de descrição de dados (DDL) que não estão incluídas na classe ROLE.
• ROLE audita instruções relacionadas a perfis e privilégios, como GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
• FUNCTION audita chamadas de funções e blocos DO.
• WRITE audita INSERT, UPDATE, DELETE, TRUNCATE e COPY quando o destino é uma relação.
• READ audita SELECT e COPY quando a origem é uma relação ou uma consulta.

Dependendo do que você deseja auditar, defina o valor do parâmetro pgaudit.log como um banco de dados, perfil ou tabela.

Usar a extensão pgaudit para auditar bancos de dados

1.    Para definir o valor do parâmetro pgaudit.log para um banco de dados, perfil ou tabela, defina o parâmetro pgaudit.log como none no nível do grupo de parâmetros:

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

2. Execute o comando a seguir para substituir a configuração do sistema para esse parâmetro somente neste banco de dados:

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

Isso altera o valor do parâmetro pgaudit.log para All, de forma que test_database seja o único banco de dados auditado na instância de banco de dados do RDS.

3.    Conecte-se a test_database e execute a seguinte consulta:

select * from test_table;

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>

Usar a extensão pgaudit para auditar perfis

Da mesma forma que a configuração do parâmetro pgaudit.log no nível do banco de dados, o perfil é modificado para ter um valor diferente para o parâmetro pgaudit.log. Nos comandos de exemplo a seguir, os perfis test1 e test2 são alterados para ter diferentes configurações de pgaudit.log.

1.    Defina valores diferentes para o parâmetro pgaudit.log para test1 e test2 executando os seguintes comandos:

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

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

2.    Verifique se as modificações foram feitas no nível do perfil, executando 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

3.    Execute as seguintes consultas para test1 e test2:

CREATE TABLE test_table (id int);
CREATE TABLE

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

A saída do log é semelhante à seguinte para 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>
...

A saída do log é semelhante à seguinte para test2 após a execução das mesmas consultas:

...
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>
...

Observação: não há entrada de auditoria para a consulta SELECT, pois o parâmetro pgaudit.log para test2 está configurado somente para DDL.

Usar a extensão pgaudit para auditar tabelas

A configuração do parâmetro pgaudit.log audita e registra instruções que afetam uma relação específica. Somente os comandos SELECT, INSERT, UPDATE e DELETE podem ser registrados pela extensão pgaudit. TRUNCATE não está incluído no registro de auditoria de objetos. Se você conceder acesso para o perfil rds_pgaudit a uma operação (como SELECT, DELETE, INSERT ou UPDATE) na tabela que deseja auditar, qualquer auditoria de concessão registrará a instrução correspondente. O exemplo a seguir concede ao perfil rds_pgaudit acesso a SELECT e DELETE, de forma que todas as instruções SELECT e DELETE em test_table sejam auditadas.

1.    Conceda acesso SELECT e DELETE para o perfil rds_pgaudit, executando o seguinte comando:

grant select, delete on test_table to rds_pgaudit;

2.    Teste se o registro em log de auditoria está configurado corretamente, executando 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:02
UTC: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>

Usar a extensão pgaudit para auditar colunas

Você também pode definir a auditoria em nível de coluna para uma tabela específica. Por exemplo, quando dados confidenciais existem em apenas uma coluna. No comando de exemplo a seguir, uma tabela de folha de pagamento é criada e tem uma coluna confidencial que inclui dados salariais que devem ser auditados:

create table payroll
(    
    name text,
    salary text
);

1.    Conceda acesso a SELECT para o perfil rds_pgaudit na coluna salary de forma que qualquer SELECT nessa coluna seja auditado:

grant select (salary)  on payroll to rds_pgaudit;

2.    Use SELECT para todas as colunas da tabela, incluindo a coluna salary:

select * from payroll;

No exemplo de saída a seguir, qualquer SELECT que inclua a coluna salary é auditado. No entanto, um SELECT que não contém a coluna salary não é auditado.

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 do Amazon RDS para PostgreSQL