KloudDBShield

Postgres

Summary

Section 1 - Installation and Patches 4/4 - (100.00%)
Section 2 - Directory and File Permissions 1/3 - (33.33%)
Section 3 - Logging Monitoring and Auditing 19/26 - (73.08%)
Section 4 - User Access and Authorization 1/2 - (50.00%)
Section 5 - Connection and Login 0/1 - (0.00%)
Section 6 - Postgres Settings 3/6 - (50.00%)
Section 7 - Replication 0/1 - (0.00%)
Section 8 - Special Configuration Considerations 0/1 - (0.00%)
Overall Score 28/44 - (63.64%)

Control Details

Postgres Version 14

Pass Fail Manual Check
Control Result Details
1.3 Ensure systemd Service Files Are Enabled
1.4 Ensure Data Cluster Initialized Successfully
1.6 Verify That 'PGPASSWORD' is Not Set in Users' Profiles
1.7 Verify That the 'PGPASSWORD' Environment Variable is Not in Use
2.1 Ensure the file permissions mask is correct
2.2 Ensure extension directory has appropriate ownership and permissions
2.3 Disable PostgreSQL Command History
3.1.2 Ensure the log destinations are set correctly
3.1.3 Ensure the logging collector is enabled
3.1.4 Ensure the log file destination directory is set correctly
3.1.5 Ensure the filename pattern for log files is set correctly
3.1.6 Ensure the log file permissions are set correctly
3.1.7 Ensure 'log_truncate_on_rotation' is enabled
3.1.8 Ensure the maximum log file lifetime is set correctly
3.1.9 Ensure the maximum log file size is set correctly
3.1.10 Ensure the correct syslog facility is selected
3.1.11 Ensure syslog messages are not suppressed
3.1.12 Ensure syslog messages are not lost due to size
3.1.13 Ensure the program name for PostgreSQL syslog messages is correct
3.1.14 Ensure the correct messages are written to the server log
3.1.15 Ensure the correct SQL statements generating errors are recorded
3.1.16 Ensure 'debug_print_parse' is disabled
3.1.17 Ensure 'debug_print_rewritten' is disabled
3.1.18 Ensure 'debug_print_plan' is disabled
3.1.19 Ensure 'debug_pretty_print' is enabled
3.1.20 Ensure 'log_connections' is enabled
3.1.21 Ensure 'log_disconnections' is enabled
3.1.22 Ensure 'log_error_verbosity' is set correctly
3.1.23 Ensure 'log_hostname' is set correctly
3.1.24 Ensure 'log_line_prefix' is set correctly
3.1.25 Ensure 'log_statement' is set correctly
3.1.26 Ensure 'log_timezone' is set correctly
3.2 Ensure the PostgreSQL Audit Extension (pgAudit) is enabled
4.5 Ensure excessive administrative privileges are revoked
4.4 Lock Out Accounts if Not Currently in Use
4.5 Ensure excessive function privileges are revoked
4.8 Ensure excessive DML privileges are revoked
4.7 Ensure Row Level Security (RLS) is configured correctly
4.8 Ensure the set_user extension is installed
4.9 Make use of predefined roles
5.1 Do Not Specify Passwords in the Command Line
5.2 Ensure PostgreSQL is Bound to an IP Address
5.3 Ensure login via "local" UNIX Domain Socket is configured correctly
5.4 Ensure login via "host" TCP/IP Socket is configured correctly
5.5 Ensure per-account connection limits are used
5.6 Ensure Password Complexity is configured
6.2 Ensure 'backend' runtime parameters are configured correctly
6.3 Ensure 'Postmaster' Runtime Parameters are Configured
6.4 Ensure 'SIGHUP' Runtime Parameters are Configured
6.5 Ensure 'Superuser' Runtime Parameters are Configured
6.6 Ensure 'User' Runtime Parameters are Configured
6.7 Ensure FIPS 140-2 OpenSSL Cryptography Is Used
6.8 Ensure SSL is enabled and configured correctly
6.9 Ensure the TLSv1.0 and TLSv1.1 Protocols are Disabled
6.10 Ensure Weak SSL/TLS Ciphers Are Disabled
6.11 Ensure the pgcrypto extension is installed and configured correctly
7.1 Ensure a replication-only user is created and used for streaming replication
7.2 Ensure logging of replication commands is configured
7.3 Ensure base backups are configured and functional
7.4 Ensure WAL archiving is configured and functional
7.5 Ensure streaming replication parameters are configured correctly
8.1 Ensure PostgreSQL subdirectory locations are outside the data cluster
8.2 Ensure the backup and restore tool, 'pgBackRest', is installed and configured
8.3 Ensure miscellaneous configuration settings are correct

Users Report

List of db users
Roles with Superuser attribute
Users with CREATEDB
Users with CREATEROLE
Users with NOINHERIT
Users with BYPASSRLS
Users without connection limits
Password expiry not set (Roles without password expiry)
Roles with default config set
Roles with replication set

HBA Scanner Report

Control Result Details
HBA Check 1 - Usage of Trust method is not secure
HBA Check 2 - Follow the least privilege method - Be specific and give the needed database(s) and not all
HBA Check 3 - Follow the least privilege method - Be specific and give the needed user(s) and not all
HBA Check 4 - Better to use scram-sha-256
HBA Check 5 - Review the lines in hba containing peer method. Although peer method might be ok to use, please check the users and the hba lines to review furthe
HBA Check 6 - Usage of Trust method is might not be secure
HBA Check 7 - Usage of password method is might not be secure
HBA Check 8 - Better to enforce ssl to secure your connections - use hostssl instead of host (after enabling ssl)
HBA Check 9 - Follow the least privilege method - Be specific and give the needed ip(s) and not all
HBA Check 2 - Follow the least privilege method - Be specific and give the needed database(s) and not all (Failure Report)
Line Number HBA Entry
90 local {all} peer
95 local {all} peer
97 host {all} all md5
99 host {all} all scram-sha-256
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
HBA Check 3 - Follow the least privilege method - Be specific and give the needed user(s) and not all (Failure Report)
Line Number HBA Entry
95 local {all} peer
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
105 local {replication} peer
106 host {replication} 127.0.0.1 scram-sha-256
107 host {replication} ::1 scram-sha-256
HBA Check 4 - Better to use scram-sha-256 (Failure Report)
Line Number HBA Entry
97 host {all} all md5
98 host {replication} all md5
HBA Check 5 - Review the lines in hba containing peer method. Although peer method might be ok to use, please check the users and the hba lines to review furthe (Failure Report)
Line Number HBA Entry
90 local {all} peer
95 local {all} peer
105 local {replication} peer
HBA Check 8 - Better to enforce ssl to secure your connections - use hostssl instead of host (after enabling ssl) (Failure Report)
Line Number HBA Entry
97 host {all} all md5
98 host {replication} all md5
99 host {all} all scram-sha-256
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
106 host {replication} 127.0.0.1 scram-sha-256
107 host {replication} ::1 scram-sha-256

Log Parser

Inactive Users

Users from DB postgres, repusr, collectadmin, testusr, testmist, ansible, chef
Users from Log collectadmin
Inactive Users in DB ansible, chef, postgres, repusr, testmist, testusr

Unique IPs

  • 127.0.0.1

Unused HBA Lines

  • Line No: 98 host replication repusr all md5
  • Line No: 99 host all collectadmin2 all scram-sha-256
  • Line No: 100 host all all 127.0.0.1/32 scram-sha-256
  • Line No: 102 host all all ::1/128 scram-sha-256
  • Line No: 106 host replication all 127.0.0.1/32 scram-sha-256
  • Line No: 107 host replication all ::1/128 scram-sha-256

Leaked Password In Logfile

No Leaked Passwords found from given log file/s.

Password Manager

Common Usernames

  • ansible
  • chef
  • postgres
  • repusr

Wraparound report

For given database cluster the percentage towards wraparound is 39 %

For given database cluster the percentage towards emergency autovaccum is 389 %


⚠️ Percent Towards Emergency Autovac is > 70%
your database cluster state is in danger.

📉 Top 5 Database Statistics

Datname Age CurrentSetting
postgres 777593807 200000000
testpersonal 777593807 200000000
template1 777593807 200000000
pagila 777593807 200000000

⏳ Transactions Per Seconds Vs Time to hit wraparound point (Reference table)

Transactions Per Second Time to exhaust 2 Billion Transactions
100 231 days, 1 Hour, 46 Minutes, 40 Seconds.
500 46 Days, 5 Hours, 40 Minutes.
1000 23 Days, 3 Hours, 20 Minutes.
5000 4 Days, 15 Hours, 40 Minutes.
10,000 2 days, 7 Hour, 20 Minutes.

⏳ Wraparound Stats

OldestRunningXactAge 7.77593807e+08
OldestPreparedXactAge 0
OldestReplicationSlotAge 0
OldestReplicaXactAge 0
OldestRunningXactLeft 1.369889841e+09
OldestPreparedXactLeft 0
OldestReplicationSlotLeft 0
OldestReplicaXactLeft 0
List of db users
Roles with Superuser attribute
Users with CREATEDB
Users with CREATEROLE
Users with NOINHERIT
Users with BYPASSRLS
Users without connection limits
Password expiry not set (Roles without password expiry)
Roles with default config set
Roles with replication set
Control Result Details
HBA Check 1 - Usage of Trust method is not secure
HBA Check 2 - Follow the least privilege method - Be specific and give the needed database(s) and not all
HBA Check 3 - Follow the least privilege method - Be specific and give the needed user(s) and not all
HBA Check 4 - Better to use scram-sha-256
HBA Check 5 - Review the lines in hba containing peer method. Although peer method might be ok to use, please check the users and the hba lines to review furthe
HBA Check 6 - Usage of Trust method is might not be secure
HBA Check 7 - Usage of password method is might not be secure
HBA Check 8 - Better to enforce ssl to secure your connections - use hostssl instead of host (after enabling ssl)
HBA Check 9 - Follow the least privilege method - Be specific and give the needed ip(s) and not all
HBA Check 2 - Follow the least privilege method - Be specific and give the needed database(s) and not all (Failure Report)
Line Number HBA Entry
90 local {all} peer
95 local {all} peer
97 host {all} all md5
99 host {all} all scram-sha-256
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
HBA Check 3 - Follow the least privilege method - Be specific and give the needed user(s) and not all (Failure Report)
Line Number HBA Entry
95 local {all} peer
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
105 local {replication} peer
106 host {replication} 127.0.0.1 scram-sha-256
107 host {replication} ::1 scram-sha-256
HBA Check 4 - Better to use scram-sha-256 (Failure Report)
Line Number HBA Entry
97 host {all} all md5
98 host {replication} all md5
HBA Check 5 - Review the lines in hba containing peer method. Although peer method might be ok to use, please check the users and the hba lines to review furthe (Failure Report)
Line Number HBA Entry
90 local {all} peer
95 local {all} peer
105 local {replication} peer
HBA Check 8 - Better to enforce ssl to secure your connections - use hostssl instead of host (after enabling ssl) (Failure Report)
Line Number HBA Entry
97 host {all} all md5
98 host {replication} all md5
99 host {all} all scram-sha-256
100 host {all} 127.0.0.1 scram-sha-256
102 host {all} ::1 scram-sha-256
106 host {replication} 127.0.0.1 scram-sha-256
107 host {replication} ::1 scram-sha-256

Inactive Users

Users from DB postgres, repusr, collectadmin, testusr, testmist, ansible, chef
Users from Log collectadmin
Inactive Users in DB ansible, chef, postgres, repusr, testmist, testusr

Unique IPs

  • 127.0.0.1

Unused HBA Lines

  • Line No: 98 host replication repusr all md5
  • Line No: 99 host all collectadmin2 all scram-sha-256
  • Line No: 100 host all all 127.0.0.1/32 scram-sha-256
  • Line No: 102 host all all ::1/128 scram-sha-256
  • Line No: 106 host replication all 127.0.0.1/32 scram-sha-256
  • Line No: 107 host replication all ::1/128 scram-sha-256

Leaked Password In Logfile

No Leaked Passwords found from given log file/s.

Common Usernames

  • ansible
  • chef
  • postgres
  • repusr

For given database cluster the percentage towards wraparound is 39 %

For given database cluster the percentage towards emergency autovaccum is 389 %


⚠️ Percent Towards Emergency Autovac is > 70%
your database cluster state is in danger.

📉 Top 5 Database Statistics

Datname Age CurrentSetting
postgres 777593807 200000000
testpersonal 777593807 200000000
template1 777593807 200000000
pagila 777593807 200000000

⏳ Transactions Per Seconds Vs Time to hit wraparound point (Reference table)

Transactions Per Second Time to exhaust 2 Billion Transactions
100 231 days, 1 Hour, 46 Minutes, 40 Seconds.
500 46 Days, 5 Hours, 40 Minutes.
1000 23 Days, 3 Hours, 20 Minutes.
5000 4 Days, 15 Hours, 40 Minutes.
10,000 2 days, 7 Hour, 20 Minutes.

⏳ Wraparound Stats

OldestRunningXactAge 7.77593807e+08
OldestPreparedXactAge 0
OldestReplicationSlotAge 0
OldestReplicaXactAge 0
OldestRunningXactLeft 1.369889841e+09
OldestPreparedXactLeft 0
OldestReplicationSlotLeft 0
OldestReplicaXactLeft 0