TimescaleDB Connector

Create Audit User

Create a dedicated TimescaleDB user for DB Audit with read access to data and TimescaleDB information schema.

                -- Create a dedicated audit user
CREATE USER dbaudit_reader WITH PASSWORD 'secure_password';

-- Grant connect privilege
GRANT CONNECT ON DATABASE metrics TO dbaudit_reader;

-- Grant schema access
GRANT USAGE ON SCHEMA public TO dbaudit_reader;
GRANT USAGE ON SCHEMA timescaledb_information TO dbaudit_reader;
GRANT USAGE ON SCHEMA _timescaledb_internal TO dbaudit_reader;

-- Grant read access to tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbaudit_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA timescaledb_information TO dbaudit_reader;

-- Grant access to pg_stat_statements
GRANT pg_read_all_stats TO dbaudit_reader;
              

Install pgAudit Extension

Enable the pgAudit extension for comprehensive audit logging.

                -- Install pgAudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure pgAudit in postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET pgaudit.log = 'all';
ALTER SYSTEM SET pgaudit.log_catalog = 'on';
ALTER SYSTEM SET pgaudit.log_client = 'on';
ALTER SYSTEM SET pgaudit.log_level = 'log';
ALTER SYSTEM SET pgaudit.log_parameter = 'on';
ALTER SYSTEM SET pgaudit.log_statement_once = 'off';

-- Reload configuration
SELECT pg_reload_conf();
              

Enable pg_stat_statements (Optional)

Enable query statistics tracking for performance insights.

                -- Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Configure in postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements,timescaledb'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

-- Query statement statistics
SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
              

Verify TimescaleDB Access

Confirm the audit user can access TimescaleDB information schema views.

                -- View hypertables
SELECT * FROM timescaledb_information.hypertables;

-- View continuous aggregates
SELECT * FROM timescaledb_information.continuous_aggregates;

-- View compression settings
SELECT * FROM timescaledb_information.compression_settings;

-- View job statistics (background workers)
SELECT * FROM timescaledb_information.job_stats;

-- Query chunk information
SELECT
    hypertable_name,
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
ORDER BY range_start DESC;
              

Test Connection

Verify connectivity and permissions using psql.

                # Test connection using psql
psql -h localhost -p 5432 -U dbaudit_reader -d metrics

# Verify TimescaleDB extension
\dx timescaledb

# Check pgAudit configuration
SHOW pgaudit.log;

# Verify user permissions
SELECT * FROM timescaledb_information.hypertables LIMIT 1;
              

Configure in DB Audit

Add the TimescaleDB connection in the DB Audit dashboard.

1. Navigate to Connections in DB Audit
2. Click Add Connection
3. Select TimescaleDB as the database type
4. Enter your connection details and credentials
5. Enable hypertable auditing options
6. Test the connection and save