Secure Postgres access

Discussion in 'Python' started by Reid Priedhorsky, Sep 6, 2006.

  1. 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
    Reid Priedhorsky, Sep 6, 2006
    #1
    1. Advertising

  2. Reid Priedhorsky

    Paul Rubin Guest

    Reid Priedhorsky <> writes:
    > 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?
    Paul Rubin, Sep 6, 2006
    #2
    1. Advertising

  3. Reid Priedhorsky

    Marshall Guest

    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.
    Marshall, Sep 6, 2006
    #3
  4. Reid Priedhorsky

    Larry Bates Guest

    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
    Larry Bates, Sep 6, 2006
    #4
  5. On Wed, 06 Sep 2006 09:29:59 -0700, Paul Rubin wrote:

    > Reid Priedhorsky <> writes:
    >> 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
    Reid Priedhorsky, Sep 8, 2006
    #5
  6. Reid Priedhorsky

    Paul Rubin Guest

    Reid Priedhorsky <> writes:
    > > 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.
    Paul Rubin, Sep 8, 2006
    #6
  7. Reid Priedhorsky

    Paul Rubin Guest

    Paul Rubin <http://> writes:
    > 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.
    Paul Rubin, Sep 8, 2006
    #7
  8. On Thu, 07 Sep 2006 18:36:32 -0700, Paul Rubin wrote:

    > Reid Priedhorsky <> writes:
    >> > 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
    Reid Priedhorsky, Sep 9, 2006
    #8
  9. Reid Priedhorsky

    Paul Rubin Guest

    Reid Priedhorsky <> writes:
    > 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.
    Paul Rubin, Sep 10, 2006
    #9
  10. Reid Priedhorsky

    Guest

    Paul Rubin wrote:
    > Reid Priedhorsky <> writes:
    > > 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, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. A.M
    Replies:
    5
    Views:
    5,437
    Teemu Keiski
    Jun 8, 2004
  2. Daniel Malcolm
    Replies:
    0
    Views:
    554
    Daniel Malcolm
    Jan 24, 2005
  3. zdrakec
    Replies:
    1
    Views:
    434
    zdrakec
    Jul 25, 2005
  4. Joe
    Replies:
    5
    Views:
    958
    Steven Cheng[MSFT]
    Dec 13, 2005
  5. Nick Black
    Replies:
    1
    Views:
    155
    Kashia Buch
    Nov 16, 2006
Loading...

Share This Page