Connecting DBeaver to remote PostgreSQL DB via Unix socket

1
votes

I recently installed https://dbeaver.io/ on a Windows PC and wish to access a database on a remote Linux server from it.

My Linux username is my_username and I also have a system user psql_user. I also have two existing PostgreSQL databases with the same name as their respective user. Typically, only the psql_user is used and is access by a php-fpm pool listening to a Unix socket and running as user psql_user, and as such have configured /var/lib/pgsql/12/data/pg_hba.conf as:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

With the above configuration, after ssh'ing onto the server, I can access the my_username database by executing psql and can also access the psql_user database by executing sudo -u psql_user psql and do not need to use a password for either.

But now, how to connect from the remote Windows PC?

To attempt to do so, I first created ssh keys without passphrases on the Windows PC for both my_username and psql_user and added the public key to each Linux user's authorized_keys (had to manually create /home/psql_user/ because it is a systems user). I can can successfully PuTTY to the server as either using the ssh keys.

Next, on the DBeaver connection settings SSH tab, I checked "Use SSH Tunnel", entered the username and private key location and the Test tunnel configuration successfully shows connected with the client version as SSH-2.0-JSCH-01.54 and server version as SSH-2.0-OpenSSH_7.4. I also made no changes to the Advanced portion of this tab such as local and remote hosts and ports, and have also left the "You can use variables in SSH parameters" at their default values.

Using my server IP in the main tab, Authentication "Database Native", and leave password empty, I test the connection but get The connection attempt failed. syslog reports that connection to the IP on port 5432 failed which makes sense because I am set up using Unix sockets.

So, then I change the server IP on the main tab to 127.0.0.1 (or localhost) and try again but get FATAL: Ident authentication failed for user "my_username". Okay, a little closer, but not quite there.

I think it might be because DBeaver is passing the port so I attempt to disable this part by got to the Edit Driver tab and changing jdbc:postgresql://{host}[:{port}]/[{database}] to jdbc:postgresql://{host}/[{database}], but now get Connection to 127.0.0.1:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Not sure where to go next. When I PuTTY into the Linux machine, all is good but not when connecting remotely using DBeaver, and thought it would be the same if I am using SSH to connect DBeaver to the server. How can this be accomplished?

2
postgresqlsshunix-socketdbeaver
So did you check that the postmaster is accepting TCP/IP connections by looking at what listen_addresses is set to in postgresql.conf? – Adrian Klaver
@AdrianKlaver No I didn't. I didn't realize that the postmaster was postgresql specific until now. listen_addresses is commented out showing the default as localhost. Don't see anything about accepting tcp/ip connections. Searched through the config files for postmaster, only found it briefly mentioned in \var/lib/pgsql/12/data/pg_ident.conf. Thanks – user1032531
From docspostmaster: "postmaster is a deprecated alias of postgres." It still shows up in error messages. So the server is listening on localhost and listening on loopback TCP/IP. See connections for more information on listen_addresses. The short version is you are listening on both TCP/IP(localhost) and sockets. The issue seems to be the ident auth method. See here ident for more info. – Adrian Klaver
FATAL: Ident authentication failed for user "my_username" The PostgreSQL server's log file should include more details about why this failed. – jjanes
Using ident with ssh tunnel seems pretty weird. Is there a reason you want to do this, or are you just trying to change as few things as possible? – jjanes

2 Answers

0
votes

While I think that ssh tunnelling can be set up to connect to a unix socket rather than a port, I don't think dbeaver offers a way to do that, so you would have to set it up separately.

Although ident should also work if your server runs the identd service. I think most linux don't do that by default, but just apt install oidentd or whatever the equiv would be on your package manager should fix that.

The easier solution would be to just change the method from ident to md5 or scram, and assign a password (which dbeaver offers to memorize).

0
votes

As pointed out in the other answer, DBeaver's SSH tunnel option doesn't support sockets currently. It is always TCP port based, so only connections using the host options in pg_hba.conf can be made (I've placed a feature request for SSH socket forwarding in DBeaver).

Here's how to set up forwarding of a local TCP port to a remote Unix socket. This allows you to use peer authentication over the Unix socket, so you don't have to provide a password for the PostgreSQL role:

ssh [email protected] -L 5555:/var/run/postgresql/.s.PGSQL.5432 -fN