Connect to a local PostgreSQL server over its Unix socket #3619

Problem

MySQL and MariaDB connections can pick Connect Using > Socket and give the socket file's path (databases/mysql.mdx:23-49, connections/connection-form.mdx:28-31), and their URLs take a socket parameter (connections/urls.mdx:118-133). PostgreSQL has neither: the picker is MySQL and MariaDB only, and other engines ignore socket in a URL.

The only socket support for PostgreSQL is through SSH: Socket Path under SSH Tunnel forwards to a socket on the remote server (connections/ssh-tunneling.mdx:108-122, from #1871). A server on this Mac that listens only on its socket, with listen_addresses = '', or a pg_hba.conf that allows peer or trust on local lines only, cannot be reached from the form.

The Host value goes to libpq as typed (apple/Plugins/PostgreSQLDriverPlugin/LibPQConnectionString.swift:101), and libpq reads a host that starts with / as a socket directory. So typing /tmp into Host may reach the server today, but nothing documents or tests that, and the rest of the app treats Host as a hostname: Copy Connection String, ~/.pgpass matching, Backup and SSL.

Proposed solution

• Connect Using on PostgreSQL, with Socket taking the socket directory or the full .s.PGSQL.<port> file path, and suggesting the common places: /tmp (Homebrew and Postgres.app) and /var/run/postgresql.
• With Socket, Host leaves the form, Network offers only Direct, and SSL is off, the way the MySQL socket works.
• The Port still picks the socket file, since PostgreSQL names it after the port.
• postgresql:// URLs accept host=/tmp in the query string and a percent-encoded path as the host, as libpq does, and Copy Connection String writes that form.
• ~/.pgpass matches a socket connection by localhost, as libpq does, and Backup and Restore pass the directory to pg_dump and pg_restore with -h.
• CockroachDB, Redshift and PGlite keep Host and Port.

Done when: a PostgreSQL server started with listen_addresses = '' connects through Socket, peer authentication signs in as the macOS user, and Copy Connection String, ~/.pgpass and Backup all work on that connection.

Alternatives considered

Turn on TCP in postgresql.conf and connect to 127.0.0.1, or type the socket directory into Host and hope the rest of the app copes.

Platform

macOS

Related database type

PostgreSQL