supabase-postgres.git / summary / log / commit / refs
commit 51ddf76b2cfc72ed302e2fa99b79ae3a9c8c59f9
Author: samrose <samuel@supabase.io>
Commit: GitHub <noreply@github.com>
Date: Mon Jun 23 21:22:16 2025 +0000
fix: pin version of libpq to 17.x (#1649)
* fix: pin version of libpq to the major version installed
* fix: the variable is postgresql_major_version
* fix: using the correct var
* fix: default to postgres 15 major version
* fix: default to 15
* fix: supposedly main contains all releases for libpq5
* fix: syntax
* fix: different syntax
* fix: rely on ppa
* fix: ppa removal
* fix: change to trigger workflow
* fix: adding a test to see postgres version
* fix: testing for failure related to libpq5 version issue
* test: include read-only test for postgrest
* test: reduce log access time
* test: make sure to fail if version wrong
* chore: bump vars
ansible/tasks/setup-postgrest.yml | 29 +++--
ansible/vars.yml | 6 +-
testinfra/test_ami_nix.py | 230 ++++++++++++++++++++++++++++++++++++++
3 files changed, 250 insertions(+), 15 deletions(-)
diff --git a/ansible/tasks/setup-postgrest.yml b/ansible/tasks/setup-postgrest.yml
index a98d1990..b6e0f951 100644
--- a/ansible/tasks/setup-postgrest.yml
+++ b/ansible/tasks/setup-postgrest.yml
@@ -6,22 +6,28 @@
url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
state: present
-- name: PostgREST - add Postgres PPA
+- name: PostgREST - add Postgres PPA main
apt_repository:
- repo: "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg {{ postgresql_major }}"
+ repo: "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main"
state: present
+ filename: postgresql-pgdg
-- name: PostgREST - update apt cache
+- name: PostgREST - install system dependencies
apt:
- update_cache: yes
-
-# libpq is a C library that enables user programs to communicate with
-# the PostgreSQL database server.
-- name: PostgREST - system dependencies
- apt:
- pkg:
+ package:
- libpq5
- libnuma-dev
+ update_cache: yes
+ state: present
+
+- name: PostgREST - verify libpq5 version
+ shell: dpkg -l libpq5 | grep '^ii' | awk '{print $3}'
+ register: libpq5_version
+ changed_when: false
+
+- name: Show installed libpq5 version
+ debug:
+ msg: "Installed libpq5 version: {{ libpq5_version.stdout }}"
- name: PostgREST - remove Postgres PPA gpg key
apt_key:
@@ -30,7 +36,7 @@
- name: PostgREST - remove Postgres PPA
apt_repository:
- repo: "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg {{ postgresql_major }}"
+ repo: "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main"
state: absent
- name: postgis - ensure dependencies do not get autoremoved
@@ -88,7 +94,6 @@
#! /usr/bin/env bash
set -euo pipefail
set -x
-
cd "$(dirname "$0")"
cat $@ > merged.conf
dest: /etc/postgrest/merge.sh
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 2838d6f9..f23aaf2a 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -9,9 +9,9 @@ postgres_major:
# Full version strings for each major version
postgres_release:
- postgresorioledb-17: "17.0.1.093-orioledb"
- postgres17: "17.4.1.043"
- postgres15: "15.8.1.100"
+ postgresorioledb-17: "17.0.1.094-orioledb"
+ postgres17: "17.4.1.044"
+ postgres15: "15.8.1.101"
# Non Postgres Extensions
pgbouncer_release: "1.19.0"
diff --git a/testinfra/test_ami_nix.py b/testinfra/test_ami_nix.py
index 1975818d..864ab286 100644
--- a/testinfra/test_ami_nix.py
+++ b/testinfra/test_ami_nix.py
@@ -507,3 +507,233 @@ def test_postgrest_ending_empty_key_query_parameter_is_removed(host):
},
)
assert res.ok
+
+
+def test_postgresql_version(host):
+ """Print the PostgreSQL version being tested and ensure it's >= 14."""
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql -c 'SELECT version();'")
+ if result['succeeded']:
+ print(f"\nPostgreSQL Version:\n{result['stdout']}")
+ # Extract version number from the output
+ version_line = result['stdout'].strip().split('\n')[2] # Skip header and get the actual version
+ # Extract major version number (e.g., "15.8" -> 15)
+ import re
+ version_match = re.search(r'PostgreSQL (\d+)\.', version_line)
+ if version_match:
+ major_version = int(version_match.group(1))
+ print(f"PostgreSQL major version: {major_version}")
+ assert major_version >= 14, f"PostgreSQL version {major_version} is less than 14"
+ else:
+ assert False, "Could not parse PostgreSQL version number"
+ else:
+ print(f"\nFailed to get PostgreSQL version: {result['stderr']}")
+ assert False, "Failed to get PostgreSQL version"
+
+ # Also get the version from the command line
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql --version")
+ if result['succeeded']:
+ print(f"PostgreSQL Client Version: {result['stdout'].strip()}")
+ else:
+ print(f"Failed to get PostgreSQL client version: {result['stderr']}")
+
+ print("✓ PostgreSQL version is >= 14")
+
+
+def test_libpq5_version(host):
+ """Print the libpq5 version installed and ensure it's >= 14."""
+ # Try different package managers to find libpq5
+ result = run_ssh_command(host['ssh'], "dpkg -l | grep libpq5 || true")
+ if result['succeeded'] and result['stdout'].strip():
+ print(f"\nlibpq5 package info:\n{result['stdout']}")
+ # Extract version from dpkg output (format: ii libpq5:arm64 17.5-1.pgdg20.04+1)
+ import re
+ version_match = re.search(r'libpq5[^ ]* +(\d+)\.', result['stdout'])
+ if version_match:
+ major_version = int(version_match.group(1))
+ print(f"libpq5 major version: {major_version}")
+ assert major_version >= 14, f"libpq5 version {major_version} is less than 14"
+ else:
+ print("Could not parse libpq5 version from dpkg output")
+ else:
+ print("\nlibpq5 not found via dpkg")
+
+ # Also try to find libpq.so files
+ result = run_ssh_command(host['ssh'], "find /usr -name '*libpq*' -type f 2>/dev/null | head -10")
+ if result['succeeded'] and result['stdout'].strip():
+ print(f"\nlibpq files found:\n{result['stdout']}")
+ else:
+ print("\nNo libpq files found")
+
+ # Check if we can get version from a libpq file
+ result = run_ssh_command(host['ssh'], "ldd /usr/bin/psql | grep libpq || true")
+ if result['succeeded'] and result['stdout'].strip():
+ print(f"\npsql libpq dependency:\n{result['stdout']}")
+ else:
+ print("\nCould not find libpq dependency for psql")
+
+ # Try to get version from libpq directly
+ result = run_ssh_command(host['ssh'], "psql --version 2>&1 | head -1")
+ if result['succeeded'] and result['stdout'].strip():
+ print(f"\npsql version output: {result['stdout'].strip()}")
+ # The psql version should match the libpq version
+ import re
+ version_match = re.search(r'psql \(PostgreSQL\) (\d+)\.', result['stdout'])
+ if version_match:
+ major_version = int(version_match.group(1))
+ print(f"psql/libpq major version: {major_version}")
+ assert major_version >= 14, f"psql/libpq version {major_version} is less than 14"
+ else:
+ print("Could not parse psql version")
+
+ print("✓ libpq5 version is >= 14")
+
+
+def test_postgrest_read_only_session_attrs(host):
+ """Test PostgREST with target_session_attrs=read-only and check for session errors."""
+ # First, check if PostgreSQL is configured for read-only mode
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql -c \"SHOW default_transaction_read_only;\"")
+ if result['succeeded']:
+ default_read_only = result['stdout'].strip()
+ print(f"PostgreSQL default_transaction_read_only: {default_read_only}")
+ else:
+ print("Could not check PostgreSQL read-only setting")
+ default_read_only = "unknown"
+
+ # Check if PostgreSQL is in recovery mode (standby)
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql -c \"SELECT pg_is_in_recovery();\"")
+ if result['succeeded']:
+ in_recovery = result['stdout'].strip()
+ print(f"PostgreSQL pg_is_in_recovery: {in_recovery}")
+ else:
+ print("Could not check PostgreSQL recovery status")
+ in_recovery = "unknown"
+
+ # Find PostgreSQL configuration file
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql -c \"SHOW config_file;\"")
+ if result['succeeded']:
+ config_file = result['stdout'].strip().split('\n')[2].strip() # Skip header and get the actual path
+ print(f"PostgreSQL config file: {config_file}")
+ else:
+ print("Could not find PostgreSQL config file")
+ config_file = "/etc/postgresql/15/main/postgresql.conf" # Default fallback
+
+ # Backup PostgreSQL config
+ result = run_ssh_command(host['ssh'], f"sudo cp {config_file} {config_file}.backup")
+ assert result['succeeded'], "Failed to backup PostgreSQL config"
+
+ # Add read-only setting to PostgreSQL config
+ result = run_ssh_command(host['ssh'], f"echo 'default_transaction_read_only = on' | sudo tee -a {config_file}")
+ assert result['succeeded'], "Failed to add read-only setting to PostgreSQL config"
+
+ # Restart PostgreSQL to apply the new configuration
+ result = run_ssh_command(host['ssh'], "sudo systemctl restart postgresql")
+ assert result['succeeded'], "Failed to restart PostgreSQL"
+
+ # Wait for PostgreSQL to start up
+ sleep(5)
+
+ # Verify the change took effect
+ result = run_ssh_command(host['ssh'], "sudo -u postgres psql -c \"SHOW default_transaction_read_only;\"")
+ if result['succeeded']:
+ new_default_read_only = result['stdout'].strip()
+ print(f"PostgreSQL default_transaction_read_only after change: {new_default_read_only}")
+ else:
+ print("Could not verify PostgreSQL read-only setting change")
+
+ # First, backup the current PostgREST config
+ result = run_ssh_command(host['ssh'], "sudo cp /etc/postgrest/base.conf /etc/postgrest/base.conf.backup")
+ assert result['succeeded'], "Failed to backup PostgREST config"
+
+ try:
+ # Read the current config to get the db-uri
+ result = run_ssh_command(host['ssh'], "sudo cat /etc/postgrest/base.conf | grep '^db-uri'")
+ assert result['succeeded'], "Failed to read current db-uri"
+
+ current_db_uri = result['stdout'].strip()
+ print(f"Current db-uri: {current_db_uri}")
+
+ # Extract just the URI part (remove the db-uri = " prefix and trailing quote)
+ uri_start = current_db_uri.find('"') + 1
+ uri_end = current_db_uri.rfind('"')
+ base_uri = current_db_uri[uri_start:uri_end]
+
+ # Modify the URI to add target_session_attrs=read-only
+ if '?' in base_uri:
+ # URI already has parameters, add target_session_attrs
+ modified_uri = base_uri + "&target_session_attrs=read-only"
+ else:
+ # URI has no parameters, add target_session_attrs
+ modified_uri = base_uri + "?target_session_attrs=read-only"
+
+ print(f"Modified URI: {modified_uri}")
+
+ # Use awk to replace the db-uri line more reliably
+ result = run_ssh_command(host['ssh'], f"sudo awk '{{if ($1 == \"db-uri\") print \"db-uri = \\\"{modified_uri}\\\"\"; else print $0}}' /etc/postgrest/base.conf > /tmp/new_base.conf && sudo mv /tmp/new_base.conf /etc/postgrest/base.conf")
+ assert result['succeeded'], "Failed to update db-uri in config"
+
+ # Verify the change was made correctly
+ result = run_ssh_command(host['ssh'], "sudo cat /etc/postgrest/base.conf | grep '^db-uri'")
+ print(f"Updated db-uri line: {result['stdout'].strip()}")
+
+ # Also show the full config to debug
+ result = run_ssh_command(host['ssh'], "sudo cat /etc/postgrest/base.conf")
+ print(f"Full config after change:\n{result['stdout']}")
+
+ # Restart PostgREST to apply the new configuration
+ result = run_ssh_command(host['ssh'], "sudo systemctl restart postgrest")
+ assert result['succeeded'], "Failed to restart PostgREST"
+
+ # Wait a moment for PostgREST to start up
+ sleep(5)
+
+ # Check if PostgREST is running
+ result = run_ssh_command(host['ssh'], "sudo systemctl is-active postgrest")
+ if not (result['succeeded'] and result['stdout'].strip() == 'active'):
+ # If PostgREST failed to start, check the logs to see why
+ log_result = run_ssh_command(host['ssh'], "sudo journalctl -u postgrest --since '5 seconds ago' --no-pager")
+ print(f"PostgREST failed to start. Recent logs:\n{log_result['stdout']}")
+ assert False, "PostgREST failed to start after config change"
+
+ # Make a test request to trigger any potential session errors
+ try:
+ response = requests.get(
+ f"http://{host['ip']}/rest/v1/",
+ headers={"apikey": anon_key, "authorization": f"Bearer {anon_key}"},
+ timeout=10
+ )
+ print(f"Test request status: {response.status_code}")
+ except Exception as e:
+ print(f"Test request failed: {str(e)}")
+
+ # Check PostgREST logs for "session is not read-only" errors
+ result = run_ssh_command(host['ssh'], "sudo journalctl -u postgrest --since '5 seconds ago' | grep -i 'session is not read-only' || true")
+
+ if result['stdout'].strip():
+ print(f"\nFound 'session is not read-only' errors in PostgREST logs:\n{result['stdout']}")
+ assert False, "PostgREST logs contain 'session is not read-only' errors even though PostgreSQL is configured for read-only mode"
+ else:
+ print("\nNo 'session is not read-only' errors found in PostgREST logs")
+
+ finally:
+ # Restore the original configuration
+ result = run_ssh_command(host['ssh'], "sudo cp /etc/postgrest/base.conf.backup /etc/postgrest/base.conf")
+ if result['succeeded']:
+ result = run_ssh_command(host['ssh'], "sudo systemctl restart postgrest")
+ if result['succeeded']:
+ print("Restored original PostgREST configuration")
+ else:
+ print("Warning: Failed to restart PostgREST after restoring config")
+ else:
+ print("Warning: Failed to restore original PostgREST configuration")
+
+ # Restore PostgreSQL to original configuration
+ result = run_ssh_command(host['ssh'], f"sudo cp {config_file}.backup {config_file}")
+ if result['succeeded']:
+ result = run_ssh_command(host['ssh'], "sudo systemctl restart postgresql")
+ if result['succeeded']:
+ print("Restored PostgreSQL to original configuration")
+ else:
+ print("Warning: Failed to restart PostgreSQL after restoring config")
+ else:
+ print("Warning: Failed to restore PostgreSQL configuration")
+
[parent: 7d78f1453b3b]