Auditing PostgreSQL Using pgAudit
Tags: postgres, database, dba
Published: 2021-01-18 22:34:47

---
title: Auditing PostgreSQL Using pgAudit
published: true
description: pgAudit is an extension that provides the logs needed to facilitate a PostgreSQL database audit. In this post, we explain how to install and interpret pgAudit.
tags: postgresql, database, dba
cover_image: https://scalegrid.io/blog/wp-content/uploads/2021/01/Auditing-PostgreSQL-Using-pgAudit-Blog.jpg
---
<link rel="canonical" href="https://scalegrid.io/blog/auditing-postgresql-using-pgaudit/" />
<p style="text-align: justify;">Auditing in information technology (IT) is a process of examining an organization&rsquo;s IT infrastructure to ensure compliance with the requirements imposed by recognized standards or established policies. Data protection rules, such as the new GDPR regulations, are becoming increasingly stringent to protect user data, so it&rsquo;s important that your database audits are set up properly to ensure both your application and user data is secure from vulnerabilities. In this blog post, we will discuss <a href="https://scalegrid.io/blog/auditing-postgresql-using-pgaudit/" style="color: #2da964;" target="_blank">pgAudit - a tool that generates the audit logs needed to facilitate the auditing of PostgreSQL</a>.</p>
<h2 style="padding-top: 15px;">What is pgAudit?</h2>
<p style="text-align: justify;">The PostgreSQL Audit Extension, <a href="https://www.pgaudit.org/" style="color: #2da964;" target="_blank">pgAudit</a>, is an open source extension that logs the events in a PostgreSQL database in a detailed audit log. It uses the native PostgreSQL logging facility, so the audit logs will be part of the PostgreSQL logs. The extension is based on the 2ndQuadrant pgAudit project authored by Simon Riggs, Abhijit Menon-Sen, and Ian Barwick, and includes enhancements by David Steele from Crunchy Data.</p>
<h2 style="padding-top: 15px;">Why pgAudit over log_statement=all?</h2>
<p style="text-align: justify;">We can log all statements in PostgreSQL just by setting <code>log_statement=all</code>. So why use pgAudit at all? The basic statement logging (using <code>log_statement</code>) will only list the operations performed against the database. It will not provide the ability to filter operations, and the logs won&rsquo;t be in the proper formatting required for auditing. pgAudit additionally provides granularity for logging specific classes of statements like <code>READ</code> (<code>SELECT</code> and <code>COPY</code>), <code>WRITE</code> (<code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>, etc.), <code>DDL</code> etc. Furthermore, it provides object level auditing where only operations on specific relations will be logged.</p>
<p style="text-align: justify;">Another advantage of pgAudit over basic statement logging is that it provides the details of the operation performed instead of just logging the operation requested. For example, consider executing the anonymous code block using a DO statement.</p>
<p><pre><code> DO $$ BEGIN EXECUTE &#39;CREATE TABLE import&#39; || &#39;ant_table (id INT)&#39;; END $$; </code></pre></p>
<p style="text-align: justify;">The basic statement logging will result in:</p>
<p><pre><code> 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: statement: DO $$ BEGIN EXECUTE &#39;CREATE TABLE import&#39; || &#39;ant_table (id INT)&#39;; END $$; </code></pre></p>
<p style="text-align: justify;">pgAudit will log the same operation as:</p>
<p><pre><code> 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,1,FUNCTION,DO,,,&quot;DO $$ BEGIN EXECUTE &#39;CREATE TABLE import&#39; || &#39;ant_table (id INT)&#39;; END $$;&quot;, 2020-12-20 23:40:11 UTC:157.230.232.139(53064):sgpostgres@test:[9091]: LOG: AUDIT: SESSION,4,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT), </code></pre></p>
<p style="text-align: justify;">The above clearly indicates the pgAudit functionality that logs the operation and its internals with structured output that eases the search.</p>
<h2 style="padding-top: 15px;">How to install pgAudit?</h2>
<p style="text-align: justify;">pgAudit is an extension that&rsquo;s available for download from the PostgreSQL repository, or can be compiled and built from source. As a first step, the package needs to be downloaded and installed on the machine running PostgreSQL (this extension package is preinstalled on all ScaleGrid PostgreSQL deployments).</p>
<p style="text-align: justify;">Once installed, it needs to be loaded into PostgreSQL. This is achieved by adding <code>pgaudit</code> to the <code>shared_preload_libraries</code> config parameter. A restart of PostgreSQL is required for this configuration change to be effective. The next step is to enable the extension on the database by running <code>CREATE EXTENSION pgaudit</code>.</p>
<p style="text-align: justify;">Now that the extension is ready, we need to make sure to set the configuration parameters for the extension to start logging. This can be as simple as setting the parameter <code>pgaudit.log</code> to value <code>all</code> and the pgAudit will start logging in <code>session</code> mode.</p>
<p style="text-align: justify;">Now that we know how to install and enable pgAudit, let&rsquo;s discuss the two audit logging modes it offers, session and object.</p>
<h2 style="padding-top: 15px;">Session Audit Logging</h2>
<p style="text-align: justify;">In <a href="https://github.com/pgaudit/pgaudit#session-audit-logging" style="color: #2da964;" target="_blank">session mode</a>, pgAudit will log all the operations performed by a user. Setting the <code>pgaudit.log</code> parameter to any of the defined values, other than <code>NONE</code>, will enable session audit logging. The <code>pgaudit.log</code> parameter specifies the classes of statements that will be logged in the session mode. The possible values are: <code>READ</code>, <code>WRITE</code>, <code>FUNCTION</code>, <code>ROLE</code>, <code>DDL</code>, <code>MISC</code>, <code>MISC_SET</code>, <code>ALL</code> and <code>NONE</code>.</p>
<p style="text-align: justify;">Setting the <code>pgaudit.log</code> parameter to <code>ALL</code> will log all the statements. The parameter can accept multiple classes using a comma-separated list and specific classes can be excluded with a - sign. For example, if you want to log all statements except <code>MISC</code> class, the value of <code>pgaudit.log</code> will be <code>ALL, -MISC, -MISC_SET</code>. You can also enable pgAudit to create a separate log entry for each relation reference in a statement by setting <code>pgaudit.log_relation</code> to on.</p>
<p style="text-align: justify;">Consider an example of creating a table. The SQL statement would be:</p>
<p><pre><code> CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20)); </code></pre></p>
<p style="text-align: justify;">The corresponding audit log entries are:</p>
<p><pre><code> 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE SEQUENCE,SEQUENCE,public.persons_id_seq,&quot;CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));&quot;, 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE TABLE,TABLE,public.persons,&quot;CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));&quot;, 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,CREATE INDEX,INDEX,public.persons_pkey,&quot;CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));&quot;, 2020-12-21 00:00:11 UTC:157.230.232.139(53178):sgpostgres@test:[11514]: LOG: AUDIT: SESSION,5,1,DDL,ALTER SEQUENCE,SEQUENCE,public.persons_id_seq,&quot;CREATE TABLE persons(ID SERIAL PRIMARY KEY, LNAME varchar(20), FNAME varchar(20));&quot;, </code></pre></p>
<h2 style="padding-top: 15px;">Object Audit Logging</h2>
<p style="text-align: justify;">In particular cases, it may be required to audit only a specific set of relations. In such cases, using session mode will only result in an unnecessarily large number of audit logs not corresponding to the required relations. <a href="https://github.com/pgaudit/pgaudit#object-audit-logging" style="color: #2da964;" target="_blank">Object mode</a> is especially suited for this purpose and can audit only a specific set of relations.</p>
<p style="text-align: justify;">Object audit logging is achieved using the PostgreSQL roles. A role can be created and assigned the permissions to access only a specific set of relations. This role should be specified in the configuration parameter <code>pgaudit.role</code>. Object mode supports only <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code> and <code>DELETE</code> statements. The classes of statements that are logged depends on the permissions granted to the role. For example, if the role has permissions to perform only <code>SELECT</code>, then only <code>SELECT</code> statements will be logged.</p>
<p style="text-align: justify;">Below is an example of object audit logging:</p>
<p style="text-align: justify;">Create a role and grant only <code>SELECT</code> permissions. Set the <code>pgaudit.role</code> to that role and run the <code>SELECT</code> SQL statement:</p>
<p><pre><code> CREATE ROLE audit_person; GRANT SELECT ON persons TO audit_person; SET pgaudit.role = &#39;audit_person&#39;; SELECT * FROM persons WHERE ID=404; </code></pre></p>
<p style="text-align: justify;">The above select statement will be logged as:</p>
<p><pre><code> 2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]: LOG: AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.persons,select * from persons where ID=404;, </code></pre></p>
<h2 style="padding-top: 15px;">How to interpret the audit log entry?</h2>
<p style="text-align: justify;">So far, we have provided details on how the audit log entry looks, now let&rsquo;s take a look at the audit log entry format. Each entry starts with the log_line_prefix mentioned for PostgreSQL logging, and then the rest of the output will be in CSV format. Consider the following simple audit log entry:</p>
<p><pre><code> 2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]: LOG: AUDIT: OBJECT,10,1,READ,SELECT,TABLE,public.persons,select * from persons where ID=404;, </code></pre></p>
<p style="text-align: justify;">In the above entry, the value <pre><code>2020-12-21 00:27:09 UTC:157.230.232.139(54900):sgpostgres@test:[21835]: </code></pre> is from the log_line_prefix format <code>%t:%r:%u@%d:[%p]:&nbsp;</code>. The audit entry contents start from <code>LOG: AUDIT:&nbsp;</code> value and it follows CSV format. The value format is of the form:</p>
<p><pre><code>AUDIT_TYPE,STATEMENT_ID,SUBSTATEMENT_ID,CLASS,COMMAND,OBJECT_TYPE,OBJECT_NAME,STATEMENT,PARAMETER</code></pre></p>
<p style="text-align: justify;">Let&rsquo;s take a look at each of the fields one by one:</p>
<table width="100%">
    <thead>
        <tr>
            <th style="width: 33%; padding: 10px; border: 1px solid #eee; background: #273039; color: #fff;"><strong>Field</strong></th>
            <th style="width: 33%; padding: 10px; border: 1px solid #eee; background: #2da964; color: #fff;"><strong>Description</strong></th>
            <th style="width: 33%; padding: 10px; border: 1px solid #eee; background: #2da964; color: #fff;"><strong>Value from example audit entry</strong></th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">AUDIT_TYPE</td>
            <td style="padding: 10px; border: 1px solid #eee;">Indicates the audit mode: SESSION or OBJECT</td>
            <td style="padding: 10px; border: 1px solid #eee;">OBJECT</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">STATEMENT_ID</td>
            <td style="padding: 10px; border: 1px solid #eee;">Unique statement identifier for each session</td>
            <td style="padding: 10px; border: 1px solid #eee;">10</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">SUBSTATEMENT_ID</td>
            <td style="padding: 10px; border: 1px solid #eee;">An identifier for each sub statement within the main statement</td>
            <td style="padding: 10px; border: 1px solid #eee;">1</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">CLASS</td>
            <td style="padding: 10px; border: 1px solid #eee;">Indicates the class of statements like READ, WRITE etc that are defined values for pgaudit.log parameter.</td>
            <td style="padding: 10px; border: 1px solid #eee;">READ</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">COMMAND</td>
            <td style="padding: 10px; border: 1px solid #eee;">The command used in the SQL statement</td>
            <td style="padding: 10px; border: 1px solid #eee;">SELECT</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">OBJECT_TYPE</td>
            <td style="padding: 10px; border: 1px solid #eee;">Can be TABLE, INDEX, VIEW, etc.</td>
            <td style="padding: 10px; border: 1px solid #eee;">TABLE</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">OBJECT_NAME</td>
            <td style="padding: 10px; border: 1px solid #eee;">The fully qualified object name</td>
            <td style="padding: 10px; border: 1px solid #eee;">public.persons</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">STATEMENT</td>
            <td style="padding: 10px; border: 1px solid #eee;">The actual statement executed</td>
            <td style="padding: 10px; border: 1px solid #eee;">select * from persons where ID=404;</td>
        </tr>
        <tr>
            <td style="padding: 10px; border: 1px solid #eee;">PARAMETER</td>
            <td style="padding: 10px; border: 1px solid #eee;">When the pgaudit.log_parameter is set to true, the quoted CSV of parameters is listed if present, or &quot;none&quot; if there are no parameters. When the pgaudit.log_parameter is not set, the value will be &ldquo;&lt;not logged&gt;&rdquo;</td>
            <td style="padding: 10px; border: 1px solid #eee;">&lt;not logged&gt;</td>
        </tr>
    </tbody>
</table>
<h2 style="padding-top: 15px;">Inference</h2>
<p style="text-align: justify;">pgAudit, with all its capabilities, simplifies the process of auditing by generating the audit trail log. Though there are a few caveats, like logging of renamed objects under the same name, it is still a robust tool that provides the required functionality. However, the audit information written in logs may not be just ideal for the auditing process - the auditing process is even better when those logs can be converted to a database schema, and audit data can be loaded to the database so you can easily query the information. This is where the PostgreSQL Audit Log Analyzer (pgAudit Analyze) is helpful. For more information, refer to the github pages of <a href="https://github.com/pgaudit/pgaudit" style="color: #2da964;" target="_blank">pgAudit</a> and <a href="https://github.com/pgaudit/pgaudit_analyze" style="color: #2da964;" target="_blank">pgAudit Analyze</a>.</p>