Secure Postgres access

[Reid Priedhorsky]

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

 

[Paul Rubin]

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?

 

[Marshall]

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.

 

[Larry Bates]

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

 

[Reid Priedhorsky]

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

 

[Paul Rubin]

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.

 

[Paul Rubin]

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.

 

[Reid Priedhorsky]

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

 

[Paul Rubin]

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.

 

[sjdevnull]

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!