How to AUDIT ACCESS in ParadeDB

Galaxy Glossary

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.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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

Frequently Asked Questions (FAQs)

Is AUDIT ACCESS available in vanilla PostgreSQL?

No. It ships with ParadeDB or with the pgAudit extension patched to support SQL-level policies.

Does auditing capture failed statements?

Yes, if you add the keyword ERROR to the ACTIONS list or enable pgaudit.log = 'read, write, ddl, role, misc'.

Can I filter by time of day?

Yes. Use a WHEN clause such as WHEN (extract(hour from current_timestamp) BETWEEN 8 AND 18).

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.