Worker fails to start when Rails is configured to use a TLS protected DB

[jlledom]

If I understood the code correctly, the que worker is able to get the DB connection from ActiveRecord. However, when ActiveRecord is configured to connect to a TLS protected database, que fails to start. The problem is in this piece of code from the class Locker

que/lib/que/locker.rb

Lines 163 to 175 in 17fb2c3

	else
	Que.pool.checkout do |conn|
	c = conn.wrapped_connection
	{
	host: c.host,
	user: c.user,
	password: c.pass,
	port: c.port,
	dbname: c.db,
	}
	end
	end

It only extracts the basic parameters like host, user, pass, dbname... but it ignores SSL parameters like sslmode, sslcert, etc. If you want the worker to connect to a TLS protected DB, the only way is to provide ssl parameter through the connection_url command line argument.

I hit this bug myself on my project: 3scale/zync#573.

The AI wrote a script to reproduce the bug:

#!/usr/bin/env ruby
# frozen_string_literal: true

# Reproduction script for Que TLS PostgreSQL connection bug
# This script demonstrates that Que's locker cannot connect to a TLS-protected PostgreSQL database

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  gem 'rails', '~> 7.1.0'
  gem 'pg'
  gem 'que', '~> 2.3.0'
end

require 'active_record'
require 'que'

def main
  puts "=== Que TLS PostgreSQL Connection Bug Reproduction ==="
  puts

  # Configure ActiveRecord to connect to a TLS-protected PostgreSQL database
  # Adjust these values to match your TLS PostgreSQL setup
  database_url = ENV.fetch('DATABASE_URL') do
    puts "ERROR: DATABASE_URL environment variable not set"
    puts "Example: DATABASE_URL='postgresql://user:pass@localhost:5432/dbname?sslmode=require'"
    exit 1
  end

  puts "Database URL: #{database_url.gsub(/:[^:@]+@/, ':***@')}"
  puts

  # Parse connection string to show SSL mode
  uri = URI.parse(database_url)
  ssl_params = URI.decode_www_form(uri.query || '').to_h
  puts "SSL Mode: #{ssl_params['sslmode'] || 'not specified'}"
  puts

  # Establish ActiveRecord connection
  puts "Connecting to database via ActiveRecord..."
  ActiveRecord::Base.establish_connection(database_url)

  # Test the connection
  begin
    ActiveRecord::Base.connection.execute("SELECT 1")
    puts "✓ ActiveRecord connection successful"
  rescue => e
    puts "✗ ActiveRecord connection failed: #{e.message}"
    exit 1
  end
  puts

  # Configure Que with the same connection
  puts "Configuring Que..."
  Que.connection = ActiveRecord

  # Try to use Que's locker
  puts "Starting Que locker..."
  puts "This will fail because the locker tries to establish its own connection"
  puts "without passing the SSL parameters from the connection URL"
  puts

  begin
    # The locker internally creates a new connection and fails with TLS
    locker = Que::Locker.new

    puts "✗ Unexpected: Locker started successfully (this shouldn't happen with TLS)"
    locker.stop!
  rescue => e
    puts "✗ Locker failed as expected!"
    puts "Error: #{e.class}: #{e.message}"
    puts
    puts "Backtrace:"
    puts e.backtrace.first(10)
  end

  puts
  puts "=== Bug Demonstration Complete ==="
  puts
  puts "The issue is that Que's locker creates its own connection without"
  puts "respecting the SSL parameters from the DATABASE_URL connection string."
  puts "This causes failures when connecting to TLS-protected PostgreSQL databases."
end

main

You can run it like this:

DATABASE_URL='postgresql://secuser@localhost:5432/que_test?sslmode=require&sslcert=tmp/postgres-certs/client.crt&sslkey=tmp/postgres-certs/client.key&sslrootcert=tmp/postgres-certs/server.crt' ruby reproduce_tls_bug.rb

I tried it and it reproduced the bug in my local machine.

If you don't have a TLS protected postgres instance, the AI also wrote a script to launch one in a container (I haven't tested this one):

#!/bin/bash
# Setup script for TLS PostgreSQL with certificate authentication
# This script sets up a PostgreSQL container that requires client certificate auth for 'secuser'

set -e

echo "=== Setting up TLS PostgreSQL with Certificate Authentication ==="
echo

# Cleanup any previous setup
echo "Cleaning up previous setup..."
docker stop postgres-tls 2>/dev/null || true
docker rm postgres-tls 2>/dev/null || true
rm -rf tmp/postgres-certs
echo "✓ Cleanup complete"
echo

# Create directory for certificates and configuration
echo "Creating certificates directory..."
mkdir -p tmp/postgres-certs
cd tmp/postgres-certs

# Generate server certificates
echo "Generating server certificates..."
openssl req -new -x509 -days 365 -nodes -text \
  -out server.crt \
  -keyout server.key \
  -subj "/CN=localhost" 2>/dev/null

chmod 600 server.key
chmod 644 server.crt
echo "✓ Server certificates created"

# Generate client certificate for 'secuser'
echo "Generating client certificate for 'secuser'..."
openssl req -new -nodes -text \
  -out client.csr \
  -keyout client.key \
  -subj "/CN=secuser" 2>/dev/null

openssl x509 -req -in client.csr -text -days 365 \
  -CA server.crt \
  -CAkey server.key \
  -CAcreateserial \
  -out client.crt 2>/dev/null

chmod 600 client.key
chmod 644 client.crt
echo "✓ Client certificates created"

# Create pg_hba.conf that requires SSL
echo "Creating pg_hba.conf..."
cat > pg_hba.conf << 'EOF'
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Require client certificate authentication for secuser
hostssl all             secuser         all                     cert
# Require SSL for all other users (password auth)
hostssl all             all             all                     scram-sha-256
# Reject non-SSL connections explicitly
hostnossl all           all             all                     reject
EOF
echo "✓ pg_hba.conf created"

cd ../..

# Run PostgreSQL with TLS required
echo "Starting PostgreSQL container with TLS..."
docker run -d \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=que_test \
  -v $(pwd)/tmp/postgres-certs/server.crt:/var/lib/postgresql/server.crt:ro \
  -v $(pwd)/tmp/postgres-certs/server.key:/var/lib/postgresql/server.key:ro \
  -v $(pwd)/tmp/postgres-certs/pg_hba.conf:/var/lib/postgresql/pg_hba.conf:ro \
  --name postgres-tls \
  postgres:15 \
  -c ssl=on \
  -c ssl_cert_file=/var/lib/postgresql/server.crt \
  -c ssl_key_file=/var/lib/postgresql/server.key \
  -c hba_file=/var/lib/postgresql/pg_hba.conf

echo "✓ PostgreSQL container started"
echo

# Wait for PostgreSQL to be ready
echo "Waiting for PostgreSQL to be ready..."
sleep 3
for i in {1..30}; do
  if docker exec postgres-tls pg_isready -U postgres -d que_test >/dev/null 2>&1; then
    echo "✓ PostgreSQL is ready"
    break
  fi
  if [ $i -eq 30 ]; then
    echo "✗ PostgreSQL failed to start"
    exit 1
  fi
  sleep 1
done
echo

# Create the secuser
echo "Creating 'secuser' in PostgreSQL..."
docker exec postgres-tls psql -U postgres -d que_test -c "CREATE USER secuser;" 2>/dev/null || true
echo "✓ User 'secuser' created"
echo

# Display connection information
echo "=== Setup Complete ==="
echo
echo "PostgreSQL is running with TLS certificate authentication enabled."
echo
echo "Connection details:"
echo "  Container: postgres-tls"
echo "  Port: 5432"
echo "  Database: que_test"
echo
echo "Authentication options:"
echo
echo "1. Password authentication (postgres user):"
echo "   export DATABASE_URL='postgresql://postgres:postgres@localhost:5432/que_test?sslmode=require'"
echo
echo "2. Certificate authentication (secuser):"
echo "   export DATABASE_URL='postgresql://secuser@localhost:5432/que_test?sslmode=require&sslcert=tmp/postgres-certs/client.crt&sslkey=tmp/postgres-certs/client.key&sslrootcert=tmp/postgres-certs/server.crt'"
echo
echo "To test the connection:"
echo "   psql \"\$DATABASE_URL\" -c 'SELECT version();'"
echo
echo "To run the bug reproduction script:"
echo "   ./tmp/reproduce_tls_bug.rb"
echo
echo "To stop and cleanup:"
echo "   ./tmp/cleanup_tls_postgres.sh"
echo