CIS TimescaleDB Benchmark

This recommendation ensures that SCRAM-SHA-256 authentication is enforced on the TimescaleDB time-series database. Enforcing this requirement establishes a minimum security standard and prevents insecure configurations.

Without this enforcement, the TimescaleDB time-series database may accept insecure configurations that weaken overall security posture. Mandating this control ensures consistent protection against common attack vectors.

# Check authentication configuration:
sudo -u postgres psql -c "SHOW hba_file;"
sudo cat $(sudo -u postgres psql -tAc "SHOW hba_file;")

# Verify password encryption:
sudo -u postgres psql -c "SHOW password_encryption;"

# Enforce scram-sha-256 authentication:
sudo -u postgres psql -c "ALTER SYSTEM SET password_encryption = 'scram-sha-256';"

# Configure pg_hba.conf – reject trust, use scram-sha-256:
# TYPE  DATABASE  USER      ADDRESS       METHOD
host    all       all       0.0.0.0/0     scram-sha-256
host    all       all       ::/0          scram-sha-256
local   all       postgres                peer

sudo systemctl reload postgresql

This recommendation verifies that roles follow least-privilege with no excess superusers on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# List database roles and privileges:
sudo -u postgres psql -c "\du+"

# Check superuser roles:
sudo -u postgres psql -c "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolsuper = true;"

# Create application-specific roles with least privilege:
sudo -u postgres psql << 'EOF'
CREATE ROLE app_read LOGIN PASSWORD 'secure_password';
CREATE ROLE app_write LOGIN PASSWORD 'secure_password';

GRANT CONNECT ON DATABASE tsdb TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;

GRANT CONNECT ON DATABASE tsdb TO app_write;
GRANT USAGE ON SCHEMA public TO app_write;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_read;
EOF

This recommendation verifies that SSL/TLS is enabled with TLSv1.3 minimum on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check SSL configuration:
sudo -u postgres psql -c "SHOW ssl;"
sudo -u postgres psql -c "SHOW ssl_cert_file;"
sudo -u postgres psql -c "SHOW ssl_min_protocol_version;"

# Enable and harden SSL/TLS:
sudo -u postgres psql << 'EOF'
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET ssl_cert_file = '/etc/postgresql/certs/server.crt';
ALTER SYSTEM SET ssl_key_file = '/etc/postgresql/certs/server.key';
ALTER SYSTEM SET ssl_ca_file = '/etc/postgresql/certs/ca.crt';
ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.3';
ALTER SYSTEM SET ssl_ciphers = 'TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256';
EOF
sudo systemctl restart postgresql

This setting ensures that listen addresses and connections are restricted on the TimescaleDB time-series database. Restricting this capability limits potential abuse and enforces the principle of least privilege across the environment.

Unrestricted access to this capability could allow unauthorized users or processes to perform actions beyond their intended scope. Applying least-privilege principles to the TimescaleDB time-series database is essential for defense in depth.

# Check listen addresses:
sudo -u postgres psql -c "SHOW listen_addresses;"

# Check port:
sudo -u postgres psql -c "SHOW port;"

# Check max connections:
sudo -u postgres psql -c "SHOW max_connections;"

# Restrict network listening:
sudo -u postgres psql << 'EOF'
ALTER SYSTEM SET listen_addresses = '127.0.0.1,10.0.0.5';
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET superuser_reserved_connections = 3;
EOF

# Configure firewall:
sudo ufw allow from 10.0.0.0/24 to any port 5432
sudo ufw deny 5432
sudo systemctl restart postgresql

This recommendation verifies that TimescaleDB extension is up to date on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check TimescaleDB extension version:
sudo -u postgres psql -c "SELECT extname, extversion FROM pg_extension WHERE extname = 'timescaledb';"

# List hypertables:
sudo -u postgres psql -c "SELECT * FROM timescaledb_information.hypertables;"

# Update TimescaleDB to latest:
sudo apt-get update && sudo apt-get install -y timescaledb-2-postgresql-16

# Update extension in database:
sudo -u postgres psql -d tsdb -c "ALTER EXTENSION timescaledb UPDATE;"

# Verify:
sudo -u postgres psql -d tsdb -c "\dx timescaledb"

This recommendation verifies that continuous aggregates have proper access controls on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check continuous aggregate policies:
sudo -u postgres psql -d tsdb << 'EOF'
SELECT view_name, materialization_hypertable_name
FROM timescaledb_information.continuous_aggregates;

SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_refresh_continuous_aggregate';
EOF

# Create continuous aggregate with security:
sudo -u postgres psql -d tsdb << 'EOF'
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, device_id
WITH NO DATA;

SELECT add_continuous_aggregate_policy('sensor_hourly',
  start_offset => INTERVAL '3 days',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- Restrict access:
REVOKE ALL ON sensor_hourly FROM PUBLIC;
GRANT SELECT ON sensor_hourly TO app_read;
EOF

This recommendation verifies that retention policies are configured for all hypertables on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check retention policies:
sudo -u postgres psql -d tsdb << 'EOF'
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

SELECT hypertable_name, num_chunks
FROM timescaledb_information.hypertables;
EOF

# Add retention policies:
sudo -u postgres psql -d tsdb << 'EOF'
-- Drop chunks older than 90 days:
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

-- Compression before retention:
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Verify policies:
SELECT * FROM timescaledb_information.jobs
WHERE hypertable_name = 'sensor_data';
EOF

This recommendation verifies that compression is enabled with proper segmentation on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check compression settings:
sudo -u postgres psql -d tsdb << 'EOF'
SELECT hypertable_name, compression_enabled
FROM timescaledb_information.hypertables;

SELECT * FROM timescaledb_information.compression_settings;
EOF

# Enable compression with proper segment-by:
sudo -u postgres psql -d tsdb << 'EOF'
ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id',
  timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- Verify compression ratio:
SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes) AS after,
       compression_ratio
FROM hypertable_compression_stats('sensor_data');
EOF

This recommendation verifies that WAL archiving and automated basebackups are configured on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check backup configuration:
ls -la /var/lib/postgresql/backups/

# Check pg_basebackup availability:
which pg_basebackup

# Verify WAL archiving:
sudo -u postgres psql -c "SHOW archive_mode;"
sudo -u postgres psql -c "SHOW archive_command;"

# Configure WAL archiving for PITR:
sudo -u postgres psql << 'EOF'
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f';
ALTER SYSTEM SET wal_level = 'replica';
EOF
sudo systemctl restart postgresql

# Set up automated basebackup via cron:
cat > /etc/cron.d/pg-backup << 'EOF'
0 2 * * * postgres pg_basebackup -D /var/lib/postgresql/backups/base_$(date +\%Y\%m\%d) -Ft -z -P
find /var/lib/postgresql/backups/ -mtime +7 -exec rm -rf {} +
EOF

This recommendation verifies that pgAudit logging is enabled for write and DDL operations on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check logging configuration:
sudo -u postgres psql << 'EOF'
SHOW log_statement;
SHOW log_connections;
SHOW log_disconnections;
SHOW log_min_duration_statement;
SHOW pgaudit.log;
EOF

# Enable comprehensive logging:
sudo -u postgres psql << 'EOF'
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_min_duration_statement = 1000;
ALTER SYSTEM SET log_line_prefix = '%t [%p] %u@%d ';
ALTER SYSTEM SET log_checkpoints = 'on';
ALTER SYSTEM SET log_lock_waits = 'on';
EOF

# Enable pgAudit:
sudo -u postgres psql << 'EOF'
CREATE EXTENSION IF NOT EXISTS pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = 'on';
EOF
sudo systemctl reload postgresql

This recommendation verifies that row-level security is enabled for multi-tenant hypertables on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check row-level security:
sudo -u postgres psql -d tsdb << 'EOF'
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

SELECT * FROM pg_policies;
EOF

# Enable row-level security on hypertables:
sudo -u postgres psql -d tsdb << 'EOF'
ALTER TABLE sensor_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON sensor_data
  USING (tenant_id = current_setting('app.tenant_id')::int);

CREATE POLICY admin_all ON sensor_data
  TO admin_role
  USING (true);

-- Force RLS even for table owner:
ALTER TABLE sensor_data FORCE ROW LEVEL SECURITY;
EOF

This recommendation verifies that data-at-rest encryption is configured on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check data-at-rest encryption:
sudo -u postgres psql -c "SHOW data_directory;"
lsblk -o NAME,FSTYPE,MOUNTPOINT,SIZE
df -h $(sudo -u postgres psql -tAc "SHOW data_directory;")

# Use filesystem-level encryption (LUKS):
# For new installations:
sudo cryptsetup luksFormat /dev/sdb
sudo cryptsetup open /dev/sdb pg_encrypted
sudo mkfs.ext4 /dev/mapper/pg_encrypted
sudo mount /dev/mapper/pg_encrypted /var/lib/postgresql/16/main

# Set proper permissions:
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main

# For column-level encryption use pgcrypto:
sudo -u postgres psql -d tsdb << 'EOF'
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Encrypt sensitive columns:
UPDATE users SET email = pgp_sym_encrypt(email, 'encryption_key');
EOF

This recommendation verifies that streaming replication with synchronous commit is configured on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check replication status:
sudo -u postgres psql << 'EOF'
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_wal_receiver;
SHOW synchronous_commit;
EOF

# Configure streaming replication:
# On primary:
sudo -u postgres psql << 'EOF'
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';
ALTER SYSTEM SET synchronous_commit = 'remote_apply';
ALTER SYSTEM SET synchronous_standby_names = 'standby1';
EOF

# Add to pg_hba.conf:
host replication replicator 10.0.0.0/24 scram-sha-256

# On standby:
sudo -u postgres pg_basebackup -h primary.example.com \
  -D /var/lib/postgresql/16/main -U replicator -Fp -Xs -P

cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
primary_conninfo = 'host=primary.example.com port=5432 user=replicator password=strong_password application_name=standby1'
EOF
touch /var/lib/postgresql/16/main/standby.signal

This recommendation verifies that TimescaleDB workers and memory settings are tuned on the TimescaleDB time-series database. Implementing this control strengthens the overall security configuration and reduces exposure to potential threats.

Failure to implement this control may leave the TimescaleDB time-series database vulnerable to attack or non-compliant with organizational security policies. This control helps establish a consistent, hardened configuration baseline.

# Check resource tuning:
sudo -u postgres psql << 'EOF'
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;
SHOW max_worker_processes;
SHOW timescaledb.max_background_workers;
EOF

# Tune TimescaleDB for production:
sudo -u postgres psql << 'EOF'
-- Memory settings (adjust for your RAM):
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';

-- TimescaleDB workers:
ALTER SYSTEM SET max_worker_processes = 16;
ALTER SYSTEM SET timescaledb.max_background_workers = 8;

-- Connection limits:
ALTER SYSTEM SET max_connections = 200;

-- Checkpoint tuning:
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '4GB';
EOF
sudo systemctl restart postgresql