<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      With the increase in the number of security incidents such as data breaches in recent years, organizations had no choice but prioritize measures against threats to ensure data security. In particular, databases that manage personal and confidential information should be given top priority with urgence. According to the international security evaluation standard (ISO15408), major database security threats include illegal connection (spoofing), illegal access, and data breach. For these security threats, measures must be set in place to prevent, stop, and detect them. Audit log explained in this article is a feature for detecting security threats.
      This article describes the audit log of PostgreSQL database and Fujitsu Enterprise Postgres.

      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.

      AUDIT: SESSION,READ,2024-01-12 10:48:19 EST,192.0.2.0,34916,psql,fepuser,postgres,3/7,5,1,SELECT,,TABLE,public.account,,
      SELECT * FROM account WHERE age >= 40;,<not logged>
      Items to be audited Information output from Fujitsu Enterprise Postgres
      When SQL execution start time 2024-01-12 10:48:19 EST
      Who Username of connection source fepuser
      From where Application name psql
      Process id 34916
      Host name or IP address of connection source 192.0.2.0
      For which data Database name postgres
      Object type TABLE
      Object name (with schema qualification) public.account
      What kind of process SQL type SELECT
      SQL SELECT * FROM account WHERE age >= 40
      Execution result SQLSTATE No output (not output if SQL execution is successful)
      Error message No output (not output if SQL execution is successful)

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

      If you are interested to learn more about Fujitsu's enterprise feature, read our whitepaper on Dedicated Audit Log.

      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.

      Related PostgreSQL Insider articles

      What are the partitioning types available in PostgreSQL, and how should I choose one?
      This article explains partitioning, the method for storing data in divided tables, based on declarative partitioning.
      Improving database performance using partitioning — step-by-step
      Explaining the mechanism of pruning, wise join, wise aggregation, that can enhance partitioning performance.
      Introduction to frequently used open source extensions
      Have a look at the most frequently used open source extensions enhancing PostgreSQL functionalities, and what they are used for.

      Fujitsu Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.

      Brochure

      See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

      1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
      View brochure

      Read our latest blogs

      Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.