Version

Retrieve the PostgreSQL version using the command:
psql -V

List of Databases
Get a list of databases using:
psql -l

Connecting to the “postgres” Database
Connect to the “postgres” database with:
psql -d postgres

Gathering Information

Execute the following commands within the PostgreSQL console (after connecting with psql) to gather system information:

1
2
3
4
5
6

select * from pg_user; 
select * from pg_roles; 
SHOW ALL; 
SELECT * FROM pg_settings; 
SELECT name,setting,unit,category,context,vartype,source,min_val,max_val,enumvals,boot_val,reset_val,sourcefile,sourceline,pending_restart FROM pg_settings; 
SHOW config_file;  

These commands provide details about users, roles, settings, and the configuration files, namely the main PostgreSQL configuration file (postgresql.conf) and the client authentication configuration file (pg_hba.conf).

A. Client Authentication

• Disable Unsecured Access: Instead of relying on trust connections, enforce robust authentication methods like MD5 or Kerberos. Trust-based authentication over TCP/IP should be avoided unless every user on every connecting machine is implicitly trusted. It’s generally only acceptable for connections from the local machine (127.0.0.1).
• Auditing Trust Configurations: When auditing, verify that there are no instances of the “trust” method defined in your pg_hba.conf file.
• Restrict IP Ranges: Configure your server to only accept connections from authorized IP addresses.
• Enforce SSL Connections:
  • Enable SSL at the server level by setting “ssl” to “on” in your postgresql.conf file.
  • Use “hostssl” entries in pg_hba.conf to enforce SSL for specific connections.
  • Ensure that SSL certificate files (ssl_ca_file and ssl_cert_file) are properly configured in your PostgreSQL settings. You can check these using pg_settings.

B. User Roles and Access Privileges

• Limit Powerful Roles: Restrict roles with the attributes “Superuser”, “Create role”, and “Create DB” to database administrators (DBAs) only.
• Auditing Role Privileges: Regularly review role attributes. You can list roles and their attributes within the PostgreSQL console using:

  1	postgres-> \du

• Implement Role Expiration: Set expiration dates for roles using the VALID UNTIL clause.
• Auditing Expiration Dates: To check if expiration dates are set, examine the “rolvaliduntil” column in the pg_roles table by running:

  1	postgres=> select * from pg_roles;

C. Connection and Login Restrictions

• Password Security:
  • Enable password encryption by setting password_encryption to “on” in your configuration.
  • Verify password storage by querying the pg_shadow table:

    1	SELECT * FROM pg_shadow;

  • Check for the availability of the ‘pgcrypto’ extension to ensure strong encryption capabilities:

    1	SELECT * FROM pg_available_extensions where name='pgcrypto';

• Session Management: Within your postgresql.conf file, configure the following settings to manage session timeouts and keepalives:
  • statement_timeout = 10000 (in milliseconds)
  • tcp_keepalives_idle = 10 (in seconds)
  • tcp_keepalives_interval = 10 (in seconds)
  • tcp_keepalives_count = 10

D. Logging

• Enable and Configure Logging: In your postgresql.conf file:
  • Set log_statement to an appropriate level other than “none.”
  • Set log_file_mode to 0600 for secure log file permissions.
  • Define the log output directory using log_destination.
  • Enable connection logging with log_connections = on.
  • Customize the log line prefix if needed using log_line_prefix.
• Additional Logging Settings: Use pg_settings to confirm the following settings are enabled:
  • log_connections should be set to on.
  • log_disconnections should be set to on.
• Check for Auditing Extensions: List shared libraries using SHOW shared_preload_libraries to see if auditing extensions like pgaudit are installed and active.

E. Unnecessary Database Components

• Remove Multiple PostgreSQL Versions: Use the following command to find and remove any redundant PostgreSQL installations:

  1	rpm -qa | grep postgres

• Manage Extensions: Review and uninstall any unnecessary extensions:
  • List non-core extensions:

    1	postgres=> select * from pg_extension where extname != 'plpgsql';

  • Show installed extensions:

    1	postgres=> SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;

• Review File System Permissions: Regularly inspect and properly set permissions for database files, log files, and backup files using commands like:

  1

  ls -lR

References

1. PostgreSQL - Internals - System Catalogs *
2. OWASP Backend Security Project PostgreSQL Hardening *
3. Tuning Your PostgreSQL Server *
4. DISA STIG: PostgreSQL STIG *