service connection string and environment variables interact differently in ruby-pg than psql

[jackc]

I have a pg_service.conf file with an entry like this:

[my-service]
host=example.com
port=25060
dbname=mydb
user=myuser
sslmode=require

I have an environment variable set:

export PGPORT=5020

psql successfully connects using the service file as it uses the port from the service:

$ psql 'service=my-service'
# succeeded

However, if I try to connect with ruby-pg it fails as the port is taken from the environment instead:

$ ruby -rpg -e 'conn = PG.connect("service=my-service")'
/Users/jack/.local/share/mise/installs/ruby/3.3.7/lib/ruby/gems/3.3.0/gems/pg-1.5.9/lib/pg/connection.rb:709:in `async_connect_or_reset': connection to server at "example.com" (xxxx.xxx.xxx.xxx), port 5020 failed: Operation timed out (PG::ConnectionBad)
	Is the server running on that host and accepting TCP/IP connections?

But it succeeds if the service is passed via PGSERVICE:

$ PGSERVICE=my-service ruby -rpg -e 'conn = PG.connect'
# succeeded

[jackc]

After further investigation, it appears that using a service parameter in a connection string has multiple issues.

The problem exists even without an explicitly set PGPORT. If that is not set then the default port of 5432 is used even if the service definition includes a port. It appears the default port is set here:

ruby-pg/lib/pg/connection.rb

Line 858 in 1f0db7b

iopts = PG::Connection.conndefaults.each_with_object({}){|h, o| o[h[:keyword].to_sym] = h[:val] if h[:val] }.merge(iopts)

If I add iopts.delete(:port) immediately after then the connection gets further.

But it then fails with fe_sendauth: no password supplied (PG::ConnectionBad). The password is in .pgpass.

And again, it works if the PGSERVICE environment variable is used instead of specifying the service in the connection string.

It also works if the lower-level PG::Connection.connect_start is called instead of PG.connect.

ruby -rpg -e 'conn = PG::Connection.connect_start("service=my-service")'
# succeeded

---

Okay. So now it seems the problem is clearer. The PG.connect path parses the connection string into a hash and then adds default values to that hash. However, those defaults do not take into account a service definition. Then the hash version of connect_start is called with argument(s) that override what is specified in the service definition.