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.