FreeBSD 12 — postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide

📖 ~4 min read  •  Source: FreeBSD VuXML

VuXML topic: PostgreSQL — SQL injection in pg_upgrade and pg_dump

Related CVEs: CVE-2014-8161 CVE-2015-0241 CVE-2015-0242 CVE-2015-0243 CVE-2015-0244 CVE-2015-3165 CVE-2015-3166 CVE-2015-3167  +12 more

Upstream summary: The PostgreSQL project reports: CVE-2018-16850: SQL injection in pg_upgrade and pg_dump, via CREATE TRIGGER … REFERENCING. Using a purpose-crafted trigger definition, an attacker can run arbitrary SQL statements with superuser privileges when a superuser runs pg_upgrade on the database or during a pg_dump dump/restore cycle. This attack requires a CREATE privilege on some non-temporary schema or a TRIGGER privilege on a table. This is exploitable in the default PostgreSQL c

Table of contents

1. Symptom & Impact
2. Environment & Reproduction
3. Root Cause Analysis
4. Quick Triage
5. Step-by-Step Diagnosis
6. Solution – Primary Fix
7. Solution – Alternative Approaches
8. Verification & Acceptance Criteria
9. Rollback Plan
10. Prevention & Hardening
11. Related Errors & Cross-Refs
12. References & Further Reading

Symptom & Impact

On FreeBSD 12 hosts that have postgresql93-server installed, operators see behaviour consistent with the FreeBSD VuXML entry: pkg audit flags the installed version; any daemon, CLI tool, or application linked against postgresql93-server may misbehave or fail to start after upgrade; and — for security-rated advisories — the host is exposed to the vulnerabilities above. Impact ranges from a single restart cycle to full availability incidents on jails, bhyve guests, or downstream consumers that depend on postgresql93-server.

Environment & Reproduction

Reproduction targets FreeBSD 12. Confirm release, installed package, and capture baseline state:

freebsd-version -kru
uname -a
pkg info postgresql93-server
pkg query "%n-%v" postgresql93-server
pkg audit -F
service -e

Trigger the workflow that exposes postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide while collecting:

tail -200 /var/log/messages
dmesg -a | tail -200
tail -200 /var/log/pkg.log

Root Cause Analysis

Root cause is tracked at FreeBSD VuXML. The FreeBSD ports security team shipped a corrective postgresql93-server port revision; hosts on an outdated build remain exposed. Correlate package logs with system logs and kernel state to isolate the change that triggered the failure mode:

tail -500 /var/log/pkg.log
tail -500 /var/log/messages
sysctl kern.lastpid
sysctl kern.osreldate     # numeric __FreeBSD_version, e.g. 1400097

Quick Triage

Run these checks on FreeBSD 12 to confirm the failure mode and current state of postgresql93-server:

pkg version -v postgresql93-server                # installed vs available version
pkg audit postgresql93-server                     # advisory match for this package
tail -100 /var/log/messages
dmesg -a | tail -100
kldstat                              # kernel module state (for kernel/driver pkgs)
pfctl -sr 2>/dev/null || ipfw list   # only if pf/ipfw is enabled
# If postgresql93-server ships an rc.d service (script name may differ from the pkg name,
# e.g. bind918→named, php83→php-fpm), check it:
service -e | grep -i postgresql93-server && service <rc-script-name> status

Step-by-Step Diagnosis

1. List enabled services (only relevant if the package provides one).

   service -e

2. Follow live logs.

   tail -F /var/log/messages
   dmesg

3. Validate firewall rules (skip if neither pf nor ipfw is enabled).

   pfctl -sr -v 2>/dev/null || ipfw show

4. Check package integrity for postgresql93-server.

   pkg check -B postgresql93-server
   pkg check -d postgresql93-server    # verify shared-library deps

5. Reinstall postgresql93-server if integrity check fails.

   pkg install -fy postgresql93-server

6. Correlate findings with /var/log/pkg.log and FreeBSD VuXML to pin the commit that introduced postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide.

Solution – Primary Fix

Install the corrective postgresql93-server port revision referenced by FreeBSD VuXML:

sudo pkg update
sudo pkg upgrade postgresql93-server              # or: sudo pkg upgrade -y for the whole system
# If postgresql93-server provides an rc.d service, restart it (script name may differ from pkg name):
# sudo service <rc-script-name> restart
pkg audit postgresql93-server                    # confirm no remaining advisory for this package

For ports-tree builders (FreeBSD 13.x and earlier used portsnap; on FreeBSD 14+ the ports tree is fetched with Git):

# FreeBSD 14+ (portsnap was removed):
sudo pkg install -y git-lite
sudo git clone --depth 1 https://git.FreeBSD.org/ports.git /usr/ports
# FreeBSD 13.x and earlier:
# sudo portsnap fetch update
cd /usr/ports/<category>/postgresql93-server
sudo make deinstall reinstall clean

Reboot only if the package ships a kernel module or replaces a shared library used by long-running daemons.

Solution – Alternative Approaches

If the primary fix is not viable, choose from these alternatives:

• Lock the package until the fix is vetted:

  sudo pkg lock postgresql93-server

• Downgrade to a known-good revision. pkg install pkgname-VERSION is not a real downgrade syntax — fetch a specific build instead:

  # 1. Discover available versions across configured repos:
  pkg search -e postgresql93-server
  pkg rquery -r FreeBSD-quarterly '%n-%v' postgresql93-server
  # 2. Install from a specific saved .pkg file:
  sudo pkg add -f /path/to/postgresql93-server-<older-version>.pkg
  # 3. Or switch the host repo to the quarterly branch (see snippet below) and:
  sudo pkg upgrade -fr FreeBSD-quarterly postgresql93-server

• Switch the pkg repository between quarterly and latest by editing /usr/local/etc/pkg/repos/FreeBSD.conf:

  FreeBSD: {
    url: "pkg+https://pkg.FreeBSD.org/${ABI}/quarterly",
    mirror_type: "srv",
    signature_type: "fingerprints",
    fingerprints: "/usr/share/keys/pkg",
    enabled: yes
  }

• Isolate the affected service in a jail with stricter firewall rules:

  iocage create -n postgresql93-server-jail -r 12.4-RELEASE
  iocage set allow_raw_sockets=0 postgresql93-server-jail
  # or with Bastille:
  bastille create postgresql93-server-jail 12.4-RELEASE 10.0.0.10

• Replace the service with a vendored static build for the period between exposure detection and full rollout.

Verification & Acceptance Criteria

All of these should pass after the fix:

pkg info postgresql93-server                # shows the expected fixed version
pkg audit postgresql93-server               # no advisory for this package (exit code 0)
tail -50 /var/log/messages   # no new errors after upgrade
# If postgresql93-server ships a service, confirm it is running under its rc.d name:
# service <rc-script-name> status

The original reproduction for postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide must not trigger across two consecutive runs.

Rollback Plan

Capture state before any change (only ZFS root has boot environments — UFS hosts skip bectl):

pkg query "%n-%v" > /root/pkg-pre.txt
# ZFS-on-root only:
sudo bectl create pre-postgresql93-server-patch

To revert if the upgrade is bad, reinstall the previously saved .pkg file:

sudo pkg add -f /var/cache/pkg/postgresql93-server-<previous-version>.pkg
# Or activate the pre-patch boot environment and reboot (ZFS-on-root only):
sudo bectl activate pre-postgresql93-server-patch
sudo shutdown -r now

For kernel/loader changes on a UFS host, boot the previous kernel from the loader prompt (press 3 at the menu, then boot kernel.old).

Prevention & Hardening

Prevent recurrence on FreeBSD 12 hosts running postgresql93-server:

• Enable the daily security pkg audit in /etc/periodic.conf:

  daily_status_security_pkgaudit_enable="YES"

• Subscribe to freebsd-security-notifications at lists.freebsd.org.

• Mirror through a local pkg repository managed by poudriere:

  poudriere jail -c -j 12amd64 -v 12.4-RELEASE
  poudriere ports -c -p default
  poudriere bulk -j 12amd64 -p default <category>/postgresql93-server

• Version-pin sensitive packages:

  sudo pkg lock postgresql93-server

• Take an automatic ZFS boot-environment snapshot before every upgrade (ZFS root only):

  sudo bectl create pre-upgrade-$(date +%Y%m%d)

• Monitor file integrity (create a baseline, verify against it later):

  # Create a baseline (use -c; target /usr/local/etc, /etc, /boot — NOT /):
  sudo mtree -c -K sha256digest -p /usr/local/etc > /var/db/usr-local-etc.mtree
  sudo mtree -c -K sha256digest -p /etc          > /var/db/etc.mtree
  # Verify later:
  sudo mtree -p /usr/local/etc < /var/db/usr-local-etc.mtree
  # Or with AIDE for a richer ruleset:
  sudo pkg install -y aide && sudo aide --init && sudo aide --check

• Harden jails with allow.* tunables in /etc/jail.conf:

  postgresql93-server_jail {
    allow.raw_sockets = 0;
    allow.sysvipc    = 0;
    allow.mount      = 0;
    allow.chflags    = 0;
  }

Related Errors & Cross-Refs

Issues that commonly surface alongside postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide: pkg lock contention, mismatched ABI after kernel/userland skew, pf rule drift, and stale shared-library references after upgrade. Triage with:

freebsd-version -kru
uname -K
pkg check -d
pfctl -sr

View all freebsd-12 tutorials on the Tutorials Hub →

Browse all common problems & solutions on the Tutorials Hub.

References & Further Reading

Primary reference: FreeBSD VuXML. Useful manual pages on FreeBSD 12:

man pkg
man freebsd-update
man pfctl
man ipfw
man bectl
man periodic.conf

Other resources: the FreeBSD Handbook, the FreeBSD Security Advisories at security.freebsd.org, and the /usr/ports/UPDATING file for port-specific notes implicated in postgresql93-server — multiple vulnerabilities (20 CVEs) — patch and remediation guide.