EDB Postgres Advanced Server allows database and security administrators, auditors, and operators to track and analyze database activities using EDB audit logging. One such feature introduced in EPAS version 14 is the Object level auditing, a new feature added under the category of security.
Object-level auditing provides the capability to perform auditing on specific data manipulation language (DML) statements, such as SELECT, UPDATE, DELETE, and INSERT, for particular objects. Additionally, it allows for the inclusion or exclusion of particular groups by using the (@) or (-) option in the edb_audit_statement parameter.
Let us understand this feature with an example.
I have created four tables table1, table2, table3 and table4.
edb=# create table table1 (i int);
CREATE TABLE
edb=#
edb=# alter table table1 set (edb_audit_group = 'group1');
ALTER TABLE
edb=#
edb=# create table table2 (i int) with (edb_audit_group = 'group1');
CREATE TABLE
edb=#
edb=# create table table3 (i int) with (edb_audit_group = 'group2');
CREATE TABLE
edb=#
edb=# create table table4 (i int) with (edb_audit_group = 'group2');
CREATE TABLE
edb=#
From the above code snippet we can see that we have allocated two Groups: group1 and group2 for the respective tables. Groups can be allocated while creating the table or even using the ALTER table command as we can see above.
To view the tables and the audit groups assigned, you can execute the below-mentioned query.
edb=# SELECT relname,reloptions FROM pg_class WHERE relname IN('table1', 'table2', 'table3', 'table4');
relname | reloptions
table1 | {edb_audit_group=group1}
table2 | {edb_audit_group=group1}
table3 | {edb_audit_group=group2}
table4 | {edb_audit_group=group2}
(4 rows)
Now, at the session level, we have set the edb_audit_statement parameter for the DML operations of SELECT, INSERT, UPDATE, and DELETE.
edb=# SET edb_audit_statement = 'select@group1@group2,insert@group1-group2,update-group1@group2,delete@group1@group2';
SET
edb=#
edb=# SELECT setting FROM pg_settings WHERE name = 'edb_audit_statement';
setting
select@group1@group2,insert@group1-group2,update-group1@group2,delete@group1@group2
(1 row)
From the above code snippet, we can see that we have used ' @ ' and ' - ' symbols when assigning the DML statements. The @ symbol represents that the groups would be audited and the - symbol represents that the group would be excluded. From the above edb_audit_statement set, we can see that:-
- select@group1@group2 audits SELECT statements for both audit groups.
- insert@group1-group2 audits INSERT statements for group1 and excludes INSERT statements for group2.
- update-group1@group2 audits UPDATE statements for group2 and excludes UPDATE statements for group1.
- delete@group1@group2 audits DELETE statements for both audit groups.
We have executed all the DML statements for our testing purpose as below:-
edb=# select * from table1;
i
(0 rows)
edb=# select * from table2;
i
(0 rows)
edb=# select * from table3;
i
(0 rows)
edb=# select * from table4;
i
(0 rows)
edb=# insert into table1 values (1);
INSERT 0 1
edb=# insert into table1 values (2);
INSERT 0 1
edb=# insert into table2 values (1);
INSERT 0 1
edb=# insert into table2 values (2);
INSERT 0 1
edb=# insert into table3 values (1);
INSERT 0 1
edb=# insert into table3 values (2);
INSERT 0 1
edb=# insert into table4 values (1);
INSERT 0 1
edb=# insert into table4 values (2);
INSERT 0 1
edb=#
edb=# update table1 set i=10 where i=1;
UPDATE 1
edb=# update table2 set i=10 where i=1;
UPDATE 1
edb=# update table3 set i=10 where i=1;
UPDATE 1
edb=# update table4 set i=10 where i=1;
UPDATE 1
edb=#
edb=# delete from table1 where i=2;
DELETE 1
edb=# delete from table2 where i=2;
DELETE 1
edb=# delete from table3 where i=2;
DELETE 1
edb=# delete from table4 where i=2;
DELETE 1
edb=#
The resulting audit log file contains the following. (Each audit log entry was split and displayed across multiple lines. A blank line was inserted between the audit log entries for visual clarity.)
-bash-4.2$ cat audit-20230507_092957.csv
2023-05-07 09:37:31.184 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,1,"SELECT",2023-05-07 09:30:25 PDT,4/30,0,AUDIT,00000,"statement: select * from table1;",,,,,,,,,"psql","client backend",,0,"SELECT","","select"
2023-05-07 09:37:36.754 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,2,"SELECT",2023-05-07 09:30:25 PDT,4/31,0,AUDIT,00000,"statement: select * from table2;",,,,,,,,,"psql","client backend",,0,"SELECT","","select"
2023-05-07 09:37:40.148 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,3,"SELECT",2023-05-07 09:30:25 PDT,4/32,0,AUDIT,00000,"statement: select * from table3;",,,,,,,,,"psql","client backend",,0,"SELECT","","select"
2023-05-07 09:37:42.579 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,4,"SELECT",2023-05-07 09:30:25 PDT,4/33,0,AUDIT,00000,"statement: select * from table4;",,,,,,,,,"psql","client backend",,0,"SELECT","","select"
2023-05-07 09:38:38.045 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,5,"INSERT",2023-05-07 09:30:25 PDT,4/35,0,AUDIT,00000,"statement: insert into table1 values (1);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert"
2023-05-07 09:38:41.454 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,6,"INSERT",2023-05-07 09:30:25 PDT,4/36,0,AUDIT,00000,"statement: insert into table1 values (2);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert"
2023-05-07 09:39:40.732 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,7,"INSERT",2023-05-07 09:30:25 PDT,4/37,0,AUDIT,00000,"statement: insert into table2 values (1);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert"
2023-05-07 09:39:43.640 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,8,"INSERT",2023-05-07 09:30:25 PDT,4/38,0,AUDIT,00000,"statement: insert into table2 values (2);",,,,,,,,,"psql","client backend",,0,"INSERT","","insert"
2023-05-07 09:55:29.529 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,9,"UPDATE",2023-05-07 09:30:25 PDT,4/46,0,AUDIT,00000,"statement: update table3 set i=10 where i=1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update"
2023-05-07 09:55:36.253 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,10,"UPDATE",2023-05-07 09:30:25 PDT,4/47,0,AUDIT,00000,"statement: update table4 set i=10 where i=1;",,,,,,,,,"psql","client backend",,0,"UPDATE","","update"
2023-05-07 09:56:01.923 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,11,"DELETE",2023-05-07 09:30:25 PDT,4/49,0,AUDIT,00000,"statement: delete from table1 where i=2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete"
2023-05-07 09:56:05.980 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,12,"DELETE",2023-05-07 09:30:25 PDT,4/50,0,AUDIT,00000,"statement: delete from table2 where i=2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete"
2023-05-07 09:56:09.111 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,13,"DELETE",2023-05-07 09:30:25 PDT,4/51,0,AUDIT,00000,"statement: delete from table3 where i=2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete"
2023-05-07 09:56:14.366 PDT,"enterprisedb","edb",11506,"[local]",6457d221.2cf2,14,"DELETE",2023-05-07 09:30:25 PDT,4/52,0,AUDIT,00000,"statement: delete from table4 where i=2;",,,,,,,,,"psql","client backend",,0,"DELETE","","delete"
From the above audit logs, we can observe that all the SELECT and DELETE statements are captured as both the groups were included whereas for INSERT only group1 tables are audited and for UPDATE only group2 tables are audited.
Based on your requirement, you can set the edb_audit_statement parameter. You can also consider configuring the parameter at the system level using the ALTER SYSTEM command or can directly set it in the postgresql.conf file. Note that you'll have to reload the cluster when you are making the changes at the cluster level.
-
You can audit for a single table as well, you should have a unique group name that should be particularly allocated for that specific table and not to be used for other tables. That's how you can audit a particular table.
-
To disable audit logging from a table, you can execute the below command:-
alter table table1 reset (edb_audit_group);
This command will remove the table from the audit group that would audit the log based on the configuration.
Hope this article gives a brief idea of Object Auditing, a new security feature added in EPAS v14. You can also read more in the EDB document on the same for reference.