The Dedicated Audit log of Fujitsu Enterprise Postgres enables the automated retrieval of details of database access as an audit log. These audit logs can be output to a dedicated log file or to the server log file. The details in the log file can be used for efficient monitoring.
This guide provides the steps for setting up each type of audit logs.
If the requirements above are not met, then install Fujitsu Enterprise Postgres database server following the steps provided in How to install Fujitsu Enterprise Postgres.
Create a 'user01' (as superuser ) in the Postgres instance and then connect to the instance as user 'user01' and execute SQL statements:
[fepuser@hostname ~]$ psql -d Postgres ;
postgres=#
postgres=# CREATE USER user01 SUPERUSER;
CREATE ROLE
postgres=# \q
[fepuser@hostname ~]$ psql –p 27500 -d postgres -U user01 ;
postgres=# select now();
postgres=# CREATE TABLE tab1001 (name text);
CREATE TABLE
postgres=# CREATE TABLE tab1002 (empno int);
CREATE TABLE
postgres=# \q
[fepuser@hostname ~]$ psql -d postgres -U user01 ;
postgres=# select now();
postgres=# INSERT INTO tab1001 VALUES ('mynameisAAA');
INSERT 0 1
postgres=# INSERT INTO tab1001 VALUES ('mynameisBBB');
INSERT 0 1
postgres=# INSERT INTO tab1002 VALUES (9001);
INSERT 0 1
postgres=# INSERT INTO tab1002 VALUES (9002);
INSERT 0 1
postgres=# \q
[fepuser@hostname ~]$ psql -d postgres -U user01 ;
postgres=# select now();
postgres=# SELECT * FROM tab1001;
postgres=# SELECT * FROM tab1002;
postgres=# \q
[fepuser@hostname ~]$
Verify that the above SQL activity for 'user01' was captured in the dedicated audit log:
[fepuser@hostname ~]$ cat ${PGDATA}/pgaudit_log/pgaudit- 2024-05-10.log
AUDIT: SESSION,CONNECT,2024-05-10 08:39:52 UTC,[local],22896,[unknown],user01,postgres,3/9,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
AUDIT: SESSION,READ,2024-05-10 08:41:31 UTC,[local],22896,psql,user01,postgres,3/10,1,1,SELECT,,,,,select now();,<not logged>
AUDIT: SESSION,DDL,2024-05-10 08:42:43 UTC,[local],22896,psql,user01,postgres,3/11,2,1,CREATE TABLE,,TABLE,public.tab1001,,CREATE TABLE tab1001 (name text);,<not logged>
AUDIT: SESSION,DDL,2024-05-10 08:43:19 UTC,[local],22896,psql,user01,postgres,3/12,3,1,CREATE TABLE,,TABLE,public.tab1002,,CREATE TABLE tab1002 (empno int);,<not logged>
AUDIT: SESSION,READ,2024-05-10 08:44:41 UTC,[local],22896,psql,user01,postgres,3/15,6,1,SELECT,,,,,select now();,<not logged>
AUDIT: SESSION,CONNECT,2024-05-10 08:55:27 UTC,[local],23116,[unknown],user01,postgres,3/34,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
AUDIT: SESSION,READ,2024-05-10 08:55:40 UTC,[local],23116,psql,user01,postgres,3/35,1,1,SELECT,,,,,select now();,<not logged>
AUDIT: SESSION,WRITE,2024-05-10 08:57:38 UTC,[local],23116,psql,user01,postgres,3/36,2,1,INSERT,,TABLE,public.tab1001,,INSERT INTO tab1001 VALUES ('mynameisAAA');,<not logged>
AUDIT: SESSION,WRITE,2024-05-10 08:58:02 UTC,[local],23116,psql,user01,postgres,3/37,3,1,INSERT,,TABLE,public.tab1001,,INSERT INTO tab1001 VALUES ('mynameisBBB');,<not logged>
AUDIT: SESSION,WRITE,2024-05-10 08:58:57 UTC,[local],23116,psql,user01,postgres,3/39,5,1,INSERT,,TABLE,public.tab1002,,INSERT INTO tab1002 VALUES (9001);,<not logged>
AUDIT: SESSION,WRITE,2024-05-10 08:59:05 UTC,[local],23116,psql,user01,postgres,3/40,6,1,INSERT,,TABLE,public.tab1002,,INSERT INTO tab1002 VALUES (9002);,<not logged>
[fepuser@hostname ~]$
AUDIT: SESSION,CONNECT,2024-05-10 10:42:55 UTC,[local],24617,[unknown],user01,postgres,3/580,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
AUDIT: SESSION,READ,2024-05-10 10:34:15 UTC,[local],24480,psql,user01,postgres,3/575,1,1,SELECT,,,,,select now();,<not logged>
AUDIT: SESSION,READ,2024-05-10 10:34:33 UTC,[local],24480,psql,user01,postgres,3/576,2,1,SELECT,,TABLE,public.tab1001,,SELECT * FROM tab1001;,<not logged>
AUDIT: SESSION,READ,2024-05-10 10:34:40 UTC,[local],24480,psql,user01,postgres,3/577,3,1,SELECT,,TABLE,public.tab1002,,SELECT * FROM tab1002;,<not logged>
[fepuser@hostname ~]$
Connect as fepuser and create emp table (just as example) and populate with some data as shown below. Then create a 'hrman01' user (as non-superuser) in the postgres instance and then grant privileges as below:
[fepuser@hostname ~]$ psql –p 27500 -d postgres;
CREATE TABLE emp (id int, name text, address text, salary real);
INSERT INTO emp VALUES ( 1001 , 'empName101', 'emp_address101', 105000 );
INSERT INTO emp VALUES ( 1002 , 'empName102', 'emp_address102', 108000 );
INSERT INTO emp VALUES ( 1012 , 'empName112', 'emp_address112', 103000 );
INSERT INTO emp VALUES ( 1013 , 'empName113', 'emp_address113', 119000 );
INSERT INTO emp VALUES ( 1015 , 'empName115', 'emp_address115', 115000 );
CREATE USER hrman01 nosuperuser login;
GRANT SELECT ON emp TO hrman01;
GRANT UPDATE (salary) ON emp TO hrman01;
GRANT UPDATE (address) ON emp TO hrman01;
Connect to the instance as user 'hrman01' user and execute following SQL statements to observe object audit logging:
[fepuser@hostname ~]$ psql –p 27500 -d postgres -U hrman01 ;
postgres=> SELECT id from emp;
id
------
1001
1002
1012
1013
1015
(5 rows)
postgres=> SELECT name from emp;
name
------------
empName101
empName102
empName112
empName113
empName115
(5 rows)
postgres=> UPDATE emp SET name = 'empName11200' WHERE id = 1012;
2024-05-10 13:11:56.652 UTC [26852] ERROR: permission denied for table emp
2024-05-10 13:11:56.652 UTC [26852] STATEMENT: UPDATE emp SET name = 'empName11200' WHERE id = 1012;
ERROR: permission denied for table emp
postgres=> UPDATE emp SET salary = 120000 WHERE id = 1012;
UPDATE 1
postgres=> UPDATE emp SET address = 'emaddress-1000013131' WHERE id = 1013;
UPDATE 1
postgres=>
postgres=> \q
[fepuser@hostname ~]$
Verify that the above SQL activity for 'hrman01' was captured in the dedicated audit log as below. Observe that there was no record for the errored out statement (ERROR: permission denied for table emp) for which there was no privilege granted to the user.
[fepuser@hostname ~]$ cat ${PGDATA}/pgaudit_log/pgaudit- 2024-05-10.log
AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.emp,SELECT id from emp;,<not logged>
AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.emp,SELECT name from emp;,<not logged>
AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.emp,UPDATE emp SET salary = 120000 WHERE id = 1012;,<not logged>
AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.emp,UPDATE emp SET address = 'emaddress-1000013131' WHERE id = 1013;,<not logged>
[fepuser@hostname ~]$
Remove the pgaudit files which were copied earlier in section 1.![]()
