PostgreSQL Database Activity Monitoring Requirements

Introduction

PostgreSQL processes over 10,000 transactions per second in high-load environments. Each transaction requires careful monitoring. A recent study shows that 60% of database breaches occur due to insufficient monitoring (a statistics from Verizon DBIR, 2023). Let’s explore how to implement comprehensive PostgreSQL database activity monitoring and data auditing.

Understanding Access and Authentication Monitoring

PostgreSQL provides robust tools for tracking user access. Here’s how to implement essential authentication monitoring:

-- Monitor active sessions
SELECT pid, usename, application_name, client_addr, 
       backend_start, state, query
FROM pg_stat_activity;

Pg_stat_activity contains all the active and idle sessions at the moment:

Monitor user sessions duration as follows:

SELECT usename, count(*), 
       avg(extract(epoch from now() - backend_start))::integer 
FROM pg_stat_activity 
GROUP BY usename;

Data Modification Tracking

Monitoring data changes helps detect unauthorized modifications. Implement these tracking mechanisms:

Create an audit trigger function

CREATE OR REPLACE FUNCTION audit_trigger_func() 
RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log(
        table_name, action, user_name, 
        changed_fields, row_data
    )
    VALUES (
        TG_TABLE_NAME, TG_OP, current_user,
        row_to_json(NEW), row_to_json(OLD)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Apply trigger to sensitive tables

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON sensitive_table
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Before implementing the audit trigger, replace ‘sensitive_table’ with the actual name of the table you want to monitor. While triggers provide a straightforward approach to auditing, they can impact performance on high-load systems and may require additional maintenance. Consider using dedicated audit logging solutions like pgAudit for production environments.

Security Configuration Monitoring

Track security-related changes with these queries:

1. Monitor role changes

SELECT rolname, rolsuper, rolcreaterole, 
       rolcreatedb, rolcanlogin
FROM pg_roles;

This may return the access matrix like this for our local PostgreSQL:

2. Track permission changes.

SELECT grantor, grantee, table_schema, 
       table_name, privilege_type
FROM information_schema.role_table_grants;

3. Check current security settings.

SHOW all;

Backup and Recovery Operations

Implement backup monitoring with these approaches:

-- Track WAL (Write-Ahead Log) status
SELECT * FROM pg_stat_wal;

-- Monitor backup history
SELECT start_time, end_time, 
       success, database_name
FROM pg_backup_history;

-- Check replication status
SELECT * FROM pg_stat_replication;

Query Activity Monitoring

Track query performance and patterns:

-- Enable query tracking
CREATE EXTENSION pg_stat_statements;

-- Monitor long-running queries
SELECT pid, age(clock_timestamp(), query_start), 
       usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' 
AND query_start < now() - interval '5 minutes';

-- Analyze query patterns
SELECT query, calls, total_time, rows, 
       mean_time
FROM pg_stat_statements 
ORDER BY total_time DESC;

System Event Monitoring

Track critical system events:

-- Monitor database statistics (backend, transaction summary)
SELECT datname, numbackends, xact_commit, 
       xact_rollback, blks_read, blks_hit
FROM pg_stat_database;

This results in the output as follows:

You may find some rolled back transactions (118 in this case).

-- Check table statistics
SELECT schemaname, relname, seq_scan, 
       seq_tup_read, idx_scan
FROM pg_stat_user_tables;

This query helps you understand how efficiently your tables are being accessed. By looking at the frequency of sequential scans versus index scans, you can spot performance issues, such as tables being scanned without using available indexes, which may signal a need for better indexing or query optimization.

-- Monitor lock conflicts
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks
  ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.locktype = 'transactionid'
  AND blocked_locks.transactionid = blocking_locks.transactionid
JOIN pg_stat_activity blocked_activity 
  ON blocked_locks.pid = blocked_activity.pid
JOIN pg_stat_activity blocking_activity 
  ON blocking_locks.pid = blocking_activity.pid
WHERE blocked_locks.granted = FALSE
  AND blocking_locks.granted = TRUE;

DataSunrise for PostgreSQL Database Activity Monitoring

DataSunrise elevates database activity monitoring by providing robust security features in a consistent manner across dozens of supported databases. It offers five flexible deployment modes, allowing you to balance database latency, feature sets, and the impact on existing infrastructure.

The dashboard offers a comprehensive view of key information about the databases under protection and monitoring:

All the databases protected listed in the Databases page with their network parameters:

Finally, all monitored events are presented in a consistent format for all databases, conveniently displayed in one place:

Beyond monitoring, DataSunrise delivers advanced data security, sensitive data discovery, automated compliance reporting, and data masking. Our user-friendly, web-based interface includes an AI-driven assistant, enabling quick access to guides and reference materials for seamless product use.

Summary and Conclusions

Effective PostgreSQL monitoring requires a comprehensive approach covering all aspects of database operations. Regular monitoring helps maintain security, performance, and reliability of your database systems.

DataSunrise PostgreSQL Security Solution

DataSunrise provides specialized tools for PostgreSQL monitoring and security. Our solution offers real-time activity monitoring, compliance reporting, and advanced security features designed specifically for PostgreSQL databases.

Experience the power of DataSunrise’s PostgreSQL security tools. Visit our website to schedule an online demo and discover how we can enhance your database security infrastructure.