PostgreSQL audit log - Detecting database security threats

What is an audit log?

An audit log is a record of access to the database such as when, who, from where, for which data, what kind of process, and execution result. Tracking and analyzing these records allows users to recognize issues such as data tampering and access from unauthorized sources. For example, data breach, which is said to have the greatest social impact among security incidents, may occur as a result of spoofing or unauthorized access. In the event of a data breach incident, audit logs can be traced so that the cause and the scale of impact can be identified, and actions can be taken to minimize the damage. Also, regular monitoring of the audit log helps prevent security incidents. In these ways, audit logs play an important role in setting database security measures.

Now, how do users access audit logs in PostgreSQL and Fujitsu Enterprise Postgres?

How to obtain audit logs

There are two ways to obtain audit logs in PostgreSQL.

• Using the log_statement parameter

  By setting the log_statement parameter that controls log output in postgresql.conf, the log equivalent to an audit log can be obtained. This parameter collects the executed SQL as a server log. However, the data collected in this log is not sufficient for the purpose of database audit. In other words, the acquired server log does not fulfil the role of an audit log. In addition, since it is output as a server log, messages and operation logs are mixed, making it difficult to audit. Moreover, the amount of output to the server log is huge, which may cause database performance degradation.

• Using the extension module pgaudit

  Audit logs can be obtained using the extension module pgaudit. Configuration is possible for the detailed settings of log collection such as "which operation should be output at what level". In addition, information that is missing in the server log (such as the schema name for object identification) can be obtained. Although there are various derivative versions of pgaudit, the server log is their common output destination, so the same problems can be seen as mentioned above with the log_statement parameter.

How Fujitsu Enterprise Postgres resolves this challenge

Fujitsu Enterprise Postgres provides extended audit log features like below and resolves the challenges with the log_statement parameter and pgaudit. It is based on the pgaudit refactored version, which has been enhanced so that pgaudit can acquire SQL execution results such as connection information and error messages.

• Users can select the audit log output destination to be a dedicated log file or the server log

  Users can choose to output the audit log to a dedicated log file, which is a capability added to the pgaudit refactored version. By outputting to a dedicated log file, handling only the audit log is possible, making it easier to analyze the log. In addition, since the amount of log output to the server log is suppressed, database performance deterioration is reduced.

• Referencing by SQL

  By using the PostgreSQL extension module file-fdw, users can reference the audit log with SQL from applications. GUI-based auditing is also available by linking with a log management tool (such as ALog EVA).

  Fujitsu Enterprise Postgres Dedicated Audit Log

  
• Additional types of logs that can be collected

  Since the audit log of Fujitsu Enterprise Postgres is based on the pgaudit refactored version, it is possible to collect logs related to connection and SQL execution result which cannot be collected by PostgreSQL or pgaudit. For example, when a SELECT is executed to reference data, the pgaudit audit log can only help to identify the fact that a SELECT was issued. In comparison, Fujitsu Enterprise Postgres’ audit log provides full in-depth information such as, "SELECT is issued from an unauthorized connection source named xxxx. SELECT was successful, which suggest the possibility that information has leaked.”
  Log output example (connection log)

  AUDIT: SESSION,CONNECT,2024-01-12 10:47:51 EST,192.0.2.0, 34916, [unknown],fepuser,postgres,3/1,,,,00000,,,
  connection authorized: user=fepuser database=postgres,,
  AUDIT: SESSION,READ,2024-01-12 EST,192.0.2.0,34916,psql,fepuser,postgres,3/7,5,1,SELECT,,TABLE,public.account,,
  SELECT * FROM account;,<not logged>

  From this entry, you can see that the connection was made from an unauthorized connection source (IP address: 192.0.2.0) and the SELECT was successful.

  Log output example (SQL execution results)

  AUDIT: SESSION,ERROR,2024-01-12 10:48:40 EST,[localhost],34916,psql,fepuser,postgres,3/6,,,,42703,,,"column""are"" does not exist (10490)",,

  From this entry, you can see that SQL execution by user ID "fepuser" failed.

Details of audit logs in Fujitsu Enterprise Postgres

As mentioned above, the audit log is a record of when, who, from where, for which data, what kind of process, and execution result for the database. Not having sufficient information affects database auditing, preventing proper analysis. Let's check the sufficiency of information of the audit log provided by Fujitsu Enterprise Postgres by mapping the information to the items to be audited.

Log output example when SQL execution is successful

If the SQL execution is successful, an audit log entry like the one below will be output, including the successful SQL type and statement. The audit log output proves that SQL execution was successful.

As you can see, Fujitsu Enterprise Postgres gathers all the information required to audit the database.

How to acquire audit log with Fujitsu Enterprise Postgres

Select audit log output mode

The Fujitsu Enterprise Postgres audit log feature offers two output modes: Session Audit Logging and Object Audit Logging. Select the output mode according to the purpose of the audit.

• Session Audit Logging
  Specify the rule for logging and only the audit log that matches the rule will be output. For example, the rule can be "output audit log when running read SQL (SELECT, COPY FROM) and write SQL (INSERT, UPDATE, TRUNCATE, COPY TO)" or "only when running read SQL (SELECT, COPY FROM)".
• Object Audit Logging
  Specify the role authorized to access the object to be audited, and output the audit log of the operations executed by the specified role. Let's say you have Table A and Table B, the role 'auditor' is specified as the output condition for the audit log, and the SELECT privilege for table A is granted to the role 'auditor'. Under this condition, if you execute SELECT on both Table A and Table B, only the audit log for Table A is output.

Preparation

We will explain the preparation required to use the audit log feature of Fujitsu Enterprise Postgres.

Set the operating conditions to enable the audit log feature

• Prepare the pgaudit configuration file

  Specify the operating conditions such as audit log acquisition conditions and output destinations in the pgaudit configuration file (user-created file). When specifying parameters, describe them in three sections separately as follows. However, the rule section and option section cannot be written at this point. Specify them as required during setup which we will discuss next.

  • output section: Specify the output destination of the audit log.
  • rule section: Specify the rule to narrow down the audit log to output. This section is used for Session Audit Logging only.
  • option section: Specify options related to the role and audit log output for Object Audit Logging.
  The following is a sample description of the pgaudit configuration file. You can see that a dedicated log file 'auditlog' is specified in the logger parameter as the output destination of the audit log. If you would like to output the log to the server log, specify 'serverlog'.
  
  [output]
  logger ='auditlog'
  
  For other conditions that can be specified in the pgaudit configuration file, refer to "Audit Log Feature" in the Fujitsu Enterprise Postgres product manual "Fujitsu Enterprise Postgres Security Operation Guide".

Setup

The setup flow is explained below.

1. Edit postgresql.conf

   Edit postgresql.conf as follows and specify the pgaudit configuration file (orange text). (In the following example, only the parameters required for the audit log feature are described.) After editing postgresql.conf, start the instance.

   = shared_preload_libraries 'pgaudit'
   pgaudit_config_file = ' pgaudit.conf ' Specify the pgaudit configuration file
   log_replication_commands = 'on'
   log_min_message = 'error' ·
   ·
2. Install the extension feature

   Use CREATE EXTENSION to load the extension feature pgaudit into the current database.

   CREATE EXTENSION pgaudit;
3. Edit the pgaudit configuration file

   Add conditions to the pgaudit configuration file as needed. After editing the pgaudit configuration file, restart the instance for the edited contents to take effect.

Use case - Session Audit Logging

As an example of acquiring the audit log using Session Audit Logging, create an account table and acquire the audit log for the table. For the operation example of Object Audit Logging, refer to "Audit Log Feature" in "Fujitsu Enterprise Postgres Security Operation Guide".

1. Specify the conditions for acquiring the audit log

   Specify as follows in the pgaudit configuration. Let's see the setting for 'rule' first. Here, 'READ, WRITE' is specified in the class parameter of the rule section. This means that the execution results of SQL such as INSERT and SELECT will be acquired. Furthermore, by specifying 'ERROR', the event that ended in error will also be acquired.

   [output]
   logger ='auditlog' Specify a dedicated log as the output destination of the audit log
   [rule]
   class ='READ, WRITE, ERROR' Specify the target operation for log output by class
   
   . The following table shows the values that can be specified for the class parameter and their meanings.
   

   READ	SELECT, COPY FROM
   WRITE	INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
   FUNCTION	Function call, DO
   ROLE	GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE
   DDL	All DDL except ROLE class DDL (CREATE..., ALTER..., etc.)
   CONNECT	Connection related events (request, authentication, disconnection)
   SYSTEM	Instance startup, promotion to primary server
   BACKUP	pg_basebackup
   ERROR	Event ended with an error (PostgreSQL error code other than 00)
   MISC	Other commands (DISCARD, FETCH, CHECKPOINT, VACUUM, etc.)

2. Run the following SQL from the client to operate on the database.
   
   CREATE TABLE account (id int, name text, birthday date, age int);
   INSERT INTO account (id, name, password, description) VALUES (1,'USER1', '1974-08-20', '44');
   INSERT INTO account (id, name, password, description) VALUES (2,'USER2', '1978-10-03', '40');
   INSERT INTO account (id, name, password, description) VALUES (3,'USER3', '1994-01-19', '24');
   SELECT * FROM account WHERE age >= 40;
   DROP TABLE account;
3. Confirm the acquired audit log

   Once you execute the SQL, the following audit log will be acquired.
   Note that while the SQL statement does not schema-qualify the table, the entry in the audit log does qualify it as "public.account". This helps when there are multiple tables with the same name, to identify the table for which the operation is performed as well as which schema the table belongs to.
   Furthermore, we can tell that the SQL execution was successful because the audit log related to ERROR was not output, although 'ERROR' was specified for the class parameter.

   AUDIT: SESSION,WRITE,2024-01-12 10:48:19 EST,[local],34916,psql,fepuser,postgres,3/3,2,1,INSERT,,TABLE, public.account ,,
   "INSERT INTO account VALUES (1,'USER1', '1974-08-20', 44 );",<not logged>
   AUDIT: SESSION,WRITE,2024-01-12 10:48:27 EST,[local],34916 ,psql,fepuser,postgres,3/4,3,1,INSERT,,TABLE, public.account ,,
   "INSERT INTO account VALUES (2,'USER2', '1978-10-3', 40 );",<not logged>
   AUDIT: SESSION,WRITE,2024-01-12 10:48:34 EST,[local],34916,psql,fepuser,postgres,3/5,4,1,INSERT,,TABLE, public.account ,,
   "INSERT INTO account VALUES (3,'USER3', '1994-1-19', 24 );",<not logged>
   AUDIT: SESSION,READ,2024-01-12 10:48:51 EST,[ local],34916,psql,fepuser,postgres,3/7,5,1,SELECT,,TABLE,public.account ,,
   SELECT * FROM account WHERE age >= 40;,<not logged>

By utilizing the audit log of the database, security threats can be detected which help you implement reliable security measures.