WebFaction
Community site: login faq

I have set up a private PostgreSQL installation, using instructions at http://docs.webfaction.com/software/private-databases.html?highlight=private%20database#private-database-instances. I have opted for private rather than shared databases because (1) I need superuser access to these DBs, and (2) I want to manage them using pgAdminIII, and I can't seem to connect with shared DBs from that tool.

So far, the only way I have found to connect from pgAdminIII is to choose the "Open a port in the server firewall" option when setting up my PostgreSQL app in the WF Control Panel. This works, but I am daunted by this warning on the Control Panel: "This will give you, and anyone else, direct access to your application. We strongly recommend you leave the port closed unless you are 100% sure and understand the risks." I'm not 100% sure and I probably don't understand the risks, so I would like to know:

  1. Is there a way I can access my private DBs from pgAdminIII without opening this port?
  2. If not, what are the risks and what can I do to minimize them?

~ Tx, Ken

PS ~ This may be the same question as https://community.webfaction.com/questions/10544/remote-access-postgres-database. But I'm not sure, so I'm asking it separately.

This question is marked "community wiki".

asked 23 Nov '14, 20:03

kenatsun
19210
accept rate: 20%

edited 23 Nov '14, 20:05


Success!

After endless floundering, even more extensive than reported here, I now have a closed-port private PG instance that meets all the requirements that motivated this thread.

What finally worked was that I set up a brand-new private instance, set it as closed-port right from the start, and then followed the instructions in the WF documentation and the advice of johns and seanf in this thread as literally as possible.

Here, I think, is why this try worked where the others failed: In each of the earlier attempts, at some point(s) in the trial-and-error process trying to make it work, I opened and/or closed the port of the private instance that I was working with. My hunch now is that when you create a private instance, the "open port" setting you choose at create-time has some irreversible effect on something in the instance setup, such that if you change the "open port" setting later, your connection attempts to that instance are permanently screwed up. So the only way out is to start over with a new instance, and don't mess with the "open port" setting!

If this hunch is correct, perhaps WF should tweak its Control Panel to make the "open port" setting read-only after the instance has been created.

permanent link
This answer is marked "community wiki".

answered 19 Dec '14, 22:52

kenatsun
19210
accept rate: 20%

You should not have to open the port to access phpPgAdmin. What you will need to do since you are trying to connect to a private postgres instance is to install your own instance of phpPgAdmin.

While the software is different it should be similiar to the steps to install phpMyAdmin

If you do want to use something such as a desktop connector to connect to your private instance of Postgres then you would need to check the Open Port box.

Just be aware that when you check the open port box that a hole is opened on the firewall and all traffic (tcp/udp) is sent to that port. You want to make sure that your postgres instance is secure.

permanent link

answered 23 Nov '14, 21:09

aaront ♦♦
7664
accept rate: 27%

The app I need to use is not phpPgAdmin (a web-based app); it's pgAdminIII (a client-based app).

permanent link

answered 24 Nov '14, 02:01

kenatsun
19210
accept rate: 20%

Than you will want to open the port and connect directly.

(24 Nov '14, 03:46) johns

So let me put my two questions again:

  1. Is there a way I can access my private DBs from pgAdminIII without opening this port? If I understand right, johns seems to say No. Can anybody else suggest a way to do this?
  2. If not, what are the risks of opening the port, and what can I do to minimize them?

~Thanks again, Ken

permanent link

answered 24 Nov '14, 04:14

kenatsun
19210
accept rate: 20%

The alternative is to use SSH to create a tunnel.

The risk of having it open to the public is the same as any other server, you would have to keep an eye on known security risks and keep it updated.

(24 Nov '14, 05:49) johns

Thanks, johns.

But alas, I can't find a way to create a tunnel to a private database. I've posted a doc at https://drive.google.com/file/d/0B-thboqjuKZTY0E1aURoTzVlbEE/view?usp=sharing that tells the story. The numbered items in the doc show my attempt to connect using the instructions at http://docs.webfaction.com/user-guide/databases.html#starting-an-ssh-tunnel-with-putty.

1 & 2 show the PuTTY setup of the tunnel. (Since this is a public forum, I've fictionalized all the credentials, but the story is true.)

3 shows the establishment of the successful connection to the WebFaction server.

4 shows what happens when I try to connect to the db with psql.

When I try to connect thru my DB app (pgAdminIII) using the same credentials, I get the same error.

So now my question is: Have I just set it up wrong (and if so, what's the right setup)? Or is it that tunnels to private DBs just can't work?

~ Tx again, Ken

(25 Nov '14, 18:35) kenatsun

The port should not be 12345 but the actual port assigned to your private server by the control panel.

(26 Nov '14, 00:44) johns

Right; the specifics (including port number) in my comment were fictional, for security purposes. The actual port I used in my connect attempts was the one assigned by the Control Panel when I created the private PG cluster.

(26 Nov '14, 01:52) kenatsun

You don't need to open a port to connect to your private DB instance with pgAdmin3.

First, get the latest version of pgAdmin3 - at this time, it is v1.20.0-rc1

Next, configure your connection properties like this...

connection properties

... and the tunnel parameters like this ...

tunnel parameters

Save the connection, and you should then be able to connect.

permanent link

answered 26 Nov '14, 18:38

seanf
12.2k42136
accept rate: 37%

edited 26 Nov '14, 18:42

I have closed the port to my private instance.

Then I tried to set up a pgAdminIII "server" like you suggest. See numbered screen shots at https://drive.google.com/file/d/0B-thboqjuKZTaHBWU1FlZzRkTzA/view?usp=sharing:

  1. Connection properties setup.
  2. Tunnel setup. The pw is my WF account pw, i.e. the one I use to sign into Control Panel.
  3. When I try to connect, I first get asked for this. There are two or three passwords involved here, and I'm not sure which one it's asking for. Is it (a) the WF account pw? (b) the pw of the private PG "application" that I set up on the Control Panel? or (c) the pw of "user sunward" on the PG database I'm trying to access? My best guess is (b), but I have tried all three, and they all produced the same result.
  4. Then I am asked for this. This seems to be wanting (again) my WF account pw, the same one I entered in step 2.
  5. Whatever I try, this is the error I get.
permanent link

answered 26 Nov '14, 19:58

kenatsun
19210
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×109
×12
×1

question asked: 23 Nov '14, 20:03

question was seen: 7,267 times

last updated: 19 Dec '14, 22:52

                              
WEBFACTION
REACH US
SUPPORT
LEGAL
© COPYRIGHT 2003-2020 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM