Enhancing PostgreSQL Management and Observability in Cloud Environments - Mailing list pgsql-hackers

Dear PostgreSQL Community,
I hope this message finds you well. I am writing to propose a set of features aimed at significantly enhancing PostgreSQL’s management and observability in cloud environments, particularly for administrators who do not have direct access to the underlying operating system of database servers. These suggestions focus on improving security auditing, backup tracking, system metrics visibility, and configuration management—all through SQL-level commands.
1. Dynamic pg_hba.conf Management
Proposal:
Introduce SQL-based commands to dynamically manage pg_hba.conf entries, removing the need for manual file edits. For example:
ALTER SYSTEM ADD PG_HBA (
    type => 'host',
    database => 'mydb',
    user => 'myuser',
    address => '192.168.1.0/24',
    method => 'md5',
    comment => 'This connection from app_prod'
);
Benefits:
•  Enables safe, cloud-based management of the pg_hba.conf file
•  Supports dynamic configuration without requiring direct OS access
•  Ideal for DBaaS environments where administrators may not have OS-level privileges

2. Login Monitoring 
Proposal:
Tracks login attempts and offers key insights into authentication status. Key fields might include:
•  user: Database user attempting to login
•  db_name: Database name
•  client_address: IP address of the client
•  client_application: The application from which the connection originated
•  last_attempt_time: Timestamp of the last login attempt
•  last_attempt_status: Outcome of the last login attempt (e.g., success, failed)
•  connection_status_remarks: Detailed remarks on the connection (e.g., no match in pg_hba.conf, SSL-related errors, etc.)
Benefits:
•  Facilitates detailed security auditing of login attempts
•  Helps troubleshoot authentication problems
•  Provides insights into connection patterns and potential issues
•  Enhances monitoring and security in DBaaS platforms

3. Backup Tracking View (pg_stat_backups)
Proposal:
Track backup details, including the backup type, status, and user initiating the backup. Proposed fields could include:
•  backup_type: Type of backup (e.g., pg_adump, pg_dumpall, pg-basebackup, physical, logical)
•  backup_status: Current backup status (e.g., Running, Completed, Failed)
•  backup_details: Error messages or backup duration
•  command: The command used to initiate the backup
•  user: The user who initiated the backup
•  client_address: Client IP
•  client_application: Client application used to perform the backup
•  backup_start_time, backup_end_time: Timestamps for the backup lifecycle
Benefits:
•  Full visibility into backup processes and their status
•  Tracks who performed the backup and from which application
•  Offers a detailed audit trail and error reporting for troubleshooting backup failures
•  Enables proactive backup monitoring, especially in managed environments

4. System Metadata
Proposal:
Introduce a system metadata view that exposes key system performance data (e.g., CPU usage, memory, disk space) and OS details, particularly useful for cloud-based PostgreSQL instances. A query might look like:
Suggested Columns:
•  hostname: Hostname of the server
•  server_ip: IP address of the server
•  os_version: Operating system version
•  cpu_model: CPU model and architecture
•  cpu_cores: Number of CPU cores
•  RAM: Total available memory
•  os_uptime: OS uptime since the last reboot
Benefits:
•  Provides critical system performance data without requiring OS-level access
•  Useful for DBaaS environments where direct server access is not available
•  Simplifies remote system monitoring and diagnostics

5. Log and WAL Directory Path Exposure
Proposal:
Enhance PostgreSQL to expose the full paths of log and WAL directories via SQL commands (e.g., data_directory_path). This would improve transparency and troubleshooting in cloud environments where file system access is typically restricted.
Benefits:
•  Transparency: Easy access to the actual file paths of log and WAL directories
•  Troubleshooting: Facilitates log management and helps pinpoint issues with file access
•  Ideal for cloud environments where filesystem access is limited

6. Parameter Change Tracking
Proposal:
Introduce a mechanism to track changes to PostgreSQL configuration parameters, logging who made the changes, the previous values, and the timestamp of the last change. This could look like:
Suggested Fields:
•  previous_value: The value of the parameter before the change
•  changed_by: The user who made the change
•  change_time: Timestamp of the change
Benefits:
•  Provides a detailed audit trail of configuration changes
•  Helps with troubleshooting issues caused by parameter modifications
•  Enhances accountability in cloud-managed environments

Conclusion
These proposed features aim to improve PostgreSQL’s functionality in cloud environments, where administrators typically lack direct OS access to the underlying systems. By adding SQL-based management features for configuration, login monitoring, backup tracking, and system performance visibility, we can significantly enhance the security, transparency, and usability of PostgreSQL in DBaaS and cloud infrastructure.
I believe these changes would provide significant value to the PostgreSQL community, particularly for users operating in environments with limited OS-level access, such as cloud-hosted PostgreSQL instances.
I look forward to hearing your thoughts and feedback. Thank you for considering these proposals.

Best regards,
Sreekanta