Logging and Auditing

PostgreSQL provides a very rich logging infrastructure. Being able to examine logs is a key skill for every database administrator—logs provide hints and information about what the cluster has done, what it is doing, and what happened in the past. This chapter will explain the basics of PostgreSQL log configuration, providing you with an explanation of how to configure the logging machinery to get the information you need about cluster activity. Logs can be analyzed manually, but database administrators often also exploit automated tools that can provide a wider insight into the cluster activity. Related to logging is the topic of auditing, which is the capability of tracking who did what to which data. Auditing is often enforced by government laws, rather than the needs of the database administrators. However, a good auditing system can also help administrators to identify what happened in the database.

In this chapter, you will learn about the following...

Technical requirements

You will need to know the following:

• How to manage PostgreSQL configurations
• How to start, restart, and monitor PostgreSQL and interact with PGDATA files

The chapter examples can be run on the chapter_14, Docker image, which you can find in the book’s GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL-Second-Edition. For installation and usage of the Docker images available for this book, please refer to the instructions in Chapter 1, Introduction to PostgreSQL.

Introduction to logging

Like many other services and databases, PostgreSQL provides its own logging infrastructure so that the administrator can always inspect what the daemon processes are doing and what the current status of the database system is. While logs are not vital for the data and database activities, they represent very important knowledge about what has happened or is happening in the whole system, and they provide an important clue by means of which an administrator can take action.

PostgreSQL has a very flexible and configurable log infrastructure that allows different logging configuration, rotation, archiving, and post-analysis.

Logs are stored in a textual form, so that they can be easily analyzed with common log analysis tools, including operating system utilities such as grep(1), sed(1), and text editors.

The term “log,” as used in this chapter, refers only to the system’s textual logs, and not to the Write-Ahead Logs (WALs...

Extracting information from logs – pgBadger

Thanks to the rich set of information that can be included in the logs, it is possible to automate log information analysis and extraction. There are several tools with this aim, and one of the most popular and complete is pgBadger.

pgBadger is a self-contained Perl 5 application that carefully reads and extracts information from PostgreSQL logs, producing a web dashboard with a summary of all the information it has found in the logs. The aim of this application is to provide you with more useful insights into the logs without having to manually search for specific information.

Using pgBadger is not mandatory; your cluster will work fine without it and you will be able to seek information and problems in the logs regardless. However, using pgBadger provides you with more useful hints about what your server has done.

It is important to note that using pgBadger, as well as performing any automated or manual log analysis...

Implementing auditing

Auditing is the capability of performing introspection over an application or user session, in other words, to be able to reproduce, step by step, what the user or the application asked the cluster to do.

Auditing is slightly different from logging, as logging provides a simple way of saving actions of the user, but without providing an easy way to reconstruct the user or application interactions with the cluster. In fact, in a highly concurrent cluster, many actions made by different users will coexist in the logs in a mixed bunch of lines. Moreover, logging does not provide any particular logic on what it is storing, and therefore it becomes hard to find out what a user has done. This becomes even more true when the user or the application executes complex statements, in particular, statements where parameters and values are not explicitly provided.

As an example, consider the following simple section:

forumdb=> PREPARE my_query( text ) AS SELECT...

Summary

PostgreSQL provides a reliable and flexible infrastructure for logging that allows a database administrator to monitor what the cluster has done in the very near past. Thanks to its flexibility, the logs can be configured to allow access by external tools for cluster analysis, such as pgBadger. Moreover, the same logging infrastructure can be exploited to perform auditing, a kind of introspection often required by local government laws.

In this chapter, you have learned how to configure the PostgreSQL logging system to match your needs, how to monitor your cluster by means of the web dashboards provided by pgBadger, and finally, how to perform auditing on your users and applications.

In the next chapter, you will learn how to back up your own cluster.

Verify your knowledge

• What is the difference between logging and auditing?

  Logging is a way to track certain activities that happen within the cluster, without any particular regard to the “target” of such an activity. On the other hand, auditing is a way to log and track specific activities that happen on specific targets. For example, logging can track “every slow query” without any regard to the table the query is run against, while auditing can track “every modification to table xyz.” See the Implementing auditing section for more details.

• What is pgBadger?

  pgBadger is an external command that can inspect the PostgreSQL textual logs and build a dashboard with the cluster activity. See the Extracting information from logs – pgBadger section for more details.

• How can the database administrator decide where to send PostgreSQL logs?

  PostgreSQL provides a set of logging configuration...

References

• The pgBadger official documentation, available at https://pgbadger.darold.net/documentation.html
• PgAudit official code repository, available at https://github.com/pgaudit/pgaudit
• The PostgreSQL log settings’ official documentation, available at https://www.postgresql.org/docs/current/runtime-config-logging.html
• PgAudit official website and documentation, available at https://www.pgaudit.org/

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku