Investigating PostgreSQL Query History with Pgcli and Logs

The error logs were silent, but the damage was done. Someone had run a query they shouldn’t have — and you need to know who, what, and when.

With PostgreSQL, the default tools for tracking query history and user actions are limited. Pgcli, the enhanced command-line interface for Postgres, can be part of a fast, streamlined workflow to investigate access history. It isn’t a full auditing system on its own, but combined with Postgres logging and a proper log-parsing process, Pgcli gives you an efficient way to inspect activity and trace events to individual users.

Enabling Postgres Logging

To answer “Who accessed what and when” with Pgcli, you first need server logs that record every statement. In postgresql.conf set:

log_statement = 'all'
log_line_prefix = '%m [%p] %u@%d '

%m logs the timestamp, %p the process ID, %u the user, and %d the database. Restart or reload PostgreSQL to apply these changes.

Using Pgcli for Fast Query Exploration

Once logging is enabled, connect with Pgcli:

pgcli -h your_host -U your_user -d your_db

Pgcli’s syntax highlighting, autocompletion, and pager make it faster to explore results when filtering for suspicious actions. You can query the server logs with SQL by loading them into a table or view:

CREATE TABLE pg_logs(ts timestamp, pid int, username text, dbname text, cmd text);
-- Load logs here using COPY or a parsing pipeline
SELECT * FROM pg_logs
WHERE username = 'target_user'
 AND ts BETWEEN '2024-05-15' AND '2024-05-16';

This makes it simple to see exact statements, execution times, and users.

Narrowing Down Critical Events

Combine filters on username, time ranges, and patterns in the cmd field to pinpoint dangerous or resource-heavy queries. For example:

SELECT * FROM pg_logs
WHERE cmd ILIKE '%DROP TABLE%';

In seconds, you can see every destructive statement and the account that ran it.

Limitations

Pgcli displays and searches logs quickly, but it depends on Postgres itself to capture and structure the data. Without audit-level logging enabled before the event, you can’t retroactively learn who did what. For regulated or high-stakes environments, enable logging permanently and centralize logs to prevent tampering.

If you need a real-time, searchable, always-on audit trail without manual setup, check out hoop.dev. See who accessed what and when — and put it live in minutes.