How to AUDIT ACCESS in ParadeDB

How do I use AUDIT ACCESS in ParadeDB to track table reads and writes?

AUDIT ACCESS records every read or write against a table, view, or database so you can trace who touched what data and when.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does AUDIT ACCESS do?

AUDIT ACCESS instructs ParadeDB (a PostgreSQL-compatible engine) to log every successful SELECT, INSERT, UPDATE, DELETE, and TRUNCATE executed on the chosen object. Each event is written to the server log or an audit table, capturing username, time, source IP, and the executed SQL.

When should I use AUDIT ACCESS?

Activate it when regulations (HIPAA, SOC2, GDPR) require a full data-access trail, or when investigating suspicious changes to sensitive entities like Customers or Orders.

How do I create an audit policy?

Create a reusable policy with CREATE AUDIT POLICY, specify the actions, then attach the policy with AUDIT POLICY. This keeps DDL tidy and lets you reuse the same rules on multiple tables.

Example: policy for read/write events

CREATE AUDIT POLICY customer_rw ACTIONS SELECT, INSERT, UPDATE, DELETE;

The policy now exists but is not yet attached to any table.

How do I attach the policy to a table?

AUDIT POLICY customer_rw ON TABLE Customers;

From this point forward every query touching Customers is captured.

Can I audit the entire database?

Yes—use ON DATABASE current_database(), but do so sparingly because logging volume explodes.

How do I view audit records?

If ParadeDB is configured with pgaudit.log_to_table = on, query pgaudit.log_entries. Otherwise inspect the PostgreSQL log directory.

Sample query

SELECT log_time, username, statement FROM pgaudit.log_entries WHERE object_name = 'Customers' ORDER BY log_time DESC LIMIT 20;

How do I stop auditing?

NOAUDIT POLICY customer_rw ON TABLE Customers;

Remove the policy entirely with DROP AUDIT POLICY customer_rw; when you no longer need it.

Best practices

Send logs to a dedicated schema or external collector (e.g., CloudWatch) to avoid bloating primary storage.
Set log_rotation_age and log_rotation_size to keep file sizes manageable.
Use WHEN clauses to limit auditing to business hours or specific users.

Common mistakes

Forgetting to EXCLUDE bulk ETL users. Large nightly loads flood audit tables. Add EXCEPT role etl_user to the policy.

Auditing entire databases in production. This degrades performance. Audit the smallest reasonable scope—ideally a single table or schema.

Does AUDIT ACCESS impact performance?

Yes, minimally for low-traffic tables, but significantly for high-frequency writes. Benchmark before enabling in a busy path.

Why How to AUDIT ACCESS in ParadeDB is important

How to AUDIT ACCESS in ParadeDB Example Usage


-- Track every change to Orders and its items
CREATE AUDIT POLICY order_activity
  ACTIONS SELECT, INSERT, UPDATE, DELETE;

AUDIT POLICY order_activity ON TABLE Orders;
AUDIT POLICY order_activity ON TABLE OrderItems;

-- Later, review who adjusted an order’s total amount
SELECT log_time, username, statement
FROM   pgaudit.log_entries
WHERE  object_name = 'Orders' AND statement ILIKE '%UPDATE%total_amount%';

How to AUDIT ACCESS in ParadeDB Syntax


-- Create an audit policy
CREATE AUDIT POLICY policy_name
  ACTIONS { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | ALL }
  [ WHEN condition ]
  [ EXCEPT role role_name [, ...] ];

-- Attach or detach the policy
AUDIT  POLICY policy_name ON { TABLE table_name | SCHEMA schema_name | DATABASE db_name };
NOAUDIT POLICY policy_name ON { TABLE | SCHEMA | DATABASE } target;

-- Drop the policy when finished
DROP AUDIT POLICY policy_name;

Common Mistakes

Auditing the whole database. This floods logs and slows queries. Scope policies to critical tables only to reduce overhead.
Not rotating audit logs. Logs can fill disks quickly. Configure log_rotation_age and log_rotation_size or archive to S3.