Database Technology

The listen_addresses GUC determines which TCP interfaces PostgreSQL binds to, but it does not enforce authentication or authorization. Relying solely on this setting creates a false sense of security regarding network exposure. Access control must be handled separately through pg_hba.conf or external firewall rules.

• listen_addresses only binds sockets; it does not filter connections.
• Never use this GUC as a security boundary for network access.
• Configure pg_hba.conf to enforce authentication and authorization.
• Use OS-level firewalls to restrict traffic before it reaches PostgreSQL.

A successful data load using explicit IDs can leave Postgres sequences out of sync, causing subsequent application inserts to fail with unique constraint violations. This occurs because inserting explicit primary key values does not automatically advance the associated sequence to reflect the new maximum ID. The result is a database that appears intact but rejects valid writes until the sequence is corrected.

• Explicit ID inserts do not update sequence metadata, causing future auto-generated inserts to collide.
• Verify sequence alignment after bulk loads or data migrations that bypass serial columns.
• Use setval() to manually sync sequences to the current max ID in the table.
• Check for duplicate key errors immediately after ETL jobs complete, not just at load time.

Andrei Lepikhov investigates whether shared hash tables can accelerate parallel aggregation in PostgreSQL by removing group lookups from under lock protection. The team developed a working patch set and benchmarked the approach on a Google Cloud many-core instance. The study evaluates if contention on LWLock is the primary bottleneck and assesses the performance trade-offs of this method.

• Shared hash tables move group lookups out of lock contention, potentially boosting parallel aggregation speed.
• Patch set tested on Google Cloud many-core instances to validate performance on high-core-count hardware.
• Investigation challenges the notion that LWLock contention is the sole bottleneck in parallel aggregation.
• Authors evaluate the specific performance costs and trade-offs of implementing shared hash tables.

Bertrand Drouvot has released pg_shmemviz, a new offline snapshot tool for visualizing PostgreSQL shared memory. It provides byte-level navigation and physical layout details that go beyond standard views like pg_buffercache, revealing C structure fields, padding, and pointer-referenced regions. This utility is designed for development and debugging to inspect the exact composition of main and dynamic shared memory segments.

• Captures offline snapshots of main and dynamic shared memory segments for deep inspection.
• Reveals physical layout, C structure fields, padding, and pointer-referenced raw bytes.
• Complements existing views like pg_buffercache and pg_shmem_allocations with granular detail.
• Useful for debugging memory allocation issues or understanding internal memory structures.
• Follows the same architectural approach as the existing pg_walviz tool.

Percona disclosed a high-severity security flaw in PMM versions 3.9.0 and earlier, where the Grafana data source configuration grants unauthorized privileged access to ClickHouse. This vulnerability allows attackers to bypass intended restrictions and interact directly with the underlying database engine. The advisory outlines the immediate remediation steps required to secure affected installations.

• Audit all PMM instances running version 3.9.0 or lower immediately.
• Check Grafana data source configurations for privilege escalation risks.
• Apply Percona's recommended patches or configuration changes now.
• Monitor ClickHouse logs for unauthorized access attempts during transition.