|
Hi all, I need to remotely access my home Linux hosted Postgresql database remotely - I'm thinking maybe a VPN is the way to go but thought I'd ask you lot first
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
I do similar and yes I use VPN for it. From my point of view an easy way and especially a very secure way.
It does not solve my Problem, but it answers my question
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Which VPN do you use ?
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
|
Were you seriously considering doing it without a VPN?
If so, your ideas may require some revision.
It ain't rocket surgery; you'll get the drift within a couple of hours.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
No I wasn’t that’s why I’m asking here 😩
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
pkfox wrote: No I wasn’t that’s why I’m asking here 😩
Cool. I can't fault you for asking (I end up telling people how to use the stuff we make, so I'm something of an expert on asking stupid questions -- which are a Good Thing).
Yes, you should use VPNs. If you hear otherwise, people are giving you bad, and very dangerous, advice.
Randor has suggested a solution I would seriously consider to use in what seems to be your problem space, so I would follow his suggestion, and also search on similar solutions (using keywords given by Randor and the site he suggests), to make a decision after looking into it with an eye on what fits your situation best.
If you're not sure, though, don't make a decision on what might seem right. It's rare to find two places with identical problems that fit the same pre-made solution, so look at everything available, before making a decision.
Good luck, and kudos for asking for advice before jumping in.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Mark_Wallace wrote: Yes, you should use VPNs. If you hear otherwise, people are giving you bad, and very dangerous, advice.
Well, he would be able to access his database. Including many others...
|
|
|
|
|
Thanks very much
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
Hi,
Wireguard[^] is quickly becoming the industry standard. The whitepaper is here[^]. You should invest a few minutes researching it.
Best Wishes,
-David Delaune
|
|
|
|
|
this is great info. thanks.
diligent hands rule....
|
|
|
|
|
Thanks very much I'll check it out
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
I use a reverse SSH connection.
This will open a port on your local machine and tunnel it to your postgresql box. It's especially usefull when developing because you can always connect to localhost:5432 on your local machine instead of having to deal with connection strings and everything else. You don't even need to supply the login credentials to your server.
It's way simpler then setting up a VPN and you still get all the security benefits (encryption, etc).
There is also another (way cooler IMO) benefit. Bind the postgresql server to localhost:5432 and no one can connect to it, even from inside your lan but this might not e usefull to your case.
IMO this makes it even more secure then just using a VPN.
|
|
|
|
|
Hi there, sounds interesting how would that work connecting from a remote windows box to a remote ( my home server ) Linux server ?
Essentially what I'm trying to achieve is, I'm in the process of writing a Winforms app that connects to a Postgresql database at my home, but I would like to be able to use/develop the app on my Windows Laptop/Tablet when I'm not at home - thanks again for your time
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
Hi!
In that case you have 2 options. The one I use is to configure your router to portforward an external port (say port 12345) to port 22 to you linux server. Then just use PuTTY on windows (or JuiceSSH on Android. I use both) to connect to <router_public_ip>:12345 as a reverse proxy.
PuTTY, in turn, will open local port 5432 on your windows laptop/home computer.
If you don't want or can't use portforward (e.g. the external IP of your router changes a lot), you have a second option. You can use something like SSHReach.me[^] . It works the same way but it's easier to setup. Then you just connect to their servers instead of your router.
After this is setup, make your WinForms app connect to localhost:5432 and it will work. The beauty of this is that PostgreSQL thinks you are connecting from localhost so you don't need to send your password.
I'm not sure if it's easier then setting up a VPN but for programming it provides (IMO) a lot more flexibility and (maybe) security then a VPN.
I'll see if I can send you my PuTTY configuration when I get home.
If all this is a little confusing (it was for me) take a look at this SO answer: networking - How does reverse SSH tunneling work? - Unix & Linux Stack Exchange[^] .
|
|
|
|
|
Wow that sounds good but how does the app know that Putty has done some magic ?
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
It doesn't. That's the great thing about it. From the app point of view, it's always connecting to a database on your local machine. You don't need to setup environment variables, hardcode credentials in code, etc, etc
Here is my PuTTY setup. Create and save a PuTTY session (just to make things easier) and then add the ports you want (replace 127.0.0.1 with the IP address of your server). When you connect to the session, PuTTY will setup everything:
https://i.imgur.com/qSX8pW2.png[^]
If you want to make everything even easier, generate a ssh key pair. In that case you don't even need to provide a username/password.Just open the session and that's it.
BTW, we are talking about databases but this works for any kind o server. As long as it's something listening on a port, you can do this.
EDIT: Just realized something. Setp 5 is wrong. After step 4, save the session otherwise you'll need to do this everytime you connect. Then do step 5
|
|
|
|
|
Sorry for all the questions but do I need to open the putty connection before I run my app ? Thanks again for your time.
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
Ahah don't worry. I'm more than happy to help wherever I can 
Answering your question, yes you do need to open the putty connection before running your app otherwise it will not be able to connect to the database. You only need to do this once (when you turn on your PC for example) and then leave it open.
|
|
|
|
|
Hi there, I'm unsure about what I need to port forward in my router and Putty, I see in your screenshot you have 127.0.0.1:5432 ; does that mean I need to port forward 5432 in my router to my database server ? and also put my public ip address in the host text box in Putty ? sorry for all the questions but I've not worked with tunneling before 
Edit
I think I've done it though maybe not the way you suggested - this is what I've done
Forwarded port 22 in my router to my local Linux box
Forwarded a port xxx in my Putty connection to my local Linux box
Changed the port number in my connection string to xxx
It worked !!! I'm amazed - thanks for all your help
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
modified 1-Feb-20 9:41am.
|
|
|
|
|
Hi, sorry for taking so long to reply (looong weekend..).
If I understand correctly then yes, that setup should work both locally on your network as well as remotely 
I should probably make a guide on how to set this up because with all the forwarding going on it gets really confusing really fast.
Anyway let me know if you have any more trouble And if you want to make everything a little bit more secure, checkout how to connect using a key pair instead of a username/password
modified 3-Feb-20 4:43am.
|
|
|
|
|
Hi and thanks again, I already use keys - I’ve been using SSH for years it just never occurred to me to use port forwarding in Putty - I run SSH and PostgreSQL on non standard ports which is an added complication- what I used to do was forward the Postgres port in my router which is not ideal - with your method I have the added benefit of SSH security - and as you say it works remotely
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
Ah! Sorry for some reason I thought you didn't know about ssh keys 
Anyway I'm glad it works It's amazing what PuTTY and SSH can do for such "simple" programs.
On a side note, be carefull about not running ssh on a standard port. You are giving up some linux protections when doing that.
|
|
|
|
|
Oh really what exactly ?
"We can't stop here - this is bat country" - Hunter S Thompson - RIP
|
|
|
|
|
This is only true if you use a port above 1024.
Ports bellow 1024 are given some extra "protections" by Linux, one of them being that your program must be executed as root to be able to bind to them. This means that if you use a port >1024 and get infected by something, that something can bind to that port, act like a ssh server and capture everything you are doing.
As usual there are tons of discussions and opinions about this if you want to take a look:
- Why not move SSH to another port? - BSD How To[^]
- Why putting SSH on another port than 22 is bad idea[^]
- Why putting SSH on another port than 22 is bad idea | Hacker News[^]
This is probably overkill for what you and I do but I like to keep it safe (and I had to worry about this kind of stuff on a previous job although I'm not a security expert, not even close, so keep that in mind).
One way to keep both benefits is to keep ssh on port 22 on your linux box but port-forward a random >1024 port on your router to port 22.
Is this worth it? I'm not sure but at least it's one less thing to worry about.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
