With PgAudit, once you set it up on your system, you can view the generated logs to understand any activity that you defined in your log policy. This post guides you in understanding what PgAudit means in PostgreSQL. Furthermore, we will discuss the steps to utilize it by giving an example on how to check the logs.
What Is PgAudit
Any organization relies on auditing to track how the activities are going and to enhance integrity. When you have PostgreSQL on your system, as the database administrator, it’s your role to audit the logs to enhance the system’s security and track which users access the system and what activities they engage in.
PgAudit is an extension that is offered by PostgreSQL to allow the users to monitor and capture the activities in their PostgreSQL database. Ideally, the extension makes it easy and possible to audit a PostgreSQL database quickly.
PgAudit helps guarantee the security in a database. It lets you monitor the database actions like connections, various queries, etc. That way, you can check when the users accessed the system, what queries they executed, and when they disconnected.
Getting Started with PgAudit for PostgreSQL Auditing
If you already installed PostgreSQL, you must also install the PgAudit extension. The simpler method is to install the postgresql-contrib as it comes bundled with different extensions that you require to work with PostgreSQL comfortably.
So, run the following command to install postgresql-contrib on your system quickly:
To start auditing logs using the PgAudit, you must enable it by modifying its configuration file on your system. Use an editor of your choice and access the PostgreSQL config file. For the nano editor, run the following command:
Enter your password when prompted.
Once the configuration file opens, scroll down to find the following line:
You will notice that it has a “#” at the start which means that it’s commented as the PgAudit extension and is yet to be enabled.
Edit the line such that you include the PgAudit and remove the “#” at the start for the line to be read when PostgreSQL runs. Your new line should be as follows:
After performing these changes, we must restart PostgreSQL. Restart it as follows:
Editing the PostgreSQL config file helps us to enable the PgAudit. However, we must also define the audit policy that we will use with our database. For instance, if we want to log all the database actions that any user will undertake, we set our PgAudit policy as in the following image:
Create a PgAudit config file and add the audit policy.
Save and exit the configuration file. Next, reload PostgreSQL for the changes to apply.
That’s it. You’ve so far managed to enable the PgAudit and defined your audit policy. We must then test if the logs are generated successfully for any action that is undertaken by a user that accesses the database.
Access the psql shell on your terminal.
We logged in to our PostgreSQL database as the default superuser.
To test the effectiveness of PgAudit, let’s perform the different database operations. First, we create a simple table.
Quickly insert the values into the table.
Perform a select query to display the table contents.
Lastly, exit the database and the psql shell using the “exit” command.
To check if PgAudit managed to monitor and record the database operations that are performed by different users, let’s access the PostgreSQL “main.log” file on our system.
For this case, we run the PostgreSQL 14. Thus, our log file can be accessed using the following path:
Open the log file using any editor and check the last lines. They display any recent activities that are performed on the database and the particular responsible user. Your log file will appear “noisy” like in the following image:
That’s how the the PgAudit extension helps audit the logs when working with PostgreSQL.
Conclusion
PostgreSQL utilizes the PgAudit extension to help perform the logs auditing to check which users accessed a database and what action they performed. Once you install PgAudit, modify its configuration file to enable it, then set the audit policy for monitoring and logging. We presented the detailed steps that you should follow in this post. All the best!
