473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Secure Postgres access

Hi folks,

I would like to access a remote Postgres server from a Python program in a
secure way. Postgres doesn't currently listen to the Internet for
connections, and I'd prefer to keep it that way.

I know how to forward ports using SSH, but I don't like doing this because
then anyone who knows the port number can connect to Postgres over the
same tunnel. (I'm not the only user on the client machine.)

What I envision is something like wrapping an SSH connection which then
opens psql once connected, but I'm not too picky.

Both Postgres and the Python program are running on Linux.

Any ideas?

Thanks very much for any help.

Reid
Sep 6 '06 #1
9 2104
Reid Priedhorsky <re**@reidster.netwrites:
I know how to forward ports using SSH, but I don't like doing this because
then anyone who knows the port number can connect to Postgres over the
same tunnel. (I'm not the only user on the client machine.)
Wouldn't they need a database password?
Sep 6 '06 #2
Can't you limit SSH tunneling access to the IP and/or MAC that you want
to access ? It's simplest than any other solution.

Sep 6 '06 #3
Reid Priedhorsky wrote:
Hi folks,

I would like to access a remote Postgres server from a Python program in a
secure way. Postgres doesn't currently listen to the Internet for
connections, and I'd prefer to keep it that way.

I know how to forward ports using SSH, but I don't like doing this because
then anyone who knows the port number can connect to Postgres over the
same tunnel. (I'm not the only user on the client machine.)

What I envision is something like wrapping an SSH connection which then
opens psql once connected, but I'm not too picky.

Both Postgres and the Python program are running on Linux.

Any ideas?

Thanks very much for any help.

Reid
Use port forwarding over SSH and use only pubkey authorization so that
you put their pubkey in authorized_keys on the server for SSH connection.
Put something like 'LocalForward 3308 databaseserver:3308' in your ssh
client config file (I don't know if you are using putty or cygwin) and
then point the Python program to localhost:3308 This then gets redirected
to proper port on the remote machine. Works great and the traffic is
encrypted and I know who the user is because they can't connect until
they give me their pubkey and I put on the server and they must have
their private key AND passphrase to establish the SSH connection.

I use this to run pgAdmin III remotely through a firewall to my
database server.

-Larry Bates
Sep 6 '06 #4
On Wed, 06 Sep 2006 09:29:59 -0700, Paul Rubin wrote:
Reid Priedhorsky <re**@reidster.netwrites:
>I know how to forward ports using SSH, but I don't like doing this because
then anyone who knows the port number can connect to Postgres over the
same tunnel. (I'm not the only user on the client machine.)

Wouldn't they need a database password?
Well, right now, no. I have Postgres configured to trust the OS on who is
who. I would prefer not to change that because I don't want another place
containing authentication information. I'd like to connect by entering
only my SSH password, not my SSH password and a database password too.

This is why straight SSH tunneling, as suggested by Marshall and Larry,
isn't satisfactory: once I've set up the tunnel, anyone on the local
machine can connect to the tunnel and then they have passwordless access
into the database.

I control the database machine, and the only user is me. I don't control
the local machine, and it has many users I don't trust.

Thanks,

Reid

Sep 8 '06 #5
Reid Priedhorsky <re**@umn.eduwrites:
Wouldn't they need a database password?

Well, right now, no. I have Postgres configured to trust the OS on who is
who.
You trust the OS on the client machine, but not the client machine's
users? Does it run identd? Maybe you could use that. I'd consider
this shaky for any real security application, but it might be better
than nothing depending on what you're doing.
I would prefer not to change that because I don't want another place
containing authentication information. I'd like to connect by entering
only my SSH password, not my SSH password and a database password too.
How about if you hack your local SSH client so its port forwarding
only accepts connections originated by your account, again using
identd to check. Your application could also open a second connection
to the hacked client, using an AF_UNIX socket, which in linux supports
a sendmsg command that sends the other side's user id (see the
SCM_CREDENTIALS message in unix(7)). You'd use SCM_CREDENTIALS to
authenticate the user ID, then send the Postgres client's originating
TCP port number over the Unix socket, and that would tell the SSH
client that it could then start forwarding the TCP packets. Yucch,
this is messy. Maybe something like it exists already somewhere.
I control the database machine, and the only user is me. I don't control
the local machine, and it has many users I don't trust.
Sooner or later they will take over your account and capture your ssh
and login passwords, and then there will be no way at all for any
program to distinguish between them and you. Your best bet is to run
on a client machine that you trust.
Sep 8 '06 #6
Paul Rubin <http://ph****@NOSPAM.invalidwrites:
You'd use SCM_CREDENTIALS to
authenticate the user ID, then send the Postgres client's originating
TCP port number over the Unix socket, and that would tell the SSH
client that it could then start forwarding the TCP packets. Yucch,
this is messy. Maybe something like it exists already somewhere.
Actually maybe this can still be spoofed, e.g. perhaps someone can
jump into someone else's existing TCP connection on the local machine
through the TAP interface. It might be ok, but you or some TCP wizard
better first think about it carefully. I'm not expert enough about
socket programming to know. You'd think there's a solution.
Sep 8 '06 #7
On Thu, 07 Sep 2006 18:36:32 -0700, Paul Rubin wrote:
Reid Priedhorsky <re**@umn.eduwrites:
Wouldn't they need a database password?

Well, right now, no. I have Postgres configured to trust the OS on who is
who.

You trust the OS on the client machine, but not the client machine's
users? Does it run identd? Maybe you could use that. I'd consider
this shaky for any real security application, but it might be better
than nothing depending on what you're doing.
Hi Paul,

Thanks for your help.

No -- I suppose I wasn't clear. There are two machines involved:

A) Database server. Run by me. I trust the OS on who is who, and there is
only one user (me). So database clients run on this box don't require
a password.

B) Work machine. Run by others, many users. I'd like to also run my
database client (Python) here. SSH tunnel is unsatisfactory because other
folks can slip down the tunnel after I set it up and then connect to the
DB as me. Having the DB on (A) listen to the Internet as well as localhost
for connections is also unsatisfactory, because I don't want to set up
database passwords.

What I'd like is functionality similar to what Subversion does with
"svn+ssh://" URLs: an SSH tunnel that accepts only one connection and
doesn't have race conditions.

Thanks again,

Reid
Sep 9 '06 #8
Reid Priedhorsky <re**@reidster.netwrites:
B) Work machine. Run by others, many users. I'd like to also run my
database client (Python) here.
Well, just how much do you distrust that machine? If you think it's
totally pwned by attackers who will stop at nothing to subvert your
client, you shouldn't run the client there. How do you propose to
open an SSH connection from a completely untrusted box, for example?
You can't type an SSH password into it since you have to assume that
the keystrokes are being logged.

If you only partially distrust the machine, then figure out what
operations on it you do trust, and work from there.
What I'd like is functionality similar to what Subversion does with
"svn+ssh://" URLs: an SSH tunnel that accepts only one connection and
doesn't have race conditions.
That doesn't sound like the right answer. It means you have to
carefully arrange your application to open just one db connection and
use it throughout its run. Many applications are somewhat cavalier
about opening and closing db conns, and and it's sometimes convenient
to write in that style. Some apps (e.g. multi-threaded ones)
inherently require multiple db conns. And even if you have an SSH
mode that accepts just one connection, since your db app is separate
and has to connect to the forwarding port after you use a separate
program open the port, how do you stop someone else from grabbing it
first?

I think what you really want is normal, multi-connection SSH port
forwarding to the db server, but that works only for you and doesn't
work for others. That seems to mean one of:

1) authentication (like a db password) in the db client, maybe using
another process that the db client gets a credential from
2) authentication through SCM_CREDENTIALS on a PF_UNIX socket
3) authentication via identd on the client machine (i.e. you trust
the admins on that machine to keep malicious stuff off of the
privileged ports)
4) some other scheme yet to be identified

Actually, looking at the doc for ssh-agent(1), it looks like it might
do something like #2 above. If I understand it, you would run your db
client as something like

ssh-agent your-client &

and the ssh agent would start your client, exporting an env variable
that your client can use to start ssh without a password and connect
to the db server. The env variable points to a PF_UNIX socket where
the doc says "the socket is made accessible only to the current user".
Although the docs aren't totally clear, this sounds sort of like what
we're discussing, so I'd say it's worth looking into.

Finally, lately for unrelated reasons I've been looking at Vtun
(vtun.sf.net), a simple VPN program that might be easier to modify
than OpenSSH. Its security features look worse than ssh's, but maybe
they're enough for your purpose.
Sep 9 '06 #9
Paul Rubin wrote:
Reid Priedhorsky <re**@reidster.netwrites:
B) Work machine. Run by others, many users. I'd like to also run my
database client (Python) here.

Well, just how much do you distrust that machine? If you think it's
totally pwned by attackers who will stop at nothing to subvert your
client, you shouldn't run the client there.
I got the impression that he didn't trust other normal users on the box
but that root wasn't hostile.
What I'd like is functionality similar to what Subversion does with
"svn+ssh://" URLs: an SSH tunnel that accepts only one connection and
doesn't have race conditions.
[SNIP]
And even if you have an SSH mode that accepts just one connection,
since your db app is separate and has to connect to the
forwarding port after you use a separate program open the port,
how do you stop someone else from grabbing it first?
(I think that's what he meant by "doesn't have race conditions".)
That seems to mean one of:

2) authentication through SCM_CREDENTIALS on a PF_UNIX socket
That looks like the best option of those you list.
Actually, looking at the doc for ssh-agent(1), it looks like it might
do something like #2 above. If I understand it, you would run your db
client as something like

ssh-agent your-client &
That's cool, I'm looking for something similar, thanks!

Sep 10 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: wlcna | last post by:
mysql v4.0.16: I had been using mysql with innodb and thought that was fine, until i used it for something requiring a few - perhaps slightly involved - joins, and have now seen the performance...
7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
4
by: Bernardo Robelo | last post by:
Hi, I am interested in migrating Microsoft Access database to Postgres database. But I do not have idea of like initiating. Maybe some tool exists for this problem. Thanks you. Bernardo
0
by: Jesse | last post by:
Hi all, I need some help with Access database and Postgres Database i am trying to link some Acces tables to Postgres tabels but id doesn't seem to work. I have a Postgres running on a server...
1
by: Hank | last post by:
Hello, We are in the process of migrating our Access back end to Postgres. Our current version is Access 2000. Among other issues, the reason for the change is to pick up some speed by way of...
1
by: Matthew Hixson | last post by:
I am currently working on a Java web application in which we are making use of the JDBC driver for Postgres 7.4.1. Part of our application allows the administrators to manage a large number of...
6
by: Prabu Subroto | last post by:
Dear my friends... Usually I use MySQL. Now I have to migrate my database from MySQL to Postgres. I have created a database successfully with "creatdb" and a user account successfully. But...
7
by: Randy Yates | last post by:
This has probably been asked before so please be gracious. I have looked on the postgres site and didn't find anything "satisfying." Is there *good* overview of postgres and associated...
10
by: Hank | last post by:
We have just recently migrated the data from our Access 2000 backend to Postgres. All forms and reports seem to run correctly but, in many cases, very slowly. We do not want to switch over until...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.