Re: Enhancing PostgreSQL Management and Observability in Cloud Environments

On Fri, 2025-06-06 at 09:08 +0530, sreekanta reddy 1996 wrote:
> 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.

In a cloud environment, you don't get a superuser, so in looking at the following
we have to keep that in mind.

>  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

If you want to allow that to a non-superuser, you'd have to invent a new role whose
members are allowed to do that.

But looking at the example of "postgresql.conf", it might also make sense to invent
something like ALTER SYSTEM for superusers to ease maintenance. With ALTER SYSTEM
it was decided to invent a second configuration file (postgresql.auto.conf), but I
fail to see how that could work with "pg_hba.conf", aince order matters a lot in that file.

So the command would have to edit the existing "pg_hba.conf".
That would be tricky if the file contains any relevant comments.
Also, you'd have to think of a way to specify where exactly the new line is to be added.

All in all, I consider this difficult to implement in a useful fashion.

> 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

There is already "log_connections".
Doesn't that do all of the above, if you set an appropriate "log_line_prefix"?

>  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

I wouldn't mix pg_dump and file system backup.
People run pg_dump for all kings of reasons other than backup, so tracking that would
be confusing.

Backups are already tracked in the *.backup files in the archive, but I guess you
want something accessible from SQL.

That might be feasible, but I see the problem of backups expiring:
At some point, you want to get rid of old backups, and that happens outside the database,
so your tracking view would get out of sync with reality. And then it would be much less
useful.

> 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

That means that you would have to write code for all supported platforms to provide that
information. Since there are many supported platforms, that would be difficult.
Even within Linux alone, you'd have to consider that the kernel could be configured
in different ways.

You'd also have to consider container environments.
What if PostgreSQL is running in a control group that restricts the CPUs or the memory
it can use? Your code would have to understand about that if the results are to me
meaningful.

I consider that very difficult to get right.

> 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

There is already pg_current_logfile(), the parameter "data_directory" exposes the data
directory, and for all that PostgreSQL knows, "pg_wal" is inside the data directory.

This information is currently restricted to privileged users, and I expect that cloud
providers want to keep it that way.

> 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

That would always be incomplete, since you can manually edit "postgresql.conf".
And audit information that is incomplete tends to be useless.

Most cloud providers provide special interfaces to change parameters. These could
easily track these changes, so I see little need to try and do that in core.

Yours,
Laurenz Albe