Add support for SOCKS proxies

[jakob]

A customer requested that we add a way for Postico to connect to PostgreSQL servers via a SOCKS proxy.

It should be doable, we can probably do it in a way similar to SSH tunnels.

I'm not sure how big demand for this feature is. Does anybody else need this?

[GalaSlE]

I have got a similar request.My pgsql server hide behind a jump host for secure reason. Is it possible to add "ProxyCommand" to ssh tunnel when connecting?

[jakob]

@xianyi0119 That's already tracked in a separate issue: #532

[a7ul]

We would love to have this feature (socks proxy) ^ @jakob We are right now manually running a proxy locally and then connecting postico via it. This would greatly help in removing the workaround.

[jakob]

Thanks for the feedback. Since this is a feature that I don't really use myself, I depend on user feedback.

I'm curious how these proxies are typically configured. Do you configure the proxy server in System Preferences, or would you prefer to configure the proxy server for each connection in Postico?

How do you authenticate with the proxy server? Username/password? Or something like Kerberos?

[olivierlacan]

@jakob Speaking only for myself, at least being able to use a SOCKS proxy set up in Network > Advanced > Proxies would be a good start.

[jakob]

@olivierlacan So Postico should then use the proxy settings for all network connections?

Does your proxy server require any authentication?

[olivierlacan]

@jakob Mine doesn't since it's just a local network proxy, and even if it did I'd expect authentication to be done at the system level.

[jakob]

I've read a bit about SOCKS proxies, and it seems that a lot of people are actually using SOCKS proxies running on their local machine, just like @olivierlacan described.

The following is mostly a note to myself so I don't forget what I researched:

macOS has built-in support for SOCKS proxies, using NSStream or CFStream, which is pretty nice! It means we wouldn't need to embed any 3rd party libraries. The APIs are ancient and the official docs are somewhat lacking, but it looks like CFStream can do what we need, and the CFStream.h header file contains all the info we need.

We can probably use CFStream to implement a SOCKSTunnel class similar to our SSHTunnel class.

Question: I assume you'd use either a SOCKS proxy, or a SSH tunnel. Or would it make sense to support using both at the same time?

[tagspace]

Hi @jakob. Just to add to this - SSH tunnel and SOCKS proxy are not mutually exclusive, so often makes sense to use both at the same time. E.g. if the database sits behind a firewall that directly exposes the database port to our fixed IP range, then we'd just need the SOCKS proxy. But if the firewall only exposes the SSH port to our fixed IP range, then we'd use SSH with SOCKS.

For a SOCKS proxy we'd usually provide username/password/proxyHost.

P.S. this feature would be very very useful.

[tobywan]

for example this is the same thing in DBeaver.

It allows more complex connection usecases.

(In this case we are creating an ssh connection using amazon session manager and using that to proxy connections through to the db)

# SSH over Session Manager
Host i-* mi-*
    User ec2-user
    ProxyCommand sh -c "aws ssm start-session --target %h --document-name AWS-StartSSHSession --parameters 'portNumber=%p'"
    DynamicForward 15432

The ssh tunnelling built into postico and DBeaver does not seem able to interpret the proxy command (mac os)

We're doing this so that we can connect into a private VPC with no public IPs exposed on either the DB or the jumpbox

[davenquinn]

Hi, I'd like to add my support for this feature request. I frequently use SOCKS proxies to connect to a SSH session within my employer's VPN. An ideal scenario (although this might be infeasible) would be to work directly with the user's configuration for the ssh command-line tool (~/.ssh/config etc.), so aliases and proxy commands could be referenced. This might simplify the "open-ended config management" aspect of this issue.

As an example, here is an entry in my ~/.ssh/macrostrat.config file (referenced from the root ~/.ssh/config). This redirects any ssh connections through a custom "OpenConnect Proxy" shell script.

Host gunnison
HostName <redacted>
User daven
IdentityFile ~/.ssh/macrostrat.key
ProxyCommand uwmadison-vpn connect %h %p
# Tunnel for Macrostrat dev PostgreSQL
LocalForward 54391 dev.macrostrat.org:5432

For now I can work around this issue with the LocalForward option, but this requires parallel configuration of each database instance in Postico and the SSH config, as well as starting an independent SSH session to mediate the Postico connection.

[jakob]

@davenquinn Does the uwmadison-vpn command in your example open a connection via a SOCKS proxy? Is that a shell script? If yes, could you share the source?

[davenquinn]

Hi @jakob, yes — that is a command that does some internal nonsense and spits out an "OpenConnect" SOCKS5 proxy. My goal in showing it is that there is quite a lot of complexity in how SSH manages and works with the configs. I don't know if all of that user configuration can be easily taken advantage of by applications such as Postico.

• Here's the shell script in question: https://github.com/davenquinn/shell-config/blob/main/bin/uwmadison-vpn
• It is a "thin wrapper" around another shell script that manages the proxy creation: https://github.com/davenquinn/shell-config/blob/main/bin/openconnect-vpn

As you can see, SSH mostly frees you to hand-roll whatever questionable connection-mediating proxy you want.

[davenquinn]

If this configuration can't be directly accessed, another more restricted option is to just specify the SOCKS proxy host/port, and run the uwmadison-vpn start command separately. This would allow Postico to not integrate as deeply with SSH while still supporting this.

Here's an example of a Kubernetes config that uses the proxy:

apiVersion: v1
clusters:
- cluster:
    certificate-authority: ...
    server: ...
    proxy-url: socks5://localhost:9053
  name: ...