Setting up and using the pgaudit extension

The pgaudit extension is available with Scaleway Managed Databases for PostgreSQL. The extension is a powerful monitoring and logging tool that allows you to keep track of the actions happening in your databases, and record them.

The main purpose of pgaudit is to allow PostgreSQL users to produce audit logs often required to comply with government, financial, and/or ISO certifications.

pgaudit allows you to log different statements executed in your Database Instance, or specific databases. You can keep track of who accessed your databases, what actions were performed, and when, under a structured format.

You can configure the extension to log only what is relevant to your audits.

Before you start

To complete the actions presented below, you must have:

• A Scaleway account logged into the console
• Owner status or IAM permissions allowing you to perform actions in the intended Organization
• A Database Instance running a PostgreSQL engine

Enabling and installing pgaudit

1. Click PostgreSQL and MySQL under Managed Databases on the side menu of the Scaleway console. A list of your Database Instances displays.

2. Click the Database Instance name or «See more Icon» > More info to access the Instance information page.

3. Click the Advanced settings tab.

4. Click «Edit Icon».

5. Click + Add parameters.

6. Select rdb.enable_pgaudit in the drop-down menu and click the toggle to set it to on.

7. Click «Validate Icon» to validate. Your Database Instance restarts.

   Important

   Restarting can take several seconds depending on the size of your Database Instance.

8. Connect to your Database Instance.

9. Run the following command to install the pgaudit extension.

   CREATE EXTENSION pgaudit
   

   If the operation is successful, no output is returned.

Configuring pgaudit

pgaudit provides two different types of logging:

• Session audit logging - logs the operations, such as queries and updates, executed by a user during a session within a database or the entire Database Instance
• Object audit logging - logs actions that involve specific data objects, like tables or files.

To use each one, you must configure the pgaudit.log and pgaudit.role settings, respectively.

Both settings can be set up at the Database Instance level, meaning that a global configuration can be applied to all databases within the Instance. You can configure them in the advanced settings of your Database Instance. You can use both settings concurrently.

pgaudit.log can also be configured for specific databases within your Instance.

Setting up pgaudit.log

The pgaudit.log setting determines which classes of statements will be recorded through session audit logging.

The possible values include:

• For a Database Instance
• For a specific database

Setting up pgaudit.role

Object audit logging is configured through the roles system. The pgaudit.role setting allows you to specify a principal audit role (also known as “master role”).

Various audit roles can be established, delegating responsibility for distinct aspects of audit logging to different groups. These roles must subsequently be assigned to the principal audit role to facilitate audit logging.

The primary function of the principal audit role is to oversee and manage the audit logging process. The auditing process is generally handled by an independent individual or company, which will use the audit logs to perform their audit.

Audit logging for an object (such as a TABLE or VIEW) takes place when the principal audit role has the necessary permissions to log the actions carried out on the object, or when it inherits these permissions from another role.

Currently, only the SELECT, INSERT, UPDATE, and DELETE permissions are supported.

To set up and grant permissions to the principal audit role follow these instructions:

1. Connect to your database as an admin user.

2. Run the following command to create a new user. If no output is returned, the operation was successful.

   Note

   If you want to specify an existing role as the principal audit, you can skip this step.

   CREATE ROLE auditor;
   

   You can replace auditor with your name of choice.

   Tip

   Run \du to see a list of your database roles and check that the new role was created.

3. Set the role you created as pgaudit.role

   SET pgaudit.role = 'auditor';
   

   Note

   Alternatively, you can follow these steps to configure pgaudit.role from the Scaleway console:

   1. Go to the Advanced settings of your Database Instance in the Scaleway console.
   2. Click «Edit Icon».
   3. Click + Add parameters.
   4. Select pgaudit.role in the drop-down and enter the name of the role you created to be the principal.
   5. Click «Validate Icon» to validate.

   Note

   The configuration takes a few seconds to be applied. During this time the Database Instance connection remains uninterrupted. However, you must wait until the new configuration is applied to edit your advanced settings again.

   You can edit the pgaudit.role values in the advanced settings anytime.

4. Run the following command in your Database Instance to grant the principal audit role permissions:

   GRANT PERMISSION1, PERMISSION2
     on <object>
     to <principal-role>;
   

   Replace <PERMISSION1> AND <PERMISSION2> with one or more of the supported permissions, <object> with the name of the data object you want to audit, and auditor with the <principal-role> with the name of the principal role.

If the operation was successful, you see GRANT as an output.

Accessing and handling logs

Your logs are accessible via the Scaleway Observability dashboard, Cockpit.

1. Follow the Cockpit Quickstart to activate the feature and retrieve your Grafana credentials.
2. Configure the Grafana agent.
3. Connect to Grafana with the credentials you created.
4. Click RDB PostgreSQL Overview on the left-side menu.
5. Scroll to the Logs section and click to expand.
6. View the logs.

Technical Information

• All management actions performed via API or the console, such as database creations and deletions, are logged, no matter the configuration. However, the associated read statements are not logged. For example, select statements used to check that actions have been correctly executed are not logged.

• When objects are renamed, they will start to be logged under their new name. For more information, refer to the official pgaudit repository on Github

• Audit logs are displayed in a specific format that you can also find referenced in the official pgaudit repository on Github

Optimizing disk usage

• Audit logs are flushed and consume disk space. The log ingestion rate is 4 MB per second.

  To avoid log generation from exceeding ingestion, and the subsequent uncontrolled growth of disk usage, make sure you closely monitor the disk usage of the Database Instance.

  Important

  If the disk space becomes full, some audit logs may be lost, and the database can become unavailable.

• Before activating any audits, make sure you define exactly which statements are mandatory.

  Some statements might not be important for auditing use cases, such as SELECT. When you generate high-volume logs, they can take up significant disk space.

  Tip

  When it comes to SELECT and DML statements, we recommend opting for an object audit logging configuration rather than a session audit one. Refer to the official pgaudit Github repository for more information.

Deactivating pgaudit

1. Connect to your database as an admin user.

2. Run the following command to deactivate the pgaudit extension in your Database Instance.

   DROP EXTENSION pgaudit
   

3. Go to the Advanced settings of your Database Instance in the Scaleway console.

4. Click «Edit Icon».

5. Click + Add parameters.

6. Select rdb.enable_pgaudit in the drop-down and click the toggle to set it to off.

7. Click «Validate Icon» to validate. Your Database Instance restarts.

   Note

   If you reactivate the extension, your previous configuration is applied.

Setup examples

Session audit logging for ALL

To set up session audit logging of all statements in all databases and roles, set pgaudit.log to ALL in the advanced settings tab of your Database Instance dashboard in the Scaleway console.

Session audit logging for only DML statements

To set up session audit logging only of Data Manipulation Language (DML) statements for all databases and roles, set pgaudit.log to WRITE in the advanced settings tab of your Database Instance dashboard in the Scaleway console.

Session audit logging for ALL statements in a specific database

To set up session audit logging of all statements in only one database, you must:

1. Set pgaudit.log to NONE in the advanced settings tab of your Database Instance dashboard in the Scaleway console.
2. Connect to your database as an admin user and run the following command:

   ALTER DATABASE mydb SET pgaudit.log = 'ALL';
   

   Replace mydb with the name of your database.

Session audit logging and object audit logging

To set up:

• session audit logging of all statements except READ and WRITE for all databases and roles, and
• object audit logging of SELECT, INSERT, UPDATE, and DELETE statements only on a specific table in a database.

1. Set pgaudit.log to FUNCTION,ROLE,DDL,MISC,MISC_SET, and the pgaudit.role as auditor in the advanced settings tab of your Database Instance dashboard in the Scaleway console. You can replace auditor with the name of your principal audit role.

   Note

   There is no default principal role. You must create a new role, or specify an existing one as the principal audit.

2. Connect to your database as an admin user and run the following command:

   GRANT SELECT, INSERT, UPDATE, DELETE
     on mydb.mytable
     to auditor;
   

   Replace mydb with the name of your database and mytable with the name of the table.