{"id":6072,"date":"2026-09-28T07:00:00","date_gmt":"2026-09-28T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6072"},"modified":"2026-09-27T20:37:38","modified_gmt":"2026-09-27T17:37:38","slug":"pos-energopoioyme-to-object-session-level-auditing-se-postgresql","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-energopoioyme-to-object-session-level-auditing-se-postgresql\/","title":{"rendered":"How to enable Object and Session level Auditing in PostgreSQL"},"content":{"rendered":"

When you manage production databases, sooner or later the time comes when security and compliance requirements will knock on your door. The question is not whether you will need to record who did what, but how to do it without bringing the Server to its knees, e.g. excluding the application server user. In this article we will see how to properly set up the pgAudit<\/strong>.<\/p>\n\n\n\n

The installation<\/h2>\n\n\n\n

We must first download and install the corresponding pgAudit version for the operating system and PostgreSQL version we have, e.g. for RedHat Linux and PostgreSQL 11:<\/p>\n\n\n\n

sudo yum install pgaudit11<\/pre>\n\n\n\n
Then we go to the file postgresql.conf<\/code> and add its library:<\/p>\n\n\n\n
su postgres\nvi $PGDATA\/postgresql.conf<\/pre>\n\n\n\n
shared_preload_libraries = 'pgaudit'\npgaudit.log_catalog = off\nlog_line_prefix = '%m [%p] %q%u@%d '<\/code><\/pre>\n\n\n\n
Then it wants to restart:<\/p>\n\n\n\n
sudo systemctl restart postgresql-11<\/pre>\n\n\n\n
Once uploaded, the extension needs to be activated from SQL window:<\/p>\n\n\n\n
CREATE EXTENSION pgaudit;<\/pre>\n\n\n\n
Session level auditing<\/h2>\n\n\n\n
With session level auditing we can monitor any user we want for any action we want (read,write,ddl,misc,all,none<\/code>) universally.<\/p>\n\n\n\n
If we want to monitor write and ddl for a user we run:<\/p>\n\n\n\n
ALTER ROLE stratos SET pgaudit.log = 'write, ddl';<\/pre>\n\n\n\n
If we want to monitor all users but to exclude the application server user<\/strong> so as not to be filled with useless information:<\/p>\n\n\n\n
ALTER SYSTEM SET pgaudit.log = 'write, ddl';\nSELECT pg_reload_conf();\n\nALTER ROLE app_user SET pgaudit.log = 'none';<\/pre>\n\n\n\n
Alternatively we can add it to postgresql.conf<\/code>(wants to reload):<\/p>\n\n\n\n
pgaudit.log = 'write, ddl'<\/code><\/pre>\n\n\n\n
Object level auditing<\/h2>\n\n\n\n
In this case, we have the ability to perform separate auditing per user and per table by creating roles.<\/p>\n\n\n\n
We can create the role we want, e.g. select_auditor<\/code>, give it the right to audit what we want and finally give this role to the users we want:<\/p>\n\n\n\n
CREATE ROLE select_auditor;\n\nGRANT SELECT ON public.customers TO select_auditor;\n\nALTER ROLE stratos SET pgaudit.role = 'select_auditor';\n<\/pre>\n\n\n\n
Because we can only give one role as pgaudit.role<\/code> If we want to give a user two different auditing policies, we associate a master role with the others:<\/p>\n\n\n\n
CREATE ROLE pgaudit_master;\nCREATE ROLE my_select_auditor;\nCREATE ROLE my_update_auditor;\n\nGRANT my_select_auditor TO pgaudit_master;\nGRANT my_update_auditor TO pgaudit_master;\n\nGRANT SELECT ON public.orders TO my_select_auditor;\nGRANT UPDATE, INSERT ON public.orders TO my_update_auditor;\n\nALTER ROLE stratos SET pgaudit.role = 'pgaudit_master';<\/pre>\n\n\n\n
Similarly, if we want to do some object level auditing again excluding the application server user<\/strong>:<\/p>\n\n\n\n
ALTER SYSTEM SET pgaudit.role = 'pgaudit_role';\n\nSELECT pg_reload_conf();<\/pre>\n\n\n\n
Alternatively we can add it to postgresql.conf<\/code>(wants to reload):<\/p>\n\n\n\n
pgaudit.role = 'pgaudit_role'<\/code><\/pre>\n\n\n\n
And after granting the rights we want to request to the role, we exclude the user from the role:<\/p>\n\n\n\n
CREATE ROLE pgaudit_role NOLOGIN;\n\nGRANT UPDATE, DELETE ON public.order_items TO pgaudit_role;\n\nALTER ROLE app_user SET pgaudit.role = ''; <\/pre>\n\n\n\n
<\/p>\n\n\n\n
How to read auditing logs<\/h2>\n\n\n\n
With the following query we can see what has been run, when, by which user, in which table:<\/p>\n\n\n\n
WITH today_log AS (\n    SELECT 'log\/postgresql-' || to_char(now(), 'Dy') || '.log' AS log_file\n),\nraw_lines AS (\n    SELECT \n        line_number,\n        log_line,\n        COUNT(CASE WHEN log_line ~ '^\\d{4}-\\d{2}-\\d{2}' THEN 1 END) OVER (ORDER BY line_number) AS event_id\n    FROM (\n        SELECT line_number, log_line\n        FROM unnest(string_to_array(pg_read_file((SELECT log_file FROM today_log)), e'\\n')) WITH ORDINALITY AS t(log_line, line_number)\n    ) s\n),\ngrouped_events AS (\n    SELECT \n        event_id,\n        MIN(log_line) AS header_line,\n        string_agg(log_line, e'\\n' ORDER BY line_number) AS full_multiline_block\n    FROM raw_lines\n    WHERE event_id > 0\n    GROUP BY event_id\n),\nparsed_events AS (\n    SELECT \n        event_id,\n        header_line,\n        full_multiline_block,\n        ac[1] AS audit_class_val,\n        ct[1] AS command_type_val,\n        COALESCE(\n            NULLIF(split_part(substring(header_line from 'AUDIT:.*'), ',', 7), ''),\n            to_obj[1],\n            'N\/A'\n        ) AS target_obj\n    FROM grouped_events\n    LEFT JOIN LATERAL (\n        SELECT regexp_matches(header_line, 'AUDIT:\\s*([A-Z]+),') \n    ) AS a(ac) ON true\n    LEFT JOIN LATERAL (\n        SELECT regexp_matches(header_line, 'AUDIT:\\s*[^,]+,\\d+,\\d+,[^,]+,([^,]+)')\n    ) AS c(ct) ON true\n    LEFT JOIN LATERAL (\n        SELECT regexp_matches(full_multiline_block, '(?:FROM|INTO|UPDATE|DELETE\\s+FROM)\\s+([a-zA-Z0-9_\\.]+)', 'i')\n    ) AS t(to_obj) ON true\n)\nSELECT \n    substring(header_line from '^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})') AS log_time,\n    substring(header_line from '\\] ([^@]+)@') AS user_name,\n    substring(header_line from '@([^ ]+)') AS database_name,\n    audit_class_val AS audit_class,\n    command_type_val AS command_type,\n    target_obj AS target_object,\n    \n    -- \u0395\u0394\u03a9 \u0397 \u0394\u0399\u039f\u03a1\u0398\u03a9\u03a3\u0397: \u03a0\u03b9\u03ac\u03bd\u03b5\u03b9 \u03c4\u03bf query \u03b5\u03af\u03c4\u03b5 \u03b3\u03c1\u03ac\u03c6\u03c4\u03b7\u03ba\u03b5 \u03bc\u03b5 \u03b5\u03b9\u03c3\u03b1\u03b3\u03c9\u03b3\u03b9\u03ba\u03ac \u03b5\u03af\u03c4\u03b5 \u03c7\u03c9\u03c1\u03af\u03c2!\n    rtrim(\n        COALESCE(\n            substring(full_multiline_block from ',\\s*\"([^\"]+)\",\\s*<not logged>'), -- 1. \u03a8\u03ac\u03c7\u03bd\u03b5\u03b9 \u03b1\u03bd \u03ad\u03c7\u03b5\u03b9 \u03b5\u03b9\u03c3\u03b1\u03b3\u03c9\u03b3\u03b9\u03ba\u03ac (multiline \/ \u03bc\u03b5 \u03ba\u03cc\u03bc\u03bc\u03b1\u03c4\u03b1)\n            substring(full_multiline_block from ',\\s*([^\",]+),\\s*<not logged>'),  -- 2. \u03a8\u03ac\u03c7\u03bd\u03b5\u03b9 \u03b1\u03bd \u03b4\u03b5\u03bd \u03ad\u03c7\u03b5\u03b9 \u03b5\u03b9\u03c3\u03b1\u03b3\u03c9\u03b3\u03b9\u03ba\u03ac (single line)\n            substring(full_multiline_block from 'QUERY:\\s*(.*)$'),                 -- 3. \u03a8\u03ac\u03c7\u03bd\u03b5\u03b9 \u03b3\u03b9\u03b1 Object Audit form\n            header_line                                                            -- 4. Fallback \u03b1\u03bd \u03b1\u03c0\u03bf\u03c4\u03cd\u03c7\u03bf\u03c5\u03bd \u03cc\u03bb\u03b1 \u03c4\u03b1 \u03c0\u03b1\u03c1\u03b1\u03c0\u03ac\u03bd\u03c9\n        ), \n        '\"'\n    ) AS executed_query,\n    \n    full_multiline_block AS full_log_line\n\nFROM parsed_events\nWHERE header_line LIKE '%LOG:  AUDIT:%'\n  AND header_line NOT LIKE '%WITH today_log%'\n  AND header_line NOT LIKE '%pg_read_file%'\nORDER BY log_time DESC;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Log maintenance<\/h3>\n\n\n\n
If the parameter is enabled log_truncate_on_rotation<\/code> it essentially makes us rollover the days we have set in the parameter log_rotation_age<\/code>, if we don't want this and want to keep the information for more days, we will have to create some mechanism to delete old files.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n