Audit Selects on a Table to an Audit Table Using RLS

In PostgreSQL—and by extension, YugabyteDB—there’s no native trigger that fires on SELECT statements. This means we can’t use a traditional AFTER SELECT trigger to audit read access on a table.

However, YugabyteDB supports Row-Level Security (RLS), and with a clever use of RLS policies, we can “simulate” SELECT auditing!

Here’s how it works:

We define a USING policy on the table for SELECT access
This policy calls a SECURITY DEFINER function that logs the access to an audit table
The policy must return TRUE to allow the row to be visible

Example:

Say we have a table named audit_me:

				
					yugabyte=# CREATE TABLE audit_me (id INT PRIMARY KEY, some_data TEXT);
CREATE TABLE

yugabyte=# INSERT INTO audit_me SELECT g, 'A' || g::TEXT FROM generate_series(1, 100) g;
INSERT 0 100

We want to audit all SELECT queries on the audit_me table and log the access details in a separate audit table.

For that, we’re going to need a table to track the SELECTs

				
					yugabyte=# CREATE TABLE audit_me_audit (
yugabyte(#     username TEXT,
yugabyte(#     pg_backend_pid INT,
yugabyte(#     access_time TIMESTAMP,
yugabyte(#     query TEXT
yugabyte(# );
CREATE TABLE

Next, we’ll create a function that inserts a record into the audit table whenever a SELECT is performed on the main table.

				
					yugabyte=# CREATE OR REPLACE FUNCTION log_audit_me_select()
yugabyte-# RETURNS BOOLEAN AS $$
yugabyte$# BEGIN
yugabyte$#   INSERT INTO audit_me_audit(username, pg_backend_pid, access_time, query)
yugabyte$#   VALUES (current_user, pg_backend_pid(), clock_timestamp(), current_query());
yugabyte$#   RETURN TRUE;
yugabyte$# END;
yugabyte$# $$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE FUNCTION

Next we’ll need an RLS policy.

				
					yugabyte=# CREATE POLICY audit_me_select
yugabyte-#   ON audit_me
yugabyte-#   FOR SELECT
yugabyte-#   USING (log_audit_me_select());
CREATE POLICY

Finally, we need to enble RLS on the table to be audited.

				
					yugabyte=# ALTER TABLE audit_me ENABLE ROW LEVEL SECURITY;
ALTER TABLE

Let’s test this with a used named gus:

				
					yugabyte=# CREATE USER gus WITH LOGIN;
CREATE ROLE

yugabyte=# GRANT SELECT ON audit_me TO gus;
GRANT

yugabyte=# \! ysqlsh -h $(hostname -I) -U gus -c "SELECT * FROM audit_me WHERE id = 1;"
 id | some_data
----+-----------
  1 | A1
(1 row)

yugabyte=# \! ysqlsh -h $(hostname -I) -U gus -c "SELECT * FROM audit_me WHERE id >= 95;"
 id  | some_data
-----+-----------
  98 | A98
  97 | A97
  96 | A96
 100 | A100
  99 | A99
  95 | A95
(6 rows)

yugabyte=# \! ysqlsh -h $(hostname -I) -U gus -c "SELECT * FROM audit_me WHERE some_data = 'A55';"
 id | some_data
----+-----------
 55 | A55
(1 row)

yugabyte=# SELECT * FROM audit_me_audit ORDER BY access_time desc;
 username | pg_backend_pid |        access_time         |                      query
----------+----------------+----------------------------+-------------------------------------------------
 yugabyte |         780378 | 2025-04-14 18:31:35.692972 | SELECT * FROM audit_me WHERE some_data = 'A55';
 yugabyte |         779815 | 2025-04-14 18:16:29.610856 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779815 | 2025-04-14 18:16:29.610534 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779815 | 2025-04-14 18:16:29.610166 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779815 | 2025-04-14 18:16:29.609821 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779815 | 2025-04-14 18:16:29.609295 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779815 | 2025-04-14 18:16:29.605198 | SELECT * FROM audit_me WHERE id >= 95;
 yugabyte |         779799 | 2025-04-14 18:16:13.644461 | SELECT * FROM audit_me WHERE id = 1;
(8 rows)

We can track queries within the same session by monitoring the value of pg_backend_pid!

Note: This above approach only works for non-superusers, and you must test with roles that do not own the table or have BYPASSRLS privileges. That being said, there’s a way to force RLS to apply even to the owner, using the setting:

				
					ALTER TABLE your_table FORCE ROW LEVEL SECURITY;

One important caveat to keep in mind: RLS policies are evaluated for each row, not just once per statement. So a query like SELECT * or SELECT COUNT(*) can trigger the audit logic multiple times—potentially inserting a large number of rows into the audit table!

Example:

				
					yugabyte=# TRUNCATE TABLE audit_me_audit;
TRUNCATE TABLE

yugabyte=# SELECT COUNT(*) FROM audit_me_audit;
 count
-------
     0
(1 row)

yugabyte=# \! ysqlsh -h $(hostname -I) -U gus -c "SELECT COUNT(*) FROM audit_me;"
 count
-------
   100
(1 row)

yugabyte=# SELECT COUNT(*) FROM audit_me_audit;
 count
-------
   100
(1 row)

Have Fun!