YugabyteDB YSQL has many cool built-in audit logging features. You can read about them on this doc page: Audit Logging
For today’s tip we’ll see how easy it is to audit only a single user.
First, we’ll need to install the pgAudit extension.
The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.
It comes prebundled with YugabyteDB so its easy to install.
yugabyte=# CREATE EXTENSION pgaudit;
CREATE EXTENSION
To audit only a single user, the trick is to modify the pgaudit.log flag for that user only.
yugabyte=# CREATE USER helen LOGIN;
CREATE ROLE
yugabyte=# CREATE USER josh LOGIN;
CREATE ROLE
yugabyte=# ALTER ROLE josh SET pgaudit.log='ALL';
ALTER ROLE
At this point we should only see audit records in the Postgres log for the user JOSH.
First, let’s do some DDL and DML as the user HELEN. There shouldn’t be any log records generated.
yugabyte=# \c - helen
You are now connected to database "yugabyte" as user "helen".
yugabyte=> CREATE TABLE helen(c1 INT);
CREATE TABLE
yugabyte=> INSERT INTO helen VALUES (1), (2), (3);
INSERT 0 3
yugabyte=> DELETE FROM helen WHERE c1 = 2;
DELETE 1
yugabyte=> \! grep "AUDIT:" /root/var/data/yb-data/tserver/logs/post*
There were no audit records generated.
Now let’s do the same as the user JOSH.
yugabyte=> \c - josh
You are now connected to database "yugabyte" as user "josh".
yugabyte=> CREATE TABLE josh (c1 INT);
CREATE TABLE
yugabyte=> INSERT INTO josh VALUES (1), (2), (3);
INSERT 0 3
yugabyte=> DELETE FROM josh WHERE c1 = 2;
DELETE 1
yugabyte=> \! grep "AUDIT:" /root/var/data/yb-data/tserver/logs/post*
2023-04-06 18:47:30.106 UTC [66724 1 642f13b9.104a4] [cloud1 datacenter1 rack1 localhost] [198.71.48.248(58314) ysqlsh josh yugabyte]LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.josh,CREATE TABLE josh (c1 INT);,<not logged>
2023-04-06 18:47:45.996 UTC [66724 2 642f13b9.104a4] [cloud1 datacenter1 rack1 localhost] [198.71.48.248(58314) ysqlsh josh yugabyte]LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,"INSERT INTO josh VALUES (1), (2), (3);",<not logged>
2023-04-06 18:47:55.832 UTC [66724 3 642f13b9.104a4] [cloud1 datacenter1 rack1 localhost] [198.71.48.248(58314) ysqlsh josh yugabyte]LOG: AUDIT: SESSION,3,1,WRITE,DELETE,,,DELETE FROM josh WHERE c1 = 2;,<not logged>
Audit records were generated for the user JOSH!
Have Fun!