Shulou(Shulou.com)06/01 Report--

Editor to share with you how to achieve PostgreSQL audit, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

The audit of PostgreSQL is still carried out with the help of PostgreSQL's extended pgaudit. Some students who are familiar with PG may say, isn't it possible to record all the sentences by log_statement = all? why bother? just check the log yourself. In fact, if the company has an audit department, this will not pass, and it needs to face the "affinity" of the audit department in a way that is similar to the function of commercial data.

Installation is very simple, if you are familiar with extension, of course pgaudit needs to load the link library

Unlike most audit methods, pg_audit is recorded in standard PostgreSQL logs. Pgaudit works by registering itself when the module is loaded and providing hooks for executorStart, executorCheckPerms, processUtility, and object_access. Therefore, pgaudit does not support reading (SELECT, COPY) from another audit-trigger. In general, with pgaudit, we can have two modes of operation, or we can use them together:

Session mode or object mode.

The way in which monitoring can be carried out

READ (select, copy from)

WRITE (insert, update, delete, truncate, copy to)

FUNCTION (function calls and DO blocks)

ROLE (grant, revoke, create/alter/drop role)

DDL (all DDL except those in ROLE)

MISC (discard, fetch, checkpoint, vacuum)

In terms of what can be audited and monitored above, it is still very comprehensive.

Now we can do a test.

We create a table that should be recorded by the audit log, and let's see if the audit log is recorded. As you can see from the following picture

A record of audit has been added to the log.

It shows that this thing is quite easy to use. Format of the log

AUDIT_TYPE-tells you whether the current audit is session or object

STATEMENT_ID-session ID of the main statement

SUBSTATEMENT_ID-the sequential ID of each child statement in the main statement.

The way Operation type operates is DDL DCL DML.

COMMAND-Command for operation

OBJECT_TYPE-OBJECT type of the operation

OBJECT_NAME-the name of the OBJECT type of the operation, such as table name, stored procedure name, etc.

STATEMENT-statement executed

PARAMETER-related parameters

At this point, someone may ask, this design is not good, why can not be designed to insert into the table, I think there are two points, since it is called the audit log, 1 he provides the audit 2 he is the log

If the log breaks out and grows in some cases, how to fill the storage space of the entire table, in many ways, it is a more standard practice to let the log be stored in the appropriate place.

Let's make a few attempts to see if the audit log can help us record the relevant actions. The following figure shows the PG-related audit options.

What do some of the related settings mean?

Pgaudit.log

Specifies which statement classes are recorded in the session audit log.

Categories that can be set: read, write, function,role, ddl,misc, All, etc.

Pgaudit.log_catalog

Specifies that session logging should be enabled when all relationships in a statement are in pg_catalog. Disabling this setting reduces log noise from tools such as psql and PgAdmin, which query a large number of directories.

Pgaudit.log_relation

Specifies whether session audit logging should create separate log entries for each relationship (table, view, and so on) referenced in the SELECT or DML statement. This is a very useful shortcut for exhaustive logging that does not use object audit logging.

For example, we need to record the operation records of ddl, role, functiton, and changing the system configuration.

Of course, such operating records are not without shortcomings, for example, I want to know that the account did something, which is still not done, just know when and what was done.

The above is all the contents of the article "how to achieve PostgreSQL Audit". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.