pgadmin won't connect to Postgresql via ssh on macOS Announcing the arrival of Valued...

Asymptotics question

Differences to CCompactSize and CVarInt

How much damage would a cupful of neutron star matter do to the Earth?

If Windows 7 doesn't support WSL, then what is "Subsystem for UNIX-based Applications"?

Moving a wrapfig vertically to encroach partially on a subsection title

A proverb that is used to imply that you have unexpectedly faced a big problem

New Order #6: Easter Egg

Why do early math courses focus on the cross sections of a cone and not on other 3D objects?

Should a wizard buy fine inks every time he want to copy spells into his spellbook?

The Nth Gryphon Number

Flight departed from the gate 5 min before scheduled departure time. Refund options

Can you force honesty by using the Speak with Dead and Zone of Truth spells together?

AppleTVs create a chatty alternate WiFi network

How does light 'choose' between wave and particle behaviour?

What initially awakened the Balrog?

Getting out of while loop on console

NERDTreeMenu Remapping

Central Vacuuming: Is it worth it, and how does it compare to normal vacuuming?

What does Turing mean by this statement?

License to disallow distribution in closed source software, but allow exceptions made by owner?

Is there hard evidence that the grant peer review system performs significantly better than random?

Is openssl rand command cryptographically secure?

What are the main differences between Stargate SG-1 cuts?

GDP with Intermediate Production

pgadmin won't connect to Postgresql via ssh on macOS

Announcing the arrival of Valued Associate #679: Cesar Manara

Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern)

Come Celebrate our 10 Year Anniversary!can not connect via SSH to a remote Postgresql databaseUbuntu get PostGreSQL runningunable to connect to PostgreSQL 8.4 on Ubuntu 10.04 serverpostgresql not connecting after upgrading to ubuntu 11.04Cannot connect to PostgreSQL DB Via pgAdminVMWare Fusion 4.0 + PostgresPostgreSQL 9.1 not accepting connectionsAfter upgrading to PostgreSQL 9.4, unable to connect from localhostPHP cant connect to postgreSQL in Centos 7 Selinuxubuntu allow local ip address to connect to other local machine using ssh

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I am trying, with no success, to connect pgAdmin 4 to my Postgresql installation on Linode using ssh tunnel as specified in https://linode.com/docs/databases/postgresql/securely-manage-remote-postgresql-servers-with-pgadmin-on-macos-x/

It did not work, with pgAdmin giving me:

Unable to connect to server:



could not connect to server: Connection refused

Is the server running on host "localhost" (::1) and accepting

TCP/IP connections on port 5432?

could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting

TCP/IP connections on port 5432?

I spent hours searching for a solution and nothing seems to work. I am for sure missing something. Here is what else I tried:

I went through all the steps in https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

my netstat -nlt shows:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:587 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp6 0 0 :::80 :::* LISTEN

tcp6 0 0 :::22 :::* LISTEN

tcp6 0 0 :::5432 :::* LISTEN

tcp6 0 0 ::1:5433 :::* LISTEN

my ufw status shows:

Status: active

To Action From

22 ALLOW Anywhere

5432/tcp ALLOW Anywhere

22 (v6) ALLOW Anywhere (v6)

5432/tcp (v6) ALLOW Anywhere (v6)

I certainly restarted Postgresql more often than not (for every single modification try)

if I run psql -h localhost -p 5433 -U postgres it works fine

also with psql -h localhost -p 5432 -U postgres all works fine

But nothing else I do works for pgAdmin.

Does anyone can shed a light on it? What am I missing? What else could I try?

Thank you
Greetings from Beijing!
Daniel

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files.

– Patrick Mevzek
Feb 8 '18 at 16:32

Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings.

– Patrick Mevzek
Feb 8 '18 at 16:33

Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing.

– Daniel
Feb 9 '18 at 0:12

add a comment |

It did not work, with pgAdmin giving me:

Unable to connect to server:



could not connect to server: Connection refused

Is the server running on host "localhost" (::1) and accepting

TCP/IP connections on port 5432?

could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting

TCP/IP connections on port 5432?

I spent hours searching for a solution and nothing seems to work. I am for sure missing something. Here is what else I tried:

I went through all the steps in https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

my netstat -nlt shows:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:587 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp6 0 0 :::80 :::* LISTEN

tcp6 0 0 :::22 :::* LISTEN

tcp6 0 0 :::5432 :::* LISTEN

tcp6 0 0 ::1:5433 :::* LISTEN

my ufw status shows:

Status: active

To Action From

22 ALLOW Anywhere

5432/tcp ALLOW Anywhere

22 (v6) ALLOW Anywhere (v6)

5432/tcp (v6) ALLOW Anywhere (v6)

I certainly restarted Postgresql more often than not (for every single modification try)

if I run psql -h localhost -p 5433 -U postgres it works fine

also with psql -h localhost -p 5432 -U postgres all works fine

But nothing else I do works for pgAdmin.

Does anyone can shed a light on it? What am I missing? What else could I try?

Thank you
Greetings from Beijing!
Daniel

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files.

– Patrick Mevzek
Feb 8 '18 at 16:32

Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings.

– Patrick Mevzek
Feb 8 '18 at 16:33

Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing.

– Daniel
Feb 9 '18 at 0:12

add a comment |

It did not work, with pgAdmin giving me:

Unable to connect to server:



could not connect to server: Connection refused

Is the server running on host "localhost" (::1) and accepting

TCP/IP connections on port 5432?

could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting

TCP/IP connections on port 5432?

I spent hours searching for a solution and nothing seems to work. I am for sure missing something. Here is what else I tried:

I went through all the steps in https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

my netstat -nlt shows:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:587 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp6 0 0 :::80 :::* LISTEN

tcp6 0 0 :::22 :::* LISTEN

tcp6 0 0 :::5432 :::* LISTEN

tcp6 0 0 ::1:5433 :::* LISTEN

my ufw status shows:

Status: active

To Action From

22 ALLOW Anywhere

5432/tcp ALLOW Anywhere

22 (v6) ALLOW Anywhere (v6)

5432/tcp (v6) ALLOW Anywhere (v6)

I certainly restarted Postgresql more often than not (for every single modification try)

if I run psql -h localhost -p 5433 -U postgres it works fine

also with psql -h localhost -p 5432 -U postgres all works fine

But nothing else I do works for pgAdmin.

Does anyone can shed a light on it? What am I missing? What else could I try?

Thank you
Greetings from Beijing!
Daniel

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

It did not work, with pgAdmin giving me:

Unable to connect to server:



could not connect to server: Connection refused

Is the server running on host "localhost" (::1) and accepting

TCP/IP connections on port 5432?

could not connect to server: Connection refused

Is the server running on host "localhost" (127.0.0.1) and accepting

TCP/IP connections on port 5432?

I spent hours searching for a solution and nothing seems to work. I am for sure missing something. Here is what else I tried:

I went through all the steps in https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

my netstat -nlt shows:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:587 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp6 0 0 :::80 :::* LISTEN

tcp6 0 0 :::22 :::* LISTEN

tcp6 0 0 :::5432 :::* LISTEN

tcp6 0 0 ::1:5433 :::* LISTEN

my ufw status shows:

Status: active

To Action From

22 ALLOW Anywhere

5432/tcp ALLOW Anywhere

22 (v6) ALLOW Anywhere (v6)

5432/tcp (v6) ALLOW Anywhere (v6)

I certainly restarted Postgresql more often than not (for every single modification try)

if I run psql -h localhost -p 5433 -U postgres it works fine

also with psql -h localhost -p 5432 -U postgres all works fine

But nothing else I do works for pgAdmin.

Does anyone can shed a light on it? What am I missing? What else could I try?

Thank you
Greetings from Beijing!
Daniel

ssh postgresql ssh-tunnel terminal connection-refused

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

edited Feb 8 '18 at 15:12

Andrew Schulman

6,437102241

asked Feb 8 '18 at 14:36

Daniel

1112

asked Feb 8 '18 at 14:36

Daniel

1112

asked Feb 8 '18 at 14:36

Daniel

1112

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files.

– Patrick Mevzek
Feb 8 '18 at 16:32

Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings.

– Patrick Mevzek
Feb 8 '18 at 16:33

Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing.

– Daniel
Feb 9 '18 at 0:12

add a comment |

If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files.

– Patrick Mevzek
Feb 8 '18 at 16:32

Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings.

– Patrick Mevzek
Feb 8 '18 at 16:33

Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing.

– Daniel
Feb 9 '18 at 0:12

If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files.

– Patrick Mevzek
Feb 8 '18 at 16:32

Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings.

– Patrick Mevzek
Feb 8 '18 at 16:33

Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing.

– Daniel
Feb 9 '18 at 0:12

add a comment |

1 Answer
1

active

oldest

votes

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.

answered Feb 9 '18 at 0:11

Daniel

1112

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "2"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f896327%2fpgadmin-wont-connect-to-postgresql-via-ssh-on-macos%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.

answered Feb 9 '18 at 0:11

Daniel

1112

add a comment |

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.

answered Feb 9 '18 at 0:11

Daniel

1112

add a comment |

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.

answered Feb 9 '18 at 0:11

Daniel

1112

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.

answered Feb 9 '18 at 0:11

Daniel

1112

answered Feb 9 '18 at 0:11

Daniel

1112

answered Feb 9 '18 at 0:11

Daniel

1112

answered Feb 9 '18 at 0:11

Daniel

1112

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Server Fault!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ryfujk